Rows Without Data

[Floppy]

How to display rows w/no data in query?

I have a crosstab query that produces 14 columns and a variable number of rows.

The columns are as follows:
Col 1: Data values in table
Col 2: Total of columns 3-14 in the row
Columns 3-14: Jan, Feb, Mar, Apr, etc (months of the year)

The number of rows maxes out at 9, which is the number of values permitted in the data field of the table. Example is:

FCD
FPA
FPO
MPR
etc.

Early in the year, if the table containing the data has no entries for certain values, such as FPA, then the displayed query lacks that row: An example would be where we have not yet entered FPO as a field value for 2003 records, it would look like:

FCD
FPA
MPR
etc.

Is there a way to force display of the FPO row even though there are no FPO values in the filtered query?

The SQL statement I created reads:

TRANSFORM Count(qryIntakeForm.IntakeDate) AS CountOfIntakeDate
SELECT qryIntakeForm.SupervisionType, Count(qryIntakeForm.IntakeDate) AS [Total Of IntakeDate]
FROM qryIntakeForm
WHERE (((qryIntakeForm.IntakeDate) Between #1/1/2003# And #12/31/2003#))
GROUP BY qryIntakeForm.SupervisionType
PIVOT Format([IntakeDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


Thanks,

Flop

 

[Jon K]

Try this. Create a lookup table tblLookup with a SupervisionType field. Place the 9 supervision types in the table.

Then create a new query, joining the lookup table and crosstab query as follows (using the correct name of the crosstab query):-

SELECT tblLookup.SupervisionType, [Total Of IntakeDate], Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
FROM tblLookup LEFT JOIN qryCrossTab ON tblLookup.SupervisionType = qryCrossTab.SupervisionType;

 

[Floppy]

Interesting

Jon

Interesting approach - kind of kludgy and I may have to resort to using it. I was hoping there would be a way of forcing the rows to be displayed in somewhat the same way that I "forced" the columns to be displayed with or without data.

Flop

 

[pbuethe]

Jon K, how do you force this to show all the columns with or without data? I got it to work for the rows.

 

[pbuethe]

Thanks Pat (and Jon). It works now.