Combine Dlookup and 'if' statement in a query

[deemat]

Hello,



Would appreciate any assistance with this.. I have 2 excel files that need to be washed against the other to get unique sales. File A has all the Sale Ids, Sale Type (this column will usually have a '1' in it) and date of sale and File B has some of the sale IDs and date of sale.

I need the query to do the following

1. Look up a sale in file A in file B and if its found then return the Sale Type (from file A)
2. If the sale is not found in File B and the date of Sale is less than equal to 5 days, count as sale (Put a number one in column), else '0'

Thank you,

[ranman256]

build a table and index the fields you dont want repeated.
import the XL files into this table, it will ignore duplicates.

1. join file A and B on matching keys, build a query that returns the data you want
2. build an outer join on A and B and put null in File B field criteria to get the items NOT in B. and
=iif(DateDiff("d",[SaleDate],date()) <=5, DateDiff("d",[SaleDate],date()), 0)