Help Creating a Commission Database

[twodoor55]

I have created some files and when I do the reports. It is using each record more than once, ie two check issued two commissions, which give me a false answer. Obviously, I don't know what I am doing. Can some one help me or tell me where I can find a template for what I need?

Thank you.

[nico5038]

Obviously an error in your report query. I think that you're joining two (or more) tables and thus generating a so-called "Cartesian product".
Can you post the report query here ?

Nic;o)

[twodoor55]

Yes, I am joining two tables. I am trying to calculate the balance of commissions due, but when I do a report I get a false answer. I have tried several ways to build the tables but keep getting the same problem, because although I made changes I still must have it set up wrong.
Table 1

ID    Job Order #    Order Line#    Commission
1    6-0463    06-0463-007    $35,272.00
2    6-0465    06-0465-004    $14,587.00
3    6-0523    06-0523-002    $15,701.25
4    6-0523    06-0523-003    $445.00
5    6-0535    06-0535-002    $3,712.50
6    7-0035    07-0035-002    $7,410.00
7    7-0047    07-0047-002    $20,015.00
8    7-0047    07-0047-009    $1,440.00
9    7-0047    07-0047-010    $1,425.00

Table 2

ID    Job Order #    OrderLine#    Check #    Check Date    Check Amount    Percentages
1    06-0463    06-0463-007    3002908    3/8/07    $14,108.80    0.4
2    06-0463    06-0463-007    3003361    5/4/07    $7,054.40    0.2
3    06-0465    06-0465-004    3002912    3/8/07    $5,834.80    0.2
4    06-0465    06-0465-004    3003471    5/22/07    $2,917.40    0.3
5    06-0523    06-0523-002        2/19/07    $4,710.38    0.3
6    06-0523    06-0523-002        4/18/07    $4,710.38    0.2
7    06-0523    06-0523-003        4/18/07    $133.50    0.3
8    06-0523    06-0523-003        4/18/07    $133.50    0.3
9    07-0035    07-0035-002    3003276        $2,223.00    0.3
10    07-0035    07-0035-002    62252        $2,223.00    0.3
11    07-0035    07-0035-002    62252        $2,223.00    0.3
12    07-0047    07-0047-002    3003276    4/18/07    $4,003.00    0.2

Any help would be greatly appreciated.

[nico5038]

Please post the query SQL (open query in SQL mode and use copy/paste)

Nic;o)

[NeoPa]

I have created some files and when I do the reports. It is using each record more than once, ie two check issued two commissions, which give me a false answer. Obviously, I don't know what I am doing. Can some one help me or tell me where I can find a template for what I need?

Thank you.

Please think about getting your question a little better organisd. This will also help you with your problem. At the moment, I'm afraid, your question is very hard to understand (as we can't see your database and have very little idea of even what you'e trying to do).
Something which will give us a big clue (also critical if we're to help) is to post the SQL of the query you're using (as Nico has asked for in both his posts).
See Nico's last post (#4) for instructions on how to see it so that you can copy and paste it into your post.
Please use the [ CODE ] tags when you do (See the hash (#) button).