Query using IIf(IsNull) expression

[jmartin]

I have 2 tables built into my query. I have the tables linked by 2 fields: 1) supplier ID, 2) invoice #.

I have the join properties set to include all records from table 1 and only those records from table 2 where joined fields are equal.

I am wanting to pull all of the fields into the query from the 1st table (grouping all fields except summing the invoice amount), while at the same time wanting to know if a specific invoice from table 1 matches table 2, then I want the expression to say "yes".



Below is the expression.

Expr1: IIf(IsNull([tbl_ARIBA Invoice Report]![Invoice Number]),"NO","YES")

I am finding out that when an invoice from table 1 is matched to an invoice in table 2, the sum of the invoice amount is twice as much as it should be. If the invoice from table 1 is not in table 2, the invoice amount is correct.

I am not sure where I am going wrong here and I hope this makes sense.

Any ideas?

Thanks so much in advance,

Jeff

[Rainlover]

I would suggest that you only use one Table for all your invoices.

This is a Normalisation Rule.

[jmartin]

I don't have all the date I need in one table...some data is in one table and the other is in the other table

[Rainlover]

Please describe your structure