Surely this must be possible........?

[anthony.carter]

Hi,

I have a table where there are several fields that hold a numerical
value. It is in fact a table that holds the results for tests. each
unit is tested on about 10 different aspects. Each one is scored from
1 to 4.

What I would dearly like is to run a query which only selects those
units which have had a test score below 4 in any of the tests, and to
only show those tests that were below 4. Even I can work out how to
select the units that scored below 4 in any of the tests, but the
query always shows all the tests, even the 4 scores the unit achieved.

Is it possible to blank out a score / field/ test if it achieves a 4?

Thanks,

Regards,

TC

PS. this will make me a god to my boss if it works!

 

[Jason Lepack]

How is your table structured?

I'm guessing the difficulty is based on the fact that your table
columns looks like this:

unit, test1, test2, test3, etc...
1, 3, 4, 4, ...
2, 4, 4, 4, ...

This structure is not normalised, and will become a pain if you decide
to add a new test to the process... think about all the things that
must change....

If that is the case then your query looks like this:
SELECT unit
FROM yourTable
WHERE (test1 < 4) or (test2 < 4) or (test3 < 4) or etc...

You have a many-to-many relationship between tests and units (many
units take many tests) A normalized design, if you're interested, is
to have 3 tables.

tbl_unit: (info about the unit)
unit_id - autonumber - PK
unit_name - text
anything else about the unit

tbl_test: (info about the test)
test_id - autonumber - PK
test_name - text
anythign else about the test

tbl_unit_test: (test of a unit)
unit_id - number - FK - PK
test_id - number - FK - PK
unit_test_datetime - date/time - PK
unit_test_result_value

Then when you wanted to get results of all tests with a result_value
less than 4 you would use a query like this:
SELECT U.unit_name,
T.test_name,
UT.unit_test_datetime
FROM tbl_unit_test as UT
INNER JOIN tbl_unit as U
ON U.unit_id = UT.unit_id
INNER JOIN tbl_test as T
ON Y.test_id = UT.test_id
WHERE unit_test_result_value < 4

Cheers,
Jason Lepack

 

[tcarter]

Cheers for that Jason.

You're absolutely right about how the table is structured. I did it that way
because 1) I'm a novice and 2) it made it easier for others to enter
information into the table via a form (and I was advised it would be simpler
and work better if the form, in turn, only entered information into one
table).

Breaking the table down into constituent parts (normalising) did occur to me
but, lacking knowledge, I was never brave enough for a first step. This was
prescient because it would indeed have been disastrously different from the
correct method!

I believe I can grasp the concept you have outlined with the structure of
three tables - with a bit more thinking time and comprehension it should
start to sink in.

Thanks very much,

Regards

TC

 

[BruceM]

Think about it htis way: a table contains information about a single
real-world entity. This is a very different way of thinking than would be
used for a spreadsheet. I know this is not your situation, but an
employee's information would be entered into an Employee table. A record
(row) would contain a unique identifying number (such as EmployeeID),
FirstName, LastName, and other information specific to one employee.
An employee gets paid periodically (let's say weekly). That information is
stored in an EmployeePay table, in which each record contains a unique
identifying number (automatically generated, if you like), the EmployeeID
(more on this in a moment), hours, rate, withholding, and other information
specific to an employee's pay on a specific week.
The Employee table (tblEmployee) and the Pay table (tblEmployeePay) are
related to each other one-to-many, since one employee will have (in most
cases) many weekly pay records. Each record in tblEmployeePay includes an
EmployeeID number, which relates that record to a particular employee.
The way this typically happens is that a form is bound to tblEmployee, and a
subform to tblEmployeePay. Because the tables are related one-to-many
through the EmployeeID field, each new record in tblEmployeePay (the
subform's Record Source) will automatically include the EmployeeID number.
All you need to do is enter the pay information. This capability is one of
the main things that makes the relational database such a versatile tool.
The really cool thing is that if an employee's name changes, all you need to
do is change one record in tblEmployee. Since the related Pay table is
storing only the EmployeeID number, it doesn't matter what happens to the
name or any other information. No matter your name, the database will know
you as 12345. Information that is part of record 12345 in tblEmployee is
available to related records. It is like the way the IRS has your SS
number. No matter your name and address changes over time, there is a
continuous tax record associated with just your SS number. The rest of the
information is there if they need to send you something in the mail or
whatever, but it is not relevant to the relationship.
I have sketeched a one-to-many relationship. Jason described a
many-to-many, which is more complex, but still follows the same principles.
By the way, the form is just the interface with the data. Ease of use is up
to the person who designs the form. A form that enters information into
just one table is not inherently simpler than a form with subforms that
enters information into several tables.

 

[anthony.carter]

Hi,

Could I possibly ask for a little more help with normalising the
design. In the three tables recommended I assume that PK means Primary
Key. What does FK stand for?

Cheers,

Regards,

TC

 

[Jason Lepack]

A FK is a foreign key. It means that that field is related to a
Primary Key in another table.

Make sure that once you've created the tables that you go to Tools-

Relationships and add in all three tables and connect the FK to it's

partner PK. Make sure that you check the box "Enforce Referential
Integrity" for each relationship.

In tbl_unit_test those three fields combined are the PrimaryKey.

Sorry if any of this sounds weird, I just woke up... Any questions
just post back.

Cheers,
Jason Lepack

 

[tcarter]

Thanks. I did some reading and believe I understand it at the moment!

Cheers!

Regards,

TC