 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / March 2005Using 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? 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 > 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 [quoted text clipped - 10 lines] > report if I could figure out how to get it to list all sales for SALESMAN > where he was either SALESMAN or SALESMAN2. Help? 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 > I have tried that and my results weren' what I hoped for. I am curious why > the following gives me syntax errors, [quoted text clipped - 6 lines] > report. I would be better at this if it were full SQL or at least MySQL > with PHP. Thanks for the help. 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. 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? > 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. > 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. 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 [quoted text clipped - 7 lines] >report. I would be better at this if it were full SQL or at least MySQL >with PHP. Thanks for the help. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.