I have a crosstab query where my column headings are the names of the months based on invoice dates.
what i currently have:
Month: MonthName(Month([AnnualIncome]![InvoiceDate]))
so this spits out April May and June in the column headings.
The problem is that it is sorting it alphabetically, not by the numerical value - so my column headings are coming out as april, june, then may. (I assume once I have july that will come in between april and june.)
I have a crosstab where I want year and month in the header so I have formatted a variable MTHYR to be of the form mm/yyyy.
This works well as Access only wants one variable in the column header. My problem is that it is not sorted nicely and with my data being between Aug 2013 and Jun 2014 the headers look like this.
1/2014,10/2013,11/2013,12/2013,2/2014, not a very nice order.
Is there a way I can reformat MTHYR or a completely different solution?
My current code is
Code: TRANSFORM Count(VOR_CALD_REGION.[Case Number]) AS [CountOfCase Number] SELECT VOR_CALD_REGION.[Dealer Name], Count(VOR_CALD_REGION.Case Number]) AS [Total Of Case Number] FROM VOR_CALD_REGION GROUP BY VOR_CALD_REGION.[Dealer Name] PIVOT VOR_CALD_REGION.MTHYR;
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
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)
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.
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.
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.
: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;
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.
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?
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?
How to sort a subset in a query. I have three fields BatchID,Month and Year. The BatchID can have multiple months and years. I would like to order the final sort so as the Batchs appeared in the order of the highest date combination with in each Batch.
The following is the SQL of my first Query which establish's the lowest and highest date in each batch.
Code: SELECT tblRevOnlySelYear.BatchID, tblRevOnlySelYear.OperatorID, Max([Year] & IIf(Len([Month])=1,0 & [Month],[Month])) AS Expr1FROM tblRevOnlySelYear GROUP BY tblRevOnlySelYear.BatchID, tblRevOnlySelYear.OperatorID HAVING (((tblRevOnlySelYear.OperatorID)=1)) ORDER BY Max([Year] & IIf(Len([Month])=1,0 & [Month],[Month]));
The next SQL is the second query which results in the Batchs in the correct order by year but the months are not correct.
Code: SELECT tblRevOnlySelYear.BatchID, tblRevOnlySelYear.OperatorID, tblRevOnlySelYear.Month, tblRevOnlySelYear.Year, qryRevTest4_1.[1stDate], qryRevTest4_1.LastDateFROM qryRevTest4 AS qryRevTest4_1 INNER JOIN tblRevOnlySelYear ON qryRevTest4_1.BatchID = tblRevOnlySelYear.BatchID WHERE (((tblRevOnlySelYear.OperatorID)=1)) ORDER BY qryRevTest4_1.LastDate;
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?
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)”
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!
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?
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?
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? :)
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?
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;
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
