User to select different fields

[Guest]

Hello,

I've got a database that stored the values of the concentration of different
chemical compounds within a sample in one table (~ 50 different chemicals),
eg:

Sample....Chemical 1...chemical 2............chemical 50

I look at different ratio of these chemicals and want to write a query that
allows the me to select different chemicals each time the query is run
without having to go back into the design view each time. Basically what I
would like is the query to prompt me for "first chemical" and then "second
chemical" then return a table with:

Sample....."first chemical"/"second chemical"

Is there a way to do this?

thanks

Susan

 

[Allen Browne]

Hi Susan. The problem here is that the table is not normalized.

One sample can contain many chemicals. One chemical can also be in many
samples. You therefore have a many-to-many relation between samples and
chemicals, so you need these three tables:

Sample table (one record for each sample you analyze). Fields:
SampleID AutoNumber
SampleDate Date/time when you analyzed it
...

Chemical table (one record for each chemical you handle). Fields:
ChemicalID AutoNumber
ChemicalName Name of this chemical
...

SampleChemical table (one record for each chemical found in sample):
SampleID Number which sample this refers to
ChemicalID Number which chemical was found in this
sample
Concentration Number (Double) the concentration of this chemical in
this sample.

Your interface will be a main form for the Sample, with a subform for the
SubformChemical. The subform will have a combo for selecting the chemical,
and you enter as many rows as you need for all the chemicals in that sample.

Now all the chemicals are in the one column, and it is very easy to filter
for a particular one.

Additionally, you do not have to redesign your tables when they think up
another chemical you might want to test for. Just add a record to the
Chemical table, and you're in business.

 

[Guest]

Hello Allen,

Thanks for the reply. I see how your solution would get around the problem
but i would really actually like to keep all my chemicals in their own
columns. Can you recommend a solution where I can keep my current design?

Cheers

Susan

 

[Vincent Johns]

Yes, it's not difficult.

(1) Do what Allen suggested.
(2) Define a Query that displays your information the way you'd like it
displayed.
(3) Design a Form or Report that uses that Query to lay out on your
display or on a printable page your results in the format that you prefer.

You're talking about a couple of different things here. "Columns", I
expect, are what you'd see in a display (such as a Form), whereas
"fields" are the objects that will contain your data. The fields don't
necessarily have to correspond directly to columns, though that's possible.

It is possible, in Access, to make your Table look like a spreadsheet,
but you'll likely find yourself doing far more (useless) work than
necessary. If you need to do something simple like changing a field
width, wouldn't you really rather do it only once instead of 50 times?!?

As you think about displaying your data (assuming you've normalized your
Tables), one of your options is to use a Crosstab Query. That will look
a lot like a spreadsheet, but will be based on your normalized (and
easy-to-maintain) Tables.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.