Crosstab Query - Row Totals - Positioning??

[kjv1611]

Access 2003 on Windows XP Pro

This seems strange to me, but I've not done much with Crosstab queries to date, so I probably am just ignorant to something.

I created a crosstab query - returned the correct results, no problem.

The item of interest is not necessarily a problem, but it has me stumped nonetheless.

I notice that when I include a Totals colum (To total all the entries for each row), that no matter where I put it in design view, it ends up at the beginning of the list of columns, whereas I would rather it be at the end. Is there some option that I can set to correct that?

If this is too vague, let me know. I am using one field for the row headers, one field for the column headers, and one additional column header, which is the totals.

And just in case this seems a little vague or confusing, I'll make up an example layout to demonstrate:

Let's say I'm looking at Fruit found in various states. For the example, I am not trying to be accurate, just providing an example.

My output is looking like this:

[b]State  Total  Apple  Orange   Bananna   Grape[/b]
Texas    3       2                          1
Ohio     5       1        3         1
Nevada   2                          1       1

But, I would prefer it to look like this:

[b]State  Apple  Orange   Bananna   Grape  Total[/b]
Texas    2                          1      3
Ohio     1        3         1              5
Nevada                      1       1      2

Any suggestions, or is this just not possible in Access 2003?

Thanks in advance.

 

[PHV]

As the final number of columns is unknown at design time the Total column is always the first one after the row headers, period.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kjv1611]

So, I guess my assumption was correct that there is no way to move that to the end? In that case, the only "work around" would be to create a table based on the cross-tab query?

 

[AlexCuse]

Hey KJV -

Can you post the SQL view for your query? I have an idea that may help you out.

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[kjv1611]

Here's the SQL - it's a very small one:

TRANSFORM Count(tblData.[Record ID]) AS [CountOf Record ID]
SELECT tblData.State, Count(tblData.[Record ID]) AS [Total Of Record ID]
FROM tblData
GROUP BY tblData.State
PIVOT tblData.[Fruit Type];

Thanks!

 

[AlexCuse]

Hm. I could've sworn I've worked on one like this before, but I just checked and what mine did was include a sum on the bottom.

The best thing I can think of for you is to build a select query off of your crosstab that will place the total at the end, but this gets hairy if a new fruit type is added.

What I would do is this:

1. Programmatically determine fruit types in your table.
2. Use VBA to construct a SQL String that selects from your crosstab query in the desired order(select state, fruit1, fruit2, total from CrosstabQuery)
3. Use querydefs to assign this SQL to an existing query, then open it. I have code for this piece (of course I know the easy part)

Application.CurrentDb.QueryDefs("Query1").SQL = "select * from table where somefield = something"

DoCmd.OpenQuery "Query1"

Of course, if you have static number of fruit types, you can skip all this and just set up your query.

If you have any other questions, I will be back Monday, and I'm sure others will be able to help if you need it before then.

Have a great Thanksgiving, and I hope all continues to go well with your new 'handful'.

Alex



A wise man once said
"The only thing normal about database guys is their tables".

 

[kjv1611]

Thanks, AlexCuse. That does sound like it would work well for what I am doing. My end goal with it, is actually to do it all in code, as I want it to be able to be done sort of ad-hoc. Basically, it can be created based on options selected from a user form in Access. I'll see if I can give it a try today or tomorrow, and post back my results.

 

[AlexCuse]

Creating your 'display' query from user selected options should be easier, because you won't need to bring a recordset or looping into the picture. Let me know how it works out.

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[SkipVought]

Regarding TOTALS at the "END"

Used to be, in the days of paper, pencil and adding machine, that the TOTALS were put at the BOTTOM/RIGHT of a series of numbers and called, "THE BOTTOM LINE," because that's how it was done, then.

Now, talking from a REPORT perspective, you can put the totals, pretty near, anywhere.

So WHY force the poor user to SEARCH for the TOTALS, when THE BOTTOM LINE, can be UP IN THE FRONT ROW for everyone to see right off, instead of having to HUNT for the bottom most row or right most column???

Skip,

_[red][/red]

 

[kjv1611]

Skip,

Thanks for the thought.

I was only going by the template given me for the particular project. One of my coworkers was doing all of manual work, and that was the basic outcome of it, so I thought it would be best to have the same formatting, so as to cause less confusion. I can really see it either way. I realize that if you've got 14 different columns, it'd be nicer to see the total first. I'll keep that in mind, as well, while working on this, so that if any of them appear to be getting very long, then I'll suggest they have the total on the left, rather than the right.