How to specify row names in MS Access 2007

I have a cross tab 开发者_JAVA百科query and it pulls only the row name if there is data associated with it in the database. For example, if I have three types of musical instruments:

Guitar

Piano 

Drums

Other

My results will show up as:

Guitar        1

Drums         2

It doesn't list Piano because there is no ID associated with Piano in the DB. I know I can specify columns in the properties menu, i.e. "1, 2, 3, 4, 5" will put columns in the DB for each, regardless of whether or not there is data to populate them.

I am looking for a similar solution for rows. Any ideas?

Also, I need NULL values to show up as 0.

Here's the actual SQL (forget the instrument example above)

TRANSFORM Count(Research.Patient_ID) AS CountOfPatient_ID
SELECT
  Switch(
    [Age]<22,"21 and under",
    [Age]>=22 And [AGE]<=24,"Between 22 And 24",
    [Age]>=25 And [AGE]<=29,"Between 25 And 29",
    [Age]>=30 And [AGE]<=34,"30-34",
    [Age]>=35 And [AGE]<=39,"35-39",
    [Age]>=40 And [AGE]<=44,"40-44",
    [Age]>44,"Over 44"
  ) AS Age_Range
FROM (Research
  INNER JOIN (
    SELECT ID, DateDiff("yyyy",DOB,Date()) AS AGE FROM Demographics
  ) AS Demographics ON Research.Patient_ID=Demographics.ID)
  INNER JOIN [Letter Status] ON Research.Patient_ID=[Letter Status].Patient_ID
WHERE ((([Letter Status].Letter_Count)=1))
GROUP BY Demographics.AGE, [Letter Status].Letter_Count
PIVOT Research.Site In (1,2,3,4,5,6,7,8,9,10);

In short, I need all of the rows to show up regardless of whether or not there is a value (for some reason the LEFT JOIN isn't working, so if you can, please use my code to form your answer), and I also need to replace NULL values with 0.

Thanks

I believe this has to do with the way you are joining the instruments table to the IDs table. If you use a left outer join from instruments to IDs, Piano should be included. It would be helpful to see your actual tables and queries though, as your question is kind of vague.

What if you union the select with a hard coded select with one value for each age group.

select 1 as Guitar, 1 as Piano, 1 as Drums, 1 as Other

When you do the transform, each row will have a result that is +1 of the result you want.

foo      barTmpCount
-------- ------------
Guitar   2
Piano    1
Drums    3
Other    1

You can then do a

select foo, barTmpCount - 1 as barCount from <query>

and get something like this

foo      barCount
-------- ---------
Guitar   1
Piano    0
Drums    2
Other    0