Further to.......Surely this must be possible?..

[anthony.carter]

Hi,

The following reply from Jason Lepack to a previous question sets out
how to normalise a design:

"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 "



In the third table 'tbl_unit_test' there are two fields
'unit_id_number' and 'test_id_number'. Are these meant to be the same
as the unit_id_autonumber in table One (tbl_unit) and
test_id_autonumber in table Two (tbl_test)? If so, how are they
related ie. how would the id number in table Three be made to be the
same as in table One or Two?

Let me explain my my naivete another way.
Id numbers in the first two tables are generated automatically. When
it comes to entering the id numbers for the unit and test in the third
table how do I know what number to enter?

Thanks, Regards,

TC

 

[Jason Lepack]

Use a form. Link the form to the tbl_unit and then create a subform.
Base the subform on tbl_unit_test. Link the subform to the main form
on unit_id. Create a combo box on the subform that looks up the names
of the tests in tbl_test. Access will automatically include the PK.

Now whenever you select an item on the main form you will see all of
the tests performed for it. You can do the opposite as well and base
the main form on tbl_test and the subform on tbl_unit_test. Then you
see all of the units that have taken that test.

Cheers,
Jason Lepack

 

[Michel Walsh]

They are.

Say it is about client (name) and their client_id. The name, you know, the
client_id is a number automatically generated. Somehow, you know the name,
and can let the system find the client_id. The problem occurs if you have
two different clients (client_id) with the same name (but with different
location, or otherwise, that allows you to differentiate who is who).

So, either you do some explicit 'lookup', DLookup on the name, either, with
Jet, you use an automatic lookup, in a query.

Hoping it may help,
Vanderghast, Access MVP

 

[Jamie Collins]

The following reply from Jason Lepack to a previous question sets out
how to normalise a design:

"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

Id numbers in the first two tables are generated automatically. When
it comes to entering the id numbers for the unit and test in the third
table how do I know what number to enter?

A fundamental problem with Jason's design (or perhaps his approach to
posting a schema <g>) is that it does not prevent duplicates e.g.

INSERT INTO Units (unit_name) VALUES ('The A Team');
INSERT INTO Units (unit_name) VALUES ('The A Team');
INSERT INTO Units (unit_name) VALUES ('The A Team');
....

Ditto for Test:

INSERT INTO Tests (test_name) VALUES ('Piano grade 2');
INSERT INTO Tests (test_name) VALUES ('Piano grade 2');
INSERT INTO Tests (test_name) VALUES ('Piano grade 2');
....

Making unit_name and test_name keys in those tables will prevent such
duplication and using them as foreign keys in your relationship table
will make its data will more readable e.g.

INSERT INTO TestsTaken (test_name, unit_name)
VALUE ('The A Team', 'Piano grade 2')
;
INSERT INTO TestsTaken (test_name, unit_name)
VALUE ('The A Team', 'Conversational French')
;
etc

Jamie.

--

 

[Jason Lepack]

Ah, you got me... I do have a problem with how I post schema...

In my design I usually set the Surrogate Key and then a Unique Index
on my "Real" key in my main tables, but the joining table is
correct!

Here's another crack.
tbl_unit: (info about the unit)
unit_id - autonumber - PK
unit_name - text - Unique Index
anything else about the unit


tbl_test: (info about the test)
test_id - autonumber - PK
test_name - text - Unique Index
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

Thanks Jamie.

Cheers,
Jason Lepack

 

[anthony.carter]

Thank you one and all for the swift responses. It has certainly given
me something to be getting on with.

Thanks,

Regards,

TC

 

[anthony.carter]

Hi,

And another thing........

In the original normalisation as set out by jason in the query bit
where it says:

"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 "

Just what is the UT in the fourth line referring to?
And the U in the fifth line?
And the T in line seven?

I'm sure I'll be embarrassed by the answer but I can't for thr life of
me figure it out.

Thanks,

Regards,

TC

 

[Dale Fye]

Anthony.

These are called aliases. You can give a table an alias by including " as
???????" right behind the name of the table in the FROM clause in your
query. They are used to make writing your SQL easier. I personally think
it make the SQL easier to read as well.

If you did not use aliases, then your query would look like:

"SELECT tbl_Unit.unit_name,
tbl_test.test_name,
tbl_Unit_Test..unit_test_datetime
FROM tbl_unit_test
INNER JOIN tbl_unit
ON tbl_Unit.unit_id = tbl_Unit_Test.unit_id
INNER JOIN tbl_test
ON tbl_Test.test_id = tbl_Unit_Test.test_id
WHERE unit_test_result_value < 4 "

Dale

 

[Jamie Collins]

These are called aliases. You can give a table an alias by including " as
???????" right behind the name of the table in the FROM clause in your
query.

Actually, they are 'correlation names'. The colloquialism 'aliases' is
merely an alias

Jamie.

--