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.