If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Suggestion's needed...
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... |
#2
|
|||
|
|||
Suggestion's needed...
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" wrote: 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... |
#3
|
|||
|
|||
Suggestion's needed...
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 "mnature" wrote: 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" wrote: 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... |
#4
|
|||
|
|||
Suggestion's needed...
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 "Heather" wrote in message ... 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 "mnature" wrote: 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" wrote: 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... |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Office activation fails can't find needed file, any suggestions? | bdt | Setup, Installing & Configuration | 2 | October 10th, 2005 12:14 AM |
Stop asking if i should make suggestions | Warrick | General Discussions | 2 | October 3rd, 2005 03:32 AM |
Check Word suggestions on 'Advise' and 'advice' in sentences | Syam | General Discussion | 6 | September 8th, 2005 01:09 PM |
Access:Mac is a relational database product that is sorely needed | wekj | General Discussion | 2 | September 1st, 2005 06:12 AM |
Suggestions for Teaching PowerPoint Class | ppt diva | Powerpoint | 3 | August 23rd, 2005 11:17 PM |