Suggestion's needed...

[Heather]

Any guidance would be really appreciated.

First, I have had some great suggestion from the query group but I am still
having such a hard time with this I just can't get it to work right.

I am trying to get results for how many tickets that were opened, closed,
work in Progress, Pending Implementation for the month. My problem is that
sometimes a ticket say’s it is closed but it has since been re-opened and the
only way to know that is look at the re-opened field.
The table has a page field so I decided to use that by selecting the Min and
Max to get the first record (Open) and the latest which could be (Closed or
Work in Progress extra).

((But I need to get the days a ticket has stayed in a State based on the
update time field))

Trying to do..
For each ticket with STATUS = Open
Number of days that the ticket contained Ticket State = Work in Progress
Number of days that the ticket contained Ticket State = Pending Implementation
The final Number of days when the Ticket was = Closed
((But then the ticket is re-open))



PNUMBER Page Assignment Status OpenTime UpdateTime Close
reopen
IM050 1 ibg OPEN 12/01/05 12/01/05
IM050 2 ibg WorkinProg 12/01/05 12/01/05
IM050 3 ibg WorkinProg 12/01/05 12/06/05
IM050 4 ibg Closed 12/01/05 12/07/05
12/07/05
IM050 5 fbss WorkinProg 12/01/05 12/08/05
12/08/05
IM050 6 fbss WorkinProg 12/01/05 12/09/05
12/08/05
IM050 7 fbss WorkinProg 12/01/05 12/14/05
12/08/08

Thank for any Suggestion's...

 

[mnature]

Rule of Thumb with any database:

Any time you are repeating information, that information should probably be
pulled into a separate table, and then referenced from the main table. You
have repeating information in Assignment and Status. Those should be made
into separate tables, that are then linked back to your ticket. You have
several Time fields. Instead, you should have a separate table that is for
times, with a table to select which kind of time you are using for any
particular ticket.

SO:

TblAssignment
AssignmentID
AssignmentText

TblStatus
StatusID
StatusText

TblTicket
TicketID
TicketText

TblDate
DateID
DateText

TblInformation
InformationID
TicketID
StatusID
AssignmentID
DateID
ActualDate

You can now search for particular Tickets, and have a complete listing of
what has occurred on that ticket. If you sort by date, you will have a
chronological list.

It might look like this:

Ticket * Assignment * Status * DateType * Date

IM050 * ibg * Open * Open * 12/01/05
IM050 * ibg * Work in Progress * Update * 12/01/05
IM050 * ibg * Work In Progress * Update * 12/06/05
IM050 * ibg * Closed * Closed * 12/07/05
IM050 * fbss * Open * Re-Open * 12/08/05
IM050 * fbss * Work In Progress * Update * 12/08/05
IM050 * fbss * Work In Progress * Update * 12/09/05
IM050 * fbss * Work In Progress * Update * 12/14/05
IM050 * fbss * Closed * Closed * 12/14/05

Personally, I think a new ticket should be started, rather than re-opening
an old ticket, but you may have other stuff happening that I don't know
about.

 

[Heather]

I should of said that I have no control over the table I am pulling it in
from an oracle db. This is the way the data gets generated into the table

 

[Jeff Boyce]

Heather

The "raw" data you import into Access (or link to) is NOT what you are stuck
with. To get the best use of Access' features and functions, consider
spending time normalizing the data structure of your Access tables (per your
earlier responder's suggestion).

You can use queries to collect/distribute data to/from a set of
well-normalized tables in Access.

Regards

Jeff Boyce
<Office/Access MVP>