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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|