Comparing two tables (1 Viewer)

[yus786]

Hi all

I have 2 identical tables with different data.

They have both been imported from excel as I couldn’t do this in excel.

Table 1 has data from Dec Table 2 has data from Jan

Fields are: EENo, Code, Date (Fixed either Dec or Jan), Value

Table 1 will have Decembers Value and Table 2 will have Januarys Value. Table 2 may also have some NEW EEno

What I would like to do is compare both tables and take the Value from each and take one away from the other.

Example: Table 1 Value is 1000 for EE1 and Table 2 Value is 500 for EE1, so I would like maybe a 3rd table which will have the difference i.e. EE1 Value = 500

Is this possible?

Thanks

 

[MSAccessRookie]

A Left Join between Table2 and Table1 will create a dataset that contains all of the details from records that are in both Tables, and when there is no record in Table1, that information would be NULL. An IIf() Query would do the rest. Something like the following should be a good start. You can make any required changes.

SELECT Table2.EENo, IIf(Table1.EE1 IS Null, Table2.EE1, Table1.EE1-Table2.EE1)
FROM Table2 LEFT JOIN Table1 ON Table2.EEno=Table1.EENo

 

[plog]

Is this a one time manipulation or are you going to be doing this monthly? Will February's data have to be worked in a similiar manner?

 

[yus786]

Is this a one time manipulation or are you going to be doing this monthly? Will February's data have to be worked in a similiar manner?

Hi, this will need to be done every period with new data. So next Period i'll be comparing Jan with Feb

Thanks

 

[yus786]

A Left Join between Table2 and Table1 will create a dataset that contains all of the details from records that are in both Tables, and when there is no record in Table1, that information would be NULL. An IIf() Query would do the rest. Something like the following should be a good start. You can make any required changes.

SELECT Table2.EENo, IIf(Table1.EE1 IS Null, Table2.EE1, Table1.EE1-Table2.EE1)
FROM Table2 LEFT JOIN Table1 ON Table2.EEno=Table1.EENo

Thanks for this, but i have over 3000 ee's. The EE1 was an example.

How would this query work for all ee's?

Thanks for your help

 

[plog]

Once you get February's data, do you care about December's any more? Will you need it again?

Also, could you post some sample data?

 

[MSAccessRookie]

Thanks for this, but i have over 3000 ee's. The EE1 was an example.

How would this query work for all ee's?

Thanks for your help

I cannot answer that without additional information. In my initial response I said "Something like the following should be a good start.". I said that because I had no additional information to work with at the time.

If you mean you have 3000 Rows, then what I said is still going to be helpful. If you have 3000 Columns, I would like to see that because I did not think that was possible. Either way, I cannot tell unless I see at least the layout of your data, if not some samples as well. The more specific the information that is available, the more specific the answer that could be possible.

-- Rookie

 

[yus786]

Thanks for all your help everyone. Here is a sample.

 

[plog]

If you paste this SQL into a query in the sample database you provided it will do what you asked:

SELECT Dec.[Employee No], Dec.Code, [Jan].[Value]-[Dec].[Value] AS Difference
FROM [Dec] INNER JOIN Jan ON (Dec.Code = Jan.Code) AND (Dec.[Employee No] = Jan.[Employee No]);

Here's what it doesn't do--include any records that are in Jan but not in Dec or vice versa.

 

[yus786]

If you paste this SQL into a query in the sample database you provided it will do what you asked:

SELECT Dec.[Employee No], Dec.Code, [Jan].[Value]-[Dec].[Value] AS Difference
FROM [Dec] INNER JOIN Jan ON (Dec.Code = Jan.Code) AND (Dec.[Employee No] = Jan.[Employee No]);

Here's what it doesn't do--include any records that are in Jan but not in Dec or vice versa.

Thanks Plog

I need to highlight those in Dec and not in Jan and vice versa. Any idea's how that can be done?

 

[MSAccessRookie]

If you paste this SQL into a query in the sample database you provided it will do what you asked:

SELECT Dec.[Employee No], Dec.Code, [Jan].[Value]-[Dec].[Value] AS Difference
FROM [Dec] INNER JOIN Jan ON (Dec.Code = Jan.Code) AND (Dec.[Employee No] = Jan.[Employee No]);

Here's what it doesn't do--include any records that are in Jan but not in Dec or vice versa.

Alter my previous post to account for the new information provided here, we get the following:

SELECT [Jan].[Employee No], [Jan].Code, IIf([Dec].[Value] IS Null, [Jan].[Value], [Dec].[Value]-[Jan].[Value]) AS Difference
FROM [Jan] LEFT JOIN [Dec] ON ([Jan].[Employee No] = [Dec].[Employee No]) AND ([Jan].Code = [Dec].Code);

Is this more to your liking?

NOTE: If you want to Highlight new items for January, they will be the ones in the dataset that have NULL values for December.

 

[plog]

I need to highlight those in Dec and not in Jan and vice versa. Any idea's how that can be done?

Define 'highlight'. What data do you want to show for those records?

 

[yus786]

Alter my previous post to account for the new information provided here, we get the following:

SELECT [Jan].[Employee No], [Jan].Code, IIf([Dec].[Value] IS Null, [Jan].[Value], [Dec].[Value]-[Jan].[Value]) AS Difference
FROM [Jan] LEFT JOIN [Dec] ON ([Jan].[Employee No] = [Dec].[Employee No]) AND ([Jan].Code = [Dec].Code);

Is this more to your liking?

NOTE: If you want to Highlight new items for January, they will be the ones int he dataset that have NULL values for December.

Thanks, i'll try this later

 

[yus786]

Define 'highlight'. What data do you want to show for those records?

Those in DEC and NOT in Jan are leavers who left in Jan. And those in Jan but NOT in Dec are new starters in Jan

Hope this makes sense.

 

[plog]

Leavers SQL:

SELECT Dec.*
FROM [Dec] LEFT JOIN Jan ON (Dec.Code = Jan.Code) AND (Dec.[Employee No] = Jan.[Employee No])
WHERE (((Jan.[Employee No]) Is Null));

New Starters SQL:

SELECT Jan.*
FROM Jan LEFT JOIN [Dec] ON (Jan.Code = Dec.Code) AND (Jan.[Employee No] = Dec.[Employee No])
WHERE (((Dec.[Employee No]) Is Null));

 

[yus786]

Leavers SQL:

SELECT Dec.*
FROM [Dec] LEFT JOIN Jan ON (Dec.Code = Jan.Code) AND (Dec.[Employee No] = Jan.[Employee No])
WHERE (((Jan.[Employee No]) Is Null));

New Starters SQL:

SELECT Jan.*
FROM Jan LEFT JOIN [Dec] ON (Jan.Code = Dec.Code) AND (Jan.[Employee No] = Dec.[Employee No])
WHERE (((Dec.[Employee No]) Is Null));

Thanks Plog, works very well.