Crosstab not showing null values/fields

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.
 
K

KARL DEWEY

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

Dode

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.
 
K

KARL DEWEY

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.
Click to expand...
Click to expand...
 
D

Dode

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.
Click to expand...
Click to expand...
Click to expand...
 
D

Dode

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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab queries, <> and null values 3
Crosstab Query Issue 1
How can you compress / group by on a crosstab query when column names are unknown 2
Select Query & Crosstab Query 1
Crosstab?? 3
Nz function not showing all records 2
Grouping and Aggregating Data in Jet SQL 1
Adding Values From Two Numeric Fields 1

Top