Complez query help

[Tekime]

I'm having a real tough time with this query I'm trying to create. I need to retreive transactions which are not related to a specific chargeback, but are related to a specific credit card number.

tbl_Transactions stores all of the transactions. Each transaction record has an orderId with a corresponding record in tbl_Orders

tbl_Chargebacks_Transactions stores all related chargeback IDs and transaction IDs.

tbl_Orders stores customer order info, including credit card number

I need to select data from tbl_Transactions where the following criteria are met:

The transactionId cannot have a record in tbl_Chargebacks_Transactions where the related chargebackId is equal to a pre-defined number. But, the transactionId can exist in tbl_Chargebacks_Transactions where the chargebackId is not equal to this pre-defined number.

The transaction must be on a pre-defined credit card number.

For example, say I know the ccNumber is 1111222233334444 and the chargebackId is 135. I need a list of all transactions which do not have an entry in tbl_Chargebacks_Transactions with a chargebackId of 135, and which do have an orderId specified in tbl_Transactions where the orderId has a ccNumber field equal to 1111222233334444.

I hope this makes some sense, I cannot figure out how to get this one right.

Thank you very much for any help with this one

 

[GumbyD]

OK try the following SQL - I hope it works for you:

SELECT tbl_Transactions.TransactionID, tbl_Transactions.OrderID, tbl_Orders.CCNum, tbl_ChargeBacks_Transactions.ChargeBackID
FROM (tbl_Transactions LEFT JOIN tbl_ChargeBacks_Transactions ON tbl_Transactions.TransactionID = tbl_ChargeBacks_Transactions.TransactionID) INNER JOIN tbl_Orders ON tbl_Transactions.OrderID = tbl_Orders.OrderID
WHERE ((Not (tbl_ChargeBacks_Transactions.ChargeBackID)="135" Or (tbl_ChargeBacks_Transactions.ChargeBackID) Is Null));


GumbyD

 

[Tekime]

Ooh, this looks promising

I'm heading out of work right now but I'll check this out first thing in the morning! Thanks for the post I'll be sure to let you know how it works

 

[Tekime]

I tweaked it out a bit to work with my specific fields and such, but it seems to be working like a charm Thanks a heap GumbyD!!

 

[Tekime]

Hmm, perhaps I spoke too soon. The query is still showing transactions where the specified chargebackId exists in tbl_Chargebacks_Transactions.

Here's the SQL I'm using:


sqlUnassignedCredits = _
"SELECT [tbl_Transactions].[trns_amount], [tbl_Transactions].[trns_date], [tbl_Transactions].[transactionId] " & _
"FROM ([tbl_Orders] INNER JOIN [tbl_Transactions] ON [tbl_Orders].[orderId] = [tbl_Transactions].[trns_orderId]) " & _
"LEFT JOIN [tbl_Chargebacks_Transactions] ON [tbl_Transactions].[transactionId] = [tbl_Chargebacks_Transactions].[cbTrns_transactionId] " & _
"WHERE (([tbl_Chargebacks_Transactions].[cbTrns_chargebackId] <> " & [Forms]![frm_ChargebackMain]![chargebackId] & ") Or (" & _
"[tbl_Chargebacks_Transactions].[cbTrns_chargebackId] Is Null)) AND ([tbl_Orders].[ord_ccNumber]= '" & [Forms]![frm_ChargebackMain]![frm_OrderDetails].[Form]![ord_ccNumber] & "') AND " & _
"([tbl_Transactions].[trns_amount]<0)"


I tried removing a few of the other criteria to no avail. Also have used

(Not ([tbl_Chargebacks_Transactions].[cbTrns_chargebackId]) = ...

instead of

(([tbl_Chargebacks_Transactions].[cbTrns_chargebackId] <> ...

I'll continue messing with it though

 

[Tekime]

Okay, I figured out my problem (just not a solution). The query is not returning transactions which are related to the specified chargebackId, BUT, if a transaction is related to the specified chargebackId and another chargebackId, it then returns the transaction since it fits the criteria.

I guess ultimately I need to retrieve all transactions which have no entries in tbl_Chargebacks_Transactions related to the specified chargebackId, not just entries which either have no record with the specified chargebackId OR have one with the specified chargebackID and another chargebackId.

I probably need a sub-query of some sort to accomplish this though.