Display fields Limited on Values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Well if you want us to think about it give us something to look at (what do
you have, what's the source etc.)
 
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
 
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.
 
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.
 
Your reply basically confirms my previous posting.

Is there any way that you would consider normalizing/correcting your table
structure?
 
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
 
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.
 
Back
Top