Creating an updateable query using multiple tables

[ptaylorc]

Hi, I'm fairly new to access so please forgive my inexperience.

I have two tables (A and B) from different data sources, and each table has it's own primary key (Access assigned autonumber). Records are matched between the two tables based on certain fields, but occasionally there are matching problems. For records that don't match between tables A and B, a query suggests potential matches (it shows a list of potential primary keys pairings in a one-to-one manner). I need to review the potential matches manually and update table A or B if appropriate. What I would like to do is create an updateable query that displays information from tables A and B side-by-side for all of the given pairings.

Example:

TABLE A:
1 FFF RRR
2 CCC PPP
3 III RRR

TABLE B:
9 TTT EEE
10 JJJ UUU
11 HHH WWW

PAIRING QUERY:
1 10
3 11

**********************
TARGET OUTCOME QUERY:
1 FFF RRR 10 JJJ UUU
3 III RRR 11 HHH WWW
**********************

The desired result is a select query displaying the merged records, where I can compare the data side-by-side and update records accordingly.

Any suggestions are welcome! Every way I've tried to do this so far has resulted in the "Recordset not updateable" error message. Thanks!

 

[MarkK]

Hi there: Welcome to the forum!
I think I would try solving this with subforms. Create a main form based on your pairing query, and link the subforms by ID, one for each ID. You can use exactly the same subform, just have two instances of it on the main form.
This way each subform has a dedicated one-table query providing its data, and you side-step a bunch of the problems you're running into, AND using forms you get a friendlier user interface too.
Cheers,

 

[ptaylorc]

Hi Lagbolt, thanks for your response! I have so far only created queries, never forms. I tried creating the mainform and subform as you suggested, but I'm having difficulty.

If I use the form wizard, selecting the keys from the main query and corresponding fields from the two tables, I get the error message:

"You have chosen fields from record sources which the wizard can't connect. You may have chosen fields from a table and from a query based on that table. If so, try choosing fields from only the table or only the query."

The query is indeed based on the tables, I can't avoid that. If I leave out the fields from the main query and just select the table fields in the wizard, I get the error message:

"You have chosen fields from these tables: A, B; One or more of the tables isn't related to the others. Click OK to edit system relationships. etc."

I have already established relationships, but in the form A <--> Query <--> B. There is no direct relationship between A and B, except by using the query.

I also tried creating forms for the query and each table individually. Opening the query form in Design View, I dragged in the table forms from the list on the left. I then switched to Datasheet View and it looked promising, but there were two problems: I have to individually expand each line in the mainform to see the underlying subform (would prefer just one big table), and only one subform shows (either Table A or Table B, I can't get both at the same time).

Please let me know if you have any suggestions. I'll try to keep experimenting with forms, or maybe I'll find a way to do this using queries.

Thank you!

 

[MarkK]

Hi there. I'll take a look at your database if you want to post it. I'm a bit daunted by trying to explain how-to in this case, but I'll implement it and you can have a look.
Cheers,

 

[ptaylorc]

That would be great, thank you!

I've attached a simplified copy of my database (the original contains more query steps, but the filesize is huge).



For your reference, the basic structure is this:

1. Two tables are imported (TableA and TableB).

2. TableA_ValidRecords and TableB_ValidRecords eliminate unwanted records based on a number of criteria.

3. TableB_Averages summarizes valid TableB records based on certain matching criteria

4. 1MatchedRecords joins TableA with TableB_Averages based on these matching criteria

5. 2UnmatchedRecords is the query I mentioned in my first post, which suggests pairings for unmatched records

6. 3Reconciliation: My goal is to have an interface like this, but editable. From here, I can easily identify the cause of the mismatches... [TableA.Field4] has a value of "T", but [TableB.Field4] is blank.


Thanks very much!

 

[MarkK]

- I can't find aything that looks like the 'Pairing Query' from your original post so I made a table called Pairs. Other than that, this database expresses what I had in mind in respect to your post.
- The mainform, fPair, is based on the 'Pairing Query' idea, and the related records are each handled as single records in subforms. This presents data from two tables at the same time so you can compare and update as required.