OT: Does anyone here know MS Access? - PPC Posting Board

Subject: OT: Does anyone here know MS Access?
Author: Huinesoron
Posted on: 2015-10-28 17:10:00 UTC

I know we have quite a few computer-skilled folks hanging around, so I thought I'd ask...

Under Access 2010, I'm trying to do something that seems like it should be really, really easy, but all the searching I can think of is telling me is practically impossible. I have a database with two tables: one of chemicals, one of risk assessments.

What I want is to give each risk assessment a list of chemicals used in it, and then to somehow have the risk assessment determine whether any of those chemicals have their 'hazardous' field ticked.

It's the sort of thing that I can think of a dozen ways to do it in Excel - but every route I try in Access ends up in a mire of non-functional VBA and SQL.

So... can anybody help? 'Link to multiple entries and check if any of them have X' must be simple to pull off, right?

Right...?

hS

Reply Return to messages

OT: Does anyone here know MS Access? by Huinesoron on 2015-10-28 17:10:00 UTC

Not an Access expert, but I know SQL! by Techno-Dann on 2015-10-29 03:15:00 UTC Reply

What's the connection between assessments and chemicals? I suspect that each assessment has multiple chemicals involved, but are chemicals referenced by multiple assessments? If so, there should be a third table called a cross table, which is just a giant set of assessment-chemical ID pairs to keep track of what is associated with what.

If there's just one assessment per chemical, you'll want a query that looks something like:

select (a.id) from assessments a
join chemicals c
on c.assessmentid = a.id
where c.hazardous = true;

(The table might be set up the other way around, with a.chemicalid = c.id)

If there are multiple assessments per chemical, and a properly set-up cross table, you'll need something more like:

select (a.id) from assessments a
join crosstable r
on r.assessmentid = a.id
join chemicals c
on r.chemical_id = c.id
where c.hazardous = true;

Hope that helps!

And now that I've hit post... by Techno-Dann on 2015-10-29 03:18:00 UTC Reply

Those queries will both give you one row per hazardous chemical in an assessment - if you're dealing with, say, the unholy child of chlorine trifluoride and nitrogen tetroxide (why would you do that?), you'd get back the id twice, once for the join with FC3 and once for the join with NO4. However, if you replace the first line with:

select (distinct a.id) from assessments a

that will only give you one copy of each assessment ID. Distinct is a useful keyword.

Thank youuuuu! by Huinesoron on 2015-10-29 18:00:00 UTC Reply

My brain now hurts from spending the morning learning SQL from scratch, but with your query design to work from, I've managed to make Access do exactly what I want.

Well, exactly what I want when it comes to queries, at least. I'd also like it to not crash when I try to use VBA to access more than three of those queries in a row... but I suppose you can't have everything. :-/

Anyway: thank you! Now I can define exactly how horrifyingly dangerous stuff is!

(I think the worst thing we have is probably the hydrofluoric acid, which is of course lethally toxic. Though cyclohexamine is toxic, flammable, corrosive, and will turn your children into mutants... oh, or we have chromium trioxide! That one's a carcinogenic oxidant - and toxic and corrosive, naturally... XD)

hS