Show ALL dates instead of only dates with data entries

E

excel/access-chump

Here's the scoop:
I have a form that shows an employee schedule.
---------
Date Day Address Key Charts
1/22/08 Tuesday 12345 (23) 23
1/25/08 Friday 34567 (40) 40
---------
What I would like to do is set up the form to show days that nothing is
scheduled (7 day week) to express holes in their schedules.
Currently I have 2 text boxes: Chart Min and Chart Max that allow me to
express the individual employee's limits of chart recovery for maximum
utilization. I set this to conditionally format the records if they are over
or under scheduled.
I understand that idealy I would have a table with all the dates. However,
I don't even know how to begin to set this up.
I am running Access 2003 and I don't have permissions to change the ODBC
server database I'm pulling the schedule from.
 
J

John W. Vinson

I understand that idealy I would have a table with all the dates. However,
I don't even know how to begin to set this up.
I am running Access 2003 and I don't have permissions to change the ODBC
server database I'm pulling the schedule from.

You could create a local (in your own frontend database, not the ODBC SQL
backend) table named AllDates with one date field. You can fill it easily by
opening Excel; put the first date (January 1 2008 maybe?) of interest in cell
A1, select cells A1 through A3654 (for ten years), and use Data... Series to
fill with sequential dates. Copy and paste the data into your AllDates table.

You'll need a "left join" of this table to your (linked) data table to see all
the dates.

John W. Vinson [MVP]
 
E

excel/access-chump

John,
I am running the "left join" as you suggested and it gives me all the dates.
However, it is listing the dates like this:

ReviewerId | InvenTracker.AppStart | AllDates.AppStart
abc | 1/25/2008 | 1/25/2008
| | 1/26/2008
abc | 1/27/2008 | 1/27/2008

How would I get them to list like this:

ReviewerId | AppStart
abc | 1/25/2008
| 1/26/2008
abc | 1/27/2008

I need it like this so that it is easier to see the holes in their schedule
(unscheduled days).

Also, on occassion there are days with multiple stops on the schedule.
These are listed as seperate records. I need it to show those stops all on
the same day. i.e.:
Have:
SiteId | AppStart
1223 | 1/25/2008
3456 | 1/25/2008

Need:
SiteId | AppStart
1223, 3456 | 1/25/2008

Last of all (I promise! :) :
I need to run all of this inline VB. The table 'InvenTracker' is updated to
only contain one ReviewerId's schedule at a time.

I know that I am asking questions from all over the place but I've read
everything I could find on the Access forum (I'm not allowed onto anyother
web help here at the office) and either couldn't find the info I need or I
don't know enough to recogize that I found it. <grin> Thanks again!
--
Thank you for your help!

-The Chump
 
J

John W. Vinson

John,
I am running the "left join" as you suggested and it gives me all the dates.
However, it is listing the dates like this:

ReviewerId | InvenTracker.AppStart | AllDates.AppStart
abc | 1/25/2008 | 1/25/2008
| | 1/26/2008
abc | 1/27/2008 | 1/27/2008

How would I get them to list like this:

ReviewerId | AppStart
abc | 1/25/2008
| 1/26/2008
abc | 1/27/2008

Try using

NZ([InvenTracker].[AppStart], [AllDates].[AppStart])

in your query. This will show the inven date if it exists, and the "hole" date
if it doesn't.
I need it like this so that it is easier to see the holes in their schedule
(unscheduled days).

Also, on occassion there are days with multiple stops on the schedule.
These are listed as seperate records. I need it to show those stops all on
the same day. i.e.:
Have:
SiteId | AppStart
1223 | 1/25/2008
3456 | 1/25/2008

Need:
SiteId | AppStart
1223, 3456 | 1/25/2008

You'll need some VBA to do this. See the sample code at
http://www.mvps.org/access/modules/mdl0004.htm
This will assemble the various site id's for a date into a comma separated
text string.
Last of all (I promise! :) :
I need to run all of this inline VB. The table 'InvenTracker' is updated to
only contain one ReviewerId's schedule at a time.

Well...? Create the query, store it, and execute it from your VBA code. It's
not quite clear what you're asking.

Constantly rewriting your InvenTracker table as the data changes is A Bad Way
To Do This and certainly not necessary, but I don't know how you're doing this
so I can't really suggest what might be done differently.
I know that I am asking questions from all over the place but I've read
everything I could find on the Access forum (I'm not allowed onto anyother
web help here at the office) and either couldn't find the info I need or I
don't know enough to recogize that I found it. <grin> Thanks again!

<wry grin>

John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top