MS ACCESS :: Sorting Crosstab Query

Crosstab Query Based On Crosstab??
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 Replies !

Blank Fields In Crosstab Query Based On Previous Query
: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 Replies !

Help With Sorting A Query?
I have a job database where I sort jobs by upcoming, inactive, closed and active. Each has a corresponding number 1-4 respectively. The main table I update the jobs in is the JOB TABLE. I print a weekly report for our weekly meeting but I only want the active jobs in the report. My question is: how do I perform a sort function in the query, so I will have only the active jobs in the database table show in the query? Thanks in advance! If more info is needed I wil post additonal.

View Replies !

Sorting In A Query
I'd like to sort in my query - used to populate a combo box - first by name, and then by ID.
Is there a way to force Access to sort in a particular order? It seems like it starts sorting with the first field that's to be sorted, but I want it to start with field 2 and then sort by field 1.

View Replies !

Sorting A Query
I am fairly new at relying heavily on Access for my programming. Problem: I have a telephone database with call detail for every call. I only want specific records, which I am pulling by selecting sort (in query design mode) for that particular field. I need to sort like you would in Excel (Column A first, Column B second, and etc). Can someone help direct me in the right path as to how I need to run a mulitple sort? Your help is much appreciated.

View Replies !

Sorting A Table, Apply The Sorting To A Form
Hi all.
I've created a database which contains information about stores. I want to have the forms automaticly sorted by the department number.
I've tried to sort the table by department, but when I try to add a department, the sorting doesn't seem to affect the form at all.

Lets say I have department 1,2,3,6,7,8 in the form, and I add department 4, it will be the last post in the form. I want it to be the fourth, and so on..

I'd apreciate some help with this :) Thanks

Here's the database (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=12934&stc=1&d=1142018915&PHPSESSID=f730b7f11f6983965698faeacbe5a1ee)

View Replies !

Sorting Further From A Duplicates Query
I have a Duplicates Query that extract the following information from my Table

Name Surname DOB Joined

The query then extracts info such as...

Bill Smith 25:12:70 1999
Bill Smith 25:12:70 2005

Most of the time the 'Joined' field has the same year. But I need to find the ones where the year differs but the rest of the information is exactly the same. Because there is 15000 odd records to check, I thought that there would be a quicker way to check.

Could someone please assist me with this query if it is possible?

Thank you in advance.

View Replies !

Sorting Records In A Query
I am having a problem sorting records in a query. I have about 5, 000 entries in a table and when I build my query based on certain fields, the numbers in certain columns are not in order although I use the sorting feature. ( e.g.) I have 5 columns with numbering data contained in the columns, and I have it set up to sort in ascending order, which it did, but I noticed that the Volume # column sorts the Volume numbers differently (see example below):

V. 1
V. 10
V. 11
V. 12
then it goes back to
V. 2
V. 3
V. 4
V. 5
V. 6
V. 7
V. 8
V. 9

It looks like it sorts anything with a 1 first then the rest later. How do I fix this?

Thanks.

View Replies !

Sorting Query Results
Hi

I have a problem sorting some query data and I wonder if someone could help point me in the right direction.

I have a query with two columns, in the first column is a number (either 6 or 7) and the second is the name of a business. Eg

Column 1 Column 2
6 Pizzas & Co
6 Pizzas & Co
7 Pizzas & Co
6 IPM Ltd
7 IPM Ltd
6 Computer Sales Ltd
6 Computer Sales Ltd
6 Computer Sales Ltd
7 Computer Sales Ltd
7 Computer Sales Ltd
7 Computer Sales Ltd
7 Computer Sales Ltd
7 Computer Sales Ltd

And I want to produce some another query so that the output which looks like:

Column 1 Column 2 (6's) Column 3 (7's)
Pizzas & Co 2 1
IPM Ltd 1 1
Computer Sales Ltd 3 5

So for each company name, I am essentially doing a frequency count for all the 6's and 7's.

I think this must be relatively easy to do, but I am getting it wrapped around my head!

Many Thanks,
Lee

View Replies !

Sorting Query & PivotChart
Hey everybody,

I know this question maybe be very lame but its been ages since i've touched on my sql knowledge. I've set out my database and i have 1 table which is holding the info i need to extract so that i may put it into my report. I wish to count the different values in a specific row:

Example:

Whats the problem
Behavioural
Physical
Physical
Behavioural

I wana be able to count these seperately as in:

SELECT COUNT(Client.[Whats the problem] LIKE 'Physical') AS Phy AND COUNT(Client.[Whats the problem] LIKE 'Behavioural') AS Beh
FROM Client;

Result:

Phy Beh
2 2

I havent got a clue if i'm doing it right but i need to get it to work in access 07. Also one more question i have created a Pivotchart in a form but when a create a button on my main startup form and click into the Pivotchart form all i get is the Datasheet view, how can i get it to open up the Pivotchart through the form.

Any info would be greatly appreciated Thank You.

View Replies !

Genaral Month Sorting Query - Help?!
Hi i have a general query and im not sure which section to put it in yet

Basically i need to have a date choosing system which allows me to enter dates once into the form and then print out monthly reports on which of our clients is to be sent a report,

some clients are half yearly, some quarterly, some yearly, at present i have a drop down box for these choices and a drop down box for month

is there a way of combining the half yearly and month ie May to come up with nov so that When i want to print out may's report, Mr X (who has a half yearly report) can have half yearly chosen and May chosen but still get on both the novembr and May print out along with mr Y who only has a yearly report in May

I do not really want to have to change the values in the drop down boxs as i do not think employees would do it and the system would be inaccurate

If a query could be made which somehow combined annual may and half Annual May, AND would also work out that half annual may would become Nov? ie: Annual Nov and Half Annual May

Im confused about the best way to do this so any suggestions would be v much appreciated :)
I dont know if the drop down system is the best way or how i would make a query to make this work and what the best method of making the reports is? any help much appreciated

H

View Replies !

Sorting Query By Date & Employee Name
I created a query which at first was simply to run a query which asks you for a drivers name, however we have decided that now we want to sort by the employee name as well as by a specified date range. I originaly had it set up to select the driver name with this in the criteria field in the Driver Name column:

[Enter Driver Name]

Then I tried to enter the following in to the criteria field in the date column:

Between [Enter Start Date: (Format MM-DD-YY)] And [Enter End Date: (Format MM-DD-YY]

However when I run the query it asks me for the date about 4 times, and then it goes to a blank report. I am sure I am not doing this properly somehow, anyone care to help out?

View Replies !

Crosstab Query
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 Replies !

Crosstab Query
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 Replies !

Crosstab Query
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 Replies !

Crosstab Query
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 Replies !

Can This Crosstab Query Be Done?
I have 2 tables:

tblStudents
tblPeriods

students holds information about students (surprisingly lol) and Periods has when periods end etc.

tblStudents:
Forename
Surname
StartDate
StartYear
LeaveDate
LeaveYear

tblPeriods:
PeriodNumber
PeriodStart
PeriodEnd
YearNumber

What I would like to be able to do is create a crosstab query has:
RowHeading: StartYear
ColumnHeading: PeriodNumber
Value: If the student was in training at the end of the period then count them.

So it would look something along the lines of....
1 2 3 4 5 6
2005 No. of students in learning
2006 at the end of each period

I have managed to do this but I have had to create 12 seperate queries calculating which students were in learning (thats the most periods there will be (12 months)) and then making a normal select query. I am just wondering if there is a different (ethical?) way?

View Replies !

Crosstab Query Help
I would be really grateful if someone could please help me with this problem. I have the following fields in a Project Management table:

fldMilestoneID (Primary key)
fldProjectID (foreign key to Projects table)
fldForecastCompletionDate (Date/time)
fldRAGStatusID (Red/Amber/Green/Completed/Not started - Foreign key to RAGStatus table)

Each milestone has a forecast completion date, an associated project and its current RAG status. I would like to create a query to use to diaplay a graph on a report that shows the number of milestones, by project, that are not yet complete BUT should have been completed by now, i.e. today's date. (Completed milestones always have a date less than or equal to today's, ie in the past).

The query should give 3data items for each project - a) the count of all completed milestones up to today, b) the count of non-completed milestones, ie those with a RAG status of Red/Amber/Green/Not started, that are late and c) a count of all other milestones whose dates are in the future.

I've tried in vain to code this using crosstabs but I'm getting nowhere. I'm able to get 3 select queries to show the correct results for complete, late and due milestones, but I can't work out how to combine these into a crosstab. Help!

View Replies !

Crosstab Query
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 Replies !

Crosstab Query
Currently I have a crosstab query with row totals. Is it possible to also display, in datasheet (not a report) column totals? Any help on this is greatly appreciated.

View Replies !

Crosstab Query?
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 Replies !

Need Help W/ Crosstab Query
Does anyone know how I can convert this Oracle crosstab query for use in Access? I was able to get the data to return the correct results using Oracle but I don't know how to write it using Transform and Pivot.

SELECT y.process_date, y.loc dealer_code, l.p_dlrgrp dealer_grp,
l.p_div division, l.p_countrycode country, y.plnrcode planner_code,
y.ordertype, y.daysofsupply, 'SOF'
FROM (SELECT process_date, plnrcode, MAX (x.loc) loc,
MAX (x.ordertype) ordertype,
MAX (x.daysofsupply) daysofsupply
FROM (SELECT TRUNC (insert_datetime) process_date, plnrcode,
DECODE (UPPER(param), 'LOC', VALUE) loc,
DECODE (UPPER(param), 'PROMOCODE', VALUE) ordertype,
DECODE (UPPER(param), 'DAYSOFSUPPLY', VALUE) daysofsupply
FROM pac.PROMO_PARAMS) x
GROUP BY process_date, plnrcode
ORDER BY 1, 2) y,
stsc.loc l
WHERE y.loc = l.loc
ORDER BY 1, 2

View Replies !

Crosstab Query
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 Replies !

Crosstab Query Help
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 Replies !

Crosstab Query
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 Replies !

Crosstab Query And Between
i have this crosstab query that works fine

TRANSFORM Sum(VoosAeronTbl.ATE) AS SomaDeATE
SELECT AeronaveTbl.Registo
FROM MissaoTbl INNER JOIN (AeronaveTbl INNER JOIN VoosAeronTbl ON AeronaveTbl.MatriculaID = VoosAeronTbl.MatriculaID) ON MissaoTbl.MissaoID = VoosAeronTbl.MissaoID
GROUP BY AeronaveTbl.Registo
PIVOT MissaoTbl.MISSAO;

when I try to select between dates set on a form field like this...

TRANSFORM Sum(VoosAeronTbl.ATE) AS SomaDeATE
SELECT AeronaveTbl.Registo
FROM MissaoTbl INNER JOIN (AeronaveTbl INNER JOIN VoosAeronTbl ON AeronaveTbl.MatriculaID = VoosAeronTbl.MatriculaID) ON MissaoTbl.MissaoID = VoosAeronTbl.MissaoID
WHERE (((VoosAeronTbl.Data) Between [forms]![ContAnFrm]![DataInicio] And [forms]![ContAnFrm]![DataFim]))
GROUP BY AeronaveTbl.Registo
PIVOT MissaoTbl.MISSAO;

...I get the follow error

microsoft jet database motor does not recognize [forms]![ContAnFrm]![DataInicio] as a field name or valid expression

Help
thanks

View Replies !

Qry: Crosstab Query With A Few Value's
Hello,

I have a question on a Database I'am building.
I need to make a Query that wil Compare Current YTD against Last YTD with Budgets and Outlook until End of Year,

I have got one query that is showing Current YTD Fields like
Dealer
Gross Value
Customer Margin
Invoice Value
our Margin or profit
Cost Value
and compare these values with Last YTD values.

Is this posible in Access ?
I know it is possible in Excell with Crosstabels. but Access wont let me add more than one value in the Cross table Query.

SELECT Invoices_By_Item.Jaar, Invoices_By_Item.Kwartaal, Invoices_By_Item.Maand, Invoices_By_Item.[Week#], Invoices_By_Item.[Afnemer groep], Invoices_By_Item.CAT1E1, Invoices_By_Item.CAT4E1, Sum(Invoices_By_Item.Aantal) AS Aantal, Sum(Invoices_By_Item.[Bruto waarde]) AS [Bruto waarde], ([Bruto waarde]-[Netto waarde])/[Bruto waarde] AS Dealermarge, Sum(Invoices_By_Item.[Netto waarde]) AS [Netto waarde], ([Netto waarde]-[Inkoop waarde])/[Netto waarde] AS PAHmarge, Sum(Invoices_By_Item.[Inkoop waarde]) AS [Inkoop waarde]
FROM (((Invoices_By_Item INNER JOIN [0,0 - CAT1 & Desci] ON Invoices_By_Item.CAT1E1 = [0,0 - CAT1 & Desci].[Category 1 Code - IN04]) INNER JOIN [0,0 - CAT2 & Desci] ON Invoices_By_Item.CAT2E1 = [0,0 - CAT2 & Desci].[Category 2 Code - IN04]) INNER JOIN [0,0 - CAT3 & Desci] ON Invoices_By_Item.CAT3E1 = [0,0 - CAT3 & Desci].[Category 3 Code - IN04]) INNER JOIN [0,0 - CAT4 & Desci] ON Invoices_By_Item.CAT4E1 = [0,0 - CAT4 & Desci].[Category 4 Code - IN04]
WHERE (((Invoices_By_Item.CAT2E1)="ACD" Or (Invoices_By_Item.CAT2E1)="ACG" Or (Invoices_By_Item.CAT2E1)="ACI" Or (Invoices_By_Item.CAT2E1)="ACL" Or (Invoices_By_Item.CAT2E1)="ACM" Or (Invoices_By_Item.CAT2E1)="ACS" Or (Invoices_By_Item.CAT2E1)="ACT" Or (Invoices_By_Item.CAT2E1)="ACV" Or (Invoices_By_Item.CAT2E1)="ACW" Or (Invoices_By_Item.CAT2E1)="ACX" Or (Invoices_By_Item.CAT2E1)="ACZ"))
GROUP BY Invoices_By_Item.Jaar, Invoices_By_Item.Kwartaal, Invoices_By_Item.Maand, Invoices_By_Item.[Week#], Invoices_By_Item.[Afnemer groep], Invoices_By_Item.CAT1E1, Invoices_By_Item.CAT4E1
HAVING (((Invoices_By_Item.Jaar)=[J,2 - Accessoire op Cat 4]![Jaar]-1) AND ((Sum(Invoices_By_Item.Aantal))<>0));

Hope you could help me on this issue.

Thanks in advanced

View Replies !