Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Field names in results of query (Access 2002)

    Greetings!

    I know I've seen this posted somewhere before, so basically what I'm looking for is someone to point me in the right direction.
    What I'd like to do is take this aggregate query/table:

    <table border=1><td>Office</td><td>Sales Count</td><td>Sales Volume</td><td>1</td><td>55</td><td>100,000</td><td>2</td><td>23</td><td>200,000</td><td>3</td><td>77</td><td>50,000</td><td>4</td><td>6</td><td>100,000</td></table>

    and use the names of the fields in the query results:
    <table border=1><td colspan=4>Offices</td><td></td><td>1</td><td>2</td><td>3</td><td>Sales Count</td><td>55</td><td>23</td><td>77</td><td>Sales Volume</td><td>100,000</td><td>200,000</td><td>50,000</td></table>

    I am able to achieve the 2nd table if I use a union query and make a gazillion calls to the same table to get the results, but I know I've seen an easier way somewhere before. Or perhaps I'm making this exercise much more complicated than it should be?

    If anyone's seen this before, or can suggest an easier way to do it, it would be SO much appreciated!

    TIA!

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field names in results of query (Access 2002)

    I'm not quite sure what you're trying to do here but the idea of a pivot table comes to mind. I was able to emulate your desired result by creating a pivot table form. Office number was the column field and volume and sales count were the data fields.

    HTH.
    Carol W.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Field names in results of query (Access 2002)

    Hi Cecilia,

    You have probably seen something about crosstab queries, but that is not quite the same as what you are asking here. In a crosstab query, the values of one or more fields are grouped in the column(s) on the left, and the values of one field are displayed as column headers. Aggregated values of yet another field (count, sum, average, ...) are displayed in the "interior".

    For example, an aggregate table

    <table border=1><td align=center>Region</td><td align=center>Product</td><td align=center>SumOfSales</td><td>East</td><td>Gizmo</td><td align=right>2</td><td>East</td><td>Widget</td><td align=right>6</td><td>North</td><td>Gizmo</td><td align=right>7</td><td>North</td><td>Widget</td><td align=right>5</td><td>South</td><td>Gizmo</td><td align=right>8</td><td>South</td><td>Widget</td><td align=right>4</td><td>West</td><td>Gizmo</td><td align=right>1</td><td>West</td><td>Widget</td><td align=right>3</td></table>
    would become the following crosstab table

    <table border=1><td align=center>Region</td><td align=center>Gizmo</td><td align=center>Widget</td><td>East</td><td align=right>2</td><td align=right>6</td><td>North</td><td align=right>7</td><td align=right>5</td><td>South</td><td align=right>8</td><td align=right>4</td><td>West</td><td align=right>1</td><td align=right>3</td></table>
    But your situation is slightly different. You would need two crosstab queries (one for Sales Count vs Office and one for Sales Volume vs Office) and a union query to display your example. The same result is easier to obtain in Excel - there, you can transpose a table easily (i.e. exchange rows and columns).

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Field names in results of query (Access 2002)

    Cecilia,

    Similar to what Hans said. You could use a union query such as the following to set up the data before doing the crosstab:

    SELECT Table1.OFFICE, "SalesCount" AS SALES, Table1.SALESCOUNT AS Measure
    FROM Table1
    UNION SELECT Table1.OFFICE, "SalesVolume" AS SALES, Table1.SALESVOLUME AS Meaasure
    FROM Table1;


    This essentially gives you the following:

    <pre>OFFICE SALES Measure
    1 SalesCount 55
    1 SalesVolume 100000
    2 SalesCount 23
    2 SalesVolume 200000
    3 SalesCount 77
    3 SalesVolume 50000
    4 SalesCount 6
    4 SalesVolume 100000

    </pre>



    You can then use the crosstab query to get your results. Base the crosstab query on the union query. Set Sales as the Heading. Office as the Column. Calculated Field as Measure set as Sum to get the following:

    <pre>SALES Total Of Meaasure 1 2 3 4
    SalesCount 161 55 23 77 6
    SalesVolume 450000 100000 200000 50000 100000

    </pre>



    Remove the total of measure if necessary. Sorry if the formatting is ugly....

    HTH
    Regards,

    Gary
    (It's been a while!)

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field names in results of query (Access 2002)

    Hi Hans,

    That's the way I'm doing it now, for most of the subreports. Then I get to a subreport that requires eight unions; it's way too complicated, and display is even trickier. At the end, I have to combine the results of three of these 8-union queries. Ick. When I produce the query that I showed at the top, I can do that in two steps, no unions, much simpler. Too bad I can't "de-crosstab" it.

    There is an issue with another one that ends up not working with a crosstab at all, so I was hoping if I could find the source that I had read (moons ago), I would be able to solve that problem with the same type of solution.

    Anyway, I guess it's not horribly important, because it *does* work with the icky unions. <img src=/S/bouncenburn.gif border=0 alt=bouncenburn width=31 height=31>

    Thanks,

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Field names in results of query (Access 2002)

    You might also ask why it's absolutely necessary to present the data in a layout that is not "natural" to Access. "It was always done that way"?

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field names in results of query (Access 2002)

    Because they think that anything you write can come out of a pc. Seriously, one bean counter came at me today saying, can't you at least print the X Region? You've been working on it for long enough!

    <img src=/S/boxedin.gif border=0 alt=boxedin width=25 height=20>

    This is the same guy who had the nerve to point out to me that a developer is worth 15% less than a PM--after we discussed my 14 hour workday schedule. <img src=/S/aflame.gif border=0 alt=aflame width=16 height=16>

    Okay, I got that out of my system. Remember, I work for the gov't. NOTHING makes sense. <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field names in results of query (Access 2002)

    Actually, that is spot on! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Unfortunately, it requires a temp table, which I'm desperately trying to avoid. I really thought there was a way around that, but I guess not. <img src=/S/broke.gif border=0 alt=broke width=31 height=23>

    Thank you so much for pointing this KB article out. I know it will come in handy in the future! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

  9. #9
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Field names in results of query (Access 2002)

    (Edited by HansV to make url clickable - see <!help=19>Help 19<!/help>)

    What about...
    http://support.microsoft.com/default.aspx?...kb;EN-US;283875
    HOW TO: Transpose Data in a Table or Query in Access 2002 (also available for Access 2000 & 97)?
    Assumption: you have no more than 255 records because the maximum number of fields in a Microsoft Access table is 255.
    The second method (VBA function) seems the most valuable, but seems a big <img src=/S/pinkelefant.gif border=0 alt=pinkelefant width=20 height=20> to kill <img src=/S/mice.gif border=0 alt=mice width=50 height=25>*...
    Still I hope it helps.
    (thanks to google + search string "transpose table Access"!)

    * board: did you ever consider yet a smily expressing a fly? Might be useful in some expressions...

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Field names in results of query (Access 2002)

    This example is literally covered by Gary's <post#=246664>post 246664</post#> which does not require a temporary table.

    Pat

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Field names in results of query (Access 2002)

    Hi Patt,

    My earlier response is that Gary's way is the way I'm currently doing it. What I'm trying to do is reverse engineer the process so that I can use a simpler query without unions and such.

    Thanks,

Posting Permissions

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