Query to include results without certain values

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Query to include results without certain values

Post by Egg 'n' Bacon »

Got another problem/issue/quandry; I need a report (based on a crosstab) that displays;
Which people are trained, on what skills, by department.

The problems are;
* there can be a specified required skill, that no-one in the department has a record for; this needs displaying.
* there can be new people who do not have any records, but these too must be displayed.

I have got so far, but I am stuck with how to include both those records that have no "values".

I have included the basic components I am using (minus the selection form and report).
The current sequence of events is;
1 query qryMatrixSWPTempTblDEL runs (deletes records in the temporary table tblTempDeptSWPSkill), followed by;
2 query qryMatrixSWP1 runs (adds records to tblTempDeptSWPSkill), followed by;
3 query qryMatrixSWP2 runs (adds records to tblTempDeptSWPSkill), followed by;
4 the report that uses qryMatrixSWP3 for source data.

At the moment, If there is a a skill where no-one in the department is trained, this shows up on the qurey and report, along with the names of persons in that department without any training records. However, if there are no such skills, these people are left out. :hairout:

More... when putting together the stripped-down version, qryMatrixSWP2, does not work the same as where it came from!!! The people without records are no longer included :bwaah:

This one is definately beyond me!
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Query to include results without certain values

Post by HansV »

I'll get back to you, but it'll take a bit of time to understand the relationships and requirements.
Best wishes,
Hans

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Query to include results without certain values

Post by Egg 'n' Bacon »

Thank you Hans.

On the relationships, there are three main tables (tblDept, TblPerson and TblSkills) that have (in effect) many-to-many relationships to the other two.

HTH

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Query to include results without certain values

Post by Wendell »

While Hans is studying the details of your database, one possible solution to a skill not showing up in the crosstab is to use the Column Headings to force the category to appear whether or not there is data.

Added: To get the people with no skills to show up, you will probably need to run a second query which uses the Crosstab query as one of it's tables and join it to a query that lists all of the people for that department using an outer join.
Wendell
You can't see the view if you don't climb the mountain!

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Query to include results without certain values

Post by Egg 'n' Bacon »

Hi Wendell, I'm afraid I'm not very good at descibing details like this, in text. But I can get the blank skill to show, it's really the people with no records that is the real problem (people... problem... sounds familiar :) ).

I'm liking the sound of using the crosstab as one of the data sources, instead of the final one. I'll have a look tomorrow (time permitting).

Cheers

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Query to include results without certain values

Post by HansV »

I have attached a version with modified queries. The two crosstab queries return the required and attended courses for everyone, one with courses as row headers and persons as column headers, the other with persons as row headers and courses as column headers.
Database8.zip
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Query to include results without certain values

Post by Egg 'n' Bacon »

Thank you. That looks just the job!

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Query to include results without certain values

Post by Egg 'n' Bacon »

Sorry; back again :evilgrin:

The queries work fine (I selected qryMatrixSWP_B), but when used as the source for the report, the following error is triggered;
Too few parameters. Expected 1.
I had added criteria ([forms]![frmDeptMatrixSel]![CboDeptName]) to qryAttendedSWP and qryPersonSWP (in place of "Sales and Marketing"), with associated Parameters.

Only to get a different error;
The Microsoft Access database engine does not recognise "forms!frmDeptMatrixSel!CboDeptName" as a valid field name or expression.
So I changed qryAttendedSWGSB to;

Code: Select all

SELECT qryAttendedSWPAll.PName, qryAttendedSWPAll.CName, TblAttendance.GSB
FROM qryAttendedSWPAll LEFT JOIN TblAttendance ON (qryAttendedSWPAll.CID = TblAttendance.CourseID) AND (qryAttendedSWPAll.PersID = TblAttendance.PersonID)
UNION SELECT [FirstName] & " " & [LastName] AS PName, TblSkills.CName, Null AS GSB
FROM TblSkills INNER JOIN tblDeptSkills ON TblSkills.CID = tblDeptSkills.SkillID, TblPerson INNER JOIN tblPersDept ON TblPerson.PersID = tblPersDept.PersID
WHERE (((TblSkills.CName) Like "SWP" & "*") AND ((TblSkills.Obsolete)=False) AND ((tblDeptSkills.Dept)=[forms]![frmDeptMatrixSel]![CboDeptName]) AND ((tblPersDept.DEnd) Is Null) AND ((tblPersDept.Dept)=[forms]![frmDeptMatrixSel]![CboDeptName]));
This too results in the Parameters error.

Maybe I should have included more in the sample database??? But there is a lot to include.

Anyway, I'm away for a few days, when I get back, I'll have a go at doing that.

Have a great weekend.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Query to include results without certain values

Post by HansV »

Open the crosstab query in design view.
Select Query | Parameters... (Access 2003 or before) or click Parameters on the Design tab of the ribbon (Access 2007 or later).
Enter [forms]![frmDeptMatrixSel]![CboDeptName] in the Parameter box, and select Text from the Data Type dropdown.
Click OK.
Save and close the query.
Does that help?
Best wishes,
Hans

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Query to include results without certain values

Post by Egg 'n' Bacon »

I'm currently away from my work PC so I'm not sure which was the crosstab query.

I tried adding to qryAttendedSWP and qryPersonSWP, but that resulted in the second error :( .

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Query to include results without certain values

Post by HansV »

The crosstab query is qryMatrixSWP_B.
Best wishes,
Hans

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Query to include results without certain values

Post by Egg 'n' Bacon »

OK added that, but still have the parameters error.

NB doing this remotely so VERY slow.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Query to include results without certain values

Post by HansV »

Would it be possible to attach an updated version of the database?
Best wishes,
Hans

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Query to include results without certain values

Post by Egg 'n' Bacon »

I'll get an updated version out next week.

Cheers

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Query to include results without certain values

Post by Egg 'n' Bacon »

Hi again,

Here is a 'fuller' sample of the database.

TIA E'n'B
Database9.zip
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Query to include results without certain values

Post by HansV »

1) Open qry000 in design view.
Click Parameters on the Design tab of the ribbon, or select Query | Parameters...
Enter [forms]![frmDeptMatrixSel]![CboDeptName] in the Parameter column, and set the Data Type to Text.
Click OK, then close and save the query.

2) Open the report in design view.
Click View Code, and go to the Report_Open event procedure.
Uncomment the line about the query parameter, and change it to

Code: Select all

  qdf.Parameters("[forms]![frmDeptMatrixSel]![CboDeptName]") = [Forms]![frmDeptMatrixSel]![CboDeptName]
Close and save the report.
Best wishes,
Hans

Egg 'n' Bacon
5StarLounger
Posts: 736
Joined: 18 Mar 2010, 11:05

Re: Query to include results without certain values

Post by Egg 'n' Bacon »

Tara! :clapping:

Thank you sir. I had no idea that would have to add that parameter to that query (as it doesn't use the criteria).


Thank you :thankyou:

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Query to include results without certain values

Post by HansV »

It uses the criteria indirectly, since the queries it's based on are parameter queries with [forms]![frmDeptMatrixSel]![CboDeptName] as parameter.
Best wishes,
Hans