Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Hello, I use an Access database

Hello, I use an Access database

Hello, I use an Access database


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.

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.


RE: Hello, I use an Access database

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.

Hook'D on Access
MS Access MVP 2001-2016

RE: Hello, I use an Access database

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

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

Your DID table has just one field...ponder

---- Andy

There is a great need for a sarcasm font.

RE: Hello, I use an Access database

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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close