Queries to reduce a set of data pairs
in [Getting Started]
|
From: Rui Maciel on 19 May 2010 16:14 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
From: John Spencer on 19 May 2010 16:54 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
From: Rui Maciel on 19 May 2010 17:39 On May 19, 9:54 pm, John Spencer <spen...(a)chpdm.edu> 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
From: John Spencer on 20 May 2010 08:11 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 <spen...(a)chpdm.edu> 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
|
Pages: 1 Prev: I am using Window 7 and Access 2007 Next: Inventory transaction table - set up |