Comparing Two Tables (1 Viewer)

[Satin]

Good Morning,
I am very new to Access. I currently do most of my work in Excel and am trying to switch over to Access as some of my data is too big for Excel. One of the things I do weekly is a review of people (compare two reports to see if they match). I do this in excel with lots of manipulations of the data and formulas. I understand this process but teaching it is not always easy and the new user who typically is a novice in Excel, gets overwhelmed and reverts back to printing the reports and highlighting the matches (time consuming, too much human error and waste of paper).

Anyways my Question(s) are:
See below an example of the data One report show Jane Doe with 2 units and the second report shows her with three. Also Mike Runner is on one report and not the other. I understand I can run a unmatch query in the query wizard and that would bring back Mike Runner. I also need to know there is an extra Jane doe. How do you suppose I go about this?

Things needed:
Are there any missing person(s) in any of the reports
If Yes which report
Does the number of persons match in each report
If No which report has too many

Report One Report Two
John Smith 1 Bedroom Ownership type John Smith 1 Bedroom Ownership type
Jane Doe 1 Bedroom Ownership type Jane Doe 1 Bedroom Ownership Type
Jane Doe 1 Bedroom Ownership Type Jane Doe 1 Bedroom Ownership Type
Jane Doe 1 Bedroom Ownership Type
Mike Runner 1 Bedroom Ownership type

The thing is this data doesn't need to be saved once it is done. Each week it is new data. can this be done and is it more difficult to do in Access than in Excel do you think?

I appreciate any thoughts on this.

Thank you, Satin

Report 1					Report 2
Doe, Jane	1 BR	Ownership Type			Smith, John	1 BR	Ownership Type
Doe, Jane	1 BR	Ownership type			Doe, Jane	1 BR	Ownership Type
Smith, John	1 BR	Ownership Type			Doe, Jane	1 BR	Ownership Type
					Doe, Jane	1 BR

 

[theDBguy]

Hi. I think comparing two sets of data is easier in Access than in Excel, but I may be bias because I don't use Excel much. However, sometimes, if you need to answer several questions out of the data, you may have to do them in separate steps.

 

[CJ_London]

not sure what your data is supposed to represent, you need to clarify - how is this supposed to 'line up'?

Report One Report Two
John Smith 1 Bedroom Ownership type John Smith 1 Bedroom Ownership type

you can add a table to your post - see the insert table button

In principle you would use a left join query but can't tell from your example what the individual fields are

 

[Ranman256]

each record should have a unique key (autonum) , if this is in the query, you should see each unique record.
don't limit it to just the name. (which can duplicate)

 

[zeroaccess]

If your data is standardized between the two, you should be able to run a duplicates query that shows which values exist in both tables. Is it okay if this is a two-step process? 1. Run unmatched query, 2. Run duplicates query?

 

[jdraw]

Reports derive their data from tables - so the comparison would be done based on query of the underlying tables/query(s).
More info required for more focused respsonses.

 

[Satin]

not sure what your data is supposed to represent, you need to clarify - how is this supposed to 'line up'?



you can add a table to your post - see the insert table button

In principle you would use a left join query but can't tell from your example what the individual fields are

I added a table. Sorry it took me a second to find it.

 

[Satin]

Hi. I think comparing two sets of data is easier in Access than in Excel, but I may be bias because I don't use Excel much. However, sometimes, if you need to answer several questions out of the data, you may have to do them in separate steps.

That makes sense. I am trying to get out of a data sheet set of mind into a database. Apparently not an easy step for me.

 

[Satin]

each record should have a unique key (autonum) , if this is in the query, you should see each unique record.
don't limit it to just the name. (which can duplicate)

That makes sense. Kind of like what I do with Excel. I have a count formula then a match formula to see if they match exactly.

 

[Satin]

If your data is standardized between the two, you should be able to run a duplicates query that shows which values exist in both tables. Is it okay if this is a two-step process? 1. Run unmatched query, 2. Run duplicates query?

That might work. let me play with it some more and see if that brings about the results I am after. Thank you.

 

[zeroaccess]

That makes sense. Kind of like what I do with Excel. I have a count formula then a match formula to see if they match exactly.

In Excel I do a MATCH, assign a number to each line on one side, then do a Sort that lines them all up. It's all recorded in a macro that is very specific to the input format. I've contemplated moving this to Access as I think it could do a better job, but it would take some work to set it all up and I can't really justify the investment.

 

[MajP]

One of the things I do weekly is a review of people (compare two reports to see if they match). I do this in excel with lots of manipulations of the data and formulas. I understand this process but teaching it is not always easy and the new user who typically is a novice in Excel, gets overwhelmed and reverts back to printing the reports and highlighting the matches (time consuming, too much human error and waste of paper).

I agree if your db is properly structured you should be able to solve a lot of these things using simpler queries. However, if you have a lot of novice users or different types of tasks like this, you may consider investing into something like Beyond Compare. Great tool for comparing tables, documents, folders etc. for changes. Works on a lot of different formats and relatively cheap. There are also freeware utilities for doing comparison.

 

[Satin]

I agree if your db is properly structured you should be able to solve a lot of these things using simpler queries. However, if you have a lot of novice users or different types of tasks like this, you may consider investing into something like Beyond Compare. Great tool for comparing tables, documents, folders etc. for changes. Works on a lot of different formats and relatively cheap. There are also freeware utilities for doing comparison.

Thank you so much. Yes a lot of what I do is compare reports. Sometimes the reports can be in the upward of 100K of data and that is when Excel begins to tell me no ma'am. I will continue to dabble in Access it seems to work great for my project management and look into Beyond Compare for my other needs.