would I use DLookup to do this?

[pduncan]

Can someone help with using dlookup? I posted another message on this but ended up getting off track because the way I was thinking I needed to do things.

Basicaly I need to create an app that will take data from excel (how I do this I am not sure of yet - I just know that I need to get in in by transferspreadsheet or a query, or something - but I am going to worry about that later in another post) once I get the data, I need to do a series of lookups in order to convert cost/price (same to me) to discounted cost/price. to make matters worse, the discount depends on location.

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.

Also - only 1 person will be using this so I am not real concerned about speed right now.

Thank you,

Patrick Duncan

 

[rac2]

I dont know about DLookup() but I think it wont be able to use the value "DiscPrice1" to find the column name DiscPrice1. Certainly normalizing the Price table will eliminate that issue. You will have a Prices table with columns PartNo, and Description, and Price; and a Discounts table with columns PartNo, DiscountName, and DiscountPrice or some such names.

Import the spreadsheet data into a table Sales with columns Location, PartNo, Quan, and Price.

Then put it together with the discount price -

SELECT s.Location, s.PartNo, s.Quan, s.Price, d.DiscountPrice
FROM Sales s
JOIN Locations loc ON loc.Location = s.Location
JOIN Discounts d ON d.PartNo = s.PartNo
                    AND d.DiscountName = loc.DiscountName

So the xls report gives the part no and location; the Locations table gives the type of discount for the location; and the Discounts table gives the discounted price for a particular part sold at a particular location.

General comment. This seems like a lot of rig-a-ma-role just to get to the discount price. If this is what 1NF leads to, then "Thanks but no thanks"!

A lot depends on the size of the dataset, how many stores, how many products, how many years of history we want. And how complex the database design is or is expected to become. Spreadsheets can do a lot, they can handle a good amount of data, and they tend to allow us to organize data in patterns that are directly meaningful.

Complex queries for simple answers is the price we pay for eliminating redundant storage and for extensibility which is achieved by the Normal Forms.