Crosstab not showing null values/fields

  • Thread starter Thread starter Dode
  • Start date Start date
D

Dode

Hi

Purpose of crosstab is to show case volumes for all employees each fiscal
period -- whether there is data or not.

Problem : not all employee names and periods are showing up in the crosstab.
Using Access 2003

Looking for the following set up where the company has just completed p03:

EmpName p01 p02 p03 p04 p05 p06 p07 ---- p12
Susan xx
Cathy xx
John xx xx
Mark xx xx

What has been done:
*Period data table : contains employee case data for each period
Field names : EmpName, Period, ScanGroup, etc
*Workgroup table : contains ALL employee names for each period (ie. each
employee name shows up twelve times -- once for each period)
Field name : EmpName, Period, Workgroup, Division, etc

*Created query called DETAILS bringing in the above two tables where
EmpName.PeriodData tbl and Period.PeriodData tbl joined to the same names in
Workgroup tbl where the join is to show all employee/periods from the
Workgroup tbl and from PeriodData tbl where the joined fields are equal.

*Created Crosstab query using PeriodData tbl and Details query where
EmpName/Period.PeriodData is joined to same names in Details query to show
all employees/periods from the Details query. The Employee Name and Period
from the Details query are used as the column and row headings, respectively
with the value being count of ScanGroup.PeriodData tbl.

Only get the records for employees where this is data. Any assistance would
be greatly appreciated.

Thank you.
 
You need to left join employee table in the crosstab.
Post your crosstab query SQL if you need more help.
 
Hi Karl,

Thanks for your response.

Still having problem with not all employees being displayed.

Did two query versions as I am now not sure which tables/query to use;
therefore, first SQL is using the DETAILS query joined to Period Table which
provides all employee/period case information. However, the values in the
crosstab are inflated/wrong.

TRANSFORM Nz(Count([TBL : Period Data].ScorecardScanGroup),0) AS
CountOfScorecardScanGroup
SELECT [****020 : Details].[Employee Name]
FROM [****020 : Details] LEFT JOIN [TBL : Period Data] ON [****020 :
Details].[Employee Name] = [TBL : Period Data].[Employee Name]
WHERE ((([TBL : Period Data].ScorecardScanGroup)="Admail/Publications") AND
(([****020 : Details].[Employee Name]) Not Like "cindy*" And ([****020 :
Details].[Employee Name]) Not Like "*murray" And ([****020 :
Details].[Employee Name]) Not Like "*reynolds") AND (([TBL : Period
Data].Division)="toronto"))
GROUP BY [****020 : Details].[Employee Name]
PIVOT [TBL : Period Data].Period;

Second SQL is using my initial Workgroup table which contains all employee
names for each period and the Period table with the employee period case
information. The values are correct with this crosstab but all employee
names/periods do not show.

TRANSFORM Nz(Count([TBL : Period Data].ScorecardScanGroup),0) AS
CountOfScorecardScanGroup
SELECT [TBL : Toronto Workgroup].[Employee Name]
FROM [TBL : Toronto Workgroup] LEFT JOIN [TBL : Period Data] ON ([TBL :
Toronto Workgroup].Period = [TBL : Period Data].Period) AND ([TBL : Toronto
Workgroup].[Employee Name] = [TBL : Period Data].[Employee Name])
WHERE ((([TBL : Period Data].ScorecardScanGroup)="Admail/Publications") AND
(([TBL : Toronto Workgroup].[Employee Name]) Not Like "cindy*" And ([TBL :
Toronto Workgroup].[Employee Name]) Not Like "*murray" And ([TBL : Toronto
Workgroup].[Employee Name]) Not Like "*reynolds") AND (([TBL : Period
Data].Division)="toronto"))
GROUP BY [TBL : Toronto Workgroup].[Employee Name]
PIVOT [TBL : Period Data].Period;

Thank you.
 
Try it without ([TBL : Toronto Workgroup].Period = [TBL : Period
Data].Period) AND ------ Watch the word wraps ---

TRANSFORM Nz(Count([TBL : Period Data].ScorecardScanGroup),0) AS
CountOfScorecardScanGroup
SELECT [TBL : Toronto Workgroup].[Employee Name]
FROM [TBL : Toronto Workgroup] LEFT JOIN [TBL : Period Data] ON ([TBL :
Toronto Workgroup].[Employee Name] = [TBL : Period Data].[Employee Name])
WHERE ((([TBL : Period Data].ScorecardScanGroup)="Admail/Publications") AND
(([TBL : Toronto Workgroup].[Employee Name]) Not Like "cindy*" And ([TBL :
Toronto Workgroup].[Employee Name]) Not Like "*murray" And ([TBL : Toronto
Workgroup].[Employee Name]) Not Like "*reynolds") AND (([TBL : Period
Data].Division)="toronto"))
GROUP BY [TBL : Toronto Workgroup].[Employee Name]
PIVOT [TBL : Period Data].Period;


Dode said:
Hi Karl,

Thanks for your response.

Still having problem with not all employees being displayed.

Did two query versions as I am now not sure which tables/query to use;
therefore, first SQL is using the DETAILS query joined to Period Table which
provides all employee/period case information. However, the values in the
crosstab are inflated/wrong.

TRANSFORM Nz(Count([TBL : Period Data].ScorecardScanGroup),0) AS
CountOfScorecardScanGroup
SELECT [****020 : Details].[Employee Name]
FROM [****020 : Details] LEFT JOIN [TBL : Period Data] ON [****020 :
Details].[Employee Name] = [TBL : Period Data].[Employee Name]
WHERE ((([TBL : Period Data].ScorecardScanGroup)="Admail/Publications") AND
(([****020 : Details].[Employee Name]) Not Like "cindy*" And ([****020 :
Details].[Employee Name]) Not Like "*murray" And ([****020 :
Details].[Employee Name]) Not Like "*reynolds") AND (([TBL : Period
Data].Division)="toronto"))
GROUP BY [****020 : Details].[Employee Name]
PIVOT [TBL : Period Data].Period;

Second SQL is using my initial Workgroup table which contains all employee
names for each period and the Period table with the employee period case
information. The values are correct with this crosstab but all employee
names/periods do not show.

TRANSFORM Nz(Count([TBL : Period Data].ScorecardScanGroup),0) AS
CountOfScorecardScanGroup
SELECT [TBL : Toronto Workgroup].[Employee Name]
FROM [TBL : Toronto Workgroup] LEFT JOIN [TBL : Period Data] ON ([TBL :
Toronto Workgroup].Period = [TBL : Period Data].Period) AND ([TBL : Toronto
Workgroup].[Employee Name] = [TBL : Period Data].[Employee Name])
WHERE ((([TBL : Period Data].ScorecardScanGroup)="Admail/Publications") AND
(([TBL : Toronto Workgroup].[Employee Name]) Not Like "cindy*" And ([TBL :
Toronto Workgroup].[Employee Name]) Not Like "*murray" And ([TBL : Toronto
Workgroup].[Employee Name]) Not Like "*reynolds") AND (([TBL : Period
Data].Division)="toronto"))
GROUP BY [TBL : Toronto Workgroup].[Employee Name]
PIVOT [TBL : Period Data].Period;

Thank you.

KARL DEWEY said:
You need to left join employee table in the crosstab.
Post your crosstab query SQL if you need more help.
 
When I remove this, it does not include the additional employees/periods and
I lose the correct results.

KARL DEWEY said:
Try it without ([TBL : Toronto Workgroup].Period = [TBL : Period
Data].Period) AND ------ Watch the word wraps ---

TRANSFORM Nz(Count([TBL : Period Data].ScorecardScanGroup),0) AS
CountOfScorecardScanGroup
SELECT [TBL : Toronto Workgroup].[Employee Name]
FROM [TBL : Toronto Workgroup] LEFT JOIN [TBL : Period Data] ON ([TBL :
Toronto Workgroup].[Employee Name] = [TBL : Period Data].[Employee Name])
WHERE ((([TBL : Period Data].ScorecardScanGroup)="Admail/Publications") AND
(([TBL : Toronto Workgroup].[Employee Name]) Not Like "cindy*" And ([TBL :
Toronto Workgroup].[Employee Name]) Not Like "*murray" And ([TBL : Toronto
Workgroup].[Employee Name]) Not Like "*reynolds") AND (([TBL : Period
Data].Division)="toronto"))
GROUP BY [TBL : Toronto Workgroup].[Employee Name]
PIVOT [TBL : Period Data].Period;


Dode said:
Hi Karl,

Thanks for your response.

Still having problem with not all employees being displayed.

Did two query versions as I am now not sure which tables/query to use;
therefore, first SQL is using the DETAILS query joined to Period Table which
provides all employee/period case information. However, the values in the
crosstab are inflated/wrong.

TRANSFORM Nz(Count([TBL : Period Data].ScorecardScanGroup),0) AS
CountOfScorecardScanGroup
SELECT [****020 : Details].[Employee Name]
FROM [****020 : Details] LEFT JOIN [TBL : Period Data] ON [****020 :
Details].[Employee Name] = [TBL : Period Data].[Employee Name]
WHERE ((([TBL : Period Data].ScorecardScanGroup)="Admail/Publications") AND
(([****020 : Details].[Employee Name]) Not Like "cindy*" And ([****020 :
Details].[Employee Name]) Not Like "*murray" And ([****020 :
Details].[Employee Name]) Not Like "*reynolds") AND (([TBL : Period
Data].Division)="toronto"))
GROUP BY [****020 : Details].[Employee Name]
PIVOT [TBL : Period Data].Period;

Second SQL is using my initial Workgroup table which contains all employee
names for each period and the Period table with the employee period case
information. The values are correct with this crosstab but all employee
names/periods do not show.

TRANSFORM Nz(Count([TBL : Period Data].ScorecardScanGroup),0) AS
CountOfScorecardScanGroup
SELECT [TBL : Toronto Workgroup].[Employee Name]
FROM [TBL : Toronto Workgroup] LEFT JOIN [TBL : Period Data] ON ([TBL :
Toronto Workgroup].Period = [TBL : Period Data].Period) AND ([TBL : Toronto
Workgroup].[Employee Name] = [TBL : Period Data].[Employee Name])
WHERE ((([TBL : Period Data].ScorecardScanGroup)="Admail/Publications") AND
(([TBL : Toronto Workgroup].[Employee Name]) Not Like "cindy*" And ([TBL :
Toronto Workgroup].[Employee Name]) Not Like "*murray" And ([TBL : Toronto
Workgroup].[Employee Name]) Not Like "*reynolds") AND (([TBL : Period
Data].Division)="toronto"))
GROUP BY [TBL : Toronto Workgroup].[Employee Name]
PIVOT [TBL : Period Data].Period;

Thank you.

KARL DEWEY said:
You need to left join employee table in the crosstab.
Post your crosstab query SQL if you need more help.

:

Hi

Purpose of crosstab is to show case volumes for all employees each fiscal
period -- whether there is data or not.

Problem : not all employee names and periods are showing up in the crosstab.
Using Access 2003

Looking for the following set up where the company has just completed p03:

EmpName p01 p02 p03 p04 p05 p06 p07 ---- p12
Susan xx
Cathy xx
John xx xx
Mark xx xx

What has been done:
*Period data table : contains employee case data for each period
Field names : EmpName, Period, ScanGroup, etc
*Workgroup table : contains ALL employee names for each period (ie. each
employee name shows up twelve times -- once for each period)
Field name : EmpName, Period, Workgroup, Division, etc

*Created query called DETAILS bringing in the above two tables where
EmpName.PeriodData tbl and Period.PeriodData tbl joined to the same names in
Workgroup tbl where the join is to show all employee/periods from the
Workgroup tbl and from PeriodData tbl where the joined fields are equal.

*Created Crosstab query using PeriodData tbl and Details query where
EmpName/Period.PeriodData is joined to same names in Details query to show
all employees/periods from the Details query. The Employee Name and Period
from the Details query are used as the column and row headings, respectively
with the value being count of ScanGroup.PeriodData tbl.

Only get the records for employees where this is data. Any assistance would
be greatly appreciated.

Thank you.
 
Hi Karl,

I was able to figure out part of the problem and do a work around on the
other.

To get all the periods showing, I went into the Query Properties and
identified all the column headings (ie p01, p02, p03.... p12)

As for the employees, since this query is being used for a Database Query in
Excel, I did a vlookup formula where false in my excel table if the employee
name is not identified.

Thank you very much for all your assistance.



Dode said:
When I remove this, it does not include the additional employees/periods and
I lose the correct results.

KARL DEWEY said:
Try it without ([TBL : Toronto Workgroup].Period = [TBL : Period
Data].Period) AND ------ Watch the word wraps ---

TRANSFORM Nz(Count([TBL : Period Data].ScorecardScanGroup),0) AS
CountOfScorecardScanGroup
SELECT [TBL : Toronto Workgroup].[Employee Name]
FROM [TBL : Toronto Workgroup] LEFT JOIN [TBL : Period Data] ON ([TBL :
Toronto Workgroup].[Employee Name] = [TBL : Period Data].[Employee Name])
WHERE ((([TBL : Period Data].ScorecardScanGroup)="Admail/Publications") AND
(([TBL : Toronto Workgroup].[Employee Name]) Not Like "cindy*" And ([TBL :
Toronto Workgroup].[Employee Name]) Not Like "*murray" And ([TBL : Toronto
Workgroup].[Employee Name]) Not Like "*reynolds") AND (([TBL : Period
Data].Division)="toronto"))
GROUP BY [TBL : Toronto Workgroup].[Employee Name]
PIVOT [TBL : Period Data].Period;


Dode said:
Hi Karl,

Thanks for your response.

Still having problem with not all employees being displayed.

Did two query versions as I am now not sure which tables/query to use;
therefore, first SQL is using the DETAILS query joined to Period Table which
provides all employee/period case information. However, the values in the
crosstab are inflated/wrong.

TRANSFORM Nz(Count([TBL : Period Data].ScorecardScanGroup),0) AS
CountOfScorecardScanGroup
SELECT [****020 : Details].[Employee Name]
FROM [****020 : Details] LEFT JOIN [TBL : Period Data] ON [****020 :
Details].[Employee Name] = [TBL : Period Data].[Employee Name]
WHERE ((([TBL : Period Data].ScorecardScanGroup)="Admail/Publications") AND
(([****020 : Details].[Employee Name]) Not Like "cindy*" And ([****020 :
Details].[Employee Name]) Not Like "*murray" And ([****020 :
Details].[Employee Name]) Not Like "*reynolds") AND (([TBL : Period
Data].Division)="toronto"))
GROUP BY [****020 : Details].[Employee Name]
PIVOT [TBL : Period Data].Period;

Second SQL is using my initial Workgroup table which contains all employee
names for each period and the Period table with the employee period case
information. The values are correct with this crosstab but all employee
names/periods do not show.

TRANSFORM Nz(Count([TBL : Period Data].ScorecardScanGroup),0) AS
CountOfScorecardScanGroup
SELECT [TBL : Toronto Workgroup].[Employee Name]
FROM [TBL : Toronto Workgroup] LEFT JOIN [TBL : Period Data] ON ([TBL :
Toronto Workgroup].Period = [TBL : Period Data].Period) AND ([TBL : Toronto
Workgroup].[Employee Name] = [TBL : Period Data].[Employee Name])
WHERE ((([TBL : Period Data].ScorecardScanGroup)="Admail/Publications") AND
(([TBL : Toronto Workgroup].[Employee Name]) Not Like "cindy*" And ([TBL :
Toronto Workgroup].[Employee Name]) Not Like "*murray" And ([TBL : Toronto
Workgroup].[Employee Name]) Not Like "*reynolds") AND (([TBL : Period
Data].Division)="toronto"))
GROUP BY [TBL : Toronto Workgroup].[Employee Name]
PIVOT [TBL : Period Data].Period;

Thank you.

:

You need to left join employee table in the crosstab.
Post your crosstab query SQL if you need more help.

:

Hi

Purpose of crosstab is to show case volumes for all employees each fiscal
period -- whether there is data or not.

Problem : not all employee names and periods are showing up in the crosstab.
Using Access 2003

Looking for the following set up where the company has just completed p03:

EmpName p01 p02 p03 p04 p05 p06 p07 ---- p12
Susan xx
Cathy xx
John xx xx
Mark xx xx

What has been done:
*Period data table : contains employee case data for each period
Field names : EmpName, Period, ScanGroup, etc
*Workgroup table : contains ALL employee names for each period (ie. each
employee name shows up twelve times -- once for each period)
Field name : EmpName, Period, Workgroup, Division, etc

*Created query called DETAILS bringing in the above two tables where
EmpName.PeriodData tbl and Period.PeriodData tbl joined to the same names in
Workgroup tbl where the join is to show all employee/periods from the
Workgroup tbl and from PeriodData tbl where the joined fields are equal.

*Created Crosstab query using PeriodData tbl and Details query where
EmpName/Period.PeriodData is joined to same names in Details query to show
all employees/periods from the Details query. The Employee Name and Period
from the Details query are used as the column and row headings, respectively
with the value being count of ScanGroup.PeriodData tbl.

Only get the records for employees where this is data. Any assistance would
be greatly appreciated.

Thank you.
 
Back
Top