Reference table (1 Viewer)

[pwbrown]

Hello, this should be easy but every thing I tried isn't working or only partly working.

Basically I got two tables, 1 main table with all my information and 1 with just values which are allowed in a column in the main table.
In a query I want to remove records where in the particular column the value is invalid eg. not in the reference table.
But an important point is that I want to accept blank values in the column too.

Any help is welcome, thanks.

Kind regards,

Peter

 

[RainLover]

There is a Query Wizard that will write this for you. Just follow the prompts.

 

[pwbrown]

Hello Rain thanks for your reply but I tried that already. The only problem is, it selects the blank records in the column too. I want the blank records to stay.

To give you a better understanding, I'm importing an excel file to access and doing validation checks to it. This field is a late reason code so will only be filled if a delivery is late. I want to select any records where the code does not match a code in another table but blank records are ok.

 

[RainLover]

Add "Not Null" to that Field's criteria.

Edit

Make that "Is Not Null" But it really doesn't matter as Access will correct it.

 

[pwbrown]

Ok so I did this using an append query so it puts the error files into another table. I had to use "Not "" " instead of "Is Not Null" but anyway I now want to delete those records from the main table after the append.
I tried but keep getting "specify the table containing the records you want to delete", obviously this is because I got two tables linked but how do I select my main table to delete from?

 

[RainLover]

Not sure of the easist way.

Can you create a new Query with two tables. The one you appended to and the one you appended from. Display only matching records and delete those by turning the query into a Delete query and only select records from the table where you want the records deleted.

The other choice is to post a cut down version of your Database.

Use 2003 or earlier.

 

[RainLover]

I remembered that I had a sample in my Library.

See if it helps.

 

[pwbrown]

Finally got it to work using parts of your help.
Thanks very much Rain, much appreciated.

Kind regards,

Peter