Hello, I use an Access database

[ronwmon]

Hello,

I use an Access database to calculate fees that I need to charge my dealers for service provided by another company.

We have dealers and dealers have accounts. We assign a dealer number to each of our dealers. We use a provider that provides a service to our dealers. The provider charges us a fee based on what the dealer sets up as a service on a per account basis. The fee differs from account to account.

When a dealer sets up an account (automationid) with the provider they don’t always use their dealer number that we assigned to them. Often they use their name or something different.

We have to manually edit the dealer number so we know who to bill for the service. We will do this by adding the correct dealerid in the DID table.

The account is always correct, the account number the dealer sets up with the provider matches the account number we have in our system.

Account is a short text field, dealerid is a number.

In my Access database I have a table titled “Working” that contains the account number known as the automationid and the dealer id known as dealerid. I also have a table titled “DID” that contains the automationid and the dealerid. When I download the data from the provider each month if the dealerid is text we have a problem.

What I am trying to accomplish is a query that will select the entries that have dealerid’s that are text and insert the automationid DID table as long as the automationid does not already exist in the DID table.

We will then use the DID table to update/insert the working table with the correct dealerid.

We then log onto the provider database and update/insert the dealerid to match our dealerid so next month our download is correct.

We want to maintain the DID table and use it to update/insert the working table in the event that we don’t update/insert our provider database or any other issue that might result in the same data next month.

Here is the code I have written but it does not work. I removed a couple of records from the DID table so the working table has null dealerid’s but my code does not insert the automationid’s of those null dealerid’s.

INSERT INTO DID
SELECT working.automationid
FROM Working inner join DID on working.automationid = DID.automationid
WHERE (((Working.dealerid) Is Null) and ([working].[automationid] not like [DID].[automationid]));

Any assistance is appreciated.

Ron--

 

[dhookom]

It isn't clear to me what you are attempting to do however your JOIN is on working.automationid = DID.automationid and your WHERE clause contains [working].[automationid] not like [DID].[automationid]

This can never return any records because you can't both equal something and not equal the something.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

First you need to create your SELECT statement that will return the record(s) you want.

Would it be something like:
[tt]SELECT automationid
FROM Working
WHERE automationid NOT IN (SELECT automationid FROM DID)[/tt]

Your DID table has just one field...


---- Andy

There is a great need for a sarcasm font.

 

[TheresAlwaysAWay]

We had a similar situation and solved it by not allowing dealers to freehand enter their names. They had to choose from a dropdown list populated from our database, and of course, when they did the correct DealerID went right along with them!