Query to sum data across two tables?

[rcrobman]

Boy do I feel inadequate with what I thought would be a simple task.


What I want to do is to sum up some data that is across 2 tables which I thought would be simple.
Table 1 has the customer data in it name, address etc and the salesperson and an order number
Table 2 is linked to Table 1 by the order number and has the sales data in it - there could be multiple lines of sales data for each order number .
The task:
On a form display the salesman name and the total of all of his sales for all of the order numbers and count the number of orders - simple in your head add them up order by order that he is responsible for.

So I have tried various attempts to do so - created new queries, tried using DSum,DCount but no matter what I do I can't make it work.
Using queries seemed the easiest but no matter how I try and structure the query I get the Salemans name and a list of the Orders and their totals. Have tried using Distinct - nope that doesn't work.
If I wanted to do this in a report it would be easy - just add the necessary grouping levels, hide the detail and use the Grand Totals - BUT I need the info on a form and you can't put a report on a form.

So after you finish laughing at my inability to figure this out I would appreciate any suggestions you might have to do what I thought was going to be simple!
Thanks in advance for any help!

[ranman256]

dsum & dcount are not used in queries. The QUERY does the sum.

in the query pull in the 2 tables and join them,
bring down the fields needed,
click the SUMMATION button (totals)
then under the $ field, set the TOTAL row, combo box to SUM.

[rcrobman]

Thanks for the reply but have tried that and it does not work for what I want. Yes it does total each of the Order items on each order BUT it gives me the Salesmans name and total for each order in the system whereas what I want is for each Salesman to have one record with the total of all the orders besides it. i.e.
Joe $20,000 - cumulative total of all of Joes orders.
Sam $30,000
Fred $40,00 etc
Instead I get:
Joe $4,000
Joe $8,000
Joe $8,000
Sam $10,000
Sam $20,000

[Gicu]

Can you please post the SQL statement of the query you use?

Cheers,

[Minty]

In your query make sure you remove all the fields except the salesman name and the Sum for the orders.

The Group By if you include the order number will add a group for each Order number. As will a date field for example.

[rcrobman]

Thanks a lot for the suggestion that seemed to do the trick . Sad that I have been working with Access for so long and did not know that was the reason it wouldn't work properly!
Kudos for the help!

[rcrobman]

Thanks for the attempted help! Fortunately the answer was provided by another user!
Appreciate your willingness to help out!