Showing Blank fields in Query

[Beginner]

Hi Everyone,

I am a beginner, so please be gentle and thank you in advance.

I need to produce a query that will show a blank fields:

Example

I have a SubcontractorID, If there are matching records in Public Liability
Insurance, show that record, if there is a matching record in Workers
Compensation Insurance, show that record, if there is a record in Trade
Contractors Licence, show that record. What happens is obviously that it
will only show records where the client has matching records in all 3 tables.
However I want it to show anything that is present and leave a blank field
if the record does not exist.

Bear in mind that the client name is taken from a query showing only active
clients, and that I have also created a field that shows which site these
clients attend, so there are already a few fields.

Is this possible?

 

[Michel Walsh]

Make a new query.

Bring the four tables. A table with all possible subcontractorID, and the
three other tables: Liabilities, Compensations, Licences.

From the table having all possible subcontractorID, drag that field over the
same field in Liabilities. Then, right click the line just drawn, and select
the option to keep all records from the table with all possible
subcontractorID.

Repeat the same process, but using Compensations table instead of
Liabilities.

Repeat the same process, but using Licences table instead of Liabilities.

Drag the fields you want from the tables you want, in the grid.


Fine?


No, because you don't have a table with all possible subcontractorID?

No problem. Make a new query, edit, in SQL view:

SELECT subcontractorID FROM liabilities
UNION
SELECT subcontractorID FROM compensations
UNION
SELECT subcontractorID FROM licences

save that query, and use it as the 'table having all possible
subcontractorID'. Saved queries can be used instead of tables, in most
cases.


Hoping it may help,
Vanderghast, Access MVP

 

[Beginner]

Thank you very much,

From your response I was able to resolve the problem.