access97 table / query to convert some fields to columns?!

[Guest]

Please help…I'm tearing my hair out with this! I have access 97, and a table
of data from a laboratory.

Each record has the fields site id, a date, a time, an analysis determinant
code number and a result of the analysis. The records may have the same site
id, date & time but a different analysis determinant code and analysis result
(ie a sample taken same date / time but being analysed for say 5
determinants). Also, other records may have different site ID's, date time
det code and results.

I would like to end up with a table where I have a the site ID, date, time,
then each of the column's after that contain the results for all the dets
analysed for that particular site / date / time!

I don't have much experience in SQL…..!

Any help much appreciated.
Ta

 

[Douglas J. Steele]

Are you saying you want multiple values in a single column? That's not how
relational databases work, and so you won't be able to do it using SQL.

Perhaps if you gave an example of what you wanted, someone will be able to
help.

 

[Guest]

Sorry I wasn't being too clear. I have the table:
Site Date Time det result
121 21/2/06 15:30 180 0.23
121 21/2/06 15:30 181 0.56
121 21/2/06 15:30 182 0.85
121 21/2/06 15:30 566 0.05
126 15/3/06 09:00 180 0.23
126 18/2/06 11:50 566 0.01
121 6/8/06 07:55 181 0.68

etc
I'd like to end up with:
Site Date Time 180 181 182 566
121 21/2/06 15:30 0.23 0.56 0.85 0.05
121 6/8/06 07:55 0.68
126 18/2/06 11:50 0.01
126 15/3/06 09:00 0.23

 

[David F Cox]

I hope your lab is working on a cure for baldness. :->

You will get the best results if you have several tables each of which
contains data about one particular component of your work. These tables will
be related to one another, which is why we talk about a relational database.

for example you might want:
a table that contains the analysis determinant code and a description of
what that code represents.
a table that has the sites id and an address and other details of that site.
a table of test results with each record containing site id, date & time but
analysis determinant code and analysis result
and perhaps other tables related to contacts, test programs and schedules.
You can set up these tables in Access, run queries and run reports without
knowing SQL or Visual Basic even exists.
I do recommend that you seek a bit of on-hand help from someone who
understands databases, because you are building a foundation here. It might
seem like you are building a small hut, but later on you might wish that you
had thought about where the elevator would fit in. Failing on-hand help a
good book, and regular visits to the Access inbuilt help and Access forums
to seek on-line resources and ask when in doubt.

 

[Douglas J. Steele]

You might be able to get that with a Crosstab query. Go to create a new
query, select Crosstab Query Wizard from the choices that are presented, and
follow the steps.