RE: Display fields Limited on Values

I'm not sure how you would ever add to or change the requirements since this
would mean changing table structures.

However, you could normalize with a union query. It would be easier to
suggest the SQL if we knew your table structure. Your query would look
something like:
SELECT PersonID, Require1 as Met, "Require1" as Requirement
FROM tblRequirements
UNION ALL
SELECT PersonID, Require2, "Require2"
FROM tblRequirements
UNION ALL
SELECT PersonID, Require3, "Require3"
FROM tblRequirements
UNION ALL
..... etc ...
SELECT PersonID, RequireN, "RequireN"
FROM tblRequirements;

You can then quite easily query the union query where [Met] is true or false.

I generally find that trying to save time early in a project by not
normalizing creates a lot more work and maintenance later.

--
Duane Hookom
Microsoft Access MVP


"lamartpm" wrote:

The reason that I chose not to go beyond 2NF is that the number of people who
will be modifying this database is limited to myself and one other person,
everyone else will be read-only, and the combination of being busy and not
feeling like further complicating my queries has kept me from doing so.

Respectfully,
Paul

"Duane Hookom" wrote:

Your reply basically confirms my previous posting.

Is there any way that you would consider normalizing/correcting your table
structure?

--
Duane Hookom
Microsoft Access MVP


"lamartpm" wrote:

The reason that I went this route is that for them to be eligible to be sent
overseas they have tons of different requirements, so i chose to just use
booleans. There names are all just the names of classes that they must
attend. For classes there are 6 seperate classes that they must attend and
its pretty much a did they or did they not attend. And the other tables are
all pretty much the same way e.g. they have 15 different forms that we have
to have on record for them to be sent. All of the records in the different
tables are being joined on a automatically generated ID that is created in
the general info table.

"Duane Hookom" wrote:

If you are using multiple boolean fields then I think your table structure is
wrong. You should not be using "requirements" as field names. Each
person/requirement should create a single record in a related table.

There is lots of good information on normalization on the web.

If you don't understand this, come back with some table, primary fields, and
boolean field names.

--
Duane Hookom
Microsoft Access MVP


"lamartpm" wrote:

Hey,

Sorry, I created the database yesterday. Well the basic layout is a
personal information table with pretty standard fields: Name, address, sex,
D.O.B., etc. The other tables (classes, lab reports, immunizations, and
forms) have boolean fields that indicate whether they have completed certain
requirements. I used a query to join all the tables (all joined on a
personal id (auto-generated)). I also made another query that displays the
names of everyone who has anything not completed. So I want to create a
report so I can say, John Doe, you need to complete this test, form, etc. I
think it would just be an expansion of the latter mentioned query, but I
don't know VBA well. I am pretty good in JAVA, so I know the basic structure
required, but I was hoping for further help.

Thanks,
Paul

"Maurice" wrote:

Well if you want us to think about it give us something to look at (what do
you have, what's the source etc.)

--
Maurice Ausum


"lamartpm" wrote:

I'm pretty fluent in MySQL but I ran into a problem. I have a database that
consists mainly of booleans. It tracks whether people did certain things and
the layout is pretty simple. However, I cannot figure out how to write a
query that will display the person's name and what requirements they still
must meet. I have created a rather lengthy query to display everyone who has
not, but I want to be able to generate a form that will show the people what
they still need to do.

Thanks,
Paul
.

Relevant Pages

  • Re: SQL for Counting 1 total of 4 fields - normalization issue onl
    ... This was my attempt to normalize an existing ... Chris F ... "Duane Hookom" wrote: ... union query by record or some other way, ...
    (microsoft.public.access.queries)
  • Re: Calculate an Average of row entry fields
    ... Ok, I somewhat understand your set-up, but the only thing is that I have more ... SELECT personID, AVG ... If that is not enough to convince you to normalize your design... ... create another query field "TotalPerformanceRating" using the following ...
    (microsoft.public.access.queries)
  • Re: Calculate an Average of row entry fields
    ... SELECT personID, AVG ... division, shop area, employee productivity rating, work order rating, ... If that is not enough to convince you to normalize your design... ... > create another query field "TotalPerformanceRating" using the following ...
    (microsoft.public.access.queries)
  • RE: Running diferent query with one command
    ... I would normalize the Shift table or use a union query ... You can then use a single append query. ... FROM NameComb RIGHT JOIN Assignments ON NameComb.ID = Assignments.ID ... Duane Hookom wrote: ...
    (microsoft.public.access.queries)
  • Re: Create chart based on calculated totals
    ... You can normalize your table structure using a union query. ... then I think I can get the chart work. ... Duane Hookom wrote: ...
    (microsoft.public.access.reports)