I need to write a query that will allow me to update a database with as little effort as possible, it is a M$ Access DB. At the moment I am manually updating the DB with about 150-200 sales a month, not a lot but it is growing and I can see the need to automate as much as possible in order to grow in the future.
The database is a Access 2007 DB. I download a excel spread sheet with sales listed and update the Access DB with that info. Right now manually.
Before the items sell I input the basic data like size, color, purchase price etc. into the database so the records I need to update are already there.
The main criteria I need is when the items sell is… the amount it sold for, fee’s, the inventory number of the item, and date sold. Maybe a few more but these are the main things.
What I am trying to do is Export sales records to a Excel spread sheet and then import that sheet into Access creating a sales_record table and then pull from that data to update the main table.
I would need to pull xx data from item xyz123 from the excel file and input the data into the correct field in the DB for item number xyz123.
What I am aiming for is something I can simply run and it will go out and grab the data from the sales table and update all of the records in the main table instead of manually checking the site or sheet and manually updating the info in the DB.
Attached is a test DB with 2 records to show what I am trying to do. Basically the Inventory table is the main table, Sales_Record is from importing the sales spread sheet and will change every time I need to update the DB so it will be deleted once the data is updated.
At this point I have a query called salesRec-select-query
that will pull the data from the Sales_Record and show it on screen and then I have update-query
that will update the record in the Inventory table with the data, it worked fine when I tried it on one record but when I tried to update more than one I realized it took data from one record and updated all the records in the inventory table with the same data. What I really need help with now is making it work so that I can update the records 1 for 1 based on the inventory number.
So each table has a record for inventory number xyz123 and I need to take specific data like from cells like “sold for Y” and “sold date” and update only these specific cells. It seems like it’s close but it just can’t handle the specific inventory numbers.
SELECT Sales_Record.[Date Sold], Sales_Record.[Sold For Y]
WHERE (((Sales_Record.[Inv Number]) LIKE "*"));
UPDATE inventory SET Inventory.[Sold Date] = DLookup("[Date Sold]","Sales_Record","[In
v Number] LIKE '*'"), Inventory.[Sold For Y] = DLookup("[Sold For Y]","Sales_Record","[Inv Number] LIKE '*'")
WHERE inventory.[Inv Number] Like '*';
I'm also open to ideas how to do it directly form the excel sheet if that makes it easier.
Thanks for any advice and help you can give.