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];

 

[rjoubert]

You can only use SUM when you are in a GROUP BY query, so you'll need a GROUP BY clause in your SQL statement.

 

[IT4EVR]

To do an inner join you have to determine which key fields exist in both tables in order to join them. THen you would add the rest in a where clause.

Is it the advisor field?

SELECT...
FROM LOG
INNER JOIN ADVISORs
ON LOG.[FieldName] = ADVISORs.[FieldName]
WHERE...
ORDER BY...

 

[jbiwill]

Ok so I need 'GROUP BY'.

IT4EVR can you do one of your nice little guides for GROUP BY like you just did for INNER JOIN?

Thanks for the quick replies.

Will

 

[rjoubert]

In order to get the sum of each advisor's commission, which I assume is what you're trying to do, you may need a separate query from the one you posted.

SELECT Advisor, SUM(YourCommission) as CommissionTotal
FROM LOG
WHERE ...
GROUP BY Advisor

Here's some info on Aggregate functions (like SUM)...

http://www.blueclaw-db.com/accessquerysql/sql_aggregate_function.htm

And some info on the GROUP BY clause...

http://www.blueclaw-db.com/accessquerysql/group_by_clause_sql.htm

 

[jbiwill]

thanks for your reply, have just sussed it. Thanks though.

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])

GROUP BY 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]

ORDER BY LOG.[Date Business Written];

 

[IT4EVR]

I could be wrong, but the problem of writing your sql statement with FROM LOG, Advisors creates a cross join. Your where clause may delimit that but I'd still try doing the Join statement.

 

[jbiwill]

Hmm you maybe right

Maybe i spoke too soon.

The sum function is not working properly. I now there are more than one result in the set as my report lists them however them sum is not adding them all up. It looks as if its just displaying one of the values in the list!

Any suggestions?

 

[PHV]

Any suggestions?
Join your tables.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

read the Join link below for more info.....


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[jbiwill]

Ok I've had a shot and am getting the error message: You tried to execute a query that doesn't include the specified expression 'Advisor' as part of an aggregate function."

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], Advisors.[Advisor], sum(LOG.[Your Commission])

FROM LOG INNER JOIN Advisors
ON LOG.[Advisor] = Advisors.[Advisor]

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])

GROUP BY 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]

ORDER BY LOG.[Date Business Written];

 

[IT4EVR]

put Advisors.advisor in your group by

 

[lespaul]

every field in your select that is not an aggregate (SUM, COUNT, AVG) must be listed in the GROUP BY clause.

SELECT Field1, Field2, SUM(Field3) FROM TableName GROUP BY Field1, Field2

leslie

 

[jbiwill]

Thanks. Although that removed the error message i'm still experiencing the incorrect sum...

 

[jbiwill]

Ok I have now added sum(LOG.[Your Commission]) AS sum to my query.

The query returns a result set like this:

name | other fields | commission | sum
-------------------------------------------------
smith | other results | £11.00 | £11.00
joy | other results | £4.00 | £4.00
Doyle | other results | £6.00 | £6.00

So the problem is that the sum field is only holding the value of that row and not the total of the commission column!

Anyone have any clue whats going on! thanks.

 

[lespaul]

Well, you have a date column listed in your select, that will cause the query to sum BY DATE.

You didn't list them, but I'm betting that the 'other fields' is what is causing the "incorrect" sums.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[jbiwill]

So are you saying that because a date field is present that it supercedes the request to sum my commission field?

My other fields are mainly text fields and a couple of currency fields.

Will

 

[lespaul]

An aggregate function performs based on all the other fields in your select.

If you have a table like:

Salesman SaleDate Amount
JR 1/1/2006 $500.00
EM 1/1/2006 $250.00
JR 1/1/2006 $100.00
EM 1/2/2006 $101.00
JR 1/2/2006 $75.00
JR 1/2/2006 $100.00

and you want to sum the sales for the month for EACH salesman:

SELECT Salesman, SUM(Amount) FROM TableName GROUP BY Salesman

the results would be:

JR $775.00
EM $351.00

If you want to know how much per day:

SELECT SalesDate, SUM(Amount) FROM TableNmae GROUP BY SalesDate

the results would be:

1/1/2006 850.00
1/2/2006 276.00

If you want by Salesman, by Date:

SELECT Salesman, SalesDate, SUM(Amount) FROM tableName GROUP BY Salesman, SalesDate ORDER BY Salesman, SalesDate

EM 1/1/2006 250.00
EM 1/2/2006 101.00
JR 1/1/2006 600.00
JR 1/2/2006 175.00

So, yes, by adding additional fields to your select, you will have different SUMs.

Can you see what the "GROUP BY" is doing now? It's GROUPING together like information and performing the aggregate function in the SELECT on that GROUP.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[randy700]

How about...

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],
       Advisors.[Full Name], Advisors.Advisor, Sum(Log.[Your Commission]
FROM Log INNER JOIN Advisors ON Log.Advisor = Advisors.Advisor
WHERE Log.[Date Business Written] [COLOR=red]BETWEEN[/color] forms!commForm!dateFrom AND
      forms!commform!dateTo
AND Log.Advisor = forms!commform!advisor
AND Log.[Business Written?] = 'yes'
GROUP BY Log.Advisor, Log.[Date Business Written], Log.[Clients Name],
         Log.[Business Written?], Log.[Single or Annual],
         Log.[Type of Policy], Log.[Premimum Paid], Log.[JBI Commission],
         Advisors.[Full Name]
ORDER BY Log.[Date Business Written]

Randy

 

[jbiwill]

Well I seem to be doing what you have said Leslie, but the total field is still not calculating all of the fields:

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], Advisors.[Advisor], sum(LOG.[Your Commission]) AS sum
FROM LOG INNER JOIN Advisors ON LOG.[Advisor] = Advisors.[Advisor]
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])
GROUP BY 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], Advisors.[Advisor]
ORDER BY LOG.[Date Business Written];