Identifying incorrect entries in a table 1

[rj51cxa]

I have a table called AIMS in which there are two fields LOADPORT and TERMINAL. These refer to ports and terminals where oil cargoes are loaded. LOADPORT is entered using a lookup from a table called PORTS and TERMINAL is entered from a combo box which lists the terminals associated with that port, taken from a table called TERMINALS.

Table TERMINALS has two fields - PORT and TERMINAL. Port is entered using a lookup from Table PORTS.

The data in Table AIMS dates back to 1996 and was originally in a DBase table. This was converted to Access in 2002 and, consequently, a number of ports have incorrect terminal data associated with them.

I need to create a query that compares AIMS.LOADPORT with TERMINALS.PORT and lists entries in AIMS.TERMINAL which do not match any of the Terminals listed in TERMINALS.TERMINAL. I then need to be able to correct the entry in AIMS.TERMINAL.

I have tried using an Unmatched Query Wizard but this does not give me the information I need.

Any ideas would be most welcome.

Best Regards
John

 

[traingamer]

This (tested):

SELECT AIMS.AimsId, AIMS.PortID, AIMS.Terminal
FROM AIMS LEFT JOIN Terminals ON (AIMS.Terminal = Terminals.Terminal) AND (AIMS.PortID = Terminals.PortId)
WHERE (((Terminals.Terminal) Is Null) AND ((Terminals.PortId) Is Null));

will return the 'unmatched' records. It is not, however, an updateable query.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[rj51cxa]

Hi Traingamer

Thanks for the code, this works fine. I will find another way to update the errors.

One question - I have a number of entries, which are actually correct, but which do not feature in the Ports Table. These are operations where we transfer from one ship to another at sea and are entered manually. They all take the format [STS ex "Vessel Name"], or something similar. I would like to make the query ignore any entry starting with [STS] but I cannot find the right syntax.

I have tried - not like "STS" and not like "STS*.*" but they are still being selected. Is there any way I can do this?

Best Regards
John

 

[rj51cxa]

Hi Traingamer

I've managed to get around the problem by running your query as a Make Table Query and then running another query based on the new table, with the criteria [not like "STS*].

This work fine, so I can only assume that the "Is Null" and "Not Like" criteria were conflicting. Takes a little bit longer but it works.

Thanks for your help, star on the way.

Best Regards
John