query probably using NOT EXIST

[ronwmon]

I have a table that is my main table(titled Working). In addition to other fields I have a field titled AutomationID and another titled DealerID. I download this main table on a monthly basis. There are many records that do not have a DealerID.

I have a secondary table that I want to use to handle data I receive on a monthly bases that has no DealerID. When I get a record with no DealerID I need to manually insert the dealer ID in the main table. I want to use this secondary table to store records with both a AutomatinID and a DealerID. I can then use this table to update the main table and eliminate the excessive manual process.

I need a query that will look at the main table and select the records without a DealerID that is not already in the secondary table. I will then update the main table with the DealerID from the secondary table using the AutomationID to match the DealerID. My monthly data import always has the AutomationID.

I assume I need to use NOT EXIST in my query but I can’t seem to figure out how to do this properly.

Any assistance is appreciated.

Ron--

 

[Andrzejek]

I have a secondary table [...] that has no DealerID" and then "I want to use this secondary table to store records with both a AutomatinID and a DealerID"
But your 'secondary table' does not have a field DealerID...

Is there a relation between AutomatinID and a DealerID ?


---- Andy

There is a great need for a sarcasm font.

 

[ronwmon]

I want the secondary table to initially show me the records without DealerID's. We will add the correct DealerID and then use this table to update the main table with the correct DealerID. The AutomationID is actually the account number for the dealer account. All records have the AutomationID.

When I run the query I need to get only the AutomationID's that don't have an associated DealerID, thus the "NOT Exist" part.

Ron--

 

[Andrzejek]

[tt]SELECT AutomationID
FROM Working
WHERE DealerID IS NULL[/tt]

Maybe...?

It would be a lot easier to help you if you would show some sample data and the outcome you expect, something like:

[pre]
Working table
AutomationID DealerID
12 54
13
14 67
15
16 88

secondary table
AutomationID
10
11
12
13
[/pre]

And the outcome would be:
???

---- Andy

There is a great need for a sarcasm font.