asked on

Need help with query syntax to update Access table from an other table, several records, only certain data, or another option to do this

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.

Some details:
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.

salesRec-select-query syntax:
SELECT Sales_Record.[Date Sold], Sales_Record.[Sold For Y]
FROM Sales_Record
WHERE (((Sales_Record.[Inv Number]) LIKE "*"));

update-query syntax:
UPDATE inventory SET Inventory.[Sold Date] = DLookup("[Date Sold]","Sales_Record","[Inv 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.
Database.zip

als315

Can you clarify your idea? You have primary key - Inv Number - in table Inventory, but you also have there date feilds. Do you like have same Inv Number with different Purchase date there?
You should think about data structure of you DB at first. Normally trade DB have different tables for purchase and sale. Then you can use FIFO or LIFO methods for closing of sold positions.
Microsoft's sample DB - Northwind - is good example of trade company

Guy Hengel [angelIII / a3]

update with join syntax explained: https://www.experts-exchange.com/A_1517.html

REIUSA

ASKER

als315:
Hello,
Basically I have an Inventory table named Inventory that holds all of the details for each item. I put a record in for each item as I buy it.

When it is sold I can download a excel spread sheet from the website that has the main identifier the Inv Number and other info like sold date and the amount it was sold for.

So I want to, based on the Inv Number, take data from a few cells like Sold for Y and Date Sold from the imported sales_record and then update the same Inv Number in the Inventory table. But i need to only update a few cells and I need to be able to update multiple records 1 for 1.

angelIII,
Thanks, I'll start reading this.

als315

But can you have many records for one Inv Number?
If you purchase 100 items of some Inv Number and sold 50 in one day and 20 in another, what result you like to have?

GRayL

Using als315's scenario above, your inventory file will have 100 records - one for each of the 100 items purchased?

REIUSA

ASKER

Each item has it's own Inv Number, for multiple items it would be something like E12345, E12345_2, E12345_3 ect., for the most part the items are different.

At some point I will probably need a different way of recording this info when we buy multiples of the same thing but for now the main thing is to figure out how to update the Inventory table with the Sales_Record data.

Thanks,

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

REIUSA

ASKER

Thanks for the help I'll be home later tonight and will check it out.

Originally I had spaces to make it easier to double click and copy the number but after starting this project to update the table I realized I needed to remove the space and forgot to take it out of the test db.

REIUSA

ASKER

Awesome, thanks so much for the help. I tested it out on the test DB and it looks like it is working. I need to setup the production DB like this and see how it works.

REIUSA

ASKER

Thanks for all the help. I am still working on applying this to a large database, I got side tracked with something else that came up but I'll post a new question if I have problems with it.