D Lookup

[SarahSB]

Hi,

I am trying to do a lookup for a price on a comparative shoe model. I have a model table (table name is Model) which lists comparative models and a separate table listing the prices (table name is ModelPrices)which are updated quarterly.

I have built a query to show models, their price and equivalent models. I'm having trouble with looking up the price of the equivalent model.

Expr1: DLookUp("[Q42017]","[ModelPrices]","[ComparativeModel1]"="[Part Number]")

Can someone please help because I am stumped and can't seem to figure it out! Any help would be greatly appreciated! Thanks!!

 

[plog]

1. There's no reason to do a Dlookup in a query.

2. The criteria argument of your DLookup is escaped incorrectly.

3. I think your table is incorrectly structured.

#3 overrides all the other issues. So let's talk about that. Field names should be generic (ModelType, ModelNumber, SalesPrice, etc.). [Q42017] is a very weird name. I think it Quarter4 of 2017, which is an invalid way of storing data. When you do that you are storing a value in a name. That's not how databases are to work. You store values in fields themselves.

Can you post a structure of ModelPrices along with a brief description of what it is for and a description of what data each field represents.

Actually, set up your Relationship Tool in Access and post a screenshot of that.

 

[Mark_]

Just to make sure, you do have a field named [Q42017]? For myself I'd rename that to something more meaningful. Also, is [Comparativemodel1] text?

IF it is numeric, you lookup would look like

Expr1: DLookUp("[Q42017]","[ModelPrices]","[ComparativeModel1] =" & [Part Number])

For some more help, please look at the link below;
https://support.office.com/en-us/article/DLookup-Function-8896cb03-e31f-45d1-86db-bed10dca5937

More specific in a query you would want to use a JOIN to get the data you are looking for.

 

[SarahSB]

The model prices are updated each quarter from our supplier, which is why I have named the fields Q42017 etc.

What we want to see is a column for the base model and it's price and a column for a comparative model and it's price.

The relationship table is as follows:

 

[plog]

Yes, you need to fix Osaga CBD Rates. Instead of storing the timeframe in a field name, you need to store it as a value in a table. I mean, you don't have a field for every part number do you? No, you have a field called [Part Number] where you store the values.

Same principle for the Quarter--make a field to store the quarter value (e.g.[RateQuarter]). That way instead of 4 fields in a table for the rates of a year, you have 4 records in a table for the rates. When you do this, I would make the field an actual Date field and store the first date of the quarter. Doing this allows you access to all the built-in date functions of Access, which makes compare that field easier than a custom built format (e.g. 042017)

Additionally, I see 3 other errors:

1. Incorrect use of primary keys. You have them, but you aren't using them. Master Components List and Assembly are joined via Component to Component--this is incorrect. Both those tables have primary key fields called ID. When you join tables you join the primary key field of one table to a field in another table. I don't know what those 2 tables represent, but I do know that they shouldn't be joined like they are. Seems all your tables have this issue.

2. Redundant data. Assembly and Osaga CBD Rates are joined via Part Number (which is wrong per #2 above), but they also both have Style Number fields. Only one of these tables needs that field because these tables are in a relationship. You don't store data in multiple places.

3. Spaces in names. When you add spaces into table/field names (e.g. [Osaga CBD Rates], [Style Number]) it makes writing queries and code harder in the future. Eliminate spaces (and other non-alpha numeric characters in names).

 

[Pat Hartman]

which is why I have named the fields Q42017 etc

This is a spreadsheet. When your column names contain data, you know you've gone off the rails. Please review the first three normal forms to help yourself understand the problem.

The simplest way to store historical prices is with start and end dates. That way you can take any date and find the correct price in the history table.

Select ..
From tblHistory as h Inner Join Active as a On h.ProductID = a.ProductID
Where a.SoldDate Between h.StartDate and h.EndDate

Your method requires that you actually know what specific quarter you are looking for and you'll need a separate query for each different quarter since the Where clause would be different.