Sort On Results Of Crosstab Query

Jan 16, 2007

Below is the code to my crosstab query. I want to sort
descending) on the GrandTotal. I can apply a sort on the
results of the query but cannot sort in the query. It is
my understanding that this is due to this being a crosstab
query. Has anyone any advice on how I can somehow
automatically descend sort on the GrandTotal Column?
PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 ), [Forms]![Queries_ReportsFRM].[SystemGroupProblem] Text ( 255 );
TRANSFORM Val(Nz(Sum([Totals]),0)) AS SumOfTotals
SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal
FROM [Trends-1-3TON-WEEK]
GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem
PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;

View Replies


ADVERTISEMENT

Sort On Total Field In CrossTab Query

Jan 11, 2007

Below is the sql code I have for a CrossTab Query. Total as GrandTotal
shows GrandTotal of all weekly columns. Is there anyway I can get the
Query Display to sort on the GrandTotal Column?

PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 );
TRANSFORM Sum([Trends-1-3TON-WEEK].Totals) AS SumOfTotals1
SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal
FROM [Trends-1-3TON-WEEK]
GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory
ORDER BY Sum([Trends-1-3TON-WEEK].Totals)
PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;

View 1 Replies View Related

Queries :: Sort Row Headings For Crosstab Query

Feb 26, 2015

I have a created a crosstab query which gives me the results I need, but I want to sort the row headings differently. These are not numbers, but machine sizes which range from 4 Metre to 20 Metre. Currently, the crosstab gives me:

10 Metre
12 Metre
14 Metre
17 Metre
20 Metre
3 Metre
4 Metre
5 Metre etc

But I need to show it as:

3 Metre
4 Metre
5 Metre
10 Metre
12 Metre
14 Metre
17 Metre etc.

The field is short text data type and data comes from an ODBC linked table to SQL server table, and I am using Access 2013.

View 4 Replies View Related

Queries :: Sort Crosstab Query Columns And Generate Report

Jul 28, 2015

This is a query, report and vba question. I'm using Ms Access 2007.

TABLE 1: projectname, activityname, totalhoursworked, employeename
TABLE 2: employeename, employeelevel
TABLE 3: employeelevel, rate

I created a select query to join the info that I need.

SELECT QUERY 1: projectname, activityname, employeename, totalhoursworked, rate, cost (calculated field (totalhoursworked*rate))

I have 2 crosstab queries.

CROSSTAB QRY 1: ROW (projectname, activityname) COLUMN (employeename) VALUE (totalhoursworked (summed))
CROSSTAB QRY 2: ROW (projectname, activityname) COLUMN (employeename) VALUE (cost (summed))

I then created a 2nd select query with inner joins to join both crosstab queries on similar fields (activity & projectname).

SELECT QUERY 2: projectname, activityname, employeename (totalhoursworked as value), employeename (calculatedcost as value)

It gives me this:

However, I want it like this:

Those employeename... refers to more employees being added after a period of time. Hence I want to know if I could use vba to generate a report every time a button is pressed on a form? I know how to link the form to the query.

View 8 Replies View Related

Top 10 Results From CrossTab Query.

Jan 11, 2007

Below is the code I have for a crosstab query. This shows me totals for System Groups by Month. The columns are months. This also gives me a grand total accross all of the months. What I would like to do is get a Top Ten based on the GrandTotal. Any suggestions on how to accomplish this? Thanks for all help.


PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 );
TRANSFORM Sum([Trends-1-3TON-MONTH].Totals) AS SumOfTotals
SELECT [Trends-1-3TON-MONTH].SystemGroup, [Trends-1-3TON-MONTH].FaultCategory, Sum([Trends-1-3TON-MONTH].Totals) AS GrandTotal
FROM [Trends-1-3TON-MONTH]
GROUP BY [Trends-1-3TON-MONTH].SystemGroup, [Trends-1-3TON-MONTH].FaultCategory
PIVOT [Trends-1-3TON-MONTH].YearMonth;

View 2 Replies View Related

Sort Query Results

Feb 27, 2008

The following is produced by a query i've built....

4a (2007 Jan - Jun)
5a (2008 Jan - Jun)
4b (2007 Jul - Dec
5b (2008 Jul - Dec)

I need it in numerical order not alphabetical so it looks like this:

4a (2007 Jan - Jun)
4b (2007 Jul - Dec
5a (2008 Jan - Jun)
5b (2008 Jul - Dec)

Any ideas

View 5 Replies View Related

Saving Crosstab Query Results??

Jun 23, 2005

Does anyone know how I can save the results of my crosstab query into a new table (like a Make Table query does)?

Any help would be greatly appreciated!

View 2 Replies View Related

Filtering Crosstab Query And Display Results

Dec 19, 2014

I have a crosstab query. There is a field for row headings that specifies QUARTER (Q1,Q2, Q3,Q4) and another field for row headings that specifies YEAR(2015,2016,...etc)

I want to be able to have the user input the criteria in to a box and filter the crosstab query to then display the filtered results.

Right now on my column headings I have MONTH.

When I try to reference the form in the criteria, I always get the error

"The Microsoft Database Engine does not recognize ..... as a valid field name or expression"

Does this mean that you can not filter crosstab queries?

View 5 Replies View Related

Forms :: How To Sort Query Results With A Form

Aug 14, 2013

Essentially I have a table for rooms around my school and what the rooms contain. Most of the details for the rooms are Boolean (e.g. If the room has a projector, Yes/No).

So what I am trying to do is have a form where i can select a checkbox(s) and if i tick one, a query that holds all the room details will only show rooms with projectors in them, which i can then display those results in another form.

My question is how do I get the check boxes in the form to narrow down the room results to only show the ones with the criteria i have selected in the form?

View 1 Replies View Related

Sort Query Results By Calendar Month

Jun 12, 2013

I'm trying to sort my query results by the month value "Data (IMPORT).KP_MM_DS" but I get alphabetical results.

SELECT [Data (IMPORT)].KP_YR_NB, [Data (IMPORT)].KP_MM_DS, Round((Count([Data (IMPORT)].KP_ISSU_ID_NB)/20)) AS CountOfKP_ISSU_ID_NB, [Data (IMPORT)].KP_MM_DS
FROM ([Data (IMPORT)] LEFT JOIN [Facility Lookup] ON [Data (IMPORT)].KP_FAC_NM = [Facility Lookup].[FAC NAME]) LEFT JOIN DEPTLOOKUP ON [Data (IMPORT)].KP_DEPT_ORG_NM = DEPTLOOKUP.[EPM DEPARTMENT NAME]
WHERE ((([Facility Lookup].[REPORTING MCA])<>"XXX") AND (([Data (IMPORT)].KP_SB_RGN_CD)="N"))
GROUP BY [Data (IMPORT)].KP_YR_NB, [Data (IMPORT)].KP_MM_DS, [Data (IMPORT)].KP_MM_DS;

View 4 Replies View Related

General :: Comment History Field To Appear On A Form - Change Sort Order Of Results

Aug 28, 2013

I have after much stress gotten my comments history field to appear on a form.

The following code gives me what I needed

Code:
=ColumnHistory([RecordSource],"LastUpdateBy","[ID]=" & Nz([ID],0))

I have only one simple thing left, that is perplexing to say the least.

Some comment fields will be many short notes, spread over time.

My desire is to change the sort order of the results so the most recent (rather than the initial) comment is at the top.

View 5 Replies View Related

Crosstab Query Based On Crosstab??

Sep 21, 2007

Hi all, I am utterly unsure if what I want to do is even possible:

I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up).

There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa.

I want to make another crosstab query which performs a calculation. To keep it simple:

If (RewCOCredit>WrapCOCredit) Then
5-RewCOCredit
Else 'WrapCOCredit>RewCOCredit
5-WrapCOCredit

Please help!!!

View 2 Replies View Related

Report Sort Different From Query Sort

Mar 3, 2006

i made a report based on a query. when i run the query, my results are sorted by Job Time Ascending, like i want...
when i made my report i chose job id as the main group priority in the wizard..

now in my report it displays the jobs in ID ascending, rather than Job time ascending as my query says..

i dont want to re-do the report it took ages.. how can i fix this?

View 3 Replies View Related

Report Sort Is Not The Same As Query Sort...

Jan 9, 2005

I have a query that sorts players batting averages from highest to lowest. When I run the query just by itself it sorts it right however when I open the report based on that query it does not sort in descending order like I asked. What am I missing??

View 3 Replies View Related

Forms :: Applying Sort And Filter In Form Results In Design Changes To Form

Dec 27, 2014

When I'm applying a sort and filter in a form, Access is updating the Filter and Order By properties of the form, so that it is possible to re-use in conjunction with Filter on Load and Order By on Load properties. However, a consequence of this is that when the form is closed, it prompts the user whether they want to save the design of the form. I want to circumvent this as I don't want to re-use the sort and filter and I don't want to be prompted to save the design of the form.

Although I can circumvent this by closing the form using a method that doesn't prompt for saving, the additional complication here is that the form in question is in the Navigation subform of a Navigation Control. Hence when I click on a another Navigation button, it (not me) closes my current form and hence prompts me whether I want to save the design of the form (if I have been sorting and/or filtering). I can't see how to circumvent this and the prompting is resulting in unacceptable usability.

View 2 Replies View Related

Blank Fields In Crosstab Query Based On Previous Query

May 31, 2006

:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....

First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.

When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)

The SQL was written by Access not by me. :)

Here is an example of the Crosstab SQL (which is using a previous query):

TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;

-----------
qryTest2 SQL: (Grouping by to remove dups)

SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;

Thanks for you time! :)

View 1 Replies View Related

Queries :: Query Error When Data Changes In Crosstab Source Query

Aug 12, 2014

I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.

I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .

My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.

View 9 Replies View Related

Crosstab Query

Aug 17, 2005

Hello All,
Your Help Required. I have send you a Database, in which I have used Crosstab query, I just want when I select the report from switchboard, and enter the datefrom / dateto (fields names) than click the preview report. Required report is open.

I have faced following problems
1-When I have selected the report and click the preview button. Report is not opened.
2-I have used cross tab query and link with the switchboard. But when I have run the query this msg is appeared “Microsoft Jet engine Does not recognized these field(name)”

Thanx
ami

View 2 Replies View Related

Crosstab Query

Sep 23, 2005

I am am wondering if it is possible to create a crosstab query that displays alphanumeric values and not numeric (computational) values.

Ex:
Table contains the following details:

Name Branch
Bob 111
Bob 222
Joe 333
Pam 444


I want to use a crosstab so I can view the data as follows:

Name Branch1 Branch2
Bob 111 222
Joe 333
Pam 444


Is this possible? I've been playing around with it and it doesnt look doable. Perhaps there is a better way of getting the data into this format? Any suggestions would be greatly appreciated!

Super Thanx.

O.

View 5 Replies View Related

Crosstab Query

Oct 2, 2005

Hi all

I'm trying to create a crosstab query with criteria that refers to a combo box on a form to allow me to filter data before the query is created. However when I refer to the combo box (e.g. = forms!frmSite!CmbSiteName) I am told that access does not recognise this as a 'valid field name or expression'. With a normal select query I dont have the same problem. Is there a way around this?

Cheers

Rhys

View 4 Replies View Related

Crosstab Query

Oct 4, 2005

Hi, I have a crosstab query I have set up, calculating the cost of a product per month

it should look like
1 2 3 4
gek22 £55 £66 £77 £88
er44 £99 $100 £101 £102
tt66 £103 £104 £105 £106


but instead it looks like

1 2 3 4
gek22 £55 £88
gek22 £66
gek22 £77
er44 £99
er44 $100 £102
er44 £101
tt66 £103 £106
tt66 £103
tt66 £104 £105

Admittedly this is my first time doing a crosstab query, but I have both my column and rowe headers set to group by, I kinda assumed this would group them in much the same way a pivot table does in excel

Any help on this would be great

Hmm, doesnt want to keep the spacing in it :( hopefully you can get the idea

View 3 Replies View Related

Crosstab Query

Jul 6, 2006

I have a report based on a crosstab query and the data changes every time the report is run even though the data is not updated. Can anyone shed some light?

View 1 Replies View Related

Crosstab Query?

Jan 25, 2007

I have a table with the following info:

Date/shift/name
1-1-07/1/tom
1-1/07/1/Bill
1-1-07/2/frank
1-1-07/2/Ben
1-1-07/3/Scott
1-1-07/3/Terry
1-2-07/1/tom
1-2/07/1/Bill
1-2-07/2/frank
1-2-07/2/Ben
1-2-07/3/Scott
1-2-07/3/Terry
1-3-07/1/tom
1-3/07/1/Bill
1-3-07/2/frank
1-3-07/2/Ben
1-3-07/3/Scott
1-3-07/3/Terry

What I want is the date to be displayed horizontally and the the names vertically with the shift as the intersection:

1-1-07 1-2-07 1-3-07
Tom 1 1 1
Bill 1 1 1
Frank 2 2 2
Ben 2 2 2
Scott 3 3 3
Terry 3 3 3

is this possible with a crosstab query or do I have to go about doing it some other way? If a crosstab query is possible how do you go about doing it?

Any help would be greatly appreciated.

View 3 Replies View Related

Crosstab Query

May 30, 2007

I need help with a crosstab query ( :eek: ). I would like the column headings to be the last 6 months, the row headings to be billers, and the data in the middle to be both the date that a payment was made (falling within the month headings) and also the amount paid in that payment. Sometimes there might be more than one payment to a biller in a month or there might be no payments to that biller in a month. All of the raw data needed is in one table. I have read about crosstab queries, tried to use the wizard, and looked at examples but I can't get it to work:confused: . Could someone show me how this should be written? :)

Something like this is what I need:

_______________Jan______________Feb_____________Ma r__________Apr

Power............1/3....200..............2/4...250...........3/7....225........4/5.....250

Lease............1/15...1200..........2/15...1200..........3/16..1200.......4/20...1200

Insurance.......1/16...175.............2/20...175...................0.....................0
.............................................. 2/26...350

Water/Gas.......1/6...150.............2/10...175...........3/3...150.........4/10...175

Thanks a lot...

View 10 Replies View Related

Crosstab Query Help

Jun 29, 2007

Hi,

I need a query which displays data as a crosstab query would but gives me the capability to edit data in the query. Basicaly, I have a Resource column, a Month column, and an Allocation column (among others). I need the months to appear in columns and the allocations to be summed by month. (Months are never repeated though, so it's not really a sum...) Can anyone help me out on this?

Thanks!

View 1 Replies View Related

Crosstab Query

Aug 16, 2007

My crosstab query doesn't take the textbox value from the form. It says it doesnt recognize [forms]![frmMain]![txtEndDate] as a valid field name or expression. How can I solve this?

TRANSFORM Sum(tblDowntimeHrs.downtimeHrs) AS downtimeHrsOfSum
SELECT tblEquipmentType.Equipment_Type
FROM tblEquipmentType INNER JOIN (tblCategory INNER JOIN tblDowntimeHrs ON tblCategory.category = tblDowntimeHrs.category) ON tblEquipmentType.type = tblDowntimeHrs.type
WHERE (((tblDowntimeHrs.date)>=[Forms]![frmMain]![txtStartDate] And (tblDowntimeHrs.date)<=[forms]![frmMain]![txtEndDate]))
GROUP BY tblEquipmentType.Equipment_Type
PIVOT tblCategory.category_description;

in my select query with the same table, it works fine:

SELECT tblEquipmentType.Equipment_Type, tblCategory.category_description, Sum(tblDowntimeHrs.downtimeHrs) AS downtimeHrsOfSum
FROM tblEquipmentType INNER JOIN (tblCategory INNER JOIN tblDowntimeHrs ON tblCategory.category = tblDowntimeHrs.category) ON tblEquipmentType.type = tblDowntimeHrs.type
WHERE (((tblDowntimeHrs.date)>=[Forms]![frmMain]![txtStartDate] And (tblDowntimeHrs.date)<=[forms]![frmMain]![txtEndDate]))
GROUP BY tblEquipmentType.Equipment_Type, tblCategory.category_description;

thanks.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved