I have been tasked to look at creating a check system which will have to find records and return a value if they match a policy number. I have to compare 3 tables in all. There names are Combined, DBO and Iris

Combined has a policy number and they other 2 tables will have the policy number, what I am after is finding something called an MI Reference which is in the other 2 tables. I have been able to do this by placing the 3 table extracts into Excel and then use VLOOKUPS:

Although I can use the policy ref in a query linking the 3 tables, but if there isn't an mi reference I am not getting the correct returned records, hence considering it in excel, but I want to see if this can be done with a Database function in Access, but still including the records if there isn't an MI reference.

Any guidance would be very much appreciated.

I have thought that an IIF statement or Dlookup would be the way forward but will consider any option and of course VBA.

Trevor

syntax is basically

numeric policy number
lookedupref = nz(dlookup("mireference","iris","policynumber = " & givennumber),"Not Found")

text policy number
lookedupref = nz(dlookup("mireference","iris","policynumber = " & chr(34) & givennumber & chr(34)),"not found")


(note - chr(34) is " character)

"mireference" is "the fieldname that you are looking for"
"iris" is the "table that you are searching"
"policynumber = " & givennumber - is the condition to identify the row you are searching for

Very similar to a vlookup, in fact - but more flexible, as it doesnt need you to have the data sorted, and doesnt need you to bother about assigning a range to search

The policy ref will be text returns, but in particular I need all Policy Ref and all MI reference not a particular extract.

Thank you for considering this.

i am not following what you are doing then. Would you mind explaining again?

you have tables called combined, dbo , and iris

will a given policy number be in all tables?
is there only one instance in the combined table?
are you trying to find mireferences for this policy number in the dbo table AND the iris table, and show them in a single list?

i am not following what you are doing then. Would you mind explaining again?

you have tables called combined, dbo , and iris

will a given policy number be in all tables?
is there only one instance in the combined table?
are you trying to find mireferences for this policy number in the dbo table AND the iris table, and show them in a single list?

Each table will have the same policy ref. What I am aiming for is the combined table doesn't have MI Reference but the DBO table and Iris do. I am aiming to have a query that will show all policy refs in combined and against them (2 columns) showing the MI Reference within Dbo and then Iris so I end up with 3 columns Combined Policy Ref, Dbo MI Reference, Iris MI Reference.

I hope this helps.

Thank you

Trevor

OK

have a single query - include all the tables - the join lines shoud connect the policy ref from the combined table to the other tables - so delete any other lines that access may add.

then just pull down the fields you want to see in the final result.

does that work?

-----------------
if you have some items in the combined table that do not exist in the other tables, these will be excluded. if you want to see these as well, then right click the join-line, select join properties, and pick the option thats says

"include all items from combined and only items from the other table that are the same" - the line should change to one with a black arrow.

Thank you I will give this ago right away and see what the result is.

I will keep you informed.

Trevor

This I believe is working wonderfully thank you so much.

Trevor