Need to use lookup table - how do I do that? 1

[pduncan]

I need to make a db that will import an invoice (from excel) and then look up the part numbers on the invoice for pricing based on location. The pricing table will be static for the most part, only changing every year or so. On the pricing table, there will be (for each line item) a part number, description, and several columns representing costs for different locations. The excel data will contain (among other things) the part number and location – I will need to “read” these values and look up the cost for the part according to the location. If it makes it any easier, each excel invoice imported will contain parts for only one location. I think I can get the excel data into Access easy enough; I just need help with looking up and assigning costs.

Any ideas or help will be greatly appreciated.

Thank you,

Patrick Duncan

 

[RickSpr]

How will you import the Excel data? If you import it as a table, you can probably just join that table with the pricing table on the part number field to get everything you need.

If you 'import' it into a bunch of variables, you must be processing those variables in VBA code. You can use the DLookup() function to retrieve data from the pricing table. In the third argument to DLookup, use the value of the part number variable.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[lespaul]

and several columns representing costs for different locations

Just from this brief comment, I would have to suggest that you reconsider this table design. From this, I would assume that you have a non-normalized table design and will have trouble down the line trying to match up locations and prices.

Check out 'The Fundamentals of Relational Database Design'



Leslie

 

[RickSpr]

Thanks, Leslie. I missed that.

Patrick: Leslie is right, and it will affect the coding of the DLookup's, so you should address the table normalization first.

You probably already have a table of Locations. What you should do is create a table ProductLocations with a foreign key to each of the Products and Locations tables. The combination of those two foreign keys should be the primary key of ProductLocations. ProductLocations will also have a single Price (or Cost?) field which replaces the several costs for different locations in the Products table.

It would also be good if you defined relationships and relational integrity rules between ProductLocations and the other two tables.

If you don't have a Locations table, it's because you don't need anything but the name/code for the location. In that case, ProductLocations would have only one foreign key, to Products, but it would have a Location column and its key would be the combination of foreign key and Location. (This is the same as above, except that Location isn't a foreign key.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[pduncan]

Leslie and Rick,
Thanks - what you are saying sounds like good advice. Actually I have to make this from scratch so nothing exists at this time but a few excel spread sheets. The cost list does in fact need to be normalized – thanks for pointing that out to me. Once I break this down into normalized tables, can anyone provide help with the VBA to lookup values?
Thanks,
Patrick

 

[RickSpr]

Sure. But you'll still have to tell us how you're importing the Excel data--into a table (what is its structure?) or into variables.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[pduncan]

Well nothing is set in stone - I was thinking about bringing each spreadsheet in as a new table or something, but have not spent much thought on the subject. I do know that all of the spreadsheets will have the same structure - I would send a sample, but am not sure how to do so.

 

[RickSpr]

Tek-Tips doesn't allow uploading files, because of the danger of distributing viruses. Most of us won't publish our email addresses here because of web crawlers harvesting them for junk mail. So in general, everything we share either has to be turned into text, or stored on some external site and referenced here.

Nevertheless, importing the table (or linking it, for that matter) gives you the ability to use a query that joins the Excel table with the Products and ProductLocations tables. Then you can just pick the fields you need. Very easy.

Note: If you use a linked table, the Excel .wks file must be available any time you use the query, or you will get an error. Therefore, you may prefer to actually import the table if the spreadsheet isn't a permanent file. But if the spreadsheet simply gets replaced periodically by one with identical structure, there's no need to recreate the query again and again.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[pduncan]

Here is an example of what I have in my price table now:


PartNo Description Price DiscPrice1 DiscPrice2 DiscPrice3 … etc
-------- -------------- ------- ------------ ------------- ------------
12345 widget123 100 90 85 87
23456 widget234 233 230 215 229
. . . . . .
. . . . . .

Here is an example of what is in my location table

Location Discount
----------- ------------
1111 DiscPrice1
2222 DiscPrice3
3333 DiscPrice2

Here is what I am sent (in xls format daily)

Location PartNo Quan Price
---------- --------- ------ -------
1111 12345 1 100
.
.

What I need to do is:
1.) Get the excel data into the DB (store it for later retrieval if needed)
2.) “Read” the location – and determine the associated discount
3.) Lookup and store (with data from step 1) the discount price

So in this example the cost would actually be 90.

Can you please give me some help/pointers/suggestions as to the best way to do this? I have spent the last day or so trying to get my tables normalized – I think they are 1NF.

I started breaking all of the discounted costs into their own tables but wanted some expert opinion before I did that – wasn’t sure if it is necessary or not.

Thank you,

Patrick Duncan

 

[RickSpr]

You need to normalize further, at least to 3NF. (1NF only means that fields are atomic, that is, you don't have 2 pieces of data stored in a single field.)

Your Price table should be: (*=key)
PartNo* Description Price
-------- -------------- -------
12345 widget123 100
23456 widget234 233
. . .
. . .

Your LocationPrices table should be:
PartNo* Location* DiscPrice
-------- -------- ------------
12345 1111 90
12345 2222 87
12345 3333 85
23456 1111 230
23456 2222 229
23456 3333 215
. . .
. . .

From what you've given, you don't need an explicit Locations table, but if you do it will be easy to add to this.

Once you have imported or linked the Excel data (I'll call the table XLImport) you can get the information you need from ths simple query:
SELECT XLImport.PartNo, XLImport.Location, LocationPrices.DiscPrice
FROM XLImport INNER JOIN LocationPrices
ON XLImport.PartNo = LocationPrices.PartNo AND XLImport.Location = LocationPrices.Location

This may not be ideal for your purpose. I see that your Location table uses an intermediate term DiscPricen, which suggests that perhaps there are groups of locations that use the same discounted price. If that's the case, I can recommend a modification to the table structure that will retain that grouping of locations/price association.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[pduncan]

Rick,
Thanks - that's exactly the advice I was looking for. I will be working on implementing your suggestions today. One thing - once I get all my table structure normalized, I am still a little fuzzy on how I will look up the values using the query provided. Sorry, not trying to be obtuse here, but I am still a bit confused.
If I understand you correctly, first I create a table structure like the example provided. Next, get data from Excel (any pointers here? I would like to do this programmatically, perhaps using transferspreadsheet action?) – And store the excel data in a table. Lastly, I would use the query provided to lookup the values based on the imported data. If this is correct, how can I pass the part number and location to the query using code? Also since I want to store the discount price along with the “normal” price I guess I would use an insert SQL to add this value back to the XLImport table.

Thanks again – and I think you have helped me enough to this point that you should at least get a star.

 

[RickSpr]

It may be me who's being obtuse, or overly helpful--a fault of mine, drowning people with too much information.

Getting the data from Excel: Definitely use TransferSpreadsheet. But given that you want to store the discounted price back into the table, I would link the spreadsheet rather than import it. If you import it, you'll then have to add a column to it for the discounted price. It's easier to link the spreadsheet and then run an SQL statement (similar to my query) to build the table all at once. That would save you from having to write code to loop through the records.

Here's some skeleton code:

    ' Delete previous link to Excel worksheet, if it exists
    On Error Resume Next
    DoCmd.RunSQL "DROP TABLE XLImport"

    ' Delete previous version of MyNewTable, if it exists
    DoCmd.RunSQL "DROP TABLE MyNewTable"
    On Error Resume Next

    ' Link the Excel worksheet
    DoCmd.TransferSpreadsheet acImport, , "XLImport"

    ' Combine Excel data with discount prices to create MyNewTable
    DoCmd.RunSQL "SELECT XLImport.PartNo, XLImport.Location, " _
        & "LocationPrices.DiscPrice " _
        & "INTO MyNewTable " _
        & "FROM XLImport LEFT JOIN LocationPrices " _
        & "ON XLImport.PartNo = LocationPrices.PartNo " _
        & "AND XLImport.Location = LocationPrices.Location"

(Note: I changed from INNER JOIN to LEFT JOIN. INNER JOIN would discard records for products which are not found in the LocationPrices table.)

If for some reason you prefer to loop through the records, you can use a DLookup() with the linked Excel table:
Set rstXL = db.OpenRecordset("XLImport")
...
curDiscPrice = DLookup("DiscPrice", "LocationPrices " _
"Location = " & rstXl!Location)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[pduncan]

Rick,
on the 16th you wrote:
Your LocationPrices table should be:
PartNo* Location* DiscPrice
-------- -------- ------------
12345 1111 90
12345 2222 87
12345 3333 85
23456 1111 230
23456 2222 229
23456 3333 215
. . .
. . .

Question:
I understand the the Location column is a foreign key to the location table, but you indicate PartNo as a key also - is this intended to be a foreign key back to the price table? I ask this because the duplicating part numbers would prevent this from being a primary key - right?

 

[lespaul]

The combination of Location and PartNo are your PK. PartNo is also a FK.



Leslie

 

[pduncan]

Thanks, that makes sense, but how do you do that (combine 2 columns in 2 different tables to make a PK) in access 2002? I am only familiar with making a PK in a single table.

 

[RickSpr]

Not sure I understand what you're saying. The columns PartNo, Location, and DiscPrice are all in the LocationParts table. Its primary key is the first two of those columns. You're not being told to combine columns from 2 different tables.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[lespaul]

you should have THREE tables:

tblParts
tblLocation
tblLocationPrices

tblPart
PK = PartID

tblLocation
PK = LocationID

tblLocationPrices
PartID - FK to tblParts
LocationID - FK to tblLocation

in the table design of tblLocationPrices, select BOTH the PartID and LocationID (select with Shift or Ctrl) and press the key. Now you have a composite PK. The combination of PartID and LocationID is unique and cannot be duplicated.

Have you read 'The Fundamentals of Relational Database Design'? It's a must read.

Leslie