Read MS Access Table Contents Into Variables

[lhooker]

Why not just transform in Excel?

Unfortunately, I'm not aware on how to do this.

 

[Gasman]

Unfortunately, I'm not aware on how to do this.

I have Google on this laptop.

excel transform columns to rows - Google Search

www.google.com

Edit: Sorry, it is actually Transpose, and you can search for it in Excel Help.

Transpose (rotate) data from rows to columns or vice versa - Microsoft Support

How to use Transpose in Excel to rotate columns and rows to rearrange your worksheet data.

support.microsoft.com

Then there is Youtube

Loading…

www.youtube.com

 

[LarryE]

The data in the table comes from an existing EXCEL spreadsheet. I've import the data into a table, but the data is vertical. not horizontal, I'm not aware that DLookup or any function can accomplish this. I need the data to be horizontal.

So you want each record from the EXCEL spreadsheet to become a separate field in a table, which is what "Horizontal" is implying here.

Is that correct? Because if it is what you meant, ACCESS has certain limits that might prevent that, even if you could do it.

 

[CJ_London]

I don't know how

Well your post is in the vba/module forum so we don’t know if you are talking about an access table or an excel table

Assuming the former here are some links to create a crosstab query

Make summary data easier to read by using a crosstab query - Microsoft Support

Crosstab queries are useful for summarizing data, calculating statistics and then grouping the results.

support.microsoft.com

Crosstab Queries | CustomGuide

How to Create Crosstab Queries in Access

www.customguide.com

How to use the Crosstab Query Wizard in Access

www.quackit.com

 

[lhooker]

The EXCEL 'Transpose' feature solved my problem. Thanks to all who tried to help ! ! !

 

[Pat Hartman]

The data in the table comes from an existing EXCEL spreadsheet. I've import the data into a table, but the data is vertical. not horizontal, I'm not aware that DLookup or any function can accomplish this. I need the data to be horizontal.

@lhooker if this is a one time event. Just do the transform in Excel. If you can do a straight inport from the data as it is, then Access is the better solution.

1. There is absolutely NO NEED to read a table into an array to use the data in a loop. The table itself is an array. Duh! So, whatever you have to do, do using the table NOT an intermediate array.
2. Why would you denormalize the data to use it in Access? It is already normalized. You are just making it harder to work with.
3. Perhaps a crosstab query would solve the pivot problem and also would not require you to store the data in a denormalized fashion.

We ask dumb questions when we think you are trying to do something you don't need to do or we think you are asking us the wrong question as you are in this case. Now that we know at least part of the secret, we know for certain, your approach is not optimal. If you are vacillating, please read #1 again.

 

[lhooker]

@lhooker if this is a one time event. Just do the transform in Excel. If you can do a straight inport from the data as it is, then Access is the better solution.

1. There is absolutely NO NEED to read a table into an array to use the data in a loop. The table itself is an array. Duh! So, whatever you have to do, do using the table NOT an intermediate array.
2. Why would you denormalize the data to use it in Access? It is already normalized. You are just making it harder to work with.
3. Perhaps a crosstab query would solve the pivot problem and also would not require you to store the data in a denormalized fashion.

We ask dumb questions when we think you are trying to do something you don't need to do or we think you are asking us the wrong question as you are in this case. Now that we know at least part of the secret, we know for certain, your approach is not optimal. If you are vacillating, please read #1 again.

1. Right 2. Using the EXCEL 'Transpose' feature No, . using the EXCEL 'Transpose' feature Thanks ! ! !