Comparing Two Databases and Reporting The Difference?

[thulot]

Hello, everyone! I hope you and your families are all healthy.

May I please ask an Access question? Thank you.

I have two Access databases containing data I imported from Excel spreadsheets.

The first contains my entire music collection. The second contains the music I have in my car. (See the attached .zip file.)

I was hoping there is a way to link or bind these two databases together, and have Access compare them, then generate a report (or table) that displays all the records that are not in the CAR database.

Please allow me to elaborate. Here's an example:

I have ten Steely Dan albums in my Master collection database:

1. Aja
2. Alive in America
3. Can't Buy a Thrill
4. Countdown to Ecstasy
5. Everything Must Go
6. Gaucho
7. Katy Lied
8. Pretzel Logic
9. Royal Scam
10. Two Against Nature

In my Car database, I have four Steely Dan albums:

1. Aja
2. Everything Must Go
3. Pretzel Logic
4. Two Against Nature

I'd like Access to compare the records, and generate a report or table that shows this:

1. Alive in America
2. Can't Buy a Thrill
3. Countdown to Ecstasy
4. Gaucho
5. Katy Lied
6. Royal Scam

Actually, of course, I'd want the artist's name along with the album title.

I want this done for the entire database, not just one artist. I hope this makes sense.
Is this possible? If so, is it easy? Difficult? Will I tear my hair out trying to figure out how to do it? I'm not really much of a db designer or expert, but I'm willing to give this a go if it can be done.
Thank you! T. Hulot

PS: I am using Office 365 on Windows 10

[Micron]

What will you do once you know the difference? If you're trying to amalgamate it would be easier to create a new db (this should be a back end only where the tables are - aka split database) and import tables from one db into it. Make sure the table(s) have a unique index(es) so that you cannot append the records you already have that are in the other db. Then use an append query to append the records that are not in the new db and you're done?

One mistake you made might have been to have multiple db's for the same purpose. Another might be that you didn't split your db, but that's just a hunch.

[thulot]

Thank you for replying. The purpose of this whole exercise is for me to know what albums are not in my car, so it will be easier for me to know what albums to add to my car music. Honestly, that's all this is about. I believe it will be easier for me to look at a report/table/query of what's NOT in my car collection, then have to go back and forth and see what's not there manually. That's all this is for.

I didn't do anything with these databases other than import the data from Excel. I just wanted the tables displayed so you can have an idea of what I want. What I plan on doing, as you said, was make one database with two tables.

I don't think I've ever done an append query, so I'll have to mess around with it and see how that turns out. I think what I'll do is first create a sample database with just a few records in it, and then experiment with it then.

You're saying each table should have its own set of primary keys? Thanks again.

[Micron]

If you want to end up with one db (which makes sense) I'd do what I posted. As to the last question, IMO every table ought to have a PK, but I didn't say PK, I said unique index. That will allow you to meld the records without repeating any. Possibly you'd want to do that as a composite index, using artist and album together because it's possible that more than one artist might have an album title that is the same as someone else's.

Regardless, if you still need to know what's missing, use the query wizard to create a "Find Unmatched" query. The learning will come in handy no doubt.

[Gicu]

Please have a look at the attached file, is that what you are looking for. I see Micron beat me to it, it is the Unmatched query in the query wizard (but you need to edit it manually to add the album and artist as the unmatched pair).

Cheers,
Vlad

[thulot]

Micron and Gicu,

Would you be upset with me or angry if I asked you to wipe the slate clean and start from scratch? I came up with an idea that might--not sure, but might--make this much easier for me. I hope you'll agree.

Instead of having two separate tables, how about I have just one master table with my entire collection, and I add one field to it, named CAR.

And how about I make it a Yes/No field?

So, if an album in the master collection is in my car collection, I simply put an X in the Yes/No "Car" field?

If the album is not in my car, then of course, I leave that field blank.

If I do this, then shouldn't it be fairly easy to create a query to search for all of the records that have an X in the Car field? That would create a list of everything in my car, wouldn't it?

More importantly, if it's possible, couldn't I create a query that displays all of the records that are blank in the Car field? That would give me a list of everything in my collection that isn't in my car…which is what I want.
I could then generate reports based on these queries, or just look at the query results. (If SQL is required, I'm sure I could request assistance with it on this forum. If not, great!)

If I want to add an album to my car, I put an X in that album's record. If I want to remove an album, I remove the X. And then I update the queries.

Is this a good idea? A bad one? Do you like it? Dislike it? What do you think?

Do you have any suggestions that might make this idea even better? (Assuming it's halfway decent to begin with. If not, please let me know. I can take the criticism.)
Thank you again for your assistance. And please feel free to call me Jack.

[Micron]

I have no idea of your db structure so any reference I made to tables (plural) was based on a normalized schema. I didn't mean to imply that you would have more than one table for any one thing. That is not a good approach.

Ideally your db should have a table for each entity. In this case I suspect that would be one for the music listing and one for the location. You'd have a 3rd (junction table) to show which is where, especially since it would be possible to have the same music in 2 places. If down the road you come up with a 3rd location (cottage) then your current idea will not allow expansion for locations, nor could your y/n field work any more. FWIW, yes/no fields often make for poor choices and this might be one of them.

Alternatively, you could simply have a Location field in your music table and put "car", "house", "cottage" etc in that field. For the sake of simplicity that's probably what I'd do rather than have tblLocations with what will probably only be 2 or 3 records in its entire life time. Some worthwhile reading on the subject if you're not up to speed:

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

[Gicu]

Hi Jack,

Here you go, much easier like this to keep track of what's where.

EDIT: Agree with Micron that a location lookup table and a join table would be a much more accomodating approach, for strictly your current need (car only) your current approach would be easier to maintain.


Cheers,
Vlad