CODEWITHSUNDEEP
sqlms-access
user3788728
user3788728

Reputation: 59

MS Access Query - Pull data from different fields

I am working with a database. I have information from Reference1, Reference2, etc. I am trying to create a query using these information. For one of the fields, my goal is to go with data from Reference1 first. If blank, to take the data from Reference2, and so on. Is this possible in Access? If so, can someone advise on how I can approach this? My experience with Access has been superficial and this is beyond my knowledge. Thanks.

Upvotes: 0

Views: 216

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

Each database typically has a function that substitutes a value with something else if it is null, in Oracle you have NVL, in MySQL you have IFNULL, etc.

In Access, you have the NZ function: http://www.techonthenet.com/access/functions/advanced/nz.php

You probably want the following:

nz(Reference1,Reference2) as reference

One thing to consider is whether each of these fields is on the same table or on a different table. If one is on table a, and the other on table b, make sure you use an outer join rather than an inner join (your question implies reference1 may be null), and add the aliases before each column name to indicate the table.

You can also use the IIF function like so:

iif(Reference1 is null, Reference2, Reference1) as reference

Essentially equating to "if reference1 is null, then reference2, else reference1"

Upvotes: 1

Related Questions