I need to take data from two different tables and reconcile the data...much like a bank account. For the life of me I can't figure out the best way to do this. I thought maybe putting information from both tables on a report and grouping the data appropriately, but how would I be able to get that data on one report without having to link them because they don't have the same amount of information and I need to see all information from both tables. I am completely drawing a blank...can anyone help please!!
Need Ideas for Format
- Thread starter mrssevans
- Start date
- Local time
- Yesterday, 19:03
- Joined
- Feb 28, 2001
- Messages
- 30,558
I've had to do this a couple of times. It always boils back down to finding something in common between them.
Suppose, for example, that their commonality was a date and a code letter or digit.
You might try to create a single table that contains ONLY the date and code from both tables, merged to hold only the unique combinations. (I.e. remove duplicate combos.) Make this a separate table that has NO OTHER FIELDS. You could do this perhaps with a make-table query based on a union query, then run this table through a process of eliminating duplicates.
Write two more queries. Outer-Join your synthesized table to one of the original tables based on the selected key. Next, Outer-Join the OTHER table to the first query, again on the selected key. Be sure that you have Null/Zero protection for all fields in both tables.
Now you can display fields from these tables side-by-side, with some special character (or just a blank) for items that were not represented on one side or the other. If you only have a few fields, the side-by-side might even fit conveniently on a single screen. If it is more complex than that, you might have to write a query on the query for each combination you wanted to compare.
Or on the other hand, it might be that all you wanted to know was how many times the tables had records for the same prime keys, and a little bit of IIF logic would suffice for that one. Heck, you could even do the Union query and an overlaying Counts query to find it out if it is that simple.
Provided you have the ability to identify the common fields on which to base the outer joins, this should work for any pair of tables. Also please note that this does not assume that the prime key YOU choose had anything at all to do with the real prime keys of the original tables.
Suppose, for example, that their commonality was a date and a code letter or digit.
You might try to create a single table that contains ONLY the date and code from both tables, merged to hold only the unique combinations. (I.e. remove duplicate combos.) Make this a separate table that has NO OTHER FIELDS. You could do this perhaps with a make-table query based on a union query, then run this table through a process of eliminating duplicates.
Write two more queries. Outer-Join your synthesized table to one of the original tables based on the selected key. Next, Outer-Join the OTHER table to the first query, again on the selected key. Be sure that you have Null/Zero protection for all fields in both tables.
Now you can display fields from these tables side-by-side, with some special character (or just a blank) for items that were not represented on one side or the other. If you only have a few fields, the side-by-side might even fit conveniently on a single screen. If it is more complex than that, you might have to write a query on the query for each combination you wanted to compare.
Or on the other hand, it might be that all you wanted to know was how many times the tables had records for the same prime keys, and a little bit of IIF logic would suffice for that one. Heck, you could even do the Union query and an overlaying Counts query to find it out if it is that simple.
Provided you have the ability to identify the common fields on which to base the outer joins, this should work for any pair of tables. Also please note that this does not assume that the prime key YOU choose had anything at all to do with the real prime keys of the original tables.
Similar threads
