Unmatched data checking for two text fields (CRXI)

[0sprey]

CR XI

I have a database that contains two tables and each table contains two only text fields.

tbl_Vendor contains the fileds {tbl_V.Serial} and {tbl_V.Part}
tbl_Inventory contains the fields {tbl_I.Serial} and {tbl_I.Part}

The serial and part numbers within these tables should be an exact match set, however, due to human error over time (typos, etc.) they are not.

I am trying to design a report that will only display those records where either the serial numbers or the part numbers are not an exact match.


Perhaps a selection criteria something like :


({tbl_V.Serial} <> {tbl_I.Serial}) OR ({tbl_V.Part} <> {tbl_I.Part})




or perhaps a formula to display, to the left of the 4 data files, that would flag the unmatched records like :


If ({tbl_V.Serial} <> {tbl_I.Serial}) OR ({tbl_V.Part} <> {tbl_I.Part}) then " "Unmatched Record"




However, at the base level I am not sure how the two tables should be joined to start with...


Any suggestions welcomed... Thanks

 

[lbass]

What type of database is this?

-LB

 

[lbass]

Create a command to use as your datasource. The following is the SQL syntax for an Oracle database:

select 'Vendor' "Type",
"tbl_V"."Serial" "Serial",
"tbl_V"."Part" "Part"

From "tbl_V" "tbl_V"
left outer join "tbl_I" "tbl_I" on
"tbl_V"."Serial"="tbl_I"."Serial" and
"tbl_I"."Serial" is null

Union all

select 'Vendor' "Type",
"tbl_V"."Serial" "Serial",
"tbl_V"."Part" "Part"

From "tbl_V" "tbl_V"
left outer join "tbl_I" "tbl_I" on
"tbl_V"."Part"="tbl_I"."Part" and
"tbl_I"."Part" is null

Union all

select 'Inventory' "Type",
"tbl_I"."Serial" "Serial",
"tbl_I"."Part" "Part"

From "tbl_I" "tbl_I"
left outer join "tbl_V" "tbl_V" on
"tbl_I"."Serial"="tbl_V"."Serial" and
"tbl_V"."Serial" is null

Union all

select 'Inventory' "Type",
"tbl_I"."Serial" "Serial",
"tbl_I"."Part" "Part"

From "tbl_I" "tbl_I"
left outer join "tbl_V" "tbl_V" on
"tbl_I"."Part"="tbl_V"."Part" and
"tbl_V"."Part" is null

Then place the type, serial, and part numbers on the report to see where there is missing data. If one of the elements is incorrect, there won't be a match, resulting in a null.

-LB

 

[0sprey]

The database in this case is MS Access and both tables are connected to CRXI.

I will try the code above. Thank you

 

[lbass]

Try this syntax:

select 'Vendor' as Type,
`tbl_V`.`Serial` `Serial`,
`tbl_V`.`Part` `Part`

From `tbl_V` `tbl_V`
left outer join `tbl_I` `tbl_I` on
(
`tbl_V`.`Serial`=`tbl_I`.`Serial` and
`tbl_I`.`Serial` is null
)

Union all

select 'Vendor' as Type,
`tbl_V`.`Serial` `Serial`,
`tbl_V`.`Part` `Part`

From `tbl_V` `tbl_V`
left outer join `tbl_I` `tbl_I` on
(
`tbl_V`.`Part`=`tbl_I`.`Part` and
`tbl_I`.`Part` is null
)

Union all

select 'Inventory' as Type,
`tbl_I`.`Serial` `Serial`,
`tbl_I`.`Part` `Part`

From `tbl_I` `tbl_I`
left outer join `tbl_V` `tbl_V` on
(
`tbl_I`.`Serial`=`tbl_V`.`Serial` and
`tbl_V`.`Serial` is null
)

Union all

select 'Inventory' as Type,
`tbl_I`.`Serial` `Serial`,
`tbl_I`.`Part` `Part`

From `tbl_I` `tbl_I`
left outer join `tbl_V` `tbl_V` on
(
`tbl_I`.`Part`=`tbl_V`.`Part` and
`tbl_V`.`Part` is null
)

-LB

 

[0sprey]

Thank you again.

I only have experience running 'built-in' Crystal Reports queries.
So I am in the process of reading thru this....

http://www.hexcentral.com/articles/crystal-sql.htm

... and perhaps a few others before I start adding the SQL above to the report.

 

[lbass]

When you go to select tables for the report in the database editor, instead choose “Add command” (at the top of the table list) and then copy and paste the command I wrote into it. Then just place the fields on the report. You should not add any tables directly into the report.

-LB