MS Access - Pull record between date from other table

0
votes

I am trying to fill [SHIP REP NAME] in Table 2 with the field [REP NAME] in Table 1 with an IF statement in MS Access. but I am running into duplicates. The criteria is to pull the correct REP during the date of the shipped order

Tables

Tables

JOIN ON CUST NO

My IF Statement: REP NAME: Iff([tbl2].[ship date] between [tbl1].[eff date] and [tbl1].[exp date], [tbl1].[rep name], "UNKNOWN")

However, each record would display two records with a total of 8 instead of the original 4.

See OUTPUT: Duplicates Duplicates

1
ms-access

1 Answers

0
votes

This should work:

Select
    Table2.*,
    Table1.[Rep Name]
From
    Table1,
    Table2
Where
    Table1.[Cust No] = Table2.[Cust No]
    And
    Table2.[Ship Date] Between Table1.[Eff Date] And Table1.[Exp Date]