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

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
http://mvp.support.microsoft.com/

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

"Chris" <chris.hogan@gmail.com> wrote in message
news:16754e68-3a0e-42dc-a6cc-77a2cb90e274@s12g2000prg.googlegroups.com...
> 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

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
-- 
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"Chris" wrote:

> 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
> 

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

On Dec 27, 8:52=A0am, "Jeff Boyce" <JeffBoyce...@msn.com-
DISCARD_HYPHEN_TO_END> wrote:
> Chris
>
> I may not be following...
>
> Are you saying you have added 12 Yes/No fields to handle three outcomes
> times 4 stages? =A0If so, what will you do when a fifth stage is added? =
=A0Or a
> fourth outcome? =A0If 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. =A0Now create two new tables, a lookup ta=
ble
> for outcome and a lookup table for stage. =A0If anything changes, you only=

> need to make a single change, in the lookup table!
>
> Good luck!
>
> --
> Regards
>
> Jeff Boycewww.InformationFutures.net
>
> Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/
>
> Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/
>

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

On Dec 27, 9:06=A0am, Dale Fye <dale....@nospam.com> wrote:
> 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? =A0
>
> 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)"? =A0Do 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
> --
> Don''t forget to rate the post if it was helpful!
>
> email address is invalid
> Please reply to newsgroup only.
>
>
>

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


"Chris" <chris.hogan@gmail.com> wrote in message 
news:0116f731-b754-45d5-9e91-232b46676fed@l1g2000hsa.googlegroups.com...
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

On Dec 27, 8:52 am, "Jeff Boyce" <JeffBoyce...@msn.com-
DISCARD_HYPHEN_TO_END> wrote:
> 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 Boycewww.InformationFutures.net
>
> Microsoft Office/Access MVPhttp://mvp.support.microsoft.com/
>
> Microsoft IT Academy Program Mentorhttp://microsoftitacademy.com/
> 

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.

On Dec 27, 11:10=A0am, "Jeff Boyce" <nonse...@nonsense.com> wrote:
> Chris
>
> I'm not sure I understand the constraint that's keeping you from leaving t=
he
> 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

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.



"Chris" wrote:

> 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
> 
> On Dec 27, 9:06 am, Dale Fye <dale....@nospam.com> wrote:
> > 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
> > --
> > Don''t forget to rate the post if it was helpful!
> >
> > email address is invalid
> > Please reply to newsgroup only.
> >
> >
> >
> 

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