moving data from column to rows

[jddealer]

have data - differring number of equal data in field 1 (some 1 record, some
11 records)
Field 1 Field 2
1 abc
1 def
1 ghi

and want to wind up with
Field1 field 2 field 3 field 4 etc
1 abc def ghi

Thoughts

 

[Tom Wickerath]

Hi JD,

What is the name of the Field 2, along with some typical values, in your
original table? The reason I ask is that it appears as if you are asking for
help in how to de-normalize your database, which is not a good thing to do.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[jddealer]

the table is a list of options for a specific stock number such as
stock# option
1 radial tires
1 2yd bucket
1 airconditioning
2 radio
2 biasply

then I need to get it into a file of one record per stock # such as
stock# option1 option2 option3 option 4
option 5
1 radial tires 2yd bucket airconditioning (if any
more options)
2 radio biasply

would set up 15 option code fields

 

[John W. Vinson]

have data - differring number of equal data in field 1 (some 1 record, some
11 records)
Field 1 Field 2
1 abc
1 def
1 ghi

and want to wind up with
Field1 field 2 field 3 field 4 etc
1 abc def ghi

Thoughts

A Crosstab query will do this.

John W. Vinson [MVP]

 

[Tom Wickerath]

I'm afraid that you are applying spreadsheet logic to Access. Having separate
columns for each option is an example of a multivalued field design, which
violates database normalization guidelines. If you later need to add an
option, or remove an option, you will need to make a design change to the
table, and to any associated objects (queries, forms, reports, etc.) that use
this data. A good rule of thumb to remember is the following:

Fields are expensive; Records are cheap.

Your design should be such that it allows you to add and remove options
simply by adding or deleting data (records) from the database. It appears as
if your current design is correct. Here is a link that you can investigate
that provides lots of database normalization articles:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

A Crosstab query will do this.

True enough, as long as the user is happy with a read-only recordset.

Crosstab Queries
http://www.access.qbuilt.com/html/crosstab_queries.html

But, I do not recommend changing the design of a table to show repeating
fields.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[jddealer]

the reason I need this is that the first design is the database. the other
structure is a thrid party software for quoting equipment that I want to be
able to populate weekly as costs and inventory change in the original
database. it looks like cross tab query just counts or sums number in the
value. I just want to put the data in fields starting in option1 to optionx,
and the values will be text, not numbers....will it still work.

 

[John W. Vinson]

the reason I need this is that the first design is the database. the other
structure is a thrid party software for quoting equipment that I want to be
able to populate weekly as costs and inventory change in the original
database. it looks like cross tab query just counts or sums number in the
value. I just want to put the data in fields starting in option1 to optionx,
and the values will be text, not numbers....will it still work.

Yes, it should. You can Export from a query just as easily as you can from a
table.

I'd suggest storing the data in normalized tables; creating a Crosstab query
to lay out the data in the manner that your external program requires; and
exporting that query.

John W. Vinson [MVP]