Query that displays values that have no current value.

[Boost]

Hi there, I'd like to achieve the following.



I have four tables, which I have pulled into individual queries that represent categories. They are as follows:
- qry_Languages
- qry_Qualifications
- qry_Registrations
- qry_Skills

Each query contains a PK that is shared by the others (PK = "SAP#").

Rather than having four separate queries for end-users to trawl through, I'd like to engineer one query that contains all the records for each PK/SAP#.

I gave it a try, but it only reports back on PK/SAP#'s that have a record in each individual query / a complete recordset.

The issue I face is that, a PK/SAP# may have only a language, and skill value in their record. While another PK/SAP# may have a language, registration, and qualification value under their record.

Ideally I would like to have the query have a list of all PK/SAP#'s - with the values held showing, as well as where there is no value.

Hope that kind of identified what I'm trying to achieve.

Boost

[pbaldy]

Sounds like you need to edit the join between the table with aal records and each of of those queries. Double or right click to do that.

[ranman256]

take all those queries and put into 1 single UNION query.

[isladogs]

Two questions
1. Do any of your tables contain the full list of PK values?
2. Do your tables each have just one record for a PK value?

BTW using special characters like # in field names is not a good idea

[Boost]

Hi ridders52,

1. Yes, one table does contain all of the PK values
2. No, the tables have multiple records under a PK (IE. PK #1 = French, PK #1 = German)

[isladogs]

Hi ridders52,

1. Yes, one table does contain all of the PK values
2. No, the tables have multiple records under a PK (IE. PK #1 = French, PK #1 = German)

1. OK - which table or query? Knowing this means it can be done with joins rather than a union query.
2. That makes it a bit more complex to do in one query especially if all tables have multiple records for each PK

Need to see your table designs and some sample data to advise on the bed approach