Resource Utilization Report

[two_smooth]

I have created a MS Access database that have many records. Using the following data fields how do I create a Report showing resource utilization by week?

• Last Name
• First Name
• Week 1
• Week2
• Week3
• Week4
• Week5

I need to know who's (by Week Number and Last Name, First Name) is assigned to work, for example:

Week1
Tom Jones
John Smith
Jerry Rice

Week2
Mike Higgins
Yolanda Mendez
Terrance Perkins

Week3, Week4 & Week5 etc.

[June7]

If you have a field for each week, this is not a normalized data structure. What data type are the week fields - Yes/No? How many week fields are there - 52?

Data structure should be:

tblEmployees
EmpID (primary key)
LastName
FirstName

tblData
EmpID (foreign key)
WeekStartDate

[two_smooth]

June7, for each record the record id data field is the primary key. The wekk1, week2, week3, week4 and week5 contain alphanumeric data (floor or warehouse are the values). So, the report should reflect the following (please note there is one record for each resource, it may have multiple weeks assigned):

Report Heading - Resource Utilization by Week

Sub-Heading - Week1
Tom Jones Floor
John Smith Floor
Jerry Rice Warehouse

Sub Heading - Week2
Mike Higgins Warehouse
Yolanda Mendez Warehouse
Terrance Perkins Floor

Sub Heading Week3
XXXXXXXXX Floor
XXXXXXXXX Warehouse

Sub Heading - Week4
XXXXXXXXX Floor
XXXXXXXXX Floor
XXXXXXXXX Floor

Sub Heading - Week5
XXXXXXXXX Warehouse
XXXXXXXXX Floor
XXXXXXXXX Warehouse

[June7]

The data structure is not compatible with that output. What you currently have is essentially what would be the result of a CROSSTAB. To reconstruct will require a UNION query, which essentially results in a normalized structure.

SELECT LastName, FirstName, "Week1" AS Period, Week1 AS Location FROM tablename
UNION SELECT LastName, FirstName, "Week2", Week2 FROM tablename
repeat for each subsequent week field ...;

Use that query as the source for report and apply Grouping & Sorting as desired.