Subform or Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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? There are some 7k records
in this file. please help
 
Tony,
Please provide a few examples of "old" data records setup vs. the "new" records setup.
From your description though... it sounds as though the new setup is more efficient for
importing into a database. Can't say for sure until we see the data.

And... please don't post duplicate individual questions to several newsgroups. This
causes duplication of responses and/or problems following the "flow" of the problem
determination process.
If you must multi-post, put a couple of newsgroup addresses on the same post. That
way, a response in one group will be seen in the other/s...
 
Old format:
idnum hgbo hgbn hghd eporxo eporxn eporxd
0001 12.2 12.3 12.4 12.1 12.2 12.3

New format:
idnum hgb hgbdate eporx
0001 12.2 10/03/06 12.1
0001 12.3 11/03/06 12.2
0001 12.4 12/03/06 12.3

As I explained in a different post, I'm helping someone at work. He is using
FoxPro and has a query he has used for a couple years on data (in the old
format) he imports from the excel spreadsheet. So my question is, how do I
get all the data on one row.
 
Do you always have three records per IdNum?

This might work for you.
Build a ranking query and save it as BaseQuery.

SELECT IDNum, HGB, EPORX,
(SELECT Count(*)
FROM YourTable as X
WHERE X.ID = YourTable.ID
AND X.HgbDate < YourTable.HgbDate) as Rank
FROM YourTable


Now use that BaseQuery to get your values.
SELECT IdNum, A.Hgb, B.Hgb, C.Hgb
, A.Eporx, B.Eporx, C.Eporx
FROM (BaseQuery as A INNER JOIN BaseQuery As B
ON A.IdNum = B.IDNUM and A.Rank = B.Rank -1)
INNER JOIN BaseQuery As C
ON A.Idnum = C.IDnum and A.Rank = C.Rank -2

Another method -
Build your table as you need it
Use an Append query to build records with the ID, HGBo, EporxO
INSERT into TargetTable (IdNum, Hgb0, Exporx0)
SELECT IdNum, Hgb, Exporx
FROM SourceTable
WHERE hdbDate Between #10/1/06# and #10/31/06#

Use two update queries to assign values to the additional fields.
UPDATE TargetTable INNER JOIN SourceTable
ON TargetTable.IDNo = SourceTable.IDNo
SET TargetTable.HGBN = [SourceTable].[HGB]
, TargetTable.EporxN = [Sourcetable].EporxN
WHERE hdbDate Between #11/1/06# and #11/30/06#

The second update query is left to you to construct.
 
Back
Top