|
tech
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Using an SQL statment in a report in Access 97Let 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? 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 Show quote "Jack Dawson via AccessMonster.com" <fo***@AccessMonster.com> wrote in message news:dc3b3ffe74d54cef925894c8dc0dc6f4@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? > > -- > Message posted via http://www.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. 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. 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 Show quote "Jack Dawson via AccessMonster.com" <fo***@AccessMonster.com> wrote in message news:745f3693599b44329a6f09bbfa824c4b@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. > > -- > Message posted via http://www.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.
Show quote
"Jack Dawson via AccessMonster.com" <fo***@AccessMonster.com> wrote in
message news:af0b593e265a468a8071b025e93873db@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. > > -- > Message posted via http://www.accessmonster.com 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? Show quote "Jack Dawson via AccessMonster.com" <fo***@AccessMonster.com> wrote in message news:af0b593e265a468a8071b025e93873db@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. > > -- > Message posted via http://www.accessmonster.com 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 >-----Original Message----- for. I am curious why>I have tried that and my results weren' what I hoped >the following gives me syntax errors, COMMISSION #2] WHERE> >( SELECT [SalesLog]![SALESMAN] , [SalesLog]![Stock #] , [SalesLog]![SOLD] >, [SalesLog]![TOTAL COMMISSION #1] , [SalesLog]![TOTAL >[SALESMAN] = [SalesLog]![SALESMAN2] ) current salesman in the> >Where SalesLog is the table and [SALESMAN] is the >report. I would be better at this if it were full SQL or at least MySQLShow quote  |
|||||||||||||||||||||||
Other interesting topics