Fill in the date gaps

[Andrew]

I'm constructing a database to track staff workload over time. Each staff
member has their own table with one day for each row. There are queries set
up to add the actions over various time periods as required by the managers.
Here comes the problem, days aren't showing up in the queries unless every
staff member has entered values for those days.

So, the data for one staff member would look like this:
ID DATE Phone Email Other
1 01/01/2009 2 7 10
2 02/01/2009 8 10 5
3 04/01/2009 5 7 4
4 05/01/2009 4 1 11

At the moment, the 3rd of January doesn't show up on any of the queries even
if other staff members have entries for that day.
Does anyone know how to create a script to fill in the gaps between date
fields with "0" values?
eg: 03/01/2009 0 0 0

Sorry if this is a newbie question - I thought I was reasonably proficient
with access until I volunteered for this project
Thank you in advance.

Andy.

 

[John W. Vinson]

I'm constructing a database to track staff workload over time. Each staff
member has their own table with one day for each row.

Ow. That's NOT a good design! Storing data - staff member names? - in
tablenames is a *very* bad idea. When a new hire comes on or an employee
leaves, you'll need to add or delete a table and change all your reports.

You should have ONE table of hours.

There are queries set
up to add the actions over various time periods as required by the managers.
Here comes the problem, days aren't showing up in the queries unless every
staff member has entered values for those days.

So, the data for one staff member would look like this:
ID DATE Phone Email Other
1 01/01/2009 2 7 10
2 02/01/2009 8 10 5
3 04/01/2009 5 7 4
4 05/01/2009 4 1 11

At the moment, the 3rd of January doesn't show up on any of the queries even
if other staff members have entries for that day.

Of course not; there is no data in the table for that date. Where would the
date come from?

Does anyone know how to create a script to fill in the gaps between date
fields with "0" values?
eg: 03/01/2009 0 0 0

Sorry if this is a newbie question - I thought I was reasonably proficient
with access until I volunteered for this project
Thank you in advance.

I'd say you need at least three tables:

Employees
EmpID <primary Key>
LastName
FirstName
<other biographical data>

Tasks
Task <Text, Primary Key - e.g. Phone, Email, ...>

WorkHours
WorkHoursID <Autonumber Primary Key>
EmpID <link to Employees, who did the work>
WorkDate <Date/Time, when they did it>
Task <what they did that day>
Hours <Number, how many hours they took>

You may need yet another table, AllDates, filled in manually (you can do it
very quickly in Excel with Insert... Fill... Series; fill in dates from
1/1/2009 through 2029 or something like that, it'll still be a tiny table).
You can create a Query joining this table to your total by a Left Outer Join
(see the help) to show all days whether or not they have data.

 

[Duane Hookom]

One solution would be to create a table of all dates and then use it in the
query with a JOIN including all records from the table [tblAllDates].

I'm not sure I agree with a system that has a table for each member. When
you get a new member, do you have to create a new table? I would consider
using one table that has a StaffMember field. I would probably also normalize
the table removing the Phone, Email, and Other as fields and make them values
stored in a field named [Task]. This would probably triple the number of
records but would create a system where you could add members and task types
without changing table structures or forms or queries or reports.

 

[Andrew]

Heh. Perhaps I should have posted in Database Design. I'm clearly more inept
than I had previously thought.

Thanks for your reply, I'll try using your ideas.

Andy.

 

[Dale Fye]

Going back to the missing dates question. Once you have redesigned your
table structure, you can either do as John mentioned, create a table of
dates, or you can create query that returns a series of dates. I generally
do the latter, since I never know how long an application will be in use.

1. Create a table (tbl_Numbers) with a single field (lngNumber). Then add
10 records to it with the values of 0 - 9
2. Create a query (qry_Numbers), which looks something like the following.
This will generate a series of 1000 numbers (0-999), which should easily
meet your requirements. Depending on the range of dates you want to
evaluate, you might even want to consider modifying this so that it only
returns 100 numbers.

SELECT Hundreds.lngNumber * 100 + Tens.lngNumber * 10 + Ones.lngNumber as
lngNumber
FROM tbl_Numbers as Hundreds, tbl_Numbers as Tens, tbl_Numbers as Ones

Depending on the range of dates you want to evaluate, you might even want to
consider modifying this so that it only returns 100 numbers.

SELECT Tens.lngNumber * 10 + Ones.lngNumber as lngNumber
FROM tbl_Numbers as Tens, tbl_Numbers as Ones

3. Create a dates query (qry_Dates) that looks something like the
following. This will give you a list of dates in the current calendar year
(assuming you are using the first example above).

SELECT DateAdd("d", lngNumber, Date()) as SomeDate
FROM qry_Numbers
WHERE DateAdd("d", lngNumber, Date()) BETWEEN Dateserial(year(date), 1, 1)
AND Dateserial(Year(date) + 1, 1, 0)

Once you have this date query, you can join it to the WorkHours table that
John mentioned in his comment using a left join. That way, all the values
from qry_Dates would show up, regardless of whether there was a matching
value in the WorkHours table.

HTH
Dale