Why are my update and append queries incrementing CustomerID Auto_Number field??? (1 Viewer)

[Lateral]

Hi guys I'm a newbie so please be patient with me. I have a table called "Customer" that contains customer records (name, address etc). There is a field called "CustomerID" that is configured as "Auto Number" When I add a new record after running a number of Update/Append queries that may only append a few records etc, that the new "CustomerID" number of huge. For example, if right now the CustomerID is 1000, and I manually add a new record, the CustomerID will be 1001...this is correct. If I run the series of queries and then manually add a new record, the CustomerID may be 20010. Is there something that I'm doing wrong??? Thanks for any help.

I've just done some more testing to try to figure out which query is the problem and I think that I have isolated it to the following query:

INSERT INTO Customers ( ContactFirstName, ContactLastName, EmailAddress, BillingAddress, PostalCode, City, StateOrProvince, PhoneNumber, MobNumber, Web_Customer_Number )
SELECT Web_Orders.[First Name], Web_Orders.[Last Name], Web_Orders.Email, Web_Orders.[Delivery Address1], Web_Orders.[Delivery Postcode], Web_Orders.[Delivery City], Web_Orders.[Delivery State], Web_Orders.[Delivery Phone], Web_Orders.[Delivery PhoneMobile], Web_Orders.[Customer No]
FROM Web_Orders;

The Web_Orders table contains 897 records which is the same number that is being incremented.....


Regards Greg

 

[JHB]

Do not use auto-numbering to something that has meaning for you, for this you've to create your own function.

 

[Lateral]

Hi JHB

Can you please explain what you mean?

 

[Minty]

You are inserting a record for every Web Order;

INSERT INTO Customers ( ContactFirstName, ContactLastName, EmailAddress, BillingAddress, PostalCode, City, StateOrProvince, PhoneNumber, MobNumber, Web_Customer_Number )
SELECT Web_Orders.[First Name], Web_Orders.[Last Name], Web_Orders.Email, Web_Orders.[Delivery Address1], Web_Orders.[Delivery Postcode], Web_Orders.[Delivery City], Web_Orders.[Delivery State], Web_Orders.[Delivery Phone], Web_Orders.[Delivery PhoneMobile], Web_Orders.[Customer No]
FROM Web_Orders
[COLOR="Red"]WHERE WEB_ORDERID or some other field = PutSome Criteria in here [/COLOR];

Add some the WHERE clause to add criteria to restrict which customer you are adding.

 

[Lateral]

Hi Minty, I'll try your idea and let you know how I go....

 

[Lateral]

Hi again Minty,

I've been mucking around trying to get this to work better but am struggling.

I though that I would explain a bit more what I am trying to achieve.

I have a ECommerce website that takes orders etc. I export these orders into a CSV file and then import the CSV file into a Table called "Web_Orders". Each record contains the name and address details of the customer.

In the Access 2007 database, I have a table called "Customers". Records can be manually added to it via a variety of Forms or I run a query each time the Access Front End is started that runs a Query called "Append_Customer_From_Web" that dynamically builds new Customer records from the WebOrders table.

In the "Customers" table I have set the "EmailAddress" field so that it is indexed without duplicates as I want each record to have a unique email address.

So, in summary, I am wanting to only add records to the "Customers| table from the "Web_Orders" table that do not already exist and to use the "EmailAddress" as the criteria.....I hope that I have explained this correctly.

Thanks again for your help.
Regards
Greg

 

[Minty]

Okay that makes sense.
What you will need to do is join the web table to your customers table on the email address with a left join, to include all web details, where the email address in the customers table is null.

Create it as a select query first to see it is getting the right records. I'd upload a picture but am on my tablet...

 

[Lateral]

Hi Minty

I played around with what you suggested and managed to get it to work!

Here is my final query:

INSERT INTO Customers ( ContactFirstName, ContactLastName, EmailAddress, BillingAddress, PostalCode, City, StateOrProvince, PhoneNumber, MobNumber, Web_Customer_Number )
SELECT Web_Orders.[First Name], Web_Orders.[Last Name], Web_Orders.Email, Web_Orders.[Delivery Address1], Web_Orders.[Delivery Postcode], Web_Orders.[Delivery City], Web_Orders.[Delivery State], Web_Orders.[Delivery Phone], Web_Orders.[Delivery PhoneMobile], Web_Orders.[Customer No]
FROM Web_Orders LEFT JOIN Customers ON Web_Orders.Email = Customers.EmailAddress
WHERE (((Customers.EmailAddress) Is Null));

Thanks again for your help.

The only little issue that I now have relates to the fact that the Web_Orders tables contains a seperate record for each of the products that have been ordered. Each record also contains the name and address details of the customer. This is how the data needs to be exported from the website.

So, as an example, an Order may have 5 products and therefore 5 records in the Web_Orders table all of which contain the email address.

I only want to create a single record in the Customer table, not 5 and my query is doing this.

The problem is that the query tries to insert all 5 records and successfully inserts a single record into the Customer table due to the index I have on the EmailAddress field set to "No Duplicates"...this is what I want and currently do.

The problem is that the Autonumber field called "Customer_ID" is incremented by a value of 5....which is not what I want...

Do you know how to resolve this issue?

Regards
Greg

 

[Minty]

You need to group on the web tables fields you are importing. In the query design click on the totals symbol (sigma) , again play around in select view first to get it right. Good luck!

 

[Lateral]

Hi Minty
Are you saying to change the import query that is being used to initially imports the data into the Web_Orders table?

If so, then I don't think that will work as I need to have all five records as each record contains information about the product that has been ordered in addition to the customers details.

If you are saying to change the query that I am using to Insert new customers into the Customer table from the Web_Orders table by using the Group By option, then that's what I am trying to do without much success.

Here is the query that currently works and inserts a single record into the Customers table but increments the Customer_ID (AutoNumber) field by 5.

INSERT INTO Customers ( ContactFirstName, ContactLastName, EmailAddress, BillingAddress, PostalCode, City, StateOrProvince, PhoneNumber, MobNumber, Web_Customer_Number )
SELECT Web_Orders.[First Name], Web_Orders.[Last Name], Web_Orders.Email, Web_Orders.[Delivery Address1], Web_Orders.[Delivery Postcode], Web_Orders.[Delivery City], Web_Orders.[Delivery State], Web_Orders.[Delivery Phone], Web_Orders.[Delivery PhoneMobile], Web_Orders.[Customer No]
FROM Web_Orders LEFT JOIN Customers ON Web_Orders.Email = Customers.EmailAddress
WHERE (((Customers.EmailAddress) Is Null));


Any help is appreciated.

Regards
Greg

 

[Lateral]

Hi Minty

I have been playing around a used the TOP parameter and it sort of works except it only provides me with a single record to insert for the first "group" of email addresses. If I have 10 new emails addresses to add across 100 records, then I need to run the query 10 times and it would then work fine....

INSERT INTO Customers ( ContactFirstName, ContactLastName, EmailAddress, BillingAddress, PostalCode, City, StateOrProvince, PhoneNumber, MobNumber, Web_Customer_Number )
SELECT TOP 1 Web_Orders.[First Name], Web_Orders.[Last Name], Web_Orders.Email, Web_Orders.[Delivery Address1], Web_Orders.[Delivery Postcode], Web_Orders.[Delivery City], Web_Orders.[Delivery State], Web_Orders.[Delivery Phone], Web_Orders.[Delivery PhoneMobile], Web_Orders.[Customer No]
FROM Web_Orders LEFT JOIN Customers ON Web_Orders.Email = Customers.EmailAddress
WHERE (((Customers.EmailAddress) Is Null));



Am I getting close???
Regards
Greg

 

[gemma-the-husky]

out of interest.

irrespective of resolving the issue, you just CANNOT rely on an autonumber to produce an intact complete sequence.

it may not. It isn't specified to do so.

 

[Lateral]

Hi Gemma

I,m not using the Auto Number field for anything important and therefore it's not a high priority for me to look into changing.....

Thanks for your input though.

Regards
Greg.

 

[Minty]

Hi Greg.

Not quite, the grouped query would be after your import. What I am suggesting will simply group the address records where they match. As long as all you bring in are the address fields the group function will restrict it to one record per different customer for your address update. Something like;

INSERT INTO Customers ( ContactFirstName, ContactLastName, EmailAddress, BillingAddress, PostalCode, City, StateOrProvince, PhoneNumber, MobNumber, Web_Customer_Number )
SELECT Web_Orders.[First Name], Web_Orders.[Last Name], Web_Orders.Email, Web_Orders.[Delivery Address1], Web_Orders.[Delivery Postcode], Web_Orders.[Delivery City], Web_Orders.[Delivery State], Web_Orders.[Delivery Phone], Web_Orders.[Delivery PhoneMobile], Web_Orders.[Customer No]
FROM Web_Orders LEFT JOIN Customers ON Web_Orders.Email = Customers.EmailAddress 
GROUP BY  Web_Orders.[First Name], Web_Orders.[Last Name], Web_Orders.Email, Web_Orders.[Delivery Address1], Web_Orders.[Delivery Postcode], Web_Orders.[Delivery City], Web_Orders.[Delivery State], Web_Orders.[Delivery Phone], Web_Orders.[Delivery PhoneMobile], Web_Orders.[Customer No]
WHERE (((Customers.EmailAddress) Is Null));

It's possible you may need to do the group query first then base your Insert query on the result of the group query. Again play with select versions first.

 

[Lateral]

Thanks Minty

I'll try your suggestion and let you know how I go.

I've just discovered a big issue that I need to resolve asap and have spent hours but I just can't figure it out and I am sure that I am missing something easy.

The data is imported into the Customer table and from then on, the User can edit the any of the data via the application, including the email address in the Customer table.

The problem is, for example, assume that there is a record for John Smith and his email address is john@email.com. If the user changes John Smith's email address to johnsmith@email.com, when they restart the application, the Query is run again against the Web_Orders data and John Smith is once again inserted into the Customer table with his original john@email.com email address because the john@email.com does not exist in the table anymore.

Each record that is inserted from the Web_Orders table into the Customers table also has a unique numeric field called Web_Orders.[Customer No]. This is generated by the website and is also inserted into the Customers table when the record for John Smith is created and cannot be changed by the user.

I have been trying to change the query so that it will not insert a new record if a record already exists that contains the same Web_Orders.[Customer No]. The field in the Customers table is called Customers].[Web_Customer_Number].

I have spent hours trying to get this damn thing to work and I am sure that I am missing something really simple.....

Any ideas?

Regards
Greg

 

[Minty]

In that case change your left join from the email address to the customer number on the tables and it should resolve that problem. That is assuming that the web customer id is unique and is repeated if they are a returning customer?

 

[Lateral]

Minty, you're a legend!

Thanks mate, I was trying to add another condition to the WHERE clause but just couldn't figure out how to do it...I previously added the Left Jon to the Web_Customer_Number but I still had the other Left join in place...

Thanks again. I'll do some more testing to make sure there are no other strange things happening..

Regards
Greg