Comparing two tables.

[elmister]

Hi, I need to create a query that will compare both tables and just send out the records that dont match to a report.



Table1 asset#,Serial#
Table2 asset#,Serial#


query that checks that in Table1 the asset OR the Serial match the what is in Table2. and if it does not find a match to send it to a report.

please help. i have tried everything.


thank you for your time.

[TheShabz]

May I ask why you have 2 tables. It looks like both contain the same details. If you can combine them into one, you wont have this kind of issue.

You can try:

SELECT [asset#], [Serial#]
FROM table1
WHERE table1.[asset#] Not in (SELECT [asset#] from table2)
UNION
SELECT [asset#], [Serial#]
FROM table1
WHERE table1.[Serial#] Not in (SELECT [Serial#] from table2)
UNION
SELECT [asset#], [Serial#]
FROM table2
WHERE table2.[asset#] Not in (SELECT [asset#] from table1)
UNION
SELECT [asset#], [Serial#]
FROM table2
WHERE table2.[Serial#] Not in (SELECT [Serial#] from table1)

You can treat the UNION part as a separator. With that you'll see 4 chunks of SQL. The first gives you records where asset# is in 1 but not in 2. The second gives you Serial# that is in 1 but not 2. The third gives you asset# in 2 but not one and the fourth gives you Serial# in 2 but not 1.

[Robeen]

Here's another way of doing what you want:

Create a query that selects all the records in Table1.
Create a query that selects all the records in Table2.

Create a third Query and pull query1 & query2 into it - in that order.

Create a join between two matching fields in the two tables by dragging the matching field from the left table to the matching field in the right table.

Right-click the join line & click Join Properties.

Select 2 [Selects all rows from the left & matching rows from right 'tables'].

Drag all the fields from query1 into your query and the Asset# & Serial# from query2 [later you can uncheck these two in design view so they don't show - right now they will give you confirmation if you want].

In the Criteria row for Query2.Asset# type in Is Null.
In the same Criteria row - for Query2.Serial# type in Is Null.

Run the Query. You should get all rows from Table1 that have no matching Serial# NOR Asset# in Table2.

All the Table2 Asset#s & Serial#s should be blank.

Rows that have a Table2 Asset# or Serial# will not show.

[TheShabz]

What about all the records in table2 that are not in table 1?

[Robeen]

I was going off this statement in the original post:

query that checks that in Table1 the asset OR the Serial match the what is in Table2. and if it does not find a match to send it to a report.

I got the impression that the only requirement was that if there is no match for a Table1 Asset# or Serial# - then display it. Not the other way round as well.

[TheShabz]

Touche. OP, if that's the case, use what Robeen said and ignore what I put =]

[elmister]

Well reasons for having two tables is that one table will be entered by one user and the other table will be data imported from a execl sheet from another department.

I need to compare both. and take out what doesnt match.

[elmister]

Robeen your right i just need the ones that dont match. But thank you TheShabz that code will be saved for future use. Robeen I will try this and let you know . thank you for your time. and god bless.

[elmister]

Robeen thank you it worked.

[Robeen]

You're welcome! I'm happy that I was able to help.

[elmister]

Yes Robeen, thank you very much. Am new to access but am loving it more and more. I just need to a quick boost here and there to keep going. thank you.

I hate to ask, How would I make a unbound textbox on a form that will flash green if the data entered in field matches a value in Table. Or flash red if it doesnt find a match. In other words . if the user enters data in field1. the unbound field compare that data with a table and if it finds a match it just turns background green. or red if no match.


I tried the conditional formating but i think my expressions are off.

[Robeen]

I have only used conditional formatting in Reports. Never tried to use it on Forms.

My first instinct would be to write code to:

1. Open a recordset and loop through it looking for your matching data in the appropriate field.
It looks something like this - but you'll have to fill in real logic:

 
Dim db As DAO.Database
Dim rs As DAO.Recordset
 
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
 
With rs
Do While Not rs.EOF
'Do your processing here. Eg:
'Get the value in the field you are searching in the current record.
strValueFromDB = rs![FieldName]
 
'Compare value on form with field name to see if there's a match.
If strValueFromDB = Me.ControlNameOnForm Then
 
'Code to change BackColor to Green
'Match found - so Close up shop & Exit.
 
End If 
 
.MoveNext 'Move to next row of data
 
Loop
 
End With
rs.Close
Set db = Nothing

** This is just guidelines - not fully operational code.

2. If a match is found, then change the BackColor property of the textbox to green.
Usually, if you type in 'Me.ControlName.' - as soon as you type in the last dot, Access will drop down a list of properties that you can modify for that control. Scroll through the list and find BackColor. You should end up with something like Me.ControlName.BackColor = vbGreen

3. If no match is found, then change the BackColor property of the textbox to red.

I hope this gives you a starting point.