Excel into access and vertical columns? how?

[Wozzzzza]

i have an excel sheet that needs to be put in access 2000 and i cant work out how to do it.
as in the attached image i need to be able to have grouped rows across the top in a vertical manner and a user definable number and also have the company name down the side, again a user definable number, this is the easy part, but im stuck on the top part.
how would i go about making the text vertical keeping in mind that the user needs to be able to add more columns and remove columns at times? i have a seperate form where people can add information to the top row but how do i make it display how i want to?

 

[sunilvedula]

did u try link tables into access

Try the option export and then link tables and u should be able to get the table into access. I think once you that you will be able to make necessary changes. just try. I am also relatively new. Sorry if it does not solve your problem.

 

[Wozzzzza]

no that is not what i want and wont work.

i need to dynamically create more rows and columns. rows come automatically with a continuous form, but how do you icrease the number of columns in a form like you do with rows? can it be done?

e.g. add a new client(row) access will just tack it on the end of the form, but add new column to a form, how do you do that dynamically??

 

[Moniker]

Search on this forum for dynamic SQL, and use an ALTER TABLE SQL statement to add columns dynamically.

 

[Wozzzzza]

Search on this forum for dynamic SQL, and use an ALTER TABLE SQL statement to add columns dynamically.

no your not listening, i DONT WANT TOalter tables, i WANT TO ALTER FORM, increase columns in form dynamically like what automatically happens with rows.

 

[Moniker]

Excuse me for trying to help.

Your "rows" are actually "records" and that's what you're adding. To add a column dynamically, you would in fact use an ALTER TABLE query (a data definition query). Access is not Excel, and it doesn't work like Excel, at least when it comes to columns.

For what you described, you'd make an "Add New Field" button (which is a "column"), have your users click the button and then input the name of the new column, run an ALTER TABLE query, and then requery and refresh (in that order) the form's record source to have said "column" appear. While Access tables occasionally can look like Excel sheets (at least in Datasheet view), their functionality is completely different and therefore you can't just add a new column without defining it since it's a field. You'd need a field name, data type, etc.

Column1   Column2   Column3
abc       def       ghi
jkl       mno       pqr
stu       vwx       y&z

You want to add Column4. Get the field name from the user (Column4 in this case), run an ALTER TABLE query (search on it), and the result is this in your form (in Datasheet view, like Excel):

Column1   Column2   Column3    Column4
abc       def       ghi
jkl       mno       pqr
stu       vwx       y&z

Remember that Column4 is a field in a table that is linked to the form.

I understand exactly what you're trying to do, and I explained that the first time around -- it's an ALTER TABLE to add a "column" -- you add the "column" (actually a field) to the form's record source table. However, I don't think you understand what you are asking for in that columns are fields in Access, which requires you to ALTER the TABLE to "insert" a "column". This is the same table that your form is based on.

You have an Excel mindset that you are trying to apply to Access problem, and it doesn't work that way. Consider understanding your own request before becoming belligerent. Learning is a lot more fun when you want to learn, but not so much fun when you don't get what you're asking for in the first place.

 

[Wozzzzza]

ok i get ya what ya mean but its not totally what im after as it wont work for my application. what i want to do i dont think i can do with access without a lot of messy coding. it needs to reset this data ever month and the old data needs to be stored. for what its worth i think it is far easier staying with excel. thanks for replying any how.