How to convert raw data in access?

[Ngabay]

Hey Guys,

I am stuck here, can someone tell me if this is possible. I currently have a table formated like this:

Date Hour1 Hour2.....Hour24 -field headings
1/1/01 33 55 66
1/2/01 44 45 56 -values

i would like to get the data from that table to be displayed in this format:

Date Hour Value
1/1/01 hour1 33
1/1/01 hour2 55
.
.
1/2/02 hour1 44

please give me any suggestions or tips on how to accomplish the above task.

Thank you,
Nir

 

[Norris68]

Run a series of 24 queries

INSERT INTO NewTable ([Date],[Hour],[Value]) SELECT [Date], 'Hour1', Hour1 FROM OldTable

INSERT INTO NewTable ([Date],[Hour],[Value]) SELECT [Date], 'Hour2', Hour2 FROM OldTable

...etc.

 

[Ngabay]

Thank you,

I have about 30 years of dates, is there any way to create a loop?

thanks,
Nir

 

[judgehopkins]

Norris68, I would ask if there is a way that Ngabay could run an update query with wildcards instead of typing in 24 inserts.

I had posted this in another forum (no one could figure it out) and, I suspect, an answer to my problem would help Ngabay also.


How do I make this update query work?


UPDATE tblOldData
SET SellerFN = Replace(Trim(StrConv([SellerFN],3))," "," "


I imported a database into Access 2000. There is only one table, consisting of 12 text fields and two date fields.

Not every field in the table has information in it, i.e., there are a lot of null or empty fields. There are lots of double and triple spaces and inconsistent cases for the text (i.e., some all upper-case, some mixed case, etc.)

In other words, it is a mess.

I want to run an unpdate query to change the values in the table that need to be fixed.

I want to do three things: (1) trim leading and trailing spaces; (2) replace the double and triple spaces in the strings with a single space; and (3) capitalize the first letter of every word in the string.

How do I do this? The code above is not working. When I run the query on the 2350+ records, I get the following error message:

Microsoft Access didn't update 1747 records due to a type conversion failure....

Thanks!

"Be the first to say what is self-evident, and you are immortal." - M. Ebner-Eschenbach

 

[Norris68]

You only have to run it 24 times - one for each 'HourX' field. You could write a bit of VBA to do it ...

Dim A As Long
DoCmd.SetWarnings False
For A = 1 To 24
DoCmd.RunSQL "INSERT INTO NewTable ([Date],[Hour],[Value]) SELECT [Date], 'Hour" & A " "', Hour" & A & " FROM OldTable"
Next A
DoCmd.SetWarnings True

Of course, you are going to end up with 24 times as many records as the original table (one for each hour), but you expected that didn't you...