grouping ...
in [Queries]
|
Prev: Running a stored parameter query from a form without pop up boxes.
Next: IF statement in a query....
From: jer on 27 Mar 2010 16:56 columnA ColumnB ColumnC columnD 12345 23 78956 10.00 12345 23 89153 15.00 56780 23 15687 20.00 56780 23 15890 20.00 12345 23 78853 30.00 12345 23 87568 30.00 I am importing a table from an external source structured as above and if I sum columnD based on grouping on columnA I would get total for 12345 = 85.00 4 detail records 56780 = 40.00 2 detail records what I want to get however is 12345 = 25.00 2 detail records 56780 = 40.00 2 detail records 12345 = 60.00 2 detail records Apart from grouping is there any other query I can build that would give me the expected result -- thanks as always for the help jer
From: PieterLinden via AccessMonster.com on 27 Mar 2010 17:55 jer wrote: >columnA ColumnB ColumnC columnD > 12345 23 78956 10.00 > 12345 23 89153 15.00 > 56780 23 15687 20.00 > 56780 23 15890 20.00 > 12345 23 78853 30.00 > 12345 23 87568 30.00 > >I am importing a table from an external source structured as above >and if I sum columnD based on grouping on columnA I would get total >for > 12345 = 85.00 4 detail records > 56780 = 40.00 2 detail records > >what I want to get however is > 12345 = 25.00 2 detail records > 56780 = 40.00 2 detail records > 12345 = 60.00 2 detail records > >Apart from grouping is there any other query I can build that >would give me the expected result I am not clear on how the grouping is supposed to work. What's the logic behind it? Do you want just the first two records per ColumnA? -- Message posted via http://www.accessmonster.com
From: John W. Vinson on 27 Mar 2010 17:59 On Sat, 27 Mar 2010 13:56:01 -0700, jer <jearl45(a)hotmail.com> wrote: > columnA ColumnB ColumnC columnD > 12345 23 78956 10.00 > 12345 23 89153 15.00 > 56780 23 15687 20.00 > 56780 23 15890 20.00 > 12345 23 78853 30.00 > 12345 23 87568 30.00 > >I am importing a table from an external source structured as above >and if I sum columnD based on grouping on columnA I would get total >for > 12345 = 85.00 4 detail records > 56780 = 40.00 2 detail records > >what I want to get however is > 12345 = 25.00 2 detail records > 56780 = 40.00 2 detail records > 12345 = 60.00 2 detail records > >Apart from grouping is there any other query I can build that >would give me the expected result I don't understand your question. Tables have no order; the only things you can use for grouping are values actually stored in the records in the table. Are you assuming that the first two 12345 rows are somehow "grouped" already? Because they aren't! More info please. -- John W. Vinson [MVP]
From: Marshall Barton on 27 Mar 2010 18:05 jer wrote: > columnA ColumnB ColumnC columnD > 12345 23 78956 10.00 > 12345 23 89153 15.00 > 56780 23 15687 20.00 > 56780 23 15890 20.00 > 12345 23 78853 30.00 > 12345 23 87568 30.00 > >I am importing a table from an external source structured as above >and if I sum columnD based on grouping on columnA I would get total >for > 12345 = 85.00 4 detail records > 56780 = 40.00 2 detail records > >what I want to get however is > 12345 = 25.00 2 detail records > 56780 = 40.00 2 detail records > 12345 = 60.00 2 detail records > >Apart from grouping is there any other query I can build that >would give me the expected result Not unless there are other fields that can be used to separate the 12345 records into two groups. It is important for you to understand that the records in a table are NOT ordered in any way that a human can make sense out of the way they appear. The ONLY way to sort records is by using a query with an Order By clause. Based on that, there is no guarantee that the records you posted above will appear in that order. Even if they appear that way today, you can not rely on them appearing the same way in the future. -- Marsh MVP [MS Access]
From: jer on 27 Mar 2010 19:05
thank you all - I understand, -- thanks as always for the help "Marshall Barton" wrote: > jer wrote: > > > columnA ColumnB ColumnC columnD > > 12345 23 78956 10.00 > > 12345 23 89153 15.00 > > 56780 23 15687 20.00 > > 56780 23 15890 20.00 > > 12345 23 78853 30.00 > > 12345 23 87568 30.00 > > > >I am importing a table from an external source structured as above > >and if I sum columnD based on grouping on columnA I would get total > >for > > 12345 = 85.00 4 detail records > > 56780 = 40.00 2 detail records > > > >what I want to get however is > > 12345 = 25.00 2 detail records > > 56780 = 40.00 2 detail records > > 12345 = 60.00 2 detail records > > > >Apart from grouping is there any other query I can build that > >would give me the expected result > > > Not unless there are other fields that can be used to > separate the 12345 records into two groups. > > It is important for you to understand that the records in a > table are NOT ordered in any way that a human can make sense > out of the way they appear. The ONLY way to sort records is > by using a query with an Order By clause. > > Based on that, there is no guarantee that the records you > posted above will appear in that order. Even if they appear > that way today, you can not rely on them appearing the same > way in the future. > > -- > Marsh > MVP [MS Access] > . > |