Newbie

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

Hello everyone. I have a question where I need to create
a query from an existing query...but have no idea where
to start. I need to do the following:

I have a small DB which tracks work assignments like
below:

Number Opened Assigned Closed
A1-1 04-Jan-04 05-Jan-04 01-Mar-04
A1-2 05-Feb-04 08-Feb-04 10-Apr-04
etc

I need to have a query showing:

Month Opened Assigned Closed
Jan A1-1 A1-1
Feb A1-2 A1-2
Mar A1-1
Apr A1-2
etc

How in the world do I even begin to do this? I have only
been using Access for a few weeks and any help/websites
would be appreciated.
 
What would you expect to see if you had two or more Numbers opened,
assigned, or closed in a single month? For instance A1-3 is opened in Feb,
Assigned in Feb, and Closed in Mar...
 
I would first normalize the table using a UNION QUERY.
SELECT Number, "Opened" as Status, Opened as StatusDate
FROM tblSmallDB
UNION ALL
SELECT Number, "Assigned", Assigned
FROM tblSmallDB
UNION ALL
SELECT Number, "Closed", Closed
FROM tblSmallDB;

Then create a crosstab query based on the UNION QUERY that sets the
Format(StatusDate,"mmm") as Row Heading
Status as Column Heading
and Count(Number) as the value
 
Back
Top