Summary Query - How to?

[ritch]

I have 9 stores inventories - using import - I have them all assigned a column - which means have 9 rows for the same UPC - how do I join the rows to 1 rows with all 9 stores running across. I know I have to do something like a summary query rather than detail - but - every time I use the Query Wizard it only lets me do a simple query not a summary. I had this at home yesterday but now at work - it simply won,t go - Help!

[pbaldy]

It sounds like you want a crosstab query. Play with that wizard and see if it produces the result you want.

[ritch]

I don't think it is a crosstab. It is a Simple Query - but I need it to summarize - I think you have to flag it to count records and add quantities (sum) - I'm missing something ---hmmm

[pbaldy]

Well, to summarize you'd use a totals query. You get that row to appear in a query in design view by clicking on the "E" on the toolbar/ribbon. That will not take rows and turn them into columns though.

[ritch]

I don't want to turn rows to columns
upc- description-retail- store1- store3- store4
123 blah 5.99 23
123 blah 5.99 27
123 blah 5.99 10

I want these 3 rows to consolidate to 1 row

123 blah 5.99 23 27 10

but I have 200-300 more items to consolidate

[pbaldy]

Well, in a sense that is turning 3 rows into 3 columns (for that particular field). If the crosstab query isn't doing what you want, there's also this:

Modules: Return a concatenated list of sub-record values

[ritch]

Turning 3 rows into 1 row and adding the sums if they appear in the same column or leaving them in the column which they belong. Like store 3 has 27 cases and store 1 has 23 cases, store 4 had 10 cases. I think it has to do with an agregate function and count>1.
There is a simple query wizard where it lists
detail (shows every field of every record)
summary
if you pick summary a summary options box opens up where you can select values like min,max,sum,avg for each column

and a check box which says Count records.

I used this on my laptop Sunday but have been unable to duplicate it.
After going through these motions I did get what I am after - 1 record per item with either the quantity moved to the row or added if there were more then 1 record per column. It did add another field - "count" and listed the amount of records which the row had used.

[ritch]

I kinda think I am so close there is only something lame stopping me! Help!

[RuralGuy]

I think a Pivot Chart does what you are looking for. Have you tried it yet?

[ritch]

I don't think it is a pivot chart. I kinda think that when I import into access everything is coming in as text. In the one table that worked columns were coming in as "double". I kinda think this is why I cannot get that summary in the query dialogue box. Perhaps by setting up the table fields before I import data.....

[RuralGuy]

Ritch,
How about a sample of one of the UPC codes and the 9 rows so we can have a better idea of what you are dealing with?

[ritch]

in attached files see source files - store 3 and 4 and access file - there are 2 items - Margarine and Foam Plates which are the same.I want to combine the two rows but keep the inventory under the right store s3 and s4.I only put 2 stores here - if there wre more stores there would be more rows of the same item.I did find that I was importing xls 97-2003 -when I switched to xlsx that made a difference.

[ritch]

In the time between the first and second post I rethought my problem - the attached is what I was after - xlsx did make a difference - with no access experience I am kinda proud that I came thru with this...currently it takes my "helper" about 5 or 6 hours to manually put this together in excel - I can foresee cutting this down to about a half hour to start then more from there....

[RuralGuy]

Ritch,
Does this mean you have your issue resolved?

[ritch]

yes it does