Calculating fields, sum, and breakdown by cust query

  • Thread starter Thread starter RLConger
  • Start date Start date
R

RLConger

Hey gang,
Total Access noob here, and reading all of your posts only reassures
me of my newness.
I have a two tables...Customers and Sales
Customers is simply CustomerID, CustName, ContractAmt, and
ContractYr; and Sales includes the following:
(there would also be purchase date information for searching on)

Customer | Item | UnitCost | Qty
Bob Hose $37.00 25
Larry Nails $4.75 100
Bob Sod $0.26 150,000
etc...

What I want the query to do is take the unit cost * Qty to get a
record total, then I want to sum the totals all the way down the
record. I would want another query that took those same totals and
broke them out by customer as grand totals only. I don't want to see
all the records, just the totals.

Query results

For contract year 08 Bob's contract was for $10,000.00. He spent a
total of $8,746.98
(the 08 contract amount would be in the Customers table)

It would search through the records, total all of Bob's sales, all of
Larry's sales...etc, and provide annual totals.

I have tried to accomplish this through the wizards with no luck, and
I also tried my hand at doing some expressions with no luck. Any help
you could provide me on this would be awesome.
Thanks In advance

Ryan
 
Create a new Select query (design view, w/o the wizard):
Add the Sales table.
Double-click Customer to add it to the grid.
Enter a new Net field, like this: Net: [UnitCost]*[Quantity]
Test-run this: it gives you line-item net figures.

To get per-customer totals, now:
Click the Totals button on the toolbar (Greek sigma / sideway "M")
Set the Total box in the grid to Group under Customer and Sum under Net.

Test-run it. This will give you the grand total for each customer.

To get grand totals, just remove the customer field from the grid.
 
Back
Top