Solved: SQL help

[Imdabaum]

Perhaps due to poor database design or poor SQL practice, but I have the following tables and I'm getting all permutations
of that record.

Example if Bob spends $15 at McDonald's on 1/1/2009 and he spent $24 at McDonald's on 2/25/2009.
Then John spends $42 at White Castle on 1/2/2009 and $10 at McDonalds, the query would return:

Bob 15 McDonalds 1/1/2009
Bob 24 McDonalds 2/25/2009
Bob 24 McDonalds 1/1/2009
Bob 15 McDonalds 2/25/2009
Bob 42 White Castle 1/2/2009... etc. same thing happens with John.

I have about 4 major tables involved in this.

Table Commission
ID
rID
VENDORID
Start
End
Amt
Type

Table Request
rID
VendorID
Invoice1
Inv1Date
Invoice2
Inv2Date
CustID
Monthly_Fee

Table Customer
CustID
CustomerName

Table Invoices
InvoiceID
InvoiceNBR
InvoiceDate
CustomerID
DatePaid

I've done a query to pull all Invoices that are paid. And am now trying to get a single corresponding data from Customer, Invoice, and Commission.

The idea is that only Invoices that get paid earn a commission. But there is a Commission record that watches for paid invoices by the customer that the invoice is tied to.


SELECT DISTINCT QRY_Request_to_Commission.VendorID, QRY_INVOICES_PAID.INVOICE_NBR,
QRY_INVOICES_PAID.DATEPAID AS DINVPDOF, QRY_INVOICES_PAID.GLPOSTDT, QRY_INVOICES_PAID.CUSTNMBR,
QRY_Request_to_Commission.Amt
FROM QRY_Request_to_Commission, QRY_INVOICES_PAID
WHERE (((QRY_INVOICES_PAID.GLPOSTDT)=IIf([QRY_INVOICES_PAID].[INVOICE_TYPE_CD]=1,[QRY_Request_to_Commission].
Inv1Date],[QRY_Request_to_Commission].[Inv2Date])) AND ((QRY_INVOICES_PAID.CUSTNMBR)=[QRY_Request_to_Commission].
CustID]))
ORDER BY QRY_INVOICES_PAID.CUSTNMBR;

QRY_INVOICE_PAID simply queries against Invoice where DatePaid is not null.

QRY_Request_to_Commission- Joins the Commission to the Request on rID.



Any join experts or is this something that a union could solve?
I've tried it with and without Distinct.

[Bob Phillips]

Do you mean that you are getting John's records when you do a query for John (and vice versa)?

And why do you have 2 invoices on the Request table, why not just one?

[Imdabaum]

Do you mean that you are getting John's records when you do a query for John (and vice versa)?

And why do you have 2 invoices on the Request table, why not just one?

To request a commission is two parts. First there is a setup invoice. And to get the rest of your commission there has to be a paid monthly invoice.(product subscription).

I am ending up with all of John's records but in a "every possible scenario" affect.


More specific example
QRY_TEST
VendorID INVOICE_NBR DINVPDOF GLPOSTDT CUSTNMBR CommissionAmount
Vendor1 INVOICE2 11/9/2009 3/2/2009 CUSTOMER1 $24.00
Vendor1 INVOICE2 11/9/2009 3/2/2009 CUSTOMER1 $60.00
Vendor1 INVOICE2 11/9/2009 3/2/2009 CUSTOMER1 $2000.00
Vendor1 INVOICE1 7/13/2009 4/1/2009 CUSTOMER1 $24.00
Vendor1 INVOICE1 7/13/2009 4/1/2009 CUSTOMER1 $60.00
Vendor1 INVOICE1 7/13/2009 4/1/2009 CUSTOMER1 $2000.00
Vendor2 INVOICE2 11/9/2009 3/2/2009 CUSTOMER1 $12.00
Vendor2 INVOICE2 11/9/2009 3/2/2009 CUSTOMER1 $430.00
Vendor2 INVOICE1 7/13/2009 4/1/2009 CUSTOMER1 $12.00
Vendor2 INVOICE1 7/13/2009 4/1/2009 CUSTOMER1 $430.00

I should have Vendor2 Invoice1 $430 and Vendor2 Invoice2 $12.
And
Vendor1 Invoice1 $2000 and Vendor1 Invoice2 $60.
Vendor1 Invoice2 $24 is also the record I'm looking for rather than having it associated with Invoice1.

[OBP]

Can you post a screen shot of your Table Relationships please?
I have a feeling that is where the secret of success lies and will increase our understanding a lot.

[Imdabaum]

Sure. Hope it helps. I'm sure it probably has something to do with the relationship.

I added a few more fields into the query to see if I could find where the link might be. I found that each of the Invoice_Type_Codes were producing a record for the invoice so even though Invoice1 is type ID=1, it is showing for type ID= 2 as well... again probably relationship issues.

Go figure.. me having relationship issues...

[OBP]

Can you now show the Query Relationships/Joins?
Are you using the default Join of All Records that match?
It may be that you will need to split the Query in to 2 Queries or have a subquery in your Query.

[Imdabaum]

And this is why I'm still a beginner... I can't find my Request_to_Commission query and I just ran the report and the numbers look better. Not sure what I did, but I now have this query.


SELECT [First_Name] & " " & [Last_Name] AS Name, REQUEST.EarnedAs,
Trim([CustomerName]) AS Customer, COMMISSIONS.InvoiceNBR,
COMMISSIONS.InvoiceAMT, COMMISSIONS.InvoicePostDate,
COMMISSIONS.CommissionType, COMMISSIONS.CommissionEarned,
COMSTRUCT.CommissionAmount, COMMISSIONS.PaymentMonth, Sum(COMSTRUCT.CommissionAmount) AS Balance,
COMMISSIONS.Date_Paid_In_Full, REQUEST.Commission_Plan_Type
FROM ((TBL_CUSTOMERS AS CUST INNER JOIN TBL_CRF AS REQUEST ON
CUST.CustomerNumber = REQUEST.CustID) INNER JOIN TBL_EMPLOYEES
ON REQUEST.VendorID = TBL_EMPLOYEES.VendorID) INNER JOIN
(TBL_COMMISSIONS_STRUCTURE AS COMSTRUCT INNER JOIN
TBL_COMMISSIONS_Earned AS COMMISSIONS ON COMSTRUCT.ID =
COMMISSIONS.CStructureID) ON REQUEST.ID = COMSTRUCT.CRFID
GROUP BY [First_Name] & " " & [Last_Name], REQUEST.EarnedAs,
Trim([CustomerName]), COMMISSIONS.InvoiceNBR, COMMISSIONS.InvoiceAMT,
COMMISSIONS.InvoicePostDate, COMMISSIONS.CommissionType, COMMISSIONS.CommissionEarned,
COMSTRUCT.CommissionAmount, COMMISSIONS.PaymentMonth,
COMMISSIONS.Date_Paid_In_Full, REQUEST.Commission_Plan_Type,
COMMISSIONS.ID, CUST.CustomerNumber
HAVING (((COMMISSIONS.Date_Paid_In_Full) Is Null));



Though the HAVING keyword was added in there.. I didn't know what that did until later.

[OBP]

I can't see anything obvious other than your Query Joins don't match your relationships.
Have you looked at using a Subquery at all?

[Imdabaum]

I think I might need to do that. Just refactoring now to see if I really have all the data that I need. Sending it to the finance guys to see if the numbers match up. I think I will need a subquery to get the sum of all the commissions per vendorID, so far the balance is just repeating the CommissionEarned even though it's set up as a Sum(commissionsearned)..

[OBP]

Can you post a database with some dummy data?

[Imdabaum]

Hope you can make sense of it.

[Imdabaum]

Hope you can make sense of it.

I'm working on the RPT_Commissions_All to display the commissions earned for each person.

There is some duplicate code on the sfrms, it's something I'm trying to refactor into a common module in my application.

[OBP]

Sorry, I can only work with Access 2000-2003 versions, can you reformat it?

[Imdabaum]

Sorry, I can only work with Access 2000-2003 versions, can you reformat it?

Yep... Let me know if you have any questions.

[OBP]

Great, I have the query, on the first name Allen there are 6 records returned.
What should be returned?
Or can you quote an example for me?

[Imdabaum]

I have 6 records showing up in my report as well; 2 for Customer39 2 for Customer113, Customer422, and Customer639.

Customer039INVC01481601-Dec-09Recurring$19.10$19.10Customer039INVC01375701-Dec-09Recurring$19.10$19.10Customer113INVC01445701-Dec-09Recurring$31.63$31.63Customer113INVC01496501-Jan-10Recurring$31.63$31.63Customer422INVC01465201-Dec-09Recurring$10.29$10.29Customer639INVC01433917-Dec-09Setup$3,427.20$3,427.20


Perhaps it's in another part of my process, but looking at the data I have there should be 13 total commissions for ALLEN each month.

SELECT TBL_CRF.[ID], TBL_CRF.[VendorID], TBL_CRF.[CustID], TBL_COMMISSIONS_STRUCTURE.StartDate, TBL_COMMISSIONS_STRUCTURE.EndDate, TBL_COMMISSIONS_STRUCTURE.CommissionAmount
FROM TBL_CRF INNER JOIN TBL_COMMISSIONS_STRUCTURE ON TBL_CRF.ID = TBL_COMMISSIONS_STRUCTURE.CRFID;

If you look at that query and filter out Allen. All the End dates that have not expired should produce a commission record when I run the process.

[Imdabaum]

As I looked at that last query I posted I realized I might be able to find the monthly commissions from the structure this way without my lovely VBA code.


PARAMETERS DateDriver DateTime;
SELECT TBL_CRF.[ID], TBL_CRF.[VendorID], TBL_CRF.[CustID], TBL_COMMISSIONS_STRUCTURE.StartDate,
TBL_COMMISSIONS_STRUCTURE.EndDate, TBL_COMMISSIONS_STRUCTURE.CommissionAmount
FROM TBL_CRF INNER JOIN TBL_COMMISSIONS_STRUCTURE ON TBL_CRF.ID = TBL_COMMISSIONS_STRUCTURE.CRFID
WHERE TBL_COMMISSIONS_STRUCTURE.EndDate > [DateDriver]
as QRY_CRF_VENDOR_TO_COMMISSION.

This returns the Vendors and each record from the commission structure.
If I then group those on CustomerID and sum, I get a total of the commissions that would be due the vendor provided there is an invoice for the month of [DateDriver]

Still working on the query to join the paid invoices to this summary.

SELECT DISTINCTROW QRY_CRF_VENDOR_TO_COMMISSION.ID, QRY_CRF_VENDOR_TO_COMMISSION.VendorID,
QRY_CRF_VENDOR_TO_COMMISSION.CustID, Sum(QRY_CRF_VENDOR_TO_COMMISSION.CommissionAmount) AS TotalAmount
FROM QRY_CRF_VENDOR_TO_COMMISSION
GROUP BY QRY_CRF_VENDOR_TO_COMMISSION.ID, QRY_CRF_VENDOR_TO_COMMISSION.VendorID,
QRY_CRF_VENDOR_TO_COMMISSION.CustID;

Unrelated to my original question of getting the report but worth a million to cleaning up my code. Feels good to still be learning.

[OBP]

I found that the limiting factor of only 6 Records for Allen is the Commissions Table and the Is Null Paid In Full date.
Can you show me what output you would like to see, say in an Excel 2003 worksheet?
In that way I will have some idea of the structure needed.
It might actually be better to use the Report's Grouping function rather than the Query's.

[Imdabaum]

I found that the limiting factor of only 6 Records for Allen is the Commissions Table and the Is Null Paid In Full date.
Can you show me what output you would like to see, say in an Excel 2003 worksheet?
In that way I will have some idea of the structure needed.
It might actually be better to use the Report's Grouping function rather than the Query's.

Ideally the report would show something like the last group in the excel spreadsheet. It's color coded simply to show that the report would be grouped on Customer.

[OBP]

OK, so just to make sure that I have this right, the 1st Amount is the value that the Commission is based on and is not included in the Commission Report?
Do you need both sets of data in a Report or just the Commssion one?

[OBP]

One thing that I don't understand is where the 2nd Amount comes from?

[Imdabaum]

What do you mean the first value?

The commission amount from the first query is the commission made per request or sale. The second query would be the total commission payable when that customer pays an invoice in full.

[OBP]

The first Customer of Allen has 687.59, 19.1 and 7.64
whereas the Commission report only has 19.1 and 7.64 = 26.74

[Imdabaum]

Well the first amount is expired. EndDate has come and gone and therefore they no longer get that commission.

[OBP]

OK.

[Imdabaum]

One thing that I don't understand is where the 2nd Amount comes from?

Sorry I missed this one.

Basically if sales person makes a sell we run an invoice to setup the equipment. Then the following month we send a monthly invoice(recurring for subscription).

They get 1/2 when the setup is paid and 1/2 when the first monthly is paid. So if a total commission is 100, they get 50 when after the first invoice and 50 when the subscription invoice is paid... there are various rules to that based on what plan year we work in.

Also if it is for a prior plan then they were getting a residual on the customer. $100 sell gets a $1 commission, that starts after 36 months. So.. I've had to recalculate because I realized as I sent that spreadsheet that for a 36 month period the startDate Jan 1-2009 would actually have an end date of Dec 1-2011 as Jan 1-2012 would actually be 37 months.

[OBP]

Sorry, I just can't see where the QRY_COMMISSIONS_REPORT_ALL has the values shown in your Excel sheet, for Allen I can see
19.10, but not 7.64
13.19, but not 5.28
10.29, but not 4.12
279.9 but not 3.11

Help!

[Imdabaum]

EDITED::: Sorry looked at the wrong qry.

The 7.64 won't show up in the Commission Report until about 2012. The S2009 plan is 5% for 36 months. After 36 months it switches to 1%. Sorry I'm not an Excel guru quite yet and my counta function didn't quite catch that. All the commissions with a start date later than Now() aren't applied.

[Imdabaum]

So I've never had this problem before I've always been able to edit a recordset from the queries I make. I've looked up the problem, there are no groupings, Sums, Unions, or other aggregate functions to force it into read-only.

The only thing left is to assume that I am performing an illegal join or something. AllenBrowne mentions that it can happen if joins are going the wrong direction. Can anyone ellaborate on that?

SELECT COMMISSIONS.ID, COMMISSIONS.VendorID,
COMMISSIONS.CommissionEarned, COMMISSIONS.InvoiceNBR,
COMMISSIONS.InvoiceAMT, COMMISSIONS.CommissionType,
COMMISSIONS.Date_Paid_In_Full, COMMISSIONS.PaymentMonth
FROM TBL_COMMISSIONS_Earned AS COMMISSIONS INNER JOIN
TBL_INVOICES ON COMMISSIONS.InvoiceNBR =
TBL_INVOICES.INVOICE_NBR;

--I think I didn't setup the primary key on my Invoice table. This is going to be ugly trying to switch all these records to the InvoiceID instead of the InvoiceNBR. Does anyone know whether this is even going to help in the long run?

[OBP]

Why do you want to edit the Recordset, I thought it was for a Report?
It is better to have a Primary Key Linking the tables. I just wish I could work out what you want it to do so that I could try it myself.

[Imdabaum]

Why do you want to edit the Recordset, I thought it was for a Report?
It is better to have a Primary Key Linking the tables. I just wish I could work out what you want it to do so that I could try it myself.

Well I have the SQL for the report that I think I've gotten working. But as I got the report working I realized that the process I had made to insert commissions was adding commissions where they didn't belong.

Then I went through testing everything else and realized that one of the subforms didn't allow me to edit them. Subform showing a commissions history for each salesperson, I can't mark them as paid off, so they're going to constantly show up in the report.

So in short. I worked through the report. And now I've discovered another problem with my query for the subform.

[OBP]

Why does your subform need a Join Query?
Shouldn't the query just be based on the Commissions Table or it's query.
The Join for the subform should be established by the Form's Master/Child links?

[Imdabaum]

Why does your subform need a Join Query?
Shouldn't the query just be based on the Commissions Table or it's query.
The Join for the subform should be established by the Form's Master/Child links?


Because I'm still learning and I thought I was normalizing the database.

The commission stores an ID, CommissionStructureID, RequestID, and the InvoiceNbr, InvoiceAmt, VendorID, Date it was paid to the vendor. I have nothing connecting it to the Customer. So I joined the Commission Table to the Invoices table with the Invoice amount, and then pulled the Customer information from the CustID in the Invoice Table.:dunno

[OBP]

Perhaps nested Subforms would be better, Customer Mainform with one subform for the Invoice with a subform of it's own for the Commission?

[Imdabaum]

Perhaps nested Subforms would be better, Customer Mainform with one subform for the Invoice with a subform of it's own for the Commission?

That's a good idea. I've got the main form showing the vendors. I'm trying to show the commissions they earned.

--Thinking--
If I put a subform showing the Customers that the Vendor had commissions with. Then Show the invoices based on that customer as another subform and the commissions as a subform to the invoices... that might work... It's just crazy enough to possibly work. Thanks OBP.

[Imdabaum]

Turns out A) adding the custID to the table wasn't that difficult and I was able to relate the tables. bada bing. B) Sometimes I wonder if I'm in the right field.... hence my signature. Cheers to all us who are learning. Thanks to everyone else for showing us the error of our ways.

[OBP]

Great :beerchug: