Require crosstab query to display null values in row (1 Viewer)

[swell]

Hi, (moved this from where I incorrectly posted.)

I have what seems to be an eternal problem with crosstab queries and null values.
Here is my simple crosstab query. The data is one month of activity giving total hours and distance in any given month.

Here is my query.

Sum(ActivityRange.Hours) AS SumOfHours1
SELECT [Activity Type].[Activity ID], [Activity Type].ActivityDesc, Sum(ActivityRange.Hours) AS SumOfHours, Sum(ActivityRange.Distance) AS SumOfDistance
FROM ActivityRange INNER JOIN [Activity Type] ON ActivityRange.Activity = [Activity Type].[Activity ID]
GROUP BY [Activity Type].[Activity ID], [Activity Type].ActivityDesc
ORDER BY [Activity Type].[Activity ID], ActivityRange.[Date of Activity]
PIVOT ActivityRange.[Date of Activity];

I need a row for every instance of [Activity ID] from the [Activity Type] table, excuse the poor naming, I created these before I was aware of access naming conventions (would rename but frightened it might break something).

The [Activity Type] table has these instances of [Activity ID] 1 through to 13, 21 through to 29 and 41 through to 47. (numeric)
I have seen the statements of using column headings in the property sheet, but these are row headings .
This is output to auto populate a formatted excel spreadsheet (well that's the aim ).

 

[arnelgp]

do you need to "fill" the "Null" values with 0?

Transform Val(Sum(ActivityRange.Hours) & "") As SumOfHours1
...
...

 

[swell]

do you need to "fill" the "Null" values with 0?

Transform Val(Sum(ActivityRange.Hours) & "") As SumOfHours1
...
...

No I just need to be able to display all values of [Activity ID] in table [Activity Type] even though there are Null values in the table of [ActivityRange].
Would your suggestion achieve this?

 

[arnelgp]

You Reverse the order of your table, then use Left Join instead of Inner Join:

..
..
FROM [Activity Type] LEFT JOIN ActivityRange ON [Activity Type].[Activity ID] = ActivityRange.Activity
..
..

see this link on how to add Additional crosstab column (distance)
Microsoft Access tips: Crosstab query tips (allenbrowne.com)

 

[swell]

I a

do you need to "fill" the "Null" values with 0?

Transform Val(Sum(ActivityRange.Hours) & "") As SumOfHours1
...
...

I applied your change which produced this error.

 

[swell]

You Reverse the order of your table, then use Left Join instead of Inner Join:

..
..
FROM [Activity Type] LEFT JOIN ActivityRange ON [Activity Type].[Activity ID] = ActivityRange.Activity
..
..

see this link on how to add Additional crosstab column (distance)
Microsoft Access tips: Crosstab query tips (allenbrowne.com)

OK! thanks
Re allenbrowne.com
nice to see that it is back up.

 

[arnelgp]

view the sample Query in SQL View.

 

[swell]

Ok this is what my sql looks like. I removed {, Sum(ActivityRange.Distance) AS SumOfDistance} , I thought that could be added later.

TRANSFORM (val(Sum(ActivityRange.Hours) & "") AS SumOfHours1
SELECT [Activity Type].[Activity ID], [Activity Type].ActivityDesc, Sum(ActivityRange.Hours) AS SumOfHours
FROM [Activity Type] LEFT JOIN ActivityRange ON [Activity Type].[Activity ID] = ActivityRange.Activity

GROUP BY [Activity Type].[Activity ID], [Activity Type].ActivityDesc
ORDER BY [Activity Type].[Activity ID], ActivityRange.[Date of Activity]
PIVOT ActivityRange.[Date of Activity];

this gives the following error.

As you can see I have a lot of difficulty with SQL when its written I can figure most of what is happening. but the nuances like the error I have no idea where to adjust.

I am sure you don't require this but here is the relationship of the two tables.

 

[swell]

oops!

 

[arnelgp]

you have extra "(" to the left:

TRANSFORM (val(Sum(ActivityRange.Hours) & "") AS SumOfHours1

must be:


TRANSFORM Val(Sum(ActivityRange.Hours) & "") AS SumOfHours1

 

[swell]

view the sample Query in SQL View.

Ok! took a break, on return I see where I had made a typing error in the sql,
It now works with just the hours, now I'll put the distance and I am sure all will be good.
Thank you very much for your patience.

 

[swell]

do you notice how you and I keep typing responses at the same instant? I think this is 3 times in about a week

 

[arnelgp]

another demo.
because some activity id has no records, 2 additional columns (hour), (dist) was created
on the 3_finalCrosstab query.

you can use a Form/Report (do not include the extra columns).

 

[swell]

As I don't really need the date columns is there any way to produce the output without displaying the date columns?
<edit> To be clear I just need the totals of the hours and distance..
Also what is the column labeled <> ?

 

[arnelgp]

s I don't really need the date columns is there any way to produce the output without displaying the date columns?

what columns do you need?

Also what is the column labeled <> ?

that is the column when there is "no date" (no transaction).
as i've said, use Form to not include that column.

 

[swell]

another demo.
because some activity id has no records, 2 additional columns (hour), (dist) was created
on the 3_finalCrosstab query.

you can use a Form/Report (do not include the extra columns).

Ah! ok I think I need to go study how to export a report to a spreadsheet.
Thank you for your guidance.

 

[swell]

what columns do you need?

that is the column when there is "no date" (no transaction).
as i've said, use Form to not include that column.

I just need the Activity ID , Activity Type, total hours and total distance
I'll go do some research and see where I get

TY

 

[arnelgp]

see qryTotals, it is Total query, not Crosstab.

 

[swell]

what columns do you need?

that is the column when there is "no date" (no transaction).
as i've said, use Form to not include that column.

I won't be able to use a form or report as the workbook exists it has a formatted spreadsheet and I need to create another spreadsheet in the same workbook and populate the existing spreadsheet from that. (unless there is another way to achieve the result).
I need to take a closer look at your latest DB.

 

[swell]

see qryTotals, it is Total query, not Crosstab.

This is how I would like the data to appear in the spreadsheet.