Show non-existent dates... (1 Viewer)

[dazza61]

Hi there,

I've attached a screen shot of a form which runs amongst other things a cross tab query at the bottom of the form...(work agency scenario)...

The records in the cross tab are basically days that a given candidate has worked....(dates being >= today) - the records above show every day a candidate has worked (or has been booked for the future)...

What I would like to achieve in the cross tab (and even in the subform above) is to be able to show Mon 12/10 Tues 13/10 Wed 14/10, etc - i.e. the days that the candidate HASN'T worked. By showing these dates, staff would be able to see far more easily as to when a candidate is "free for work"...

If I could get a Mon - Fri scenario with free dates showing as "blanks" I would be in heaven. That way, if you are looking for candidates who are free say, on a Tuesday, you can focus on the same column....

Currently, the columns dates are always changing, because the crosstab query is only pulling records for where candidates have actually worked i.e. a record actually exists...

I know this topic as been covered of sorts in other posts, but I've been searching for hours now and can't seem to find anything that solves the exact problem....

Any pointers in the right direction would be very much appreciated.


Darren

 

[speakers_86]

Go to the query that controls the form, and that is where you need to make some adjustments. You will will probably want to get the query to return results from today, to some set date in the future (one week, two weeks?). The trick will be getting the query to return all dates, not just dates with records. This may take 2 queries to get to work. If you can post your db, I may be able to help.

 

[DCrake]

In the sample database section you will find a topic on inteligent dates. In that there is a pre-populated table of dates, and all the attributes of those dates.

If you copy this into your back end and link it to your front end. Then when you create your crosstab query include the TblDates table and create ajoin between the activity dates in your origninal table and the appropriate date in the new table. Then change the join so it shows all records in the dates table and use this as your column headings. Don't forget to apply a date filter on this field as well. The field that is counting the activity will need a Nz() wrapped around it to show null values as zeros (for dates with no activity).

The good thing about using this table you can isolate working days and weekend days also.

David

 

[dazza61]

Thanks for your replies guys - it's really appreciated...

David, I found your Master Dates database in the examples section here and had been working with this for a couple of days before posting my question...

I need to correct something;the grid at the bottom of the original image is based on a pivot table NOT a cross tab. I've done cross tabs, etc that show blanks where a candidate doesn't work on a particular day. Problem is, because a cross tab can only have ONE intersection - the value you can show would be the start or end of a shift but NOT both. Also a cross tab may struggle with multiple shifts in one day....

I've included another image of a pivot table to give a better idea of what I want to achieve. In this image you can see candidate 681 works two shifts today and tomorrow (yes they are actually working as I type this ....) - they aren't working Saturday (but you can't see a blank - sadly)...

I really can't see a way to show a column for Saturday - I've done the outer join thing to show all dates from the master table, etc - using nulls, etc. If you show ALL candidates in the pivot table you DO get blanks, simply because Access is comparing shifts against other candidates where records exist, but soon as you only show ONE candidate in the table - you lose the blanks...if that makes sense...

Perhaps speakers 86 has a valid point that to achieve the end result you may need more than one query...

**pulls hair out** - (well I would do if I had any LOL)

Darren