Alphanumeric Field Query

[GoingCrazy]

Just wondering if someone might have the answer...
I have a table filled with drawing numbers and descriptions and it has a revision level field that is alphanumeric. The table lists each drawing number several times depending on the number of revisions. So for example, it may look like:

Dwg No. Description Rev
123456 System Installation A
123456 System Installation A1
123456 System Installation B

What I want to do is be able to compare a table with information that was collected yesterday to a table with today's info and determine if there is a new drawing revision level.

I've tried the No match query. I've tried an IIf function that would put OK in a column if the records matched or ALERT if they didn't match. Both methods seem to add a duplicate record just to put OK or ALERT in the block. I currently have a query that tells me how many matching records there are and I tried comparing it to the original table. There are only three records that don't match (looking at the list manually) but I can't seem to find the right query to pull just those three records.

Any assistance would be greatly appreciated.

Thanks

 

[Databaseguy]

Perhaps switching to VBA and using the Option Compare Text declaration ?

 

[MichaelRed]

If you must do it with 'yesterdays news', then the 'unmatched query' IS the correct approach.

I my opinion, you really should just add a date field to the db, and have it just be defaulted to 'NOW'. The query would then only need to look at a single table to find all of the records added the previous day (or for any time interval you choose).

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[gol4]

This should be a piece of cake.
place both tables you want on the QBE grid
link table1 (yesterdays table) to table2(todays data)on 2 fields
link table1 drawing number to table 2 drawing number
link table1 revnum to table2 revnumber
click on view - join properties
click on the box that show view all records from table2 and only the records that are equal from table 1

now you want to drag in the all the fields from table 2
and then under revnum place IS NULL as the criteria

this should bring in only the records that there is no match for from yesterdays data in other words records that were added since then.

 

[GoingCrazy]

Thanks very much for the help. I will try each of the suggestions and see which works best for me.