Report based on difference between two tables.

[egptech]

I have a coin inventory DB with the following tables;

tblCoins - Holds all possible coin types available for this collection
ID - Autonumber - Unique ID
Country - Text- Country of mint
CoinType - Text- Denomination and type of coin
Year - Text- Year and Mint mark
Remarks - Memo- Notes on individual Coin

tblCollection - Holds all coins actually in this collection
CoinID - Autonumber - Unique ID
Country - Text - Country of mint
CoinType - Text - Denomination and type of coin
Year - Text - Year and Mint mark
Grade - Text - Grade of Coin
Value - Currency - Appraised value of coin
Remarks - Memo - Notes on Coin in collection

What I wish to do is compare both tables and output a report showing the coins I need to fill the collection. Specifically, if the coin is in tblCoin and not in tblCollection, add to report. I imagine a query will accomplish this task but it is currently beyond my skills to create it.

Thank you for any assistance,
Gene

 

[jrbarnett]

Hi,

Create a query using the Find Unmatched wizard which will return the data you need.
Then, base a report on this query.

John

 

[egptech]

Thanks John,

Shortly after I posted I found that Find Unmatched wizard and tried it. It did just what I wanted. All I can say is don't try working on anything important when you have just come from a root canal. It tends to cloud your mind, vision, etc.
Mea Culpa,
Thanks,
Gene

 

[egptech]

A curious thing occurred with the above solution. The query "loses" a couple of hundred records. For example; There is a series of silver dollars called "American Eagle Silver Dollars" with about 18 coins in the series. I don't have any of them in the collection but the query says I am only missing 5 of them. I think that it is time for a complete re-design of the whole DB.

 

[jrbarnett]

egptech,

Were you using the two autonumber fields in the tables as primary keys, and therefore means to join the two together in the query?

If you were, then this is the cause of the problem: because there is no other ID that uniquely identifies the coins, the same coins in each table cannot be guaranteed to have the same ID.
Let me know if this is the case and I will suggest a workaround for this.

John

 

[egptech]

John,
Thanks for the assist. I had already re-written the offending section and the problem went away. I had already deleted the old queries, tables and reports so I don't know if your suggestion was the problem but the new stuff works perfectly and I only have the tables joined at the CoinTypeYearAndMint field. The tbleCoin uses the CoinTypeYearAndMint as a key and the tblCollection uses an Autonumber field as the key. As you suggested, I think I was using the autonumber as the key in both the old tables. Everything is now working as desired (and I did rewrite the whole thing).
Thanks again,
Gene

P.S.
Just a note of curiosity. I put a calculated field in my reports. One is =COUNT([CoinTypeYearAndMint]) and the other is =SUM([Value]). If I put the fields in the Report Header they both work as expected BUT, if I put the field in the Page Header section so I can see the total on each page I get #error. Is this a bug in Access 2K or am I missing something?