Ack! Help getting an updateable recordset from a distinct query?

[Tekime]

Hi all,

I'm trying to get an updateable recordset using a complex query involving many joins, but I'm running into some problems. I'll explain what the database is for and a bit about how it is designed to give you an idea of what's going on. I would appreciate any help with this

The database is for processing chargebacks on my company's merchant account. For those of you unfamiliar with the term, a chargeback is a dispute from a merchant account provider (credit card company) on behalf of their customer. We receive a chargeback that specifies the card number the charges were on, as well as all transactions that chargeback is disputing.

Every day we will import orders and transactions into respective tables (tbl_Orders and tbl_Transactions) from our order manager. When we receive a chargeback, we need to associate all disputed transactions in tbl_Transactions with the chargeback record we create in tbl_Chargebacks; since this is a many-to-many relationship I have a table called tbl_Chargebacks_Transactions (fairly self-descriptive).

Here is a screenie of a chunk of the database schema so you can see the relationship between chargebacks and orders:

http://www.fury-tech.com/showimage.php?image=/images/user/Tekime/CbOrd_Rel01.gif

This works fine for the most part. Although, since the only relationship between chargebacks and orders is the Chargebacks_Transactions and Transactions tables, which can possess numerous records for one chargeback, if I need a field from the order table in a complex query based on the chargebackId, it will return multiple records if there are multiple entries in Chargebacks_Transactions.

Okay, so I can use DISTINCTROW and this works fine in limiting the records. Except, I can't get an updateable recordset this way!

I can use a bound subform which allows me to update records, but I cannot search the fields in that subform to find records in the parent form. (Say I want to search for chargebacks by credit card number, well, the ccnumber is in the orders table).

Here is my (half finished) Chargeback Details form:

http://www.fury-tech.com/showimage.php?image=/images/user/Tekime/CbDetails.png

I'm not sure if there is a way to construct my query so I can retrieve all of the fields present on this details form and be able to update them as well, but this is my goal.

If I can't reqrite the query to fit my schema, I believe I can create an additional relationship between chargebacks and orders by storing an orderId in the chargebacks table. This is repetitious however, since I can find the orderId based on the transactions, but so far it's not fitting my needs.

I hope this makes SOME sense to you, and if so, I hope you can throw me an idea on making this work.

Thanks kindly.

 

[Tekime]

1. The CIO here removed RI from this relationship when creating the import scripts to import orders and transactions. I haven't touched it for this reason, although I have designed every other part of the DB to enforce RI. Will this make a difference regarding my problem? If so I will dig into the import scripts and try to 'fix' them.

2. The form is broken into subforms right now. The rebuttal details, order details, transaction history and product details are all subforms. The reason I don't want them to be subforms is so I can search based on fields within these forms. As subforms, I cannot search within these fields since it will only search within records in that subform (which of course are only the records related to the parent form), as opposed to all records related to all records loaded in the parent form.

Thanks again for your help Pat.

 

[Tekime]

I've already done that, two versions in fact. I would like to experiment with this method, though. It would be faster for the people using the system. Not to mention, once I run into a problem the last thing I want to do is give up on it. Unless you are saying it's impossible to create an updateable query pulling records across an associative table like I am trying to do.

 

[Tekime]

You can see the relationship between the four tables in question above. If there is more than one record in tbl_Chargebacks_Transactions, then a record will be returned for each record in that table.

I fixed RI between tbl_Transactions and tbl_Orders and the problem still persists, though.

I made a quick dummy query and recordset so you can see what happens.

Query:

SELECT tbl_Chargebacks.cb_caseNumber, tbl_Orders.orderId
FROM (tbl_Orders INNER JOIN tbl_Transactions ON tbl_Orders.orderId = tbl_Transactions.trns_orderId) INNER JOIN (tbl_Chargebacks INNER JOIN tbl_Chargebacks_Transactions ON tbl_Chargebacks.chargebackId = tbl_Chargebacks_Transactions.cbTrns_chargebackId) ON tbl_Transactions.transactionId = tbl_Chargebacks_Transactions.cbTrns_transactionId;

Recordset returned:

C12345 1121666427696
C12345 1121666427696