Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    12

    Red face Unanswered: Access query problem to make multi row table fom multi column table

    This one is a pain.
    the data will be exported to somewhere else that needs it in the format I am describing in the problem.

    I describe the problem as turning the database on its side.


    It is a little difficult to explain, so I am describing in table style.

    I have table with columns
    Part number, Fruit type, fruit color, fruit shape, msrp, flavor ... not all of which will contain data for a given part number.
    Part number is key field no duplicates


    I need the query to make a table in the form below
    new table column titles are Part Number and Field name and Field data


    example output table
    Part Number Field name Field data
    A223 Fruit type Apple
    A223 Fruit shape Round
    A223 msrp 23.33
    Dda3 Fruit type Grape
    Dda3 Fruit shape Round
    Dda3 Flavor Tart
    B23a msrp 12.96


    Is Access even of creating such a table from another table’s data?

    Jeff

  2. #2
    Join Date
    Jul 2009
    Posts
    9
    Is Access even of creating such a table from another table’s data?
    Yes you can create the table you describe using access. The good news is it not to difficult. There are several steps.
    1. Create a query with the part number and the fruit type
    2. In the criteria type “Is Not Null” without the quotes under the fruit type
    3. Create another query with the part number and the shape
    4. In the criteria type “Is Not Null” without the quotes under the shape
    5. Do this for as many time as necessary to include all of the properties you are looking for
    6. Now open another query close the show tables
    7. Click on SQL
    8. Open the first query you created and
    9. click on SQL View. Copy the code and past it in the new query and remove the ‘;” from the end of the SQL press enter and type UNION
    10. Open the second query you created and repeat starting at 8. When you are finished save the query
    11. Run the query to see if it has everything you needed
    12. Open another query and using the query from 10 select the two filed change the query type to make table name it and run the query

  3. #3
    Join Date
    Jul 2009
    Posts
    12
    thanks for the information. I will try it shortly

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •