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.

[vba]
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));

[/vba]

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.

[vba]
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]
[/vba] 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.

[vba]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;[/vba]

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?