MS ACCESS query

[muralibox]

Below is the sample SQL we basically use to compare fields from two tables
and display the result. However when I have to convert this to MS ACCESS
query, I found it difficult as SELECT CASE works differently in MS ACCESS. I
was able to compare just one condition and throw result using below MS ACCESS
syntax.


SQL

Select Table1.Field1, Table1.Field2, Table1.Field3, Table1.Field4, Table1.
Field5, Table2.Field1, Table2.Field2, Table2.Field3, Table2.Field4,Table2.
Field5,
Case when Table1.Field1<>Table2.Field1 then ‘Field1_not_matched’
when Table1.Field2<>Table2.Field2 then ‘Field2_not_matched’
when Table1.Field3<>Table2.Field3 then ‘Field3_not_matched’
when Table1.Field4<>Table2.Field4 then ‘Field4_not_matched’
else ‘All_Matched’
END AS result
From table1 inner join table 2 on table1.field5=table2.field5


MS ACESS

IIF(Table1.field1=Table2.Field1,’Field1_matched’,’Field1_Not_matched’) AS
result

I want to see the result more appropriate, if field1 not matched my result
column should indicate field1 not matched and if field2 is not matched then
my result column should indicate field2 not matched and so on. In case more
than one field not matched in a single row then it should display the first
mismatched field message. This is how the SQL query works and I want to see
this code converted to MS ACCESS.

So the query result should look like this

Table1.field1 Table1.field2 Table1.field3 Table1.field4 Table2.Field1 Table2.
field2 Table2.field3 Table2.field4 Result
123 Sarala Palos Hills IL 123 Sarala Palos Hills IL All_Matched
111 Ashok Columbus OH 110 Ashok Columbus OH Field1_notmatched
453 Bhar Orlando FL 444 Bhargavi Orlando FL Field1_notmatched
422 Ragu Auroraa IL 422 Ragu Aurora FL Field3_notmatched


Please please give me a solution for this .............

my email is (e-mail address removed)

thanks for your Help.

 

[John Spencer]

You can nest IIF statements or you can use the Switch function

IIF(Table1.Field1 <> Table2.Field1,"Field 1 Not Matched",
IIF(Table1.Field2 <> Table2.Field2,"Field 2 not matched",
IIF(table1.field3<>Table2.Field3,Field 3 not matched","All Matched")))

The switch function is a vba function that can be used in a query.

SWITCH(Table1.Field1 <> Table2.Field1,"Field 1 Not Matched"
, Table1.Field2 <> Table2.Field2,"Field 2 not matched"
, table1.field3<>Table2.Field3,Field 3 not matched"
, table1.field4<>Table2.Field4,Field 4 not matched"
,True, "All Matched')

Or you could write a VBA function that would allow you to compare the
sets of fields and return a string that reports field 1 AND field 3 did
not match.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Access doesn't use the CASE statement, it can use the IIF() function
instead. E.g.:

IIf(Table1.Field1<>Table2.Field1,‘Field1_not_matched’,
IIf(Table1.Field2<>Table2.Field2,‘Field2_not_matched’,
IIf(Table1.Field3<>Table2.Field3,‘Field3_not_matched’,
IIf(Table1.Field4<>Table2.Field4,‘Field4_not_matched’,
‘All_Matched’)))) AS result

The above is a multiple nested IIf() function which, I believe, matches
your CASE statement.

IIf() format:

IIf(comparison expression, expression if true, expression if false)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSRopXIechKqOuFEgEQKsygCgwGyyZ8ID+7MfiUrw3mdpm7bn4KAAnj/A
CX9EWwFKrzUb+WyOBXWuEH2B
=N2Nv
-----END PGP SIGNATURE-----

 

[muralibox via AccessMonster.com]

thank you very much John.

Could you please give me details about the vba function ?

Murali

You can nest IIF statements or you can use the Switch function

IIF(Table1.Field1 <> Table2.Field1,"Field 1 Not Matched",
IIF(Table1.Field2 <> Table2.Field2,"Field 2 not matched",
IIF(table1.field3<>Table2.Field3,Field 3 not matched","All Matched")))

The switch function is a vba function that can be used in a query.

SWITCH(Table1.Field1 <> Table2.Field1,"Field 1 Not Matched"
, Table1.Field2 <> Table2.Field2,"Field 2 not matched"
, table1.field3<>Table2.Field3,Field 3 not matched"
, table1.field4<>Table2.Field4,Field 4 not matched"
,True, "All Matched')

Or you could write a VBA function that would allow you to compare the
sets of fields and return a string that reports field 1 AND field 3 did
not match.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Below is the sample SQL we basically use to compare fields from two tables
and display the result. However when I have to convert this to MS ACCESS

[quoted text clipped - 41 lines]

thanks for your Help.

 

[John Spencer]

You could try this one. It does not return the names of the fields, but the
position. You could probably modify it to return something else. Copy the
code and paste it into a VBA module. Save the module with a name other than
the name of any procedure. I usually use something like modUtilityFunctions.

You would call it in a query as follows

fMatchPairs(Table1.Field1, Table2.Field1, Table1.field2, table2.field2,
Table1.field3, table2.field3,Table1.field4, table2.field4)


'=================== Code Starts ============================
Public Function fMatchPairs(ParamArray ValuePairs()) As String
'Limit in a query of 14 pairs
'Pairs of items should be of the same data type.
Dim I As Long
Dim strReturn As String

If UBound(ValuePairs) - LBound(ValuePairs) Mod 2 = 0 Then
strReturn = "Uneven number of items to compare"
Else
For I = LBound(ValuePairs) To UBound(ValuePairs) Step 2
If ValuePairs(I) = ValuePairs(I + 1) Or _
IsNull(ValuePairs(I)) And IsNull(ValuePairs(I + 1)) Then
'Matched
Else
strReturn = strReturn & ":" & (I \ 2) + 1
End If
Next I
End If

If Len(strReturn) = 0 Then
strReturn = "All matched"
ElseIf strReturn Like ":*" Then
strReturn = "Following pairs unmatched " & Mid(strReturn, 2)
End If

fMatchPairs = strReturn
End Function
'========================= Code Ends ============================


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

thank you very much John.

Could you please give me details about the vba function ?

Murali

You can nest IIF statements or you can use the Switch function

IIF(Table1.Field1 <> Table2.Field1,"Field 1 Not Matched",
IIF(Table1.Field2 <> Table2.Field2,"Field 2 not matched",
IIF(table1.field3<>Table2.Field3,Field 3 not matched","All Matched")))

The switch function is a vba function that can be used in a query.

SWITCH(Table1.Field1 <> Table2.Field1,"Field 1 Not Matched"
, Table1.Field2 <> Table2.Field2,"Field 2 not matched"
, table1.field3<>Table2.Field3,Field 3 not matched"
, table1.field4<>Table2.Field4,Field 4 not matched"
,True, "All Matched')

Or you could write a VBA function that would allow you to compare the
sets of fields and return a string that reports field 1 AND field 3 did
not match.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Below is the sample SQL we basically use to compare fields from two tables
and display the result. However when I have to convert this to MS ACCESS

[quoted text clipped - 41 lines]

thanks for your Help.

 

[muralibox via AccessMonster.com]

thank you very much.

Access doesn't use the CASE statement, it can use the IIF() function
instead. E.g.:

IIf(Table1.Field1<>Table2.Field1,‘Field1_not_matched’,
IIf(Table1.Field2<>Table2.Field2,‘Field2_not_matched’,
IIf(Table1.Field3<>Table2.Field3,‘Field3_not_matched’,
IIf(Table1.Field4<>Table2.Field4,‘Field4_not_matched’,
‘All_Matched’)))) AS result

The above is a multiple nested IIf() function which, I believe, matches
your CASE statement.

IIf() format:

IIf(comparison expression, expression if true, expression if false)

Below is the sample SQL we basically use to compare fields from two tables
and display the result. However when I have to convert this to MS ACCESS

[quoted text clipped - 41 lines]

thanks for your Help.