Results 1 to 8 of 8
  1. #1
    thulot is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    12

    Comparing Two Databases and Reporting The Difference?

    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
    Attached Files Attached Files

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    thulot is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    12
    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.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    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
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    thulot is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    12
    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.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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/
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    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
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Reporting
    By licka in forum Reports
    Replies: 2
    Last Post: 11-29-2020, 05:41 PM
  2. Reporting
    By licka in forum Reports
    Replies: 4
    Last Post: 11-19-2020, 07:00 PM
  3. Replies: 4
    Last Post: 09-08-2015, 07:44 AM
  4. Replies: 2
    Last Post: 01-28-2015, 10:04 AM
  5. Replies: 2
    Last Post: 08-29-2013, 01:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums