Help with blank/null fields

[aefv2010]

Hi,

I'm trying to extract blank fields in a query. I have written the following script:

SELECT dbo_dod_Person.Person_ID, dbo_dod_Person.Known_as_first_name, dbo_dod_Person.Known_as_surname, dbo_dod_Biog_sub_heading.Sub_heading, IIf(IsEmpty([dbo_dod_Biog_entry].[Normal_text]),"0",[dbo_dod_Biog_entry.Normal_text]) AS Normal_text, dbo_dod_Body.Body_ID, dbo_dod_Body.Body_name, IIf(IsNull([dbo_dod_Address_detail.Address_1]),"0",[dbo_dod_Address_detail.Address_1]) AS Address_1, IIf(IsNull([dbo_dod_Address_detail.Address_2]),"0",[dbo_dod_Address_detail.Address_2]) AS Address_2, IIf(IsNull([dbo_dod_Address_detail.Address_3]),"0",[dbo_dod_Address_detail.Address_3]) AS Address_3, IIf(IsNull([dbo_dod_Address_detail.Address_4]),"0",[dbo_dod_Address_detail.Address_4]) AS Address_4, IIf(IsNull([dbo_dod_Address_detail.Town_city]),"0",[dbo_dod_Address_detail.Town_city]) AS Town_city, IIf(IsNull([dbo_dod_Address_detail.Postcode]),"0",[dbo_dod_Address_detail.Postcode]) AS Postcode, IIf(IsNull([dbo_dod_Address_detail.Telephone]),"0",[dbo_dod_Address_detail.Telephone]) AS Telephone, IIf(IsNull([dbo_dod_Address_detail.Fax]),"0",[dbo_dod_Address_detail.Fax]) AS Fax, IIf(IsNull([dbo_dod_Address_detail.Email]),"0",[dbo_dod_Address_detail.Email]) AS Email

FROM (((((dbo_dod_Body INNER JOIN dbo_dod_Body_in_group ON dbo_dod_Body.Body_ID = dbo_dod_Body_in_group.Body_ID) INNER JOIN dbo_dod_Body_group ON dbo_dod_Body_in_group.Group_ID = dbo_dod_Body_group.Group_ID) INNER JOIN ((dbo_dod_Person INNER JOIN dbo_dod_Tenure ON dbo_dod_Person.Person_ID = dbo_dod_Tenure.Person_ID) INNER JOIN (dbo_dod_Role INNER JOIN dbo_Role_title ON dbo_dod_Role.Role_title_ID = dbo_Role_title.Role_title_ID) ON dbo_dod_Tenure.Role_ID = dbo_dod_Role.Role_ID) ON dbo_dod_Body.Body_ID = dbo_dod_Role.Body_ID) INNER JOIN (dbo_dod_Address_link INNER JOIN dbo_dod_Address_detail ON dbo_dod_Address_link.Address_detail_ID = dbo_dod_Address_detail.Address_detail_ID) ON dbo_dod_Tenure.Tenure_ID = dbo_dod_Address_link.Row_ID) INNER JOIN dbo_dod_Biog_entry ON dbo_dod_Person.Person_ID = dbo_dod_Biog_entry.Person_ID) INNER JOIN dbo_dod_Biog_sub_heading ON dbo_dod_Biog_entry.Biog_sub_heading_ID = dbo_dod_Biog_sub_heading.Biog_sub_heading_ID

WHERE (((dbo_dod_Person.Person_ID) In (25790,25752)) AND ((dbo_dod_Biog_sub_heading.Biog_sub_heading_ID) In (1,2,47)) AND ((dbo_dod_Body_group.Group_ID) In (79,161,162,189)) AND ((dbo_dod_Address_link.Table_ID)=34));

When I run the script, I only get results for one of the Person_IDs I am using. I am trying to return all fields, including blank fields. I have tried using IsEmpty and IsNull, but neither functions are returning the results I need.

Can someone point me in the right direction please?

Many thanks.

 

[Galaxiom]

This is an unreadable query in this format.

Use a code box to format it into lines.

Use aliases to refer to the tables everwhere except in the From statement
eg SELECT A.fieldname FROM tablename as A

 

[aefv2010]

Hi GalaxiomAtHome,

Thanks for your reply.

I wrote this in Access 2003 design view - the SQL script that I posted was automatically generated.

Can I still use your recommendations to change my script, or would I need to do something different?

Thanks again

 

[namliam]

What GAH means is that instead of something like:
FROM (((((dbo_dod_Body INNER JOIN dbo_dod_Body_in_group ON dbo_dod_Body.Body_ID = dbo_dod_Body_in_group.Body_ID) INNER JOIN dbo_dod_Body_group ON dbo_dod_Body_in_group.Group_ID = dbo_dod_Body_group.Group_ID) INNER JOIN ((dbo_dod_Person INNER JOIN dbo_dod_Tenure ON dbo_dod_Person.Person_ID
You want to use a code box and format your sql a little...

FROM (((((   dbo_dod_Body
INNER JOIN   dbo_dod_Body_in_group   ON dbo_dod_Body.Body_ID           = dbo_dod_Body_in_group.Body_ID) 
INNER JOIN   dbo_dod_Body_group      ON dbo_dod_Body_in_group.Group_ID = dbo_dod_Body_group.Group_ID) 
INNER JOIN ((dbo_... etc

You will agree that to be much more readable...

Now... as to your problem, this is probably due to one of two things
1)
Your using only inner join's that means the query requires data everywhere...
You might want to try makeing them outer joins (left or right depending on your requirement)

2)
The second Person_ID may not adhere to the full where clause ...

 

[Galaxiom]

Unfortunately Access is not very good at formatting SQL.

For the formatting on this site go to the Advanced Editor. Highlight the code and press the # button.

This will stop the code wrapping, start a new line where you tell it to and include all spaces you use rather than condensing them to one.

 

[aefv2010]

Hi,

Thank you both for your help. I will implement your suggestions and see what happens.

Thanks again