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

swell

Member
Local time
Today, 17:50
Joined
Mar 10, 2020
Messages
77
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.

Code:
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 :rolleyes:).
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:50
Joined
May 7, 2009
Messages
19,169
do you need to "fill" the "Null" values with 0?

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

swell

Member
Local time
Today, 17:50
Joined
Mar 10, 2020
Messages
77
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?
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:50
Joined
May 7, 2009
Messages
19,169
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)
 
Last edited:

swell

Member
Local time
Today, 17:50
Joined
Mar 10, 2020
Messages
77
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.
1616743995452.png
 

swell

Member
Local time
Today, 17:50
Joined
Mar 10, 2020
Messages
77

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:50
Joined
May 7, 2009
Messages
19,169
view the sample Query in SQL View.
 

Attachments

  • sample.accdb
    440 KB · Views: 547

swell

Member
Local time
Today, 17:50
Joined
Mar 10, 2020
Messages
77
Ok this is what my sql looks like. I removed {, Sum(ActivityRange.Distance) AS SumOfDistance} , I thought that could be added later.

Code:
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.
1616745371426.png


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.
1616745994416.png
 

Attachments

  • 1616745179722.png
    1616745179722.png
    19.4 KB · Views: 527

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:50
Joined
May 7, 2009
Messages
19,169
you have extra "(" to the left:

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

must be:


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

swell

Member
Local time
Today, 17:50
Joined
Mar 10, 2020
Messages
77
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

Member
Local time
Today, 17:50
Joined
Mar 10, 2020
Messages
77
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:50
Joined
May 7, 2009
Messages
19,169
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).
 

Attachments

  • activity_db.accdb
    560 KB · Views: 527

swell

Member
Local time
Today, 17:50
Joined
Mar 10, 2020
Messages
77
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 <> ?
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:50
Joined
May 7, 2009
Messages
19,169
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

Member
Local time
Today, 17:50
Joined
Mar 10, 2020
Messages
77
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

Member
Local time
Today, 17:50
Joined
Mar 10, 2020
Messages
77
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:50
Joined
May 7, 2009
Messages
19,169
see qryTotals, it is Total query, not Crosstab.
 

Attachments

  • activity_db.accdb
    576 KB · Views: 324

swell

Member
Local time
Today, 17:50
Joined
Mar 10, 2020
Messages
77
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.
 

Users who are viewing this thread

Top Bottom