Best Way To Query The Following Information

[Chris]

I have a form and a database in MS Access 2003 that was developed by a
programmer over 4 years ago. The database was created through
unconventional methods so one table holds a majority of the fields.
The form is used to input help desk tickets now, but management wants
to start querying different types of information from it. I was
tasked to create a section on the form that indicates if a technician
group has either "passed", "failed" or is in a "failed but
resubmitted" for a certain ticket. I have a checkbox to indicate each
state. There are a series of for stages on this form that have the
three checkboxes; so to sum it all up, each stage has 3 checkboxes so
with 4 stages, there are a total of 12 checkboxes. Each checkbox
corresponds with a yes/no attribute in the database (so there is 12
yes/no) boxes in the db

The complication comes from the query. How in the world can i make a
query for example of all of the tickets with stage 2 fails, without
pulling a ticket that has a stage 2 fails, but other stage fails too.
I know this sounds like a mess (it is). But this is as best as I can
explain it.

The end result I would like is to be able to make a query that gives
me all tickets that are in failure status at the present time..(not
the ones that have failed in the past and been resubmitted)

I can try to give more details if needed.

Thanks

 

[Jeff Boyce]

Chris

I may not be following...

Are you saying you have added 12 Yes/No fields to handle three outcomes
times 4 stages? If so, what will you do when a fifth stage is added? Or a
fourth outcome? If that is your design, you will have a maintenance
headache, modifying your table, your queries, forms, reports, macros, code,
....

Instead, try a table structure that includes (add these) one field for
outcome and one field for stage. Now create two new tables, a lookup table
for outcome and a lookup table for stage. If anything changes, you only
need to make a single change, in the lookup table!

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Dale Fye]

Chris,

As you have indicated, this would be simpler were your database normalized
properly.

Reading your post, what I cannot figure out is how to determine what "the
present time" is?

Since Yes/No fields can only have two values (True/False, -1/0), what does
the data look like that defines a record this "in failure status at the
present time.. (not the ones that have failed in the past and been
resubmitted)"? Do you end up with multiple records, or when they are
resubmitted, do you start checking checkboxes in the next "phase"?

It would help if you gave us some data that depicts each of the instances
you mention above!

Dale

 

[Chris]

Hi Jeff,

Thank you for the prompt response.

You've got it exactly right! and believe me, I know of the
maintenance nightmare...the way this DB was constructed is utterly
ridiculous. I've been tasked with coming up with a way to track
progress of help desk tickets that already exist and will be coming
through in this DB.

The original form was constructed with no thought of proper design,
just end result, so it leaves me in a bit of a bind to try to work
around what is already there.

What the management wants is a way to indicate the status of a ticket
in a particular stage (i.e. checkboxes) and to be able to query at any
time the number of tickets that are in a particular status (pass/fail/
retried) at any given time. Sort of a report card if you will of how
technician teams are doing...

I totally understand where you are going with your suggestion; and
with any other DB, that's the first thing I would have done....but I
think I am constrained to just checkboxes for now unless mgmt. comes
back and decides otherwise...

I know it is complicated; and if you have any way to point me in the
right direction of how to logically approach and attempt a query in
this fashion, it will be very much appreciated.

Thank you in advance

Chris

 

[Chris]

Hi Dale,

Thanks for your response.

Basically, the data in this table consists of about 70 fields (waaaay
too many for my taste, but I didn't design it)

The form feeds off the table and data is entered in for each ticket
for fields such as (tech name, manager name, priority, issue type,
type of work, completion data, hrs to complete, date opened, date
started, date closed, % completed, ticket description....then in a
different tab, still feeding off of the fields in the table, i've
labeled the 4 stages, then added 3 checkboxes to each stage (pass/fail/
retry) and each of those checkboxes have a corresponding yes/no field
in the table (12 yes/no fields in all) Each record will have all of
the fields I stated above, and the checkboxes I just mentioned; There
won't be duplicate records.

The query or type of query that mgmt is looking for would be:

Let me have a count of all of the tickets that are currently in failed
status. That would be some what easy to do, except the fact that the
query could pull tickets that might have failed stage 1, but has
retried and made it to stage 3 and failed. So basically, the query
will pick up the stage 1 and stage 3 failure; thus giving an
inaccurate count.

Hope this sets the stage a little better..

Thanks again for your response

 

[Jeff Boyce]

Chris

I'm not sure I understand the constraint that's keeping you from leaving the
old db as is and creating new tables to hold what you know you need to use
to get better functionality from Access' relationally-oriented features.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hi Jeff,

Thank you for the prompt response.

You've got it exactly right! and believe me, I know of the
maintenance nightmare...the way this DB was constructed is utterly
ridiculous. I've been tasked with coming up with a way to track
progress of help desk tickets that already exist and will be coming
through in this DB.

The original form was constructed with no thought of proper design,
just end result, so it leaves me in a bit of a bind to try to work
around what is already there.

What the management wants is a way to indicate the status of a ticket
in a particular stage (i.e. checkboxes) and to be able to query at any
time the number of tickets that are in a particular status (pass/fail/
retried) at any given time. Sort of a report card if you will of how
technician teams are doing...

I totally understand where you are going with your suggestion; and
with any other DB, that's the first thing I would have done....but I
think I am constrained to just checkboxes for now unless mgmt. comes
back and decides otherwise...

I know it is complicated; and if you have any way to point me in the
right direction of how to logically approach and attempt a query in
this fashion, it will be very much appreciated.

Thank you in advance

Chris

 

[Chris]

I guess I am at a loss as to what needs to be done on how to implement
this so that the user interface (the form)...has the checkboxes that
mgmt. wants to see, while keeping the back end less complex, and still
providing them with the query (the previous mentioned query) that they
would need.

I see where you were going with your earlier suggestion; I'm just
unsure on how to give them what they want while keeping the
development simple.

 

[Dale Fye]

So, if retry is checked, does that mean it originally failed at that stage
and then passed?

So for Phase 1, your data could look like:

Example # P1_Pass P1_Fail P1_Retry
1 X
2 X
3 X X

Where in Example 1, it passed Phase 1 on the initial attempt. In this case,
I assume that I would then have to check Phases 2, 3, and 4.

In example #2, it failed Phase1 and has not been retried. If this were the
case, woud there ever be any entries in Phase 2,3, or 4.

In exampe #3, it initially failed Phase 1, but was then retried, and passed.
In which case I would then have to check phases 2, 3, and 4.

Is this accurate?

Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[signfrom]

If these stages follow each other then
you need just one single combo box for each ticket showing the present
status.

Just fill your combo as ( stage,status )
1 pass
1 fail
1 retry
2 pass
2 fail
2 retry and so on

Then you can base your query on this combo box easily.
For finished works for instance your criteria would be "4 pass".
For stage 2 records "like 2*"

rgds