combining queries

[Guest]

Access 2000
I have 2 queries to look up dates used for billing for First Aid and one for
CPR.
Problem is if client had 2 entries FA/CPR on that date he/she gets billed
twice when it is the same price to take one class or both of them.
The current queries use the field names 'CPR' and 'First Aid' is sepreate
queries.
I would like to join them into one query and omit the duplicate results (if
there are any). You know, since we are looking at two fields and the client
took two classes, there will be the same date in both fields.
Oh yea, we keep track of which clients received training on what date for
re-certification purposes.

 

[Tom Ellison]

Dear Doug:

I sounds like your difficulty is based on the table design. A good
design would have just one column for the type of class, not separate
columns for each possible type. Each class would be recorded in its
own row.

Another table should then create combinations of classes so you can
charge for each distinct combination of classes taken. For the
situation at hand, it would look like:

Class Combination
FA FA/CPR
CPR FA/CPR

Joining to this and then selecting only distinct rows, the query can
produce just one row for any combined set of classes, whether there be
1, 2, or 100 classes taken together as a combination.

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

 

[Guest]

Here is my dilemma,
I don't understand.
I need to queary two fields that = my criteria (one OR both fields) without
displaying duplicate clients.
An example would be:
2 people for CPR only
3 People for First Aid only
10 People for CPR AND First Aid
for a total of 15 people.
My current queries return results for CPR =12
or First Aid =13
I can make a query that looks at both fields but it will only return the
results if BOTH fields match the criteria.=10
I need to find a way to return the result of 15.
The clients who took only one class/course MAY have already had the other
class on a prior date.
This is confusing, it's hard to explain, and I thank you for your time.
I am desinging a database from scratch with very little experience but have
come a long way.

 

[Tom Ellison]

Dear Doug:

I am suggesting you have a table listing all the classes. Another
table lists all the students without any reference to which class they
take. Perhaps a third table lists all the "semesters" or other time
periods over which each class is taken. A fourth table would then
show which classes are available in each time period. For each
student, each time period, a fifth table would record which classes
are taken by each student in each time period. A sixth table would
record which classes are considered as "one class" for purposes of
billing, assuming they are taken by the same student in the same time
period. You could then query to arrive at the billable units, rather
than by the individual classes, resulting in just what you said.

If you try to do this with a "simpler" table structure you will not
unlikely find that you will spend more time trying to query it than
what it would have taken to build it properly to begin. This general
problem is perhaps at the top of the list of the errors made by
designers early in their career.

Modelled correctly, the necessary flexibility will be provided for
users to do what is needed.

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


On Mon, 27 Sep 2004 10:09:07 -0700, Doug Goodwrench <Doug

 

[Guest]

Thank you. I agree with the structure of the table.
Everywhere I looked for information made me think that I should have split
up the information in different tables originally.