Need Help With Make Table Query

[EricG]

To start, I'm terrible at Access, but good at all forms of VBA. I have been
trying to create a query for the last few days without success, and would
appreciate some advice.

I have two tables. The first, 'tbl_DART' is a list of all users who are
authorized to add records to the database. Each user has only one (unique)
record in the database. This table is keyed on "EmpID". For simplification,
the table has "EmpID", "LastName" and "FirstName" as fields.

The second table, 'tbl_Status' contains one record per employee which tracks
that employee's progress towards an objective. If an employee has not yet
entered a record, it does not exist in 'tbl_Status' (i.e. there is not a
default, blank record for each employee). Again for simplication, this table
has "ID", "Step1Complete", "Step1Date", "Step2Complete", "Step2Date",
"Step3Complete", "Step3Date" as fields and is linked one-to-one with
'tbl_DART' via "ID" = "EmpID". "Step1Complete", etc are booleans (check
boxes on a form). "Step1Date", etc are the dates that the employee marked
that step complete.

I am trying to create a new table to help track overall progress for the
entire list of users. I want to be able to extract a timeline of progress
for:
1. % of total authorized users who have created their record in 'tbl_Status'
2. % of records in 'tbl_Status' for which Step 1 is complete
3. % of records in 'tbl_Status' for which Step 2 is complete
etc.

The new table (call it 'tbl_Metrics') will look something like this:

Date %Records Created %Step 1 Complete %Step 2 Complete
7/1/2009 2.0% 0.0%
0.0%
7/3/2009 15.3% 8.1%
5.2%
7/5/2009 27.3% 18.9%
11.1%

I'm trying to gather this data for every unique date in 'tbl_Status'. The
percentages need to be cumulative as time goes by. Some dates will have only
one entry, others could have many.

I'm baffled and could use some suggestions. I'm perfectly happy to
implement as a query in Access, or as some VBA, whichever is easier.

TIA,

Eric

 

[Jerry Whittle]

Your data isn't normalized properly as evidenced by the Step1, Step2, Step3,
etc. fields. What happens to all your queries, forms, and reports when
someone adds a new step? Back to the drawling board, that's what!

Your tbl_Status should have many records per employee and look something
like below:

"ID", "Step", "StepCompleteDate"
123 1 1/1/2009
123 2 6/4/2009
456 1 2/2/2009

Notice that there isn't any need for the check box. If there's a completed
date, it's complete.

You could also have a table of Steps which could better explain what the
various steps are.

Then you don't even need a 'tbl_Metrics' ! What you need is a crosstab
query to display the data in your other tables as you want to see it.

 

[vanderghast]

Would be much preferable to have one record per step, per user:


UserID StepNumber DateCompleted ' fields name
1010 1 1.1.2001
1010 2 5.1.2001
1011 1 3.1.2001


rather than


UserID StepIDate Step2Date Step3Date .... ' fields
name
1010 1.1.2001 5.1.2001 null ...
1011 3.1.2001 null null ...



The query could simply be:

TRANSFORM COUNT(*)/(SELECT COUNT(*) FROM tbl_DART)
SELECT DateCompleted AS AsOfDateOf
FROM newTable
GROUP BY DateCompleted
PIVOT "StepNumber_" & StepNumber



To be sure a given employee don't have TWO records for a given step, build
an index, not allowing duplicated, over the pair of fields (UserID,
StepNumber).




Vanderghast, Access MVP