Access2000 SQL help

[wEnShEnG]

Hi guys,

There is a problem now. I'm currently using Access2000, two tables A & B respectively. The both tables contain one columns of data. What I am needed to do now is a query that results the difference in the data.

Eg. Table A contains 1,2,3 | Table B contains 1,3,5

The similar numbers: 1, 3
The different numbers: 2, 5

My query results need to show 2 & 5 as they are not in both the tables.

Can anyone enlighten me on this? I've tried Unmatch Query Wizard. But my results only shows 2 but not 5 because the left join only applies on one table - A.

Thanks for help in advance! Hope to hear from you guys real soon. This is a urgent problem. = )

 

[davidpcurtis]

This is not an easy process, but can be done. You will need to use VBA code to create 2 independent recordsets as well as a new record set which is a new tableto handle the differences and process them together. Set up both recordsets, move first record on both. Check if both records match, if they do read next record for both, if different, add to the third recordset the unmatched record. read the opposite recordset and continue processing untill both are exhausted of data. Treat this as if it was a master fiel update process. if you need code respond and I will give you a start.

 

[davidpcurtis]

try this code and expand:

apply to a form with a button labled command0

Option Compare Database
Option Explicit

Private db As DAO.Database
Private rst1 As DAO.Recordset
Private rst2 As DAO.Recordset
Private rst3 As DAO.Recordset

Private Sub Command0_Click()
Dim sqltxt As String
' change the msgbox's into append queries and your done
Set db = DBEngine.Workspaces(0).Databases(0)

sqltxt = " SELECT table1.* FROM table1 ORDER BY Field1"

Set rst1 = db.OpenRecordset(sqltxt)

sqltxt = " SELECT table2.* FROM table2 ORDER BY Field1"

Set rst2 = db.OpenRecordset(sqltxt)
rst1.MoveFirst

rst2.MoveFirst

Do Until rst1.EOF Or rst2.EOF
If rst1!field1 = rst2!field1 Then
rst1.MoveNext
rst2.MoveNext
ElseIf rst1!field1 > rst2!field1 Then
MsgBox rst2!field1
rst2.MoveNext
ElseIf rst1!field1 < rst2!field1 Then
MsgBox rst1!field1
rst1.MoveNext
End If
Loop

If Not rst1.EOF Then
Do Until rst1.EOF
MsgBox rst1!field1
rst1.MoveNext
Loop
Else
Do Until rst2.EOF
MsgBox rst2!field1
rst2.MoveNext
Loop
End If

End Sub

 

[dk]

As an alternative you could use a query. This uses a similar logic to that of the Unmatched Query wizard for each outcome (values in table 1 not in table 2 and vice versa) and then unions the two results:-

SELECT Table1.Field1 FROM Table1 LEFT JOIN Table2 ON Table1.Field1=Table2.Field1 WHERE Table2.Field1 IS NULL

UNION 

SELECT Table2.Field1 FROM Table2 LEFT JOIN Table1 ON Table1.Field1=Table2.Field1 WHERE Table1.Field1 IS NULL;

 

[wEnShEnG]

Hi davidpcurtis,

I've tried creating a form, after that add in a command button, right click go to build event, then to code builder.
I cut the codes u provided me but there's an error i don't understand.
take a look at below:

The expression On Click you entered as the event property setting produced the following error:
* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro.


How should i go about solving this error? i'm really new to this Access2000 thing, pls guide me through pls.. ray:

 

[dk]

Why don't you save yourself the hassle and use the query method? It's easier, quicker and more reliable. Post back if you need help implementing it...

 

[wEnShEnG]

hi, thanks again for replyin.

Just wanna tell u that i tried using union queries. but i reached 7th table and there was an error. "Query too complex!"

Do u have any other methods to go about for SQL?

 

[dk]

Hi,

In your original post you said you were comparing two tables. Are you actually comparing more than two tables?

It may be helpful for you to post details about your database:-

1. What are the names of the tables you're comparing?
2. What are the fields within those tables that you're comparing?
3. What is the SQL of the query which gave you the error?

 

[wEnShEnG]

1. table 1,2,3,4,5,6,7,8,9,10
2. Object ID [ all tables have this same field name]
3.

select [Different Component Numbers].[Object ID], [Different Component Numbers].[Object Description]
from [Different Component Numbers] LEFT OUTER JOIN [2] on [Different Component Numbers].[Object ID]=[2].[Object ID]
where [2].[Object ID] is null;

UNION select [2].[Object ID], [2].[Object Description]
from [Different Component Numbers] RIGHT OUTER JOIN [2] on [Different Component Numbers].[Object ID] = [2].[Object ID]
where [Different Component Numbers].[Object ID] is null;


UNION select [Different Component Numbers].[Object ID], [Different Component Numbers].[Object Description]
from [Different Component Numbers] LEFT OUTER JOIN [3] on [Different Component Numbers].[Object ID]=[3].[Object ID]
where [3].[Object ID] is null;

UNION select [3].[Object ID], [3].[Object Description]
from [Different Component Numbers] RIGHT OUTER JOIN [3] on [Different Component Numbers].[Object ID] = [3].[Object ID]
where [Different Component Numbers].[Object ID] is null;


UNION select [2].[Object ID], [2].[Object Description]
from [2] LEFT OUTER JOIN [3] on [2].[Object ID]=[3].[Object ID]
where [3].[Object ID] is null;

UNION select [3].[Object ID], [3].[Object Description]
from [2] RIGHT OUTER JOIN [3] on [2].[Object ID] = [3].[Object ID]
where [2].[Object ID] is null;



this query is error-free.. however when i continue till 8th table, the error speaks "Query too complex!"