How to deal with Null values in query (1 Viewer)

setis · Oct 11, 2017

Dear all,

I have a query in which based on the CaseID on the active form, it should return several values from that form.

One of the values is ServiceType but when there's no data (it can be), the query is returned empty for all values.

There is a relationship on the query so it should return ServiceType instead of ServiceTypeID, but this is probably irrelevant.

I've seen some examples using the Nz function for numeric values, but this should return text and my attempts failed..

Thanks in advance

Minty · Oct 11, 2017

We will need to see the relationships and the SQL of the query in order to help you.

setis · Oct 11, 2017

Minty said:
We will need to see the relationships and the SQL of the query in order to help you.

This is it. Thanks for looking into it.

The problem is when any of the tblOut3rdPartyServices or the currencies are empty

Minty · Oct 11, 2017

Your data isn't stored in a normalised fashion, which is one reason you are now having some issues.
If you have multiple tables storing the same information you have a design problem.
Any time you have a table or fields called 1stThing, 2ndThing, 99thThing, etc you are heading to the dark side of pain and suffering.

The short term fix is to click on the link between the tblOut3rdPartyServices and the other tables that may not have records and select the option to include all rows from IncomingCases and only the matching rows from the other table. This is called a left join.

Currently you're joining only where the items exist in both tables, called a full or inner join.

But fix your data layout first - it's not correct.

arnelgp · Oct 11, 2017

Edit you Relationship.
Right-Click on those lines.
choose Edit Relationship...
click Join Type..

and choose this one:

Include ALL records from 'IncomingCases' and only those records from
'[theOtherTableHere]' where the joined fields are equal.

this will create a Left Join to table IncomingCases.

setis · Oct 11, 2017

Minty said:
Your data isn't stored in a normalised fashion,

Thanks for this, actually the 4 "Currency" tables are the same one and same for "tblOut3rdPartyServices". The thing is that I need returned in the query the values for 1#, 2# and 3# invoice currency and 3rdPartyServicesID1, 2 and 3. But I need the values and not the Keys

Can I still get that in the results if the relationships are as attached?

arnelgp said:
Edit you Relationship.

Thanks for this. I did this now, but I'm getting "The SQL statement could not be executed because it contains ambiguous outher joins"

Minty · Oct 11, 2017

setis said:
Thanks for this, actually the 4 "Currency" tables are the same one and same for "tblOut3rdPartyServices". The thing is that I need returned in the query the values for 1#, 2# and 3# invoice currency and 3rdPartyServicesID1, 2 and 3. But I need the values and not the Keys

Can I still get that in the results if the relationships are as attached?

If it's the same table - in that case leave things as you had them, just change the join type - which you look like you had achieved. The Aliased table name fooled me.

Thanks for this. I did this now, but I'm getting "The SQL statement could not be executed because it contains ambiguous outher joins"

I think putting back the currency table multiple times will sort this out.

You should still have a separate table for the third parties/ invoices though, then you would only need the related tables once against the new related 3rd Party table.

setis · Oct 11, 2017

It works now. Thank you very much to both of you!

How to deal with Null values in query (1 Viewer)

setis

Registered User.

Minty

AWF VIP

setis

Registered User.

Attachments

Minty

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

setis

Registered User.

Attachments

Minty

AWF VIP

setis

Registered User.

Similar threads

Users who are viewing this thread