Query to include results without certain values
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Query to include results without certain values
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.
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
This one is definately beyond me!
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.
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
This one is definately beyond me!
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query to include results without certain values
I'll get back to you, but it'll take a bit of time to understand the relationships and requirements.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Query to include results without certain values
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
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
-
- 4StarLounger
- Posts: 482
- Joined: 24 Jan 2010, 15:02
- Location: Colorado, USA
Re: Query to include results without certain values
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.
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!
You can't see the view if you don't climb the mountain!
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Query to include results without certain values
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
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
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query to include results without certain values
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Query to include results without certain values
Thank you. That looks just the job!
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Query to include results without certain values
Sorry; back again
The queries work fine (I selected qryMatrixSWP_B), but when used as the source for the report, the following error is triggered;
Only to get a different error;
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.
The queries work fine (I selected qryMatrixSWP_B), but when used as the source for the report, the following error is triggered;
I had added criteria ([forms]![frmDeptMatrixSel]![CboDeptName]) to qryAttendedSWP and qryPersonSWP (in place of "Sales and Marketing"), with associated Parameters.Too few parameters. Expected 1.
Only to get a different error;
So I changed qryAttendedSWGSB to;The Microsoft Access database engine does not recognise "forms!frmDeptMatrixSel!CboDeptName" as a valid field name or expression.
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]));
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.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query to include results without certain values
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?
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
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Query to include results without certain values
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 :( .
I tried adding to qryAttendedSWP and qryPersonSWP, but that resulted in the second error :( .
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query to include results without certain values
The crosstab query is qryMatrixSWP_B.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Query to include results without certain values
OK added that, but still have the parameters error.
NB doing this remotely so VERY slow.
NB doing this remotely so VERY slow.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query to include results without certain values
Would it be possible to attach an updated version of the database?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Query to include results without certain values
I'll get an updated version out next week.
Cheers
Cheers
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Query to include results without certain values
Hi again,
Here is a 'fuller' sample of the database.
TIA E'n'B
Here is a 'fuller' sample of the database.
TIA E'n'B
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query to include results without certain values
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
Close and save the report.
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]
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 736
- Joined: 18 Mar 2010, 11:05
Re: Query to include results without certain values
Tara!
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
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
-
- Administrator
- Posts: 78236
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Query to include results without certain values
It uses the criteria indirectly, since the queries it's based on are parameter queries with [forms]![frmDeptMatrixSel]![CboDeptName] as parameter.
Best wishes,
Hans
Hans