Inventory Update - Updated rows from one table to another & add rows for new orders

[werderexpat]

Hi dear Forum! This my first post, so bear with me for any mistakes I make or conventions I do not observe (unintentionally, of course )

I would like to achieve the following:

I have an Inventory of mobile phones (ca. 15000 entries), which is stored as a table in Access and contains information like Number, First Name, Last Name, Email, Tariff, Contract Start Date/End Dates.

I receive weekly a table with a summary of orders (ca. 20 per week), which include: changes to User Details, Tariffs, Contract Start/Ending Dates and new orders.

Now I would have to have to updates: one to add the new numbers as rows, and one to update the relevant fields for the existing numbers.

I have created two queries:

One two add the new numbers:

INSERT INTO InventoryReportRaw ( [Mobile Number], Tariff, [Cost Centre], [Contract end date], [Activation date], [First Name], [Last Name], Email )
SELECT OrderReport.EndUserNr, OrderReport.Tariff, OrderReport.CostCentre, OrderReport.ContractEndDate, OrderReport.FutureActivationDate, OrderReport.EndUserFirstName, OrderReport.EndUserLastName, OrderReport.EndUserEmail
FROM OrderReport, InventoryReportRaw
WHERE (((OrderReport.TransactionType)="Change-BAN") AND ((OrderReport.Status)="Completed")) OR (((OrderReport.TransactionType)="CTN-PortingIn") AND ((OrderReport.Status)="Completed")) OR (((OrderReport.TransactionType)="CTN-TransferIn") AND ((OrderReport.Status)="Completed")) OR (((OrderReport.TransactionType)="Svc-Delivery") AND ((OrderReport.Status)="Completed"));

And one to update existing fields:

UPDATE OrderReport RIGHT JOIN InventoryReportRaw ON OrderReport.EndUserNr = InventoryReportRaw.[Mobile Number] SET InventoryReportRaw.[Last Name] = [OrderReport].[EndUserLastName], InventoryReportRaw.[First Name] = [OrderReport].[EndUserFirstName], InventoryReportRaw.Email = [OrderReport].[EndUserEmail], InventoryReportRaw.Tariff = [OrderReport].[Tariff], InventoryReportRaw.[Contract end date] = [OrderReport].[closure date], InventoryReportRaw.[Contract end date] = [OrderReport].[closure date]
WHERE (((OrderReport.TransactionType)="Svc-chg-details" Or (OrderReport.TransactionType)="Svc-chg-details" Or (OrderReport.TransactionType)="Svc-chg-details" Or (OrderReport.TransactionType)="Svc-Chg-Tariff" Or (OrderReport.TransactionType)="Terminate-Nr" Or (OrderReport.TransactionType)="CTN-TransferOut") AND ((OrderReport.Status)="Completed"));

When I run the queries, Access tells me it is going to Append/Update over 100000 records - which just cannot be true.

Do you have any suggestions as to how I can solve my problem? I am open to any tips!

Thanks in advance,

werderexpat

[alansidman]

What happens when you run the query? If you haven't run it yet, then make a backup and run it. See if the results are as expected.

[werderexpat]

Hi! Thanks for your answer!

If I run the query, the records will increase from ca 15k rows to over 100k rows in both queries.

[alansidman]

In this code

Please Login or Register  to view this content.

It appears that you are inserting data from the OrderReport and then reinserting from the InventoryReportRaw if I am not mistaken. I think that you do not want the second FROM reference. Look at this SQL tutorial on syntax for this type of statement

http://www.w3schools.com/sql/sql_insert_into_select.asp

The FROM portion of the statement only references the source table.

[werderexpat]

Hi Alan,

thank you so much! This worked like a charm!

Do you, or anyone else, have any insights into the Update query?
UPDATE OrderReport RIGHT JOIN InventoryReportRaw ON OrderReport.EndUserNr = InventoryReportRaw.[Mobile Number] SET InventoryReportRaw.[Last Name] = [OrderReport].[EndUserLastName], InventoryReportRaw.[First Name] = [OrderReport].[EndUserFirstName], InventoryReportRaw.Email = [OrderReport].[EndUserEmail], InventoryReportRaw.Tariff = [OrderReport].[Tariff], InventoryReportRaw.[Contract end date] = [OrderReport].[closure date], InventoryReportRaw.[Contract end date] = [OrderReport].[closure date]
WHERE (((OrderReport.TransactionType)="Svc-chg-details" Or (OrderReport.TransactionType)="Svc-chg-details" Or (OrderReport.TransactionType)="Svc-chg-details" Or (OrderReport.TransactionType)="Svc-Chg-Tariff" Or (OrderReport.TransactionType)="Terminate-Nr" Or (OrderReport.TransactionType)="CTN-TransferOut") AND ((OrderReport.Status)="Completed"));

Thanks,

Roman

[werderexpat]

Hi,

I have just played around and fixed it. The new code now is:

UPDATE OrderReport RIGHT JOIN InventoryReportRaw ON OrderReport.EndUserMSISDN = InventoryReportRaw.[Mobile Number] SET InventoryReportRaw.[Last Name] = [OrderReport].[EndUserLastName], InventoryReportRaw.[First Name] = [OrderReport].[EndUserFirstName], InventoryReportRaw.Email = [OrderReport].[EndUserEmail], InventoryReportRaw.Tariff = [OrderReport].[Tariff], InventoryReportRaw.[Contract end date] = ""
WHERE (((OrderReport.TransactionType)="Svc-chg-details") AND ((OrderReport.Status)="Completed")) OR (((OrderReport.TransactionType)="Svc-Chg-Tariff") AND ((OrderReport.Status)="Completed"));