Lookup question? 1

[edhead]

Access newbie here trying to figure out the following, 2 tables:

tblCookTime
Product (PK)
LotNumber (PK)
CookTime

tblDataLogger
Product (PK)
LotNumber (PK)
Description (PK)
CookTime
Various other fields

There will be multiple records in tblDataLogger for each LotNumber. I need to pull the CookTime for tblDataLogger from tblCookTime based on LotNumber and Product. I can't seem to figure it out.

Thanks in advance!

 

[PHV]

Why storing derived/calculated values ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[edhead]

Thanks for the quick reply.

tblCookTime is popoulated with a form by user "A" several days before the tblDataLogger is populated by user "B" with another form.

Considered using an expression in a query to pull CookTime from tblCookTime but wasn't sure how to do it based on the Product and LotNumber fields.

As a noob if I am way off base on my approach I would love to hear any suggestions.

 

[PHV]

Base your "B" form on a query:
SELECT DL.Product, DL.LotNumber, DL.Description, CT.CookTime, DL.[Various other fields]
FROM tblDataLogger AS DL INNER JOIN tblCookTime AS CT
ON DL.Product = CT.Product AND DL.LotNumber = CT.LotNumber

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[edhead]

PH Thanks for your help. One last follow up question, I already have a query the form is based on, I tried modifiying your SQL for the inner join to work on my exisiting query but can't figure it out. Once again if I am wayy of track just let me know...

Query is named qryBPDataLogger

SELECT tblBPDataLogger.Product, tblBPDataLogger.LotNumber, tblBPDataLogger.Description, tblBPDataLogger.[180Time], tblBPDataLogger.[196Time], tblBPDataLogger.Below196Time, tblBPDataLogger.Below180Time, tblBPDataLogger.[140Time], tblBPDataLogger.[70Time], tblBPDataLogger.[40Time], tblBPDataLogger.RemovedTime, DateDiff("n",[180Time],[Below180Time]) AS TimeAbove180, DateDiff("n",[196Time],[Below196Time]) AS TimeAbove196, DateDiff("n",[140Time],[70Time]) AS Time140to70, DateDiff("n",[70Time],[40Time]) AS Time70to40, tblBPDataLogger.CookTime
FROM tblBPDataLogger

 

[PHV]

In qryBPDataLogger replace this:
, tblBPDataLogger.CookTime
FROM tblBPDataLogger
with this:
, tblCookTime.CookTime
FROM tblBPDataLogger INNER JOIN tblCookTime ON tblBPDataLogger.Product = tblCookTime.Product AND tblBPDataLogger.LotNumber = tblCookTime.LotNumber

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886