Field names in results of query (Access 2002)

Topic

New Reply

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:

Office	Sales Count	Sales Volume
1	55	100,000
2	23	200,000
3	77	50,000
4	6	100,000

and use the names of the fields in the query results:

Offices
	1	2	3
Sales Count	55	23	77
Sales Volume	100,000	200,000	50,000

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!

Reply | Quote

Replies

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.

Reply | Quote

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

Region	Product	SumOfSales
East	Gizmo	2
East	Widget	6
North	Gizmo	7
North	Widget	5
South	Gizmo	8
South	Widget	4
West	Gizmo	1
West	Widget	3

would become the following crosstab table

Region	Gizmo	Widget
East	2	6
North	7	5
South	8	4
West	1	3

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).

Reply | Quote

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.

Thanks,

Reply | Quote

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”?

Reply | Quote

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!

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.

Okay, I got that out of my system. Remember, I work for the gov’t. NOTHING makes sense.

Reply | Quote

(Edited by HansV to make url clickable – see Help 19)

What about…
http://support.microsoft.com/default.aspx?…kb;EN-US;283875%5B/url%5D
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 to kill *…
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…

Reply | Quote

Actually, that is spot on!

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.

Thank you so much for pointing this KB article out. I know it will come in handy in the future!

Reply | Quote

This example is literally covered by Gary’s post 246664 which does not require a temporary table.

Pat

Reply | Quote

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,

Reply | Quote

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:

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

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:

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

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

HTH

Reply | Quote