INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Unmatched data checking for two text fields (CRXI)

Unmatched data checking for two text fields (CRXI)

(OP)
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

RE: Unmatched data checking for two text fields (CRXI)

What type of database is this?

-LB

RE: Unmatched data checking for two text fields (CRXI)

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

RE: Unmatched data checking for two text fields (CRXI)

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

I will try the code above. Thank you


RE: Unmatched data checking for two text fields (CRXI)

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

RE: Unmatched data checking for two text fields (CRXI)

(OP)
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.


RE: Unmatched data checking for two text fields (CRXI)

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close