Queries

  • Thread starter Thread starter myheadache
  • Start date Start date
M

myheadache

In my table I have a field named Cause 1 and a field named
Cause 1 Hrs. I want the query to look at Cause 1 (which
is a Yes/No field) and if the answer is Yes insert the Hrs
from Cause 1 Hrs. So I can get a total for all the Hrs
that are associated with Yes. I know this can be done by
using the criteria field in the query except that I have
12 causes and I don't think there are enough lines
associated with criteria to do it this way.
 
Dear head:

I think you are saying you have columns in the table for Cause1,
Cause1Hrs, Cause2, Cause2Hrs, and so forth.

This is a design that doesn't work well with databases, and that's the
biggest reason you're having trouble creating the query.

If you take the primary key column(s) of your existing table and
create another table with these same column(s), you could then add
columns to it, such as:

CauseNumber (a number 1-12 or more)
CauseYN
CauseHrs

In this separate table, store all the causes for each "event." Not
only will you no longer limit yourself to 12 causes (which may not be
an advantage) but you will be able to query across all the causes
(hundreds if ever needed) with a minimum of query work.

Designing tables in this way is called "normalization" and there is a
small set of rules for doing this, which you can find in many books
and by searching the internet.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks for the help. I think I may have too much
information in the table and no way of sorting it out.
I'm tracking trouble/down time so I have a code related to
the downtime plus whether or not it is controllable plus
the hours plus the party responsible for the downtime.
Like I said for some of the records I can have up to 12
different occasions(codes) of downtime. And then for some
I may only have a couple of instances(codes).
 
Dear ache:

Starting from a design that is not optimal creates a lot of extra work
eventually. I recommend back-tracking and getting it right, after
you've made study and applied it to your application.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top