Using an SQL statment in a report in Access 97

[Jack Dawson via AccessMonster.com]

Let me try to explain this. I am trying to create a sales commision report.
In this report I have to give each an itemized list of their sales
commisions for the month. I have it almost done with on minor hitch. The
salesmen sometimes split the commissions. The input data I draw from the
accounting system comes out with the following fields:

SALESMAN DATE STOCK# BUYER COMMISSION SALESMAN2 COMMISSION2

For example, I need the report for salesman 1 to list all of his individual
sales, all of his splits where he was SALESMAN with the name of SALESMAN2
(all of which I have so far) and finally, this is my sticking point, all of
the sales where he was SALESMAN2. I could theoretically do it in the
report if I could figure out how to get it to list all sales for SALESMAN
where he was either SALESMAN or SALESMAN2. Help?

 

[MacDermott]

Because this is for use in a report (not a form, where you might want to
update some data), you can write a UNION query like this: (I'm calling your
table tblSALES)
SELECT SALESMAN, [DATE], [STOCK#], BUYER, COMMISSION FROM tblSALES
UNION
SELECT SALESMAN2, [DATE], [STOCK#], BUYER, COMMISSION2 FROM tblSALES

This will give you your data in a form you can easily query.

HTH

 

[Jack Dawson via AccessMonster.com]

I have tried that and my results weren' what I hoped for. I am curious why
the following gives me syntax errors,

( SELECT [SalesLog]![SALESMAN] , [SalesLog]![Stock #] , [SalesLog]![SOLD]
, [SalesLog]![TOTAL COMMISSION #1] , [SalesLog]![TOTAL COMMISSION #2] WHERE
[SALESMAN] = [SalesLog]![SALESMAN2] )

Where SalesLog is the table and [SALESMAN] is the current salesman in the
report. I would be better at this if it were full SQL or at least MySQL
with PHP. Thanks for the help.

 

[Jack Dawson via AccessMonster.com]

Could I create a seperate header that serches the table for any field where
SALESMAN2 = SALESMAN like this:
SALESMAN
His sales here
SALESMAN2
Sales where SALESMAN splits with another salesman as the primary
NEW HEADER
Sales where SALESMAN from above was listed as SALESMAN2

If so, how? Just a momentary brainstorm.

 

[MacDermott]

Well, for one thing, your SQL statement lacks a FROM clause.
ANSI SQL (the dialect Access speaks) requires a FROM clause in most cases.

Another note, which may not be relevant -
I've always seen ANSI SQL written with dots where you have bangs.
A quick experiment suggested that the bangs may not be a problem,
though.

Finally, if you added FROM [SalesLog] before your WHERE clause, I would
expect this to return all records where [SALESMAN] in the table was equal to
[SALESMAN2].
Access will not resolve a reference to a control on a form or report unless
it is fully qualified:
Forms!MyForm![SALESMAN], OR
Reports!MyReport![SALESMAN]

In trying to help you resolve this, it would be useful to know what a record
looks like if one salesman gets the entire commission.
Is that salesman listed as both SALESMAN and SALESMAN2?
Is the entire commission listed as COMMISSION?
or is it split between COMMISSION and COMMISSION2?

HTH

 

[Jack Dawson via AccessMonster.com]

If there is no split the field SALESMAN2 is blank and COMMISSION2 contains
$0.00 (as it is formatted for Currency). When there is a split that
operation has already been done in the accounting software so it is split
between COMMISSION1 and COMMISSION2. As for the lack of a FROM clause, I
can't believe I missed that...*hiding face in shame* Thanks for the help
so far.

 

[MacDermott]

If there is no split the field SALESMAN2 is blank and COMMISSION2 contains
$0.00 (as it is formatted for Currency). When there is a split that
operation has already been done in the accounting software so it is split
between COMMISSION1 and COMMISSION2. As for the lack of a FROM clause, I
can't believe I missed that...*hiding face in shame* Thanks for the help
so far.

 

[MacDermott]

In that case, what is the problem with the UNION query I suggested?

It would seem that you could use a totals query based on it to pull the
total commission for each salesman, if that's what you want.

Now that you have a FROM clause, is your SQL working adequately?

 

[kongju]

You might want to try without the table name like
(SELECT [SALESMAN], [Stock #1]... so on)
Of course you have to include all the fields in the
table "SalesLog"'s in your query.
Good luck