Hello, I have created a crosstab query where I specify the order of Column Headings, however, I can't figure out how to specify the order of Row Headings. I only see that Microsoft Access offers the option to sort ascending vs. descending. I'd like to customize my sort order such as "Under 18" row first followed by "18-29" row and then "30-39" etc.
Any help you may provide would be great.
Diana
Is it possible to create a custom label for a column header in a crosstab query?
My crosstab query displays sales totals for a particular sales rep number (rep # 102 in this case), by month. For example:
Month....102
1..........$500
2..........$300
3..........$450
etc.
I would like the column to be labeled "Sales" instead of "102" - is that possible?
Thanks for any help you can provide!!! :o
I have attached part of my database.
I need to show the complete rows for all the status in each year with or without any record/data.
Wondering if it is possible to order by particular values??
That is I have a column (varchar) named "Status" with values such as; Entered, Active, Completed etc (I didnt design the DB).
What I need is to order by Entered, then by Active then Completed etc.. Is this possible??
Ive got this code for SQL; but it doesnt work in Access (03)..
ORDER BY
CASE Status
WHEN 'Entered' THEN 0
WHEN 'Active' THEN 1
WHEN 'Completed' THEN 2
ELSE 3 END, Status
I have a form with tab pages which I can not change the tab order.
When I bring up the Tab Order box no fields are displayed only a Custom Tab Order! Never seen this before.
Question is how to get rid of this and be able to change tab orders.
Thanks
I have a combo box with the following entries:
Document
Other
Process
Validate
Verify
I want the list sorted so that "Other" is at the bottom. Everything else is alphabetical ascending. Is this possible?
Hi,
I have a Form which contains a datasheet and I have some code which allows users to custom sort this datasheet. Another table based on this datasheet is then exported as a csv file.
However, the data is obviously not exported in the same order as the datasheet and it is quite critical for the application to be a sucess that it is exported in the custom order chosen by the user.
I have done some re-search, searching groups, forums etc and found the solution of having a 'sort column' in the table the datasheet relates to, I can then use this in the query that exports the data. My only problem with this is I had to manually fill in this sort column in the datasheet, once I had decided upon my final sort order. Is there anyway to fill this column automatically based on a row's position in a datasheet?
Many thanks in advance
I have a subform with year and month, subform shows result of a select query.
When I open query separately it show data as required order but when i show data in subform related to this query, data show in rearrange order.
How can I set order in subform as query result.
I have data like:
Code:
SubjectID VisitID VisitNum VisitDate VisitDetail Rspns
1 5 1 2/10/2001 Fever_1 Yes
1 5 1 2/10/2001 Age_1 12
2 7 1 5/08/2010 Fever_1 No
2 7 1 5/08/2010 Age_1 18
2 8 2 9/30/2010 Fever_2 Yes
2 8 2 9/30/2010 Age_2 18
etc.
I need to convert it to a non-normalized wide format, like this:
Code:
SubjectID VisitDate_1 Fever_1 Age_1 VisitDate_2 Fever_2 Age_2
1 2/10/2001 Yes 12
2 5/08/2010 No 18 9/30/2010 Yes 18
etc.
If I didn't have to worry about the VisitDate, a simple crosstab would do the trick:
Code:
TRANSFORM First(MyTable.Rspns) AS FirstOfRspns
SELECT MyTable.PatientID
FROM MyTable
GROUP BY MyTable.PatientID
PIVOT MyTable.VisitDetail;
I have created a form and a subform from a table.
Initially I created a table with 11 rows. After completion of my project I added an extra of 480 rows to the main table. But the newly added rows are not coming in my subform.
Attached is my project.
Main : Main table (It consists of around 491 rows)
MForm : Main Form (It shows of only 11 records which I was 1st created)
How to refresh my subform inorder to display all the rows.
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!!!
Hi all,
I cant make work, a query with creteria filled within controls of a form. I will be more specific to make u understand what i want to ask.
There are 2 text fields and 2 combo boxes. All of them call a report (by pressing a button) from the main table.
in the "totalquery" query i call each control this way:
select tblPeople.name,tblPeople.tel,tblPeople.age,tblPeop le.Car from tblPeople where tblPeople.Name=[forms]![frmMyform]![combo1] OR tblPeople.tel=[forms]![frmMyform]![text1field] OR tblPeople.age=[forms]![frmMyform]![text2field] OR tblPeople.Car=[forms]![frmMyform]![combo2]
GROUP BY tblPeople.name,tblPeople.tel,tblPeople.age,tblPeop le.Car;
I want to make it display (the right) results if one of the text filed/combo is filled or with data, or all together or in combinations.
i use the OR statement,but it doesnt work if I fill in two fields/combos together and etc.
Anyone? :rolleyes:
: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! :)
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.
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
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.
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
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
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 RelatedI 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.
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...
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!
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.
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
Hi,
I've been trying to solve this problem for almost a full day now and i'm starting to get desperate.
Can't seem to find any help on the net, so i hope maybe one of you will be able to save me.
I have 2 list boxes :
1. MainBox - shows a list of records from one table.
2. FilterBox - this is actually a query that shows a list of records from another table, filtered by the record i choose in the MainBox.
I added the MainBox a custom line displaying "All" by adding this code to its RowSource :
UNION SELECT "(All)" from MainTbl
So now i see the list of records like before, with a "(All)" as the 1st line.
Now i want to set up the FilterBox query so that if i select the "(All)" line, it will not filter the table and just show me all records, so i entered this code as the appropriate column condition :
IIf(([Forms]![frmTest]![MainBox])="(All)",([tblFilter]![FilterColumn]) Is Not Null,[Forms]![frmTest]![MainBox])
That's supposed to check if i have selected the "(All)" line in the MainBox.
If i didn't - then the FilterBox gets filtered by the MainBox value - this is working fine.
If i did - then the column filter value should become "Is Not Null" which, according to my understanding, means that the column won't get filtered - that's not working! When i select the "(All)" line, i just don't get any records in the FilterBox.
Does anyone have any idea what i can do to fix this ?
Sorry for the long explanation.
Thank you very much!
I have a query in my database, and I have recently added new possible entries for area. My report tells me how many male chickens vs. how many female chickens I have. Now with the new entries for area, I want my breakdown to EXCLUDE any chickens that live in a specific area.
For instance:
Pen A has 15 males & 15 females.
Pen B has 12 Males & 13 Females.
Pen C has 50 males & 50 females.
Pen C is the recent addition...
Right now my report shows: 77 Males & 78 Females...
I WANT it to EXCLUDE Pen C, so it would report: 27 Males & 28 Females.
Can anyone tell me how best to do this? I figured I would add a custom field to the query that the criteria for is "Pen = C" and then on my report I would change the data source from: =Count([Chicken Query]![Male] --- and make it =Count([Chicken Query]![Male] - Count([Pen="C"]) --- or something like that.
Can anyone help me? And if so could you help with the proper code for the second part that I am adding new? I know the Count([Pen="C"]) isn't right, but until I know exactly how to add a custom field to the current query, with the critieria of Pen="C", I don't know how to write the -Count([NewField]) code, if that would even work.
Thanks!