Transposing data from row into column

[Welshgasman]

AFAIK, you cannot rs!variableNameHere because of how the bang operator works. You'd have to use rs.fields(variableNameHere)? However, what's the point of assigning the field name to a variable when you know the field name? Might as well just use rs!Field1 if that is the field name.
IIRC, someone suggested that "fields" is not required with that syntax but I've always invoked it and won't ever change. That's how shortcuts and sloppy code can stop working after Access updates.

I think O/P just copied @June7's code from post 3 without fully amending for their situation.?
All they changed was the table name?

[Micron]

Right, and I'm guilty of mixing up strF1 with Field1New

[June7]

Did you add Field1New and Field2New fields to the table?

I tested code and worked.

[DBS1]

Did you add Field1New and Field2New fields to the table?

I tested code and worked.

June7 - I just added the two fields to the table and it worked. I misunderstood your original suggestion, thinking the code would add them...my bad. I can live with this, but the source is a txt file, and gets updated periodically, so I would need to re-import when revisions are done and would have to add the two fields each time. That is not a problem, but if there's a way to build a query and do something similar with the code, then that might be better. If not, I am content with what you've shown me here. Either way thanks to you and all of the others for your time and patience.

[Welshgasman]

You do not add two fields each time, you add them once (which you have already done), and be done with it. Then you just update them.

[June7]

Aren't you importing to existing table? If not, why not?
If using existing table, probably should modify procedure to only pull new records in recordset. How to do that? Add another field such as ImportDate and also populate it in the update. Then recordset only pulls records where ImportDate IS NULL.

[DBS1]

Not sure we can trust the sql you have posted, or the source you are displaying is not the source you are referencing in the query -Hurdat2_1Field1 should be Hurdat2_1.Field1 otherwise will generate an error, Hurdat2_1.field2 would have appeared as Hurdat2_1.Field2 - and all those 'AS Expr' implies you have not used the query builder - or if you have they are somehow calculated


And given we are now seeing a more realistic set of data, you need to remove

AND ((Hurdat2.field3) Is Not Null)

from the criteria since you have values in that field for ID=6 for example.

More importantly, that means that field3 is text, not numeric so the bit of the criteria you removed needs to be replaced with

(Hurdat2_1.ID)<=[Hurdat2].[ID]+val([Hurdat2].[Field3]))

That is probably the reason you had a problem with the other method as well.

shame you didn't include row 6 in your original example data, wasted a lot of time

CJ London - first I apologize for the lack of data and appreciate that you took the time to help me. June7's post gets me what I need, but I'd also like to see if I can get what you've suggested to work. I've tried to follow along with your suggestions but it is still not working, and I'm getting a "Data type mismatch in criteria expression", perhaps because the fields are text fields? Here is the latest code based on your post #6. If you see where the problem might be, please let me know.

SELECT Hurdat2.Field1, Hurdat2.Field2, Hurdat2_1.ID, Hurdat2_1.Field1, Hurdat2_1.Field2
FROM Hurdat2 INNER JOIN Hurdat2 AS Hurdat2_1 ON Hurdat2_1.ID>[Hurdat2].[ID] AND Hurdat2_1.ID<=[Hurdat2].[ID]+val([Hurdat2].[Field3]);

[DBS1]

Aren't you importing to existing table? If not, why not?
If using existing table, probably should modify procedure to only pull new records in recordset. How to do that? Add another field such as ImportDate and also populate it in the update. Then recordset only pulls records where ImportDate IS NULL.

Thanks June7 and Welshgasman. I haven't had the need to import updated data yet, as I'm just now building this, so that's really helpful to know. I think you guys have solved this for me. Can't thank you guys enough, including all of the others who responded in the other posts.

[CJ_London]

you've moved to the other suggestion I made

suggest provide some example data - perhaps 100 rows