If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using an SQL statment in a report in Access 97
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 |
#2
|
|||
|
|||
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" wrote in message news:dc3b3ffe74d54cef925894c8dc0dc6f4@AccessMonste r.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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. -- Message posted via http://www.accessmonster.com |
#5
|
|||
|
|||
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" wrote in message news:745f3693599b44329a6f09bbfa824c4b@AccessMonste r.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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
"Jack Dawson via AccessMonster.com" wrote in message news:af0b593e265a468a8071b025e93873db@AccessMonste r.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 |
#8
|
|||
|
|||
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? "Jack Dawson via AccessMonster.com" wrote in message news:af0b593e265a468a8071b025e93873db@AccessMonste r.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 |
#9
|
|||
|
|||
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----- 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 . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Print field description of a table | Larissa25 | General Discussion | 34 | February 6th, 2005 04:30 AM |
Unable to insert record to Access via SQL from an ASP page | Norman Yuan | General Discussion | 1 | February 2nd, 2005 08:23 PM |
upsized access to SQL now there is a report field error | Me! | Setting Up & Running Reports | 7 | January 28th, 2005 09:47 PM |
exporting accress reports into word | marc | General Discussion | 8 | December 29th, 2004 04:40 PM |
Still Hoping for help with a Query problem | Don Sealer | Using Forms | 15 | November 13th, 2004 06:24 AM |