Records between Tables (1 Viewer)

[ethan.geerdes]

So I am tracking the amount of trouble tickets that come in. I'm tracking what is assigned and what is completed. I am trying to figure out how to make the value that's in the assigned table to be a null value (not included in a count) if it is in the completed table.

Basically I have a form that is keeping track of the total number of tickets, what's currently assigned and what's been completed so far and the assigned and the completed is what i'm having an issue with. Any help would be greatly appreciated.

 

[Galaxiom]

Assigned and Completed should not be stored by moving records between tables.

Put all the data in one table and add a TicketStatus field.

 

[ethan.geerdes]

I am not trying to move it. I want it to not show up in my count for percentage complete

 

[Galaxiom]

Either way, the data should all be in one table. There should only be one record for a ticket.

 

[jdraw]

Following Galaxiom's approach with a TicketStatus field, it seems your percentage complete would be

%Complete = (Count(Tickets status "Completed")/Count(Total Tickets)) * 100

 

[ethan.geerdes]

Ok. So if I added a column for complete or incomplete, Then I would need to adjust one of my other forms. is there a way that I could have a button input the value as complete? I'm trying to minimize as much user interaction as possible. I think I may need to redesign my database a little bit too. Why do you recommend this instead of moving them?

 

[ethan.geerdes]

Also, I am not sure if this matters but it's not entirely for trouble tickets. It's for security patches applied to computer systems. The person I'm doing this for has multiple computer systems that are all different flavors. So it is to assign personnel to do the security patch and track what's complete for each system. That was why I used the term ticket.

I'm guessing the concept is the same though. I mean either the update is complete or incomplete.

 

[jdraw]

Getting your tables designed to meet your business rules is critical to successful database.

All we have heard is that you record info about TroubleTickets.
A TroubleTicket is assigned to ?? and starts with blank status or Incomplete

When the "trouble" has been resolved, the TroubleTicket is updated to Status "Completed"

What are the other form(s)? Their purpose?

At any time you have (I'm sure there are other fields...)

TroubleTicket ...... Status
1                          Incomplete
2                          Completed
3                          Completed
....
98                        Incomplete

Just got you latest post while i was typing.

Yes these could all be called Work Tickets/ JobTickets/TroubleTickets...

The Ticket would identify somethiing along this approach
TicketNumber
WorkDescription
AssignedTo
AssignedDate
Status

If you leave the work description fairly loose, you can include just about anything and always have some ability to control and monitor various Tasks/Jobs/Fixes...

Good luck.

 

[The_Doc_Man]

The reason you want a status flag vs. two tables is the cost of moving data vs. just flipping the state of a separate flag that says "busy/done" or pick your favorite other nomenclature. You can then do counts (and, for that matter, if you use report wizards, they have the option to include percentages in the group footers.)

However, a DCount("*", "mytable", "[Closed]=TRUE") and a similar DCount for status FALSE cases will give you all you need to know about percentages, and all from the same table.