Crosstab Query Sorting 1

[zevw]

I want the results to be sorted by TempCnt.TaxDedID but not to be displayed. How can I write that in my statement?

TRANSFORM FormatNumber(Sum([TempCnt].[Amount]),2) AS SumOfAmount 
SELECT tblSysCoDepartment.DeptDesc
FROM ((tblEmployee INNER JOIN tblPRItemHist ON tblEmployee.EmployeeID = tblPRItemHist.EmployeeID) INNER JOIN tblSysCoDepartment ON tblEmployee.DepartmentID = tblSysCoDepartment.CoDeptID) INNER JOIN TempCnt ON tblPRItemHist.ItemID = TempCnt.ItemID
GROUP BY tblSysCoDepartment.DeptDesc
PIVOT TempCnt.TmpName;

Thanks

 

[dhookom]

Try:

TRANSFORM FormatNumber(Sum([TempCnt].[Amount]),2) AS SumOfAmount 
SELECT tblSysCoDepartment.DeptDesc
FROM ((tblEmployee INNER JOIN tblPRItemHist ON tblEmployee.EmployeeID = tblPRItemHist.EmployeeID) INNER JOIN tblSysCoDepartment ON tblEmployee.DepartmentID = tblSysCoDepartment.CoDeptID) INNER JOIN TempCnt ON tblPRItemHist.ItemID = TempCnt.ItemID
GROUP BY tblSysCoDepartment.DeptDesc,TempCnt.TaxDedID 
ORDER BY TempCnt.TaxDedID 
PIVOT TempCnt.TmpName;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[zevw]

Thanks!

I tried your solution.

Well this groups each department and seperately each TaxDedID. (the departments are displayed muliple times)

I want each department (rows) displayed once and the header columns sorted by the TaxDedID.

 

[dhookom]

I'm lost regarding what you want display and/or sort by. You mention TaxDedID as the columns sort.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[zevw]

I want the rows to be department (tblSysCoDepartment.DeptDesc)

The Tax Type to be the header (but I don't want the columns to display alphabetically (tblSysCoDepartment.DeptDesc), rather by its id (TempCnt.TaxDedID) which is the sorting order. Here is what I don't know how to do, sort it by the TaxDedID but don't display it.

This is the way it is displayed now:

[tt]DeptDesc 401KCont CTLILLIN fc2
Housekeep. 5,709.07 1,837.00 2,081.04
Maintenan. 2,688.91 888.00
Office 4,872.24 1,608.00
Service 5,182.75 924.00[/tt]

I want 401K Last

 

[dhookom]

You can add the DeptDesc values in the desired order into the Column Headings property of the crosstab query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[zevw]

Duane!

Thanks for taking the time to respond. To be very honest I do not have much experience with crosstab Queries, never really needed them.

I see what you are saying about the property of Column Headings, but what would I enter on that line.

Secondly, what happens if I will get different column headings how would that be sorted. Can I specify in the property anything and even though it is not there, and it will be sorted accordingly?

Thanks again

William

 

[dhookom]

You would enter something like:
[tt][blue]Column Headings: "fc2","401KCont","CTLILLIN"[/blue][/tt]

Enter all possible column headings. Any value you leave out will not show. Any value you enter that doesn't generate data will display the column with null values.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[zevw]

Sorry I am bothering you so much, Is there a way how to do this in code.

The reason is simple I do not know today, what all the header fields will be (various different tax types might come up). Can I run a query and then place the FieldNames in then Column Headings property. If so can I ask how that can be done?

 

[dhookom]

There is a sample crosstab report mdb at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

This solution dynamically creates the column headings on the fly based on your data.

Another solution would be to use DAO to modify the SQL property of your saved crosstab query based on your data. Something like:

  Dim strSQL as String
  strSQL = "TRANSFORM FormatNumber(Sum.."
  Currentdb.QueryDefs("qxtbName").SQL = strSQL

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[zevw]

I was not clear in my previous post.

I know how to create queries with code.

What I want to know is, can I with code of insert the sorting order I want, in the column heading property.

for example: I have the results FIT, FICA, SIT

I want to insert with code in the column heading property the following string:

"FIT";"SIT";"FICA"

 

[zevw]

Sorry I just realized the In Clause

I guess I can run a query and sort it based on the TaxDedID. I will set a recordset based on the query, move through each record and create a string (called sortstring) and concatenate the name of each field based on that sort order. I will then add the string as variable to the end of this query

Something like this

TRANSFORM FormatNumber(Sum([TempCnt].[Amount]),2) AS SumOfAmount 
SELECT tblSysCoDepartment.DeptDesc
FROM ((tblEmployee INNER JOIN tblPRItemHist ON tblEmployee.EmployeeID = tblPRItemHist.EmployeeID) INNER JOIN tblSysCoDepartment ON tblEmployee.DepartmentID = tblSysCoDepartment.CoDeptID) INNER JOIN TempCnt ON tblPRItemHist.ItemID = TempCnt.ItemID
GROUP BY tblSysCoDepartment.DeptDesc
PIVOT TempCnt.TmpName [COLOR=red] In " & SortString & [/color]";"

 

[zevw]

Duane!

Thanks for all your help, I downloaded you crosstab DB and WO!!! that is a piece of art. I liked the way you wrote it in the explanation "If you are more confused now!!" me to....

But I need that piece of work, and I will work it through.

Thanks, I think it will get my report finished earlier.