A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Queries to reduce a set of data pairs



 
 
Thread Tools Display Modes
  #1  
Old May 19th, 2010, 09:14 PM posted to microsoft.public.access.gettingstarted
Rui Maciel
external usenet poster
 
Posts: 4
Default Queries to reduce a set of data pairs

I have a table of values which has the following fields:

AreaElement, Joint, GlobalX, GlobalY, M_sd

This list consists basically in a long list of M_sd values. Each
AreaElement has 4 Joints, each one with GlobalX and GlobalY as it's
coordinates along with a M_sd as it's value. Yet, although different
AreaElements may share common Joints, all M_sd values are independent
of each element. So, for a small example, consider the following
sample:

sample
AreaElem Joint GlobalX GlobalY M_sd
1000 ~1044 6.32 11.91 12.17
1000 ~919 6.32 12.15 10.50
1000 ~1052 6.56 11.91 13.50
1000 ~927 6.56 12.15 12.00
1001 ~1045 6.56 10.20 20.46
1001 ~1046 6.56 10.44 20.32
1001 1733 6.80 10.20 19.38
1001 ~1053 6.80 10.44 19.31
1002 ~1046 6.56 10.44 20.13
1002 ~1047 6.56 10.69 19.39
1002 ~1053 6.80 10.44 19.60
1002 ~1054 6.80 10.69 18.86
/sample

What I want to do is for each AreaElement's Joints which share a
common GlobalX calculate the average of it's M_sd values and it's
GlobalY values and return a list consisting of the following fields:

AreaElem Joint GlobalX Avg(GlobalY) Avg(M_sd)

Is this possible? If so, how is it done?


Thanks in advance,
Rui Maciel
  #2  
Old May 19th, 2010, 09:54 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Queries to reduce a set of data pairs


SELECT AreaElem, Joint, GlobalX
,Avg(GlobalY) as AvgY
,Avg(M_sd) as AvgM
FROM YourTable
GROUP BY AreaElem, Joint, GlobalX

In query design view
== Add your table
== Add the five fields
== Select View: Totals from the menu
== Change GROUP BY to Avg under GlobalY and M_sd



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Rui Maciel wrote:
I have a table of values which has the following fields:

AreaElement, Joint, GlobalX, GlobalY, M_sd

This list consists basically in a long list of M_sd values. Each
AreaElement has 4 Joints, each one with GlobalX and GlobalY as it's
coordinates along with a M_sd as it's value. Yet, although different
AreaElements may share common Joints, all M_sd values are independent
of each element. So, for a small example, consider the following
sample:

sample
AreaElem Joint GlobalX GlobalY M_sd
1000 ~1044 6.32 11.91 12.17
1000 ~919 6.32 12.15 10.50
1000 ~1052 6.56 11.91 13.50
1000 ~927 6.56 12.15 12.00
1001 ~1045 6.56 10.20 20.46
1001 ~1046 6.56 10.44 20.32
1001 1733 6.80 10.20 19.38
1001 ~1053 6.80 10.44 19.31
1002 ~1046 6.56 10.44 20.13
1002 ~1047 6.56 10.69 19.39
1002 ~1053 6.80 10.44 19.60
1002 ~1054 6.80 10.69 18.86
/sample

What I want to do is for each AreaElement's Joints which share a
common GlobalX calculate the average of it's M_sd values and it's
GlobalY values and return a list consisting of the following fields:

AreaElem Joint GlobalX Avg(GlobalY) Avg(M_sd)

Is this possible? If so, how is it done?


Thanks in advance,
Rui Maciel

  #3  
Old May 19th, 2010, 10:39 PM posted to microsoft.public.access.gettingstarted
Rui Maciel
external usenet poster
 
Posts: 4
Default Queries to reduce a set of data pairs

On May 19, 9:54*pm, John Spencer wrote:
SELECT AreaElem, Joint, GlobalX
,Avg(GlobalY) as AvgY
,Avg(M_sd) as AvgM
FROM YourTable
GROUP BY AreaElem, Joint, GlobalX

In query design view
== Add your table
== Add the five fields
== Select View: Totals from the menu
== Change GROUP BY to Avg under GlobalY and M_sd


I've tried to follow your suggestion but I wasn't able to make my
query return a condensed version of the original table. Unfortunately
it still returns 4 joints per node. Could you please take a look at
my SQL to try to see what I'm doing wrong?

The SQL is as follows:
sql
SELECT [Objects And Elements - Joints Consulta].AreaElem, [Objects And
Elements - Joints Consulta].Joint, [Objects And Elements - Joints
Consulta].GlobalX, Avg([GlobalY]) AS AvglY, Avg([M_sd]) AS AvgM
FROM [Objects And Elements - Joints Consulta]
GROUP BY AreaElem, Joint, GlobalX
/sql


Thanks for the help,
Rui Maciel
  #4  
Old May 20th, 2010, 01:11 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Queries to reduce a set of data pairs

SELECT [O].AreaElem
, [O].Joint
, [O].GlobalX
, Avg([GlobalY]) AS AvglY
, Avg([M_sd]) AS AvgM
FROM [Objects And Elements - Joints Consulta] as O
GROUP BY AreaElem, Joint, GlobalX

The query looks good so obviously I don't understand what you are trying to
accomplish. For instance, what is a "node"?

The best I can see is to remove Joint and GlobalX from the query if you want
the averages for each areaElem.

SELECT [O].AreaElem
, Avg([GlobalY]) AS AvglY
, Avg([M_sd]) AS AvgM
FROM [Objects And Elements - Joints Consulta] as O
GROUP BY AreaElem

It might help if you took the same data and gave us what you want returned
from the sample data. Your original posting said you wanted the following
fields returned. So what do you expect to see based on your sample data?

AreaElem Joint GlobalX Avg(GlobalY) Avg(M_sd)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Rui Maciel wrote:
On May 19, 9:54 pm, John Spencer wrote:

snip

I've tried to follow your suggestion but I wasn't able to make my
query return a condensed version of the original table. Unfortunately
it still returns 4 joints per node. Could you please take a look at
my SQL to try to see what I'm doing wrong?

The SQL is as follows:
sql
SELECT [Objects And Elements - Joints Consulta].AreaElem, [Objects And
Elements - Joints Consulta].Joint, [Objects And Elements - Joints
Consulta].GlobalX, Avg([GlobalY]) AS AvglY, Avg([M_sd]) AS AvgM
FROM [Objects And Elements - Joints Consulta]
GROUP BY AreaElem, Joint, GlobalX
/sql


Thanks for the help,
Rui Maciel

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:05 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.