Trying to do a Dlookup to automatically fill in a column

[TreizeRXH]

I'm working on a project for school but I'm not great at technical jargon yet, soI figured I'd upload two pics to start out with so that everything is clear



I've tried a Dlookup to get this to work but it won't automatically tie the two columns together. SOOO, here we go....

The first pic is of my products table I constructed. It's pretty self explanatory (ID, Item Name, Product Cat., etc.):

the second is of the orders table. You'll notice the Item name column is (for the most part) blank. I'm trying to get that to automatically fill in based off the ID# and Item Name from the Products table WHEN the Orders.Item ID# matches both Products.ID and Products.Item Name;





Also, I'm trying to get Orders.Price to automatically fill in as well based off Orders.Qty multiplied by Products.Price.

Any help you guys can give would be really appreciated. I'm using this randomly generated data to try and create some tableau reports for my class but I can't get it to work the way I want it to.

Thanks again!!

[orange]

A few points.

- you should avoid a naming convention that allows embedded spaces--these will come back to haunt you with syntax errors
-write a 5-6 line description in plain English of WHAT you are trying to accomplish --avoid telling us how you did something
-create a data model based on your description and some sample data to test/vet that model
-once the model works with test data (paper and pencil) then begin with Access
-see this regarding using Product.Price and historical records

Good luck with your project.

[June7]

Should not save the Item Name into Orders table - this violates basic relational database principle not to duplicate data. Build a query that joins the related tables so all associated info is available. Same could be said for price. However, since prices are subject to change over time, there is justification for saving the price into Orders. This can be done with an UPDATE sql action. First build query that joins the related tables then click UPDATE from the ribbon Design tab. Modify the query as needed to do update. Then for future records, use code behind a form to input the price when the record is created.

Can each order have multiple products?

Should not use spaces or special characters/punctuation (underscore is exception) in naming convention.