Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    1

    Unanswered: Working backwards from a crosstab query

    I ran a crosstab query with Species names as the column headings, Sample # as the lefthand column ("row" headings), and counts as the filled in data. I copied this table into Excel and populated all empty squares with zeroes. Is there a way to take this populated table and turn it into a table with three columns: Species, Sample #, and Count?
    Zeb

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Run a Make Table over the crosstab table
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by schobze
    I ran a crosstab query with Species names as the column headings, Sample # as the lefthand column ("row" headings), and counts as the filled in data. I copied this table into Excel and populated all empty squares with zeroes. Is there a way to take this populated table and turn it into a table with three columns: Species, Sample #, and Count?
    Zeb
    Why did you export to Exel in the first place ? What are you trying to do overall?

    I assume you can import your crosstab table back into Access. To get this back into three columns you need to write a series of queries (one for each species) that effectively strip out each column. Then put all the queries together into a Union query to effectively stack all the data together.

    So suppose you have a species called Dog i.e. one of your headers is Dog, then the following query will create the three column data for just that species:
    SELECT SampleNo, "Dog" AS Species, Dog AS SampleCount
    FROM myCrossTable;

    Note: I've avoided using the hash (#) symbol because characters like this can cause problems. Similarly I've avoided using Count as a column name as Count is a key word.

    If you have say 6 species then you will have 6 queries similar to the above. The final union query will look like:

    SELECT SampleNo, "Dog" AS Species, Dog AS SampleCount
    FROM myCrossTable
    Union
    SELECT SampleNo, "Cat" AS Species, Cat AS SampleCount
    FROM myCrossTable
    Union
    SELECT SampleNo, "Fish" AS Species, Fish AS SampleCount
    FROM myCrossTable
    .
    .
    etc

    You can then use this query as the source for a make-table query to create your table.

    hth
    chris

Posting Permissions

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