Condensing field values with a query?

  • Thread starter Thread starter ucdcrush
  • Start date Start date
U

ucdcrush

I have a table that looks like this:

-------------
Patient ID - Site ID - Error 1 - Error 2 - Error 3 - Error 4 (etc.
through Error 70)
1211 1 133 145 118
1211 1 133 115
-------------

etc. for 94 rows.

What I'd like to do is make that into this:
-------------
Patient ID - Site ID - Error 1 - Error 2 - Error 3 - Error 4 (etc.
through Error 70)
1211 1 133 145 118 115
-------------

i.e., condense the data for that patient by moving the 115 from the 2nd
record up into the next available field in the other row. The 133
needn't be moved up since it's already there.

How can this be accomplished? Any input is greatly appreciated.
 
Is there any chance you can normalize your table structure?
Is there any way that you can tell exactly which record would provide the
Error 2?

Do you understand how to write code to create recordsets?
 
Hi Duane..

Can I ask what you mean by "normalize" the table structure? This table
is actually a linked table from a named range in Excel that I used to
put the errors one after another so that in our Access report, we can
just list the fields for a given patient in a row and they will be
presented without any blanks.

We can tell which record caused the error #2. For brevity, I used
numbers in the above example, but the actual error code looks like
this:
PA101022-12/2/2003-SystolicBPMissing

that is the patient ID number, the medical visit date, and then the
error message. So we know from the error code where the error
originates.

I do not know of coding to create recordsets. Is there a resource or
link you could suggest?

Thanks.
 
Normalize suggests that each value create a new record in a related table.
You would not have fields with numbers in their names like 1, 2, 3, ... Jeff
Conrad has a some good links at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101.

Your sample had error 133 in two consecutive records in the same field. How
would you handle the same error in different fields? Are you just looking
for a unique list of errors per Patient and Site?
 
Back
Top