Transpose data or Array Needed?

[pgoulet]

I have the following data.

Date Period U PC G V
04/12/2004 12:15:00 AM 2 2 0 NOV
04/12/2004 12:15:00 AM 3 3 0 MMT
04/12/2004 12:15:00 AM 18 19 0 VGT
04/12/2004 12:45:00 AM 1 1 0 NOV
04/12/2004 12:45:00 AM 1 1 0 SDG
04/12/2004 12:45:00 AM 1 1 0 SED
04/12/2004 12:45:00 AM 6 6 0 MMT
04/12/2004 12:45:00 AM 24 25 0 VGT
04/12/2004 1:15:00 AM 1 1 0 MMT
04/12/2004 1:15:00 AM 1 1 0 SDG
04/12/2004 1:15:00 AM 2 2 0 NOV
04/12/2004 1:15:00 AM 18 19 0 VGT

What I am trying to do, is present the data in a linear format, so that only 1 date and time is presented.
date time U,PC,G,V,U,PC,G,V,U,PC,G,V etc..
date (next) time etc.
(next) date (next) time etc.

Not a great example, but if you copy and paste to notepad you will get a better idea.

Date Period U PC G V U PC G V U PC G V U PC G V U PC G V
04/12/2004 12:15:00 AM 2 2 0 NOV 3 3 0 MMT 18 19 0 VGT
04/12/2004 12:45:00 AM 1 1 0 NOV 6 6 0 MMT 24 25 0 VGT 1 1 0 SDG 1 1 0 SED
04/12/2004 1:15:00 AM 2 2 0 NOV 1 1 0 MMT 18 19 0 VGT 1 1 0 SDG

I have tried a series of sub queries, and then joining all the sub queries (on date, time and V) on something that would have to be considered a summary query, with great success.

I am looking for something that will not require me to recreate the query everytime V changes.

Any ideas?

Thanks

Paul

 

[legos]

I am guessing the data comes from a text file, but it won't matter too much with this example
Here is a thought, you can use the mid and Instr functions along with Len function.
For the data you have above here is what i would do.
dim string1 as string
dim string2 as string
dim string3 as string
dim string4 as string
dim string5 as string
dim string6 as string
dim string7 as string
dim strlen
dim mlen as interger
dim mycomp

'04/12/2004 12:15:00 AM 2 2 0 NOV
'04/12/2004 12:15:00 AM 3 3 0 MMT
'04/12/2004 12:15:00 AM 18 19 0 VGT
'04/12/2004 12:45:00 AM 1 1 0 NOV
'04/12/2004 12:45:00 AM 1 1 0 SDG
'04/12/2004 12:45:00 AM 1 1 0 SED

Do While f.AtEndOfStream <> True
sting1 = f.readline
'i assume you will get it in some sort of .readline fassion
strlen = len(string1)
mlen = InStr(string1, "M")
'since all the times end in 1 you can use that to your
'advantage.
string2 = Mid(string1, 1, mlen)
string3 = Mid(string1, mlen, strlen)
'This breaks the string into a time side and a info side
string4 = f.readline
strlen = len(string4)
mlen = InStr(string4, "M")
string5 = Mid(string4, 1, mlen)
string6 = Mid(string4, mlen, strlen)
mycomp = strcomp(string2, sting5, 1)
if mycomp = 0
string7 = string3 & string6
else
if mycomp = 1
'you can set up your own output file
string7 = string2 & string7
'this will put the time in front
a.writeline string7
end if
Loop

There are a few errors in the routine but such as a better transition between times, but that should get you rolling. if you need more help just ask. Good Luck


Durible Outer Casing to Prevent Fall-Apart