Crosstab Query With TEXT Values?
Nov 14, 2007
This is almost a cross-forum post. I started a post in the reports forum to see if it were possible to sort/group the data by field values. Everyone knows this can be done row-by-row, but I was looking for a way to do it horizontally as well.
...........[quarter]="Fall".....[quarter]="Winter"........[quarter]="Spring"........[Quarter]="Summer"
2005.....Relevant Fields........Winter Stuff..................Spring stuff................Summer Stuff
|
2006
|
2007
It should look something like that where the years are values of [year] field and the seasonal quarters are values of [quarter] field.
I have been searching for something that will help me do this when I ran across information on the "crosstab" query. This, in principal is what I am looking for BUT I am not dealing with numeric data here. Is there still a away to make a crosstab query/report with text fields where there would normally be numeric data?
View Replies
ADVERTISEMENT
Aug 3, 2005
Hi, I have a crosstab query, but for any product that has no entries on a certain date a NULL value is put in its place. Is there a way to have a 0 display there? Example of NULL:
DatePRODAPRODBPRODCPRODDPRODE
08/01/200513633
08/02/2005112643
Aug Total:216276
See how there is a NULL for 8/1 under PRODB? I would like that to be a 0
View 4 Replies
View Related
May 6, 2007
I have a columns that do not incur a count value within the time period of my query, yet i still want them to be displayed with a 0 or no value in the query view, how do i do this?
View 1 Replies
View Related
Jan 31, 2008
I would like to know how to display or change the value in crosstab query to text instead of numbers. I understand that the value must be numeric. Is there another method?
I would like to create in the folliwng format....
View 2 Replies
View Related
Jan 7, 2007
I am fairly new to developing with Access.
I would really value some advice on changing text direction in crosstab query column labels.
I have just created a crosstab query to function as a training matrix. It has rather long column labels (there are good practical reasons for this), which makes the query very wide and difficult to print one one page.
Is there a way to change the text direction of the column headings so they are printed running up the page?
I know how to change text direction in Word or Excel, but not Access.
At the moment I am copying the query results into Excel and manipulating the text direction in Excel, but this is rather cumbersome.
Thanks in anticipation of any help. :cool:
View 3 Replies
View Related
Nov 28, 2007
Don't worry, this isn't about parameters. I've searched the forum and can't find the answer to this (but loads on crosstab perameters!).
I have a table:
DateWorked | StaffPIN | StartTime | FinishTime | ShiftType
------------|-------- -|-------- --|-----------|----------
.01/11/07....|..1234.....|...07:00....|....19:00....|.... .Day
.01/11/07....|..5678.....|...19:00....|....07:00....|.... .Night
.02/11/07....|..1234.....|...07:00....|....19:00....|.... .Day
.02/11/07....|..5678.....|...07:00....|....07:00....|.... .Sick
I want to turn this in to a report with the format:
DateWorked | 1234 | 5678 |
-------------|------|------|
..01/11/07....|..12...|...12...|
..02/11/07....|..12...|....S...|
Where the values (12, S) are the number of hours worked for that staff member on that day or a summary of what they did.
I have created a Function:
WorkedHours(DateWorked, StaffPIN)
This returns the number of hours worked by a saff member on a perticular day as an int.
I know I need to use a crosstab. My 1st attempt had [DateWorked] as the row heading, [StaffPIN] as column heading and the expression "expr: WorkedHours([DateWorked],[StaffPIN])" as the value with 'expression' as the crosstab property. It kept throwing the error "Data type mismatch in criteria expression". There are no Null fields in the table it's performing the query on.
For my 2nd attempt I created a simple query that had the fields I needed for the crosstab plus the extra field "Hours: WorkedHours([DateWorked],[StaffPIN])". I could then use a crosstab query on this simple query to create something aproaching the table I need. This new crosstab had [DateWorked] as the row heading, [StaffPIN] as the column heading, [Hours] as the value with 'Last' as the crosstab property.
1. Do I have to use a seperate query to calculate the WorkedHours() or can I do it in the crosstab?
2. I don't want the crosstab to average, sum or count etc. the data. I just want it to display the hours worked but I have to choose something in the crosstab properties. What do I select? (I selected 'Last' and it seems to work but I'm not sure why)
3. How do I get it to display something other than the number of hours worked for certain shift types? (I have all the shift types in a table with a flag for the ones that need to be calculated, the absence of this flag would mean it needs to show a summary eg 'S' for a sick shift, 'H' for a holiday shift)
I know I've asked for a lot of help recently, I really appreciate everyone's help. :D
View 9 Replies
View Related
Aug 9, 2005
I currently have a crosstab query that compares data for two years. The results look like this.
month 2004 2005
January 98% 95%
.....
Decemeber 98
What I am getting at is that the query returns null values for the fields that do not have data recorded for the months in 2005 because we have not reached them yet. Is there a way for me to make sure that at least the value 0 is entered in the null value areas?
View 2 Replies
View Related
Apr 24, 2006
Hi there, instead of blank fields in my crosstab query, and hence my report, i would like a standard comment, such as 'No Booking'.
My crosstab is something like this:
.........A...........B..............C............. D
1.......X
2.......X..........................X
3...................X............................. .X
(Ignore the dots, obviously)
Currently, when i try to open the report based on this query, it fails unless each column has data in it for at least one record.
I've tried using the Nz function but haven't been able to make it work. Thankyou in advance! :)
View 5 Replies
View Related
Jun 8, 2006
Hi all, I know this is a real easy one, but I am not the smartest when it comes to access. Can you help me out.
I am running a crosstab query to count and sum records in my database. I have a fied called "Amount". In my form the user is not always required to enter an amount. When I run the query, I would like the results to exclude the records that have a null value or $0.00 in the "Amount" field.
How do I format the query to exclude those records?
Thanks in advance.
View 3 Replies
View Related
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
Sep 3, 2013
I have a query which uses values in two hidden text boxes, in order to populate a sub form.Unfortunately some times the data in the text box contains brackets within it as follows:
'120/60 ZR17 (55W)'
When this occurs the query returns no data, even though records with a matching code exists in the table I am working with where Speed is 'FR'.
Code:
WHERE (((stockdyn.SPEED)="FR") AND ((stockdyn.DESCRIPN) Like '*' & [forms]![frmSearch]![Text10] & '*'))
How can I get around this, as surely if I used quote marks it would take "[forms]![frmSearch]![Text10]" as the value I am searching for.
View 3 Replies
View Related
May 13, 2014
I have a text field that contains a comma delimited list of numbers.
I am trying to read one of these numbers from OLEDB through to an access database.
I am trying to do the following:
SELECT SPLIT(CSVData, ValueNo) as ReturnValue FROM MyCSVDataTable
Where CSVData is a split field containing values like "123,234,345,..." and ValueNo is a value to identify which one i want.
Any way of achieving this using the standard SQL functions. I am reading the data via OleDB and therefore cannot use a user defined VBA function?
View 1 Replies
View Related
Sep 17, 2013
I a trying to search some product from a search button and two combo boxes text values ,and on serch the vba code is :
Code:
Private Sub Command4_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varRecords As Variant
[code]...
I just dont know,the fuile is created on click of button but with headers only,dtaa is not coming but wheni debug ,in immediate window,data is oming but just not coming in excel file.
View 4 Replies
View Related
Sep 17, 2013
In Access 2010 I have a Data Entry Form on which I have an unbound textbox in the header that the user can put a default date in. In the body of the form is a bound textbox that records the date and the default value is set as =defaultdatestat (obviously the name of the box in the header).
Problem: The default date shows up perfectly until a value is put in any of the other text boxes.
For further info : If you put values in text boxes default value disappears; if you then push escape the default value reappears when the values in the text boxes disappear.
View 4 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
Nov 21, 2007
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 3 Replies
View Related
