Crosstab showing all dates in a range

[C4rtm4N]

Hi,

I'm currently writing a report that looks at staff sales but have a problem where all sales that are finalised on days that they're not due to work are getting omitted. Due to the way that I have to do other things, I need to group working time records by day & then add sales on in a later query. Clearly if there was a record for each staff member for every day whetehr they work or not, all sales would be correctly attributed but I'd like to avoid having to create a table full of zero values for 3000 staff if I can. I have created a 'calendar' table with all dates in but that still doesn't seem to work. The SQL I'm using is as follows -

PARAMETERS [Forms]![Productivity_Dates]![DateFrom] DateTime, [Forms]![Productivity_Dates]![DateTo] DateTime;
TRANSFORM IIf(Sum([Tbl_TCS (Total)]![Minutes]) Is Null,0,Sum([Tbl_TCS (Total)]![Minutes])) AS Minutes
SELECT [Tbl_TCS (Total)].[Staff ID], [Tbl_TCS (Total)].[Data Date]
FROM Tbl_Calendar LEFT JOIN [Tbl_TCS (Total)] ON Tbl_Calendar.Date = [Tbl_TCS (Total)].[Data Date]
WHERE ((([Tbl_TCS (Total)].[Data Date]) Between [Forms]![Productivity_Dates]![DateFrom] And [Forms]![Productivity_Dates]![DateTo]))
GROUP BY [Tbl_TCS (Total)].[Staff ID], [Tbl_TCS (Total)].[Data Date]
PIVOT [Tbl_TCS (Total)].[Work Code];


Anyone give me a pointer (as you'll have guessed I'm a bit of a newbie at this) as to how I can get my crosstab to enter a date then zero values for each day in the range for each member of staff ?

[Tbl_TCS (Total)] is where all of the working time data is held with [Staff ID] being the person [Work Code] being what they're actually doing (several different types of work) & [Minutes] for how long on each day ([Data Date])

Thanks

Steve

 

[SkipVought]

Hi,

Create one or more DUMMY row(s) containing the dates that are missing.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[C4rtm4N]

Thanks Skip

How would I go about doing that in the query? I don't want to manually have to add rows to the main table

Steve

 

[SkipVought]

Just create another table with the dates and join them on date

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[GingerR]

You have to have a table of all possible STAFF+DAYS. I'm not great at disecting someone's sql statement unless I have the db in front of me, so I'll just explain what to do and you can see if you can do it.

I'm sure you already have a table of Staff (unique list), right? And you said you have one of calendar days.

Make a query with those two tables, and no join between them. Put Staff name and Calendar Date in the query grid, run the query. That's every combination of Staff and Calendar Date. Call it something like StaffDays.

Then whenever you make a query where you want to see all the days for all staff, put this query in there. Put STAFF NAME and Calendar Date into the query grid from this new query. Put in i.e. your sales table/query AND your Working table/query. Make joins from Staff and CalendarDate point toward each of the other two tables. Bring down fields from each of those tables as you wish. If you want to filter a certain date range or staff name, do that criteria on the Staff Name and CalendarDate fields in the new query, not on the other tables/queries.

Sorry I don't have a final sql statement for you, but hopefully you can figure out what I'm talking about.

g

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[C4rtm4N]

Thanks GingerR I worked out that that was the easiest way to do it yesterday but didn't get a chance to post

Steve