Help with Price Table

[dayeez]

I have a price table with about 70,000 line items (some lines are duplicates).


There are about 12 columns each with different qualities such as item name, size, thickness, price and etc.
I've been building this table in excel and just importing it into Access for the time being.

When a customer sends me an order, I'd like to be able to look up the items in my price table (matching across multiple columns) and automatically return the price for each item.

Where do I start? What do I need? I literally have no idea where to even begin and have not had much luck trying to google my problem.

Any help or even just pointing me in the right direction is much appreciated!

[burrina]

You would need a table for your Prices. The use a lookup to get the price for each item. Below is an example;

Me.ItemPrice = DLookup("ItemPrice", "QryItemPrices")

tblCustomers
tblPrices
tblOrders
tblOrderDetails

Here is a link to a great site for Database Modeling. http://www.databaseanswers.org/data_models/

HTH

[June7]

Possible options:

1. multi-column combobox where user can view all 12 fields and select item - 12 columns is a lot

2. 12 cascading comboboxes to help with isolating the specific record ID of pricing table, then a parameterized query with dynamic parameter on the record ID field of the price table, review
http://www.datapigtechnologies.com/f...combobox2.html
http://www.datapigtechnologies.com/f...tomfilter.html

[orange]

Record your selling/agreedToPrice in your OrderDetails table. Do not refer only to the Price in the Product table to calculate the Price of Orders. Doing so will cause all previous Orders to change Price when you change the Price in the Product table.

See these videos for help with your database structure and design as needed.
If you are quite good at learning by Watching/Listening
rather than reading, then this list might be useful to you.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

https://www.youtube.com/watch?v=lXAG...6FE5448948D9B4 E-R Diagramming Price and Quantity

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

[dayeez]

Thanks everyone! I had no idea what you guys were saying at first but I've been going through the tutorials and links all day. Will be back with more questions later.