One lookup to pull through multiple fields

[simon238]

I'm fairly new to access and this is the first real problem I've encountered - surprisingly I couldn't find an answer on here/yahoo answers etc.



I have Suppliers, Products and Purchase Order tables.

In the purchase order I have the following fields, PO Number, Supplier, Product, Unit Price, Unit, Qty Ordered, Total Cost.

I want to be able to select the product from the Product table, and then automatically pull through the corresponding unit price and unit, however I can't seem to find out how to do this without having to add a manual lookup to all fields. In which case I may aswell put them in manually.

I imagine this is a fairly common requirement and easy to solve, but it's left me stumped.

[orange]

Please post a jpg of your Tables and Relationships.

Do you understand queries?

[simon238]

Ok, so have tried to show example with 2 versions of the same form. Basically when I select the product I want it to automatically fill in some other fields which relate to said product.

[orange]

Can you get a screen print of your Relationships window? You have to have your tables set up to do what you want before getting into the Forms.

[simon238]

Relationships are as follows

[orange]

Your model needs some work.

See these models - perhaps you can adjust what you have based on these.

http://www.databaseanswers.org/data_...ucts/index.htm
http://www.databaseanswers.org/data_...ders/index.htm

Good luck with your project

[simon238]

This level of complexity seems like overkill to me, would all these tables really be necessary?

Take the first scheme for example, for user input would the form for adding a new order to the database include fields from both Customer_Orders and Order_Items?

Going back to my original problem, I understand how this would then bring through from the product_id the product name. But how could I make this pull through more than one field?

i.e. The user puts in the customer, and the product purchased, on selecting purchased product I would want the form to display the current price to the customer then user would input quantity purchased. Upon which the total cost would be calculated, displayed to the user and then stored in the order table.

[orange]

All the tables from the models are not required. They were meant to show you some of the things that could exist in a model of Customer and Product/ Customer and Order etc. and how the things might be related.
Your situation may be different and require different structures and relationships. These existing models are used as guides/references. They are NOT NECESSARILY meant for wholesale adoption, nor are they meant as the ONLY way to do something.

Be cautious not to confuse WHAT you are dealing with, with HOW you will do it. In my experience the key is to get the Tables and relationships established, test the set up with sample data (good and bad) and check that the model will let me deal with the data at the level I need. Then work on HOW you may enter data, and How you may retrieve data.

You should research Normalization and Entity Relationship Diagramming to get your tables and relationships designed.

Here are some documents and videos related to Database and Access. I hope you find them useful.

Principles of relational Design http://forums.aspfree.com/attachment...2&d=1201055452
Entity Relationship Diagramming http://www.rogersaccesslibrary.com/T...lationship.zip

Video tutorials:

These are quite good for learning by Watching/Listening rather than reading.

Logic Data Modeling 1 - Introduction - YouTube Logical data modeling

Logic Data Modeling 2 - Candidate Key - YouTube Candidate key

Logic Data Modeling 3 - Normalization - YouTube Normalization

Logic Data Modeling 4 - Normalization Example - YouTube Normalization example

Logic Data Modeling 5 - 1st Normal Form - YouTube 1st Normal form

Logic Data Modeling 6 - 2nd Normal Form - YouTube 2nd Normal form

Logic Data Modeling 7 - 3rd Normal Form - YouTube 3rd Normal form

Logic Data Modeling 8 - Entity Relationship Diagram, part 1 - YouTube E_R Diagramming

Complete set of tutorials on Acc2010.
https://www.youtube.com/playlist?lis...FoilxbUY0yUqZP

[simon238]

Thanks a lot for the tips, very interesting read. I see now my approach is wrong and I am still too influenced into thinking in a way that I would use Excel. I will take this back to the drawing board

[orange]

Well, part of your learning curve will be dealing with unlearning some of the practices you learned with Excel/spreadsheet.

Watch the videos. I can assure you that the key to database is getting the tables and relationships designed. If the structures and relationships are not "right", you'll waste unbelievable amounts of time trying to code around a structure problem -- basically trying to work against the DBMS.

Good luck with your project -- let us know how you're doing.

[simon238]

I have now set up the tables in a way I think fits in with what I read on your suggested reading. However, I am wondering if there should ever be a loop throughout the diagram?

I am also confused now how I would put this into one entry form, I assume using a query would be the way to set this up, but when I am selecting say employee, the only drop down box is from the foreign key and is there unique ID, which means nothing to the user. Also then it doesn't seem to pull through their name, and other attached data.

[orange]

Is the Supplier the organization/person that provides the Product?
If so, then remove the relationship between Supplier and Order.

see this model (it's more detailed/complex than yours, but Supplier is not directly related to Order)

The Supplier is related to the Product.

http://www.databaseanswers.org/data_...ders/index.htm

[simon238]

The problem is the some products are available from more than one supplier so not listing supplier attached to the order could be confusing

[orange]

Please explain what Order means in your business.

Customer Orders Products (from you)

or You (the Business) Orders Product from Supplier

I suggest the Supplier supplies Products to you.
and the Customer orders Products (as OrderItems) from you.

If you need to know which Supplier supplied a specific Product, and a Product could have been supplied by many Suppliers, then make a junction table Supplier_Product between Suppliers and Products.
You will need a FK to Supplier and a FK to Product. and the OrderItem would be a record from this Supplier_Product table.(and drop the relationship between Product and OrderItem)

(untested)