Re: combining queries

From: Tom Ellison (tellison_at_jcdoyle.com)
Date: 09/27/04 

Date: Mon, 27 Sep 2004 12:24:38 -0500

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
Goodwrench@discussions.microsoft.com> wrote:

>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" wrote:
>
>> 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
>>
>>
>> On Mon, 27 Sep 2004 06:27:03 -0700, Doug_Goodwrench
>> <Doug_Goodwrench@discussions.microsoft.com> wrote:
>>
>> >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.
>>
>>

Relevant Pages

  • Re: combining queries
    ... "Tom Ellison" wrote: ... > show which classes are available in each time period. ... > are taken by each student in each time period. ... You could then query to arrive at the billable units, ...
    (microsoft.public.access.queries)
  • Re: How to Group and Count Unique Records
    ... First a query to calculate amount still to pay. ... Than a query grouping by student and adding OwnsMoney. ... This is a consolidated list from a total of 10 lists during a week of ...
    (microsoft.public.access.queries)
  • Combo Problem on Form
    ... How can I get my combo box (this lists employees)to work ... in conjuction with my query which which shows all 'Call ... Outs' over a certain time period. ...
    (microsoft.public.access.formscoding)
  • Re: Update Query using Table & Query
    ... || I have a Query that lists anyone enrolled that is a senior... ... If this query is not updateable, then the "update" query, based on it, ... || the students are Seniors. ... || Withdrawals TABLE matches the Student ID in the Seniors QUERY. ...
    (microsoft.public.access.queries)
  • Re: sql views for denomalizing
    ... >> the field were in the student relation. ... include such lists. ... yes, which also permits lists of foreign keys, so you are more likely ... in pick to think of the foreign key in the parent, often multivalued, ...
    (comp.databases.theory)
Loading