create query displaying text from one table using key from another

[JJ1109]

Apologies for the subject. I have trouble describing my exact problem. I can
change it if someone else can suggest a better one!

I'm having a little trouble with a query. I think it has to do with setting
up relationships properly.

I have two tables in my database.
One is "Risk Assessments" and has three fields: RA_ID (primary), RA_Code and
RA_Descript.
Second is "Chemicals" and has a number of fields: Chem_ID (Primary),
Chemical, a few others, then RA_ID, RA1, RA2, etc up to RA6.

RA_ID in tblRiskAssessments is linked to RA_ID in tblChemicals.

I have a combo box in my form that drops the RA_ID from tblRisk Assessments
into RA1, then a second that drops another one into RA2 etc, as users do many
different things with the chemicals.

I now want to create a query that will list the chemical name (from
tblChemicals), RA_Code and RA_Descript (from tblRisk Assessments) so that I
can create a report with those in them. I'm stuck though - I can't work out
how to relate things to each other so that the query will display any data.

Hope that's understandable!

regards
JJ

 

[KenSheridan via AccessMonster.com]

Your risk assessments table is OK, but your chemicals table structure is
wrong I'm afraid. What you appear to have here are two entity types
RiskAssessments and Chemicals, so you start with tables to model these:

RiskAssessments
….RA_ID
….RA_Code
….RA_Descipt

Chemicals
….Chem_ID
….Chemical

You then have a relationship type between them. Its unclear from your post
just what is being modelled here, but lets assume its some sort of risk
assessment audit, so we'll call it RA_Audits. The relationship is many-to-
many, i.e. each risk assessment can relate to one or more chemicals, and each
chemical can relate to one or more risk assessments. A many-to-many
relationship between two tables is modelled by a table with two foreign key
columns, each referencing the primary key of one of the other tables. Other
columns in this table model other attributes of the relationship, so if we
assume this is an audit of each employee's exposure to risks then it might
have an EmployeeID column referencing the key of an employees table, or it
might be an audit of workspace hazards, in which case it would have a
WorkspaceID column. If we assume the former then the table would be like
this:

RA_Audits
….RA_ID
….Chem_ID
….EmployeeID
….Audit_Date

In this case the relationship being modelled is a 3-way one between risk
assessments, chemicals and employees. As the same employee might be subject
to audit a number of times, on different dates, the primary key of this table
is a composite one of all four columns.

You mention other columns in you current chemicals table. Which table these
go in depends on just what they are attributes of. If a column is an
attribute of the chemical itself then it goes in the Chemicals table; if its
an attribute of the relationship between the employee and the chemical then
it would go in the RA_Audits table.

While the above is really only an example and probably does not accurately
reflect your own real world situation, I hope it gives you an idea of the
principles involved. As you'll see your multiple RA columns have been
replaced by multiple rows in the a related table, which is the correct way of
modelling this sort of thing. Joining the tables in queries as the basis for
reports is then very simple.

If you need further advice on this post back with a description of the real
world situation which you are trying to model here.

Ken Sheridan
Stafford, England

 

[JJ1109]

Thanks for the reply Ken!

I'll try to explain the situation I've got:

I had a spreadsheet of chemicals, catalogue numbers, amounts, etc. I'm
trying to make a database of these chemicals, as we previously were writing
out Risk Assessments by hand - this way, I have a report for the risk
assessment that is simply printed. I think that might still be a bit unclear.

RA_Descript is the description of a task that is done with the chemical, eg.
"Decant into a narrow neck container" or "Heat using a heating mantle". Each
of those tasks have their own assessment of risk, but that is not required
here. All I am doing is having a report that lists the chemical name, hazards
identified with that chemical (which are entered in various fields in
tblChemicals) and those pesky risk assessment names.

What you're explaining here is that I need a third table - and in that table
are the names of the primary keys from the other tables? Then I create the
many-to-many relationship between the (for example) RA_ID key in
tblRiskAssessments and the field RA_ID in the third table?

I'll have a play now and see what happens.

thanks!

 

[JJ1109]

That worked nicely!

I have now moved onto creating the form that users will do the data-entry
into, the wizard created a subform for the Risk assessment parts (so that you
enter the chemical once, then can enter multiple RA codes into the sub form).
I'm trying to make some combo-boxes in the subform now, so that the user can
just select the code from the box, rather than having to type it in. Having a
bit of trouble with that one now. I'll keep fiddling though whilst I wait!

thanks again
JJ

 

[KenSheridan via AccessMonster.com]

What you'll need is a subform which is based on the third table, RA_Audits in
my example, bound to the foreign key RA_ID column, but which hides this and
shows in its list the RA_Code and RA_Descipt values. To do this se up the
combo box's properties like so:

Name: cboRA_ID

ControlSource: RA_ID

RowSource: SELECT RA_ID, RA_Code, RA_Descipt FROM RA_Audits ORDER BY
RA_Code;

BoundColumn: 1
ColumnCount: 3
ColumnWidths: 0cm;2cm;4cm
ListWidth: 6cm

If your units of measurement are imperial rather than metric Access will
automatically convert them. The important thing is that the first
ColumnWidths dimension is zero to hide the first column. Experiment with the
other two ColumnWidths to get the best fit. The ListWidth is the sum of the
ColumnWidths.

Add an unbound text box to the subform with a ControlSource of:

=cboRA_ID.Column(2)

When you select an item from the combo box the code will show in the combo
box and the description in the unbound text box. The Column property is zero-
based, so Column(2) is the third column, RA_Descipt.

You can of course include other controls in the subform id there are other
attributes of the relationship which you are modelling by further columns in
the RA_Audits table.

Ken Sheridan
Stafford, England

 

[JJ1109]

Thanks again Ken,

That works great! A quick one - at the moment, the query only displays
records that have data in the RA_ID field. Is there a way to get it to
display all records that I have classed as Hazardous (have a "Y" in the
Hazardous field) even though they haven't been assessed for risks yet?

I can get it to display the records that have a Y in the Hazardous field
easily enough, but as soon as I add the linked tables in, only the ones with
the extra data appear.

More confusing descriptions, sorry. You're being so helpful, thanks - I can
post up some pictures of what I'm trying to describe if that will help.

thanks
JJ

 

[KenSheridan via AccessMonster.com]

By default, when you create a query it uses an INNER JOIN. This only returns
rows where there are matches in the joined tables. To show al rows from one
table regardless of matches in another an OUTER JOIN is used. These can be
LEFT or RIGHT, but LEFT OUTER JOIN tend to be more commonly used. The OUTER
is optional so when you create a query of this type in query design view it
will just say LEFT JOIN in the SQL statement.

To do this in query design view you join the tables ( not 'link' BTW, that
means something different) in the usual way. Then select the join line by
clicking on it (it will thicken when selected). From the View menu select
'Join Properties'. In the dialogue select the option which says something
like: Include ALL records from 'Chemicals' and only those from 'RA_Audits'
where the Joined fields are equal.

If you are joining three tables, e.g Chemicals----<RA_Audits----
<RiskAssessments
you'll also need to change the join between RA_Audits and RiskAssessments to
an outer join in the same way, in this case selecting: Include ALL records
from 'RA_Audits' and only those from 'RiskAssessments ' where the Joined
fields are equal. In SQL this will give you one LEFT JOIN and one RIGHT join.


You should then see all the rows from Chemicals regardless of whether there
are matches in RA_Audits. One thing to note about this type of join is that
you can only put criteria on columns form the left side of the join, not the
right side. It doesn't raise an error if you do so, but in ewffect turns the
join into an inner join. So you can include a criterion on the Hazardous
field from the Chemicals table because that's on the left side, but not on
any columns from RA_Audits or RiskAssessments. The only exception to this is
where you put a criterion of Is Null on a column from a table on the right
side of a left outer join to return only rows from the table on the left side
which have no matches in the table on the right side.

Ken Sheridan
Stafford, England