Help with SQL statement in Access 1

[jbiwill]

Hi there

I am getting really stuck on this SQL query. I have just added SUM() to it and realise I need to change it to an INNER JOIN query but having on where to start!!

Any help will be appreciated, thanks:

SELECT LOG.[Advisor], LOG.[Date Business Written], LOG.[Clients Name], LOG.[Business Written?], LOG.[Single or Annual], LOG.[Type of Policy], LOG.[Premium Paid], LOG.[JBI Commission], LOG.[Your Commission], Advisors.[Full Name], sum(LOG.[Your Commission])
FROM LOG, Advisors
WHERE (LOG.[Date Business Written]>=[forms]![commForm]![dateFrom]) And (LOG.[Date Business Written]<=[forms]![commForm]![dateTo])
AND (LOG.[Advisor] = [forms]![commForm]![advisor] OR [forms]![commForm]![advisor] IS NULL)
AND LOG.[Business Written?] LIKE "yes"
AND (Advisors.[Advisor] = [forms]![commForm]![advisor])
ORDER BY LOG.[Date Business Written];

 

[lespaul]

can you provide some sample data and your expected results from that data? what do you mean by 'the total field is still not calculating all of the fields'? How does this query not do what you need it to?

leslie

 

[jbiwill]

The query is used to populate a report, to list an employees commission they have earnt on each case of new business they have made.

The rows lest each client's name, type of product they have sold and commission earnt from that sale. At the bottom of the commission earnt column I just need a total of that colum. Like you would click the sutosum function excel to do the same.

I am testing the report with a couple of records. 1 has earnt the advisor £4, another £10 and another £3. The problem is that the sum(LOG.[Your Commission]) is giving me the value of the last row on the report (£3).

I just can not seem to get it to work!! Thanks

 

[lespaul]

you mean you want to have a total of all the commission listed in the query? You do that in the report, not the query. I don't do much in Access, I help more with query issues, but I know there is a way to "total" all the information on a report, if that is what you are trying to accomplish.

The other way is a union query. Using the Sales example I posted above, if you need a total of all sales, you would use a UNION query:

SELECT Salesman, SUM(Sales) FROM TableName GROUP BY Salesman
UNION
SELECT 'TOTAL', SUM(Sales) From TableName

HTH

Leslie

 

[randy700]

Try using the Report Wizard to create the report from your Table. After selecting the fields you want, make sure you choose at least one field for Grouping. At the next step, select Summary Options. I think that might provide you with the help you need.


Randy