fields as columns

[jagsdj]

Hello all,

First time here, so bear with me.

I have a table that goes something like this:

samDate samTime samDO samPH samBOD samNickel
11/05/03 11:23 4 6 7 4
11/04/03 1:30 2 3 <30 5

Now, I would like to get the table to look like this:
samDate samTime ParameterID samResult DL comments
11/05/03 11:23 samDO 4
11/05/03 11:23 samPH 6
11/05/03 11:23 samBOD 7
11/05/03 11:23 samNickel 4
11/04/03 1:30 samDO 2
11/04/03 1:30 samPH 7
11/04/03 1:30 samBOD 30 15 <
11/04/03 1:30 samNickel 4

Notice for samBOD since there was a "<" we havled the number and stored
the "<" in a seperate column.

Now, I know there is an issue with doing it this way, the database is
going to grow by leaps and bounds and is in return going to slow the
app down.

I'm open to suggestions and ideas!

I appreciate any help and time. Thank you.

jagsdj

 

[John Vinson]

Hello all,

First time here, so bear with me.

I have a table that goes something like this:

samDate samTime samDO samPH samBOD samNickel
11/05/03 11:23 4 6 7 4
11/04/03 1:30 2 3 <30 5

aha... good old spreadsheet logic, and you're trying to normalize it.
Good on ya!

Now, I would like to get the table to look like this:
samDate samTime ParameterID samResult DL comments
11/05/03 11:23 samDO 4
11/05/03 11:23 samPH 6
11/05/03 11:23 samBOD 7
11/05/03 11:23 samNickel 4
11/04/03 1:30 samDO 2
11/04/03 1:30 samPH 7
11/04/03 1:30 samBOD 30 15 <
11/04/03 1:30 samNickel 4

Notice for samBOD since there was a "<" we havled the number and stored
the "<" in a seperate column.

Now, I know there is an issue with doing it this way, the database is
going to grow by leaps and bounds and is in return going to slow the
app down.

Au contraire. You're moving from a faulty design to a much better one.
I'd suggest storing the Date and Time in a single Date/Time field, and
(perhaps) getting rid of the repeated "sam" in the ParameterID; and
I'm not at all sure where the 15 in DL came from. The Query that can
do this is what I call a "Normalizing Union Query" - you'll need to
get into the SQL window to create it, it's a bit more complex than the
grid can handle. Air code:

SELECT samDate + samTime AS SamDateTime, "DO" As ParameterID,
IIF(Left(samDO, 1) = "<", Val(Mid(samDO), 2), Val(samDO)) AS
samResult, IIF(Left(samDO, 1) = "<"), ??, NULL) AS DL, IIF(Left(samDO,
1) = "<", Left(samDO, 1), NULL) As Comment
WHERE samDO Is Not Null
UNION
SELECT samDate + samTime AS SamDateTime, "PH" As ParameterID,
IIF(Left(samPH, 1) = "<", Val(Mid(samPH), 2), Val(samPH)) AS
samResult, IIF(Left(samPH, 1) = "<"), ??, NULL) AS DL, IIF(Left(samPH,
1) = "<", Left(samPH, 1), NULL) As Comment
WHERE samPH Is Not Null
UNION
<etc. etc. for all the other columns>

Base an APPEND query on this saved UNION query to populate your new
normalized table.

 

[jagsdj]

I appreciate you taking the time to answer John. I will try out this
method and let you know how it goes.

Thank you again.

I will post again if i don't understand -- thank you!

Jagsdj