Display blanks

[Aoife]

Hi

I've got one table called CompanyIndustry details comprising of

ciID (autonumber)
CompanyIndustry (food, agriculture and pharmaceutical)

A second table called CompanyDetails has following fields:

cdID (autonumber)
CompanyIndustry
Address
etc

I'd like to create a query so that all industries are listed even if there are no corresponding records for eg a food company in the CompanyDetails table.

I've attempted to do this through left join on the CompanyIndustry fields with no success. Any pointers?

 

[sneuberg]

Sometime I get them backwards. The join should say all records in CompanyIndustry.... Would you show us the SQL of your query?

 

[CJ_London]

not sure why a left join would not work unless you had it the wrong way round

SELECT *
FROM CompanyIndustry LEFT JOIN CompanyDetails ON CompanyIndustry.CiD=CompanyDetails.CompanyIndustry

From your field naming, my guess is you are using a lookup in your companydetails table for companyindustry. If so, I recommend you stop using them - they cause more trouble than they are worth

 

[Aoife]

Many thanks to you both, I went back to basics/had a sleep/used your SQL and the query worked on all records.

The problem I had was that I was using a > date criteria to retrieve records of a certain age. Had to add 'Is Null' to the Or line in the query to pull in any blank records on the CompanyIndustry side.

Cheers