Query or what?

[Guest]

I have imported files from an excel spreedsheet into a new database in an
attempt merge some fields. In previous years this data was provided to me
with patient data being on "one" row with certain fields that have different
dates being denoted with the test perfomed and the letter of the month beside
it ex: hgbo for hemoglobin october. This year the contractor split the data
into "three" different rows to accomodate the three different months of tests
instead of the one row with three columns. How can I get this in the form I
need it? Do I create a subform or write a query? Also, I realize I will
have to create the column headings "hgbo, hgbn, hgbd" but I don't know how to
tell acess to take the second and third instance of the "idnum" and place
values in hgbn and hgbd fields. There are some 7k records in this file.
please help

 

[Douglas J. Steele]

If you feel you need to post to more than one group (HINT: it's seldom
necessary), please have the courtesy to cross-post (send the one message to
all groups at once), rather than multi-post (send individual messages to
each group). In this way, all responses to your post will be available
together, regardless of what group the responder was in, and the rest of us
won't have to read your post multiple times. (It also uses fewer server
resources)

If you're using Microsoft's web interface to post, you should see an
"Advanced Options" link at the bottom of the page. You can type the names of
the various groups into the Newsgroup box, separating each newsgroup name
with a semicolon.

Note that it's generally consider to be A Bad Thing to cross-post to more
than about 2 or 3 newsgroups.

That having been said, I'm afraid you haven't given nearly enough
information.

First, don't assume that you need to arrange the data the same way you're
getting it from the source. Having columns named "hgbo, hgbn, hgbd" is
usually an indication that the tables haven't been properly normalized. I'm
assuming that hgbo, hgbn and hgbd are the names of specific tests. Using
those as field names in your table means that you're actually "hiding" data.
Rather than having data like (pardon me if the numbers don't make any
sense!):

PatientID hgbo hgbn hgbd
1 1.42 32.1 530.1
2 1.12 41.6 499.3
3 2.02 9.2 521.2

you actually want something like

PatientID Test Result
1 hgbo 1.42
1 hgbn 32.1
1 hgbd 530.1
2 hgbo 1.12
2 hgbn 41.6
2 hgbd 499.3
3 hgbo 2.02
3 hgbn 9.2
3 hgbd 521.2

If you can't figure out the optimal database design, try posting in
microsoft.public.access.tablesdbdesign with details of what it is you're
modelling. The layout of what you're getting containing the data is
incidental: once you've got the proper database design, then we can help you
map from your imported data to the actual database.