Look up price

[matt4003]

Hello,



I am not totally new to access, but lack some key skills.

Basically, I have a database that tracks consignment sales. I have a limited number of items (called parts), I have a consumption table and a price/spec table. The consumption table has date of consumption, part name and a variety of other informations (I am not sure is necessary for this problem). The spec/price table has each part and prices with a validity period. For example:

Part | Price | Start Date | End Date|
"A" , $50, 10/01/09, 12/31/09
"A", $55, 01/01/10, 06/30/10
"B", $40, 10/01/09, 12/31/09
"B", $35, 01/01/10, 06/30/10

I would like a query that looks at the part, the consumption date and picks the price based on the spec/price table Start Date and End Date.

Any ideas?? Do I need to post more information?

Thanks so much!

Cheers,
Matt

[RuralGuy]

Have you tried using the query builder yet?

[matt4003]

Hello RuralGuy,

Yes, but really struggled to get it to work. I seem to get the prices added together for each product. So, it will find one consumption, but add the two prices together in the results. Does that make sense? It won't find the right time range valid price.

Thanks,
Matt

[RuralGuy]

You have basically three criteria:
PartNumber
Date() => StartDate AND Date() =< EndDate

[matt4003]

Hello Again RuralGuy,

This worked great! Can't believe I missed it.

Thanks for your help!

Cheers,
Matt

[RuralGuy]

That's great! Glad I could help. Are you ready to follow the link in my sig and mark this thread as Solved?

[matt4003]

Yes, done. Thanks again!

[RuralGuy]

You're very welcome and thanks for marking the thread as Solved.