moving data from column to rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
Click to expand...

A Crosstab query will do this.

John W. Vinson [MVP]
 
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
__________________________________________
 
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.
 
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.
Click to expand...

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]
 
Back
Top