Queries to reduce a set of data pairs

  • Thread starter Thread starter Rui Maciel
  • Start date Start date
R

Rui Maciel

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
 
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
 
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
Click to expand...

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
 
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
 
Back
Top