Split data based on date & recombine automatically

[NeoFax]

I have a table that contains part numbers, allowed ship duration and
date of change. So, there are multiple instances of the same part
number, but each instance is different from the last based on change
date and allowed ship duration. When I combine this table in a query
with another table I get duplicates of each part number that has
duplicates in the first table. What I would like to do is have the
query do a iif() function that basically goes if part number x with
purchase doc x matches tblA part number X and between #1/1/1900# and
#dateX# then use ship duration X, if not check for next instance of
partX and is the date between #dateX# and #dateY# (this instance of
partX date field), if not check for next instance until exhausted or
match happens. Is there any way to accomplish this?

 

[PieterLinden via AccessMonster.com]

I have a table that contains part numbers, allowed ship duration and
date of change. So, there are multiple instances of the same part
number, but each instance is different from the last based on change
date and allowed ship duration. When I combine this table in a query
with another table I get duplicates of each part number that has
duplicates in the first table. What I would like to do is have the
query do a iif() function that basically goes if part number x with
purchase doc x matches tblA part number X and between #1/1/1900# and
#dateX# then use ship duration X, if not check for next instance of
partX and is the date between #dateX# and #dateY# (this instance of
partX date field), if not check for next instance until exhausted or
match happens. Is there any way to accomplish this?

Sounds like you should be able to do a plain union query (without the ALL
keyword, because you don't want the duplicates, right?) Then you could just
create a parameter query based on the union query and get what you need
(prompt the user for values etc)