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

 

[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

 

[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