Help!! Need to sum mutiple criteria (1 Viewer)

[mateogp]

Hello,

I have a table setup as follows:
Type, Style, Quantity, Weekending

Type would consist of: Coat/Vest/Tie/Pants/Shirt/Shoes
Style consists of probably about 100 different codes for each type
Quantity: Quantity of each style ordered.
Weekending: The weekend each order is due for

I need to create a report based on each Type for each weekend.

Whats the best way to go about this? Should I use dCount and list each and every style? This would be very very time consuming since each style would be about 100 and styles are added and deleted through out the year.

Is there a better way to dynamically list only the styles used for each weekending?

Help would be greatly appreciated, my bosses expect me to get this done very quickly, and havent had much time to do research into it.


Please heeelpp!!


Mateo

 

[Pat Hartman]

You can create a totals query and use that as the RecordSource for the report. The report will still need grouping so that you can calculate sub totals.

Select Type, Style, WeekEnding, Sum(Quantity) As SumQuantity
From YourTable
Group by Type, Style, WeekEnding;

 

[mateogp]

You can create a totals query and use that as the RecordSource for the report. The report will still need grouping so that you can calculate sub totals.

Select Type, Style, WeekEnding, Sum(Quantity) As SumQuantity
From YourTable
Group by Type, Style, WeekEnding;

Hey Phil,

Thanks for your nudge in the right direction. I've actully never created a totals query, kinda was forced into access as a neccessity so i've been learning as i go. How do you create a totals query? Is Select Type, Style, Weekending, Sum(Quantity) As SumQuantity a code of some sort?

Any help would be appreciated, under the gun!!


Thanks,
Mateo

 

[Pat Hartman]

My name is not Phil.

Open the QBE, select the tables you need. Draw the necessary join lines if you added multiple tables. Select the columns you want in the query. Press the sigma tool on the toolbar. It will add "Group By" to every column in your query. Change "Group By" to "Sum" for the columns you want to sum.

 

[mateogp]

Pat,

Thanks for your help! I honestly didnt mean to call you Phil. I think I was thinking of the Saturday Night Live comedian.

Once again, thanks!

Mateo

 

[mateogp]

Hey Pat,

Still having some trouble...

I have my query setup like this:

GRID Weekending Type Style Quantity
(N/A) Group By Group By Group By SUM


Its still not totaling correctly. Right now its going
Type Style CountofQuantity
Coat 270 1
Coat 270 1
Coat 270 1
Coat 270 1

What i need it to do is Group By the weekend, and count the total of how many 270's are being used. So basically i'd need in every weekending a total of how many 270's are being used. There may be mulitple records of 270's, so i need the query to add up the quantity of every 270 and give me a grand total.


As always any help would be appeciated.


Thanks,
Mateo

 

[Pat Hartman]

You'll need to post your query. You have something that is preventing the data from being aggregated.

 

[mateogp]

Hey Pat,

I posted the file for you.

(to big to post this file as an attachment)
http://www.blacktietuxes.com/gr/GlobalRegistry.zip



Thanks!
Mateo

 

[Pat Hartman]

I don't want to look at your database. Just post the SQL string.

 

[mateogp]

You can tell im a bit unexperienced! How do I post an SQL String?

Mateo