how to get unrepresented categories to show up in an xtab query

[starlingseven via AccessMonster.com]

hello:

I have set up a db that creates several cross-tab queries in the end that I
want to export to Excel to an existing template that has all of the analyses
I need in it -
that part I've done, but I'm having problems with something seemingly simple -

in the crosstabs, if a group is not represented, it is dropped out - this is
fine in Access, but the moment it is exported into Excel, the data aren't
analysed correctly. so for example, w/ the following age groups in my
student data

Under 20
21-25
26-30
31-35
36-40
41-45
46-50
51-55
56-60
60-65
66+

many times, the last three groups don't have any entries in the crosstab,
therefore Access just drops them off the list - is there any way to have all
of the possible groups displayed even if there are no entries in that group
so the data are exported to Excel in exactly the same format each time?

thanks!

mya

 

[George Nicholson]

In Query design view:
-View>Properties
-Click in the 'table' area of the query to get "Query Properties" (as
opposed to Field Propeties)
-Set the ColumnHeadings property to a comma delimited list of your
groups ("Under 20","21-25", etc.). The list should be in the order that you
wish them to appear and must exactly match the values of whatever field is
set to be the Column Heading field.

All of the specified Headings will have a column in the output, data or not.

HTH,

 

[Guest]

If you need your age groups to to row headings then create a table that has
them and left join it to the rest of the crosstab query like this ---
TRANSFORM Nz(Sum([Trial].qty_req),0) AS SumOfqty_req
SELECT AgeGroup.Group
FROM AgeGroup LEFT JOIN [Trial] ON AgeGroup.Group = [Trial].Group
GROUP BY AgeGroup.Group
PIVOT [Trial].[Week Num];
--
KARL DEWEY
Build a little - Test a little

In Query design view:
-View>Properties
-Click in the 'table' area of the query to get "Query Properties" (as
opposed to Field Propeties)
-Set the ColumnHeadings property to a comma delimited list of your
groups ("Under 20","21-25", etc.). The list should be in the order that you
wish them to appear and must exactly match the values of whatever field is
set to be the Column Heading field.

All of the specified Headings will have a column in the output, data or not.

HTH,

 

[starlingseven via AccessMonster.com]

I tried to do a left-join with a table that already contains the row headings
- there's an issue that it also has to include null values (some students did
not write in their ages, this is recorded as a null in the state-maintained
db that contains the data i start with) as some of the row headings. when I
join the two tables, even if I put a null value as a row header in the second
table, it drops these values out - any ideas how I can get Access to include
these?

thanks!

mya

If you need your age groups to to row headings then create a table that has
them and left join it to the rest of the crosstab query like this ---
TRANSFORM Nz(Sum([Trial].qty_req),0) AS SumOfqty_req
SELECT AgeGroup.Group
FROM AgeGroup LEFT JOIN [Trial] ON AgeGroup.Group = [Trial].Group
GROUP BY AgeGroup.Group
PIVOT [Trial].[Week Num];

 

[Michel Walsh]

Make an age group with a special label, such a 'unknown' and


.... FROM AgeGroup LEFT JOIN [Trial] ON AgeGroup.Group = Nz([Trial].Group,
"unknown")


So, if Trial.Group is NULL, it will be changed to 'unknown', the special
label you created, and then, will fit the special group.



Vanderghast, Access MVP

I tried to do a left-join with a table that already contains the row
headings
- there's an issue that it also has to include null values (some students
did
not write in their ages, this is recorded as a null in the
state-maintained
db that contains the data i start with) as some of the row headings. when
I
join the two tables, even if I put a null value as a row header in the
second
table, it drops these values out - any ideas how I can get Access to
include
these?

thanks!

mya

If you need your age groups to to row headings then create a table that
has
them and left join it to the rest of the crosstab query like this ---
TRANSFORM Nz(Sum([Trial].qty_req),0) AS SumOfqty_req
SELECT AgeGroup.Group
FROM AgeGroup LEFT JOIN [Trial] ON AgeGroup.Group = [Trial].Group
GROUP BY AgeGroup.Group
PIVOT [Trial].[Week Num];