Force ROW heading in a crosstab

[Big Pat]

Being an amateur at Access, I've only recently realized that you can force column headings for crosstab queries, by typing those headings into the properties of the field selected as the column. But can you do the same for ROWS?

My data concerns patients of a specific type and the times of day they show up at our A&E (ER). I've grouped these by hour and now I want to crosstab them by day of week. But if there aren't any between say 3am and 5am, those hours don't show in the query, which is screwing up a pre-formatted report I have.

I've found some assistance on other threads.....
http://www.access-programmers.co.uk/forums/showthread.php?t=92471
http://www.access-programmers.co.uk/forums/showthread.php?t=83820
.....but to be honest I didn't really follow it.

Is there an easy solution? If not, it's not a huge problem and I can "fudge" it once a month, offensive as that must sound to you professional types!

Thanks as always.

Pat

 

[gromit]

Hi Pat -

Here's a work around.
1. Set up a table that has all the Hours that you want to appear as row headings. This field needs to be compatible with the existing rows that you are getting out of the Crosstab query.

2. Make a new query that draws upon the the new table and your existing Crosstab query as sources. Make sure that you establish a relationship between the new Hours field and the X-Tab hours field.

3. Click on the line for the relationship. Where it says "join properties" include ALL the records for the new table.

hth,

- g

 

[Big Pat]

Thanks gromit,

That had occurred to me too but I had wondered if there was a "proper" way, as I'm always keen to learn. But it's great to get some confirmation that I'm along the right lines.

Thanks again,

Pat

 

[gromit]

I think the "proper" answer is that you are trying to get Access to work in a way that it isn't really intended. I.e. reporting on records that do not exist.

Sounds like you already had the workaround figured out!

- g