Column headings for crosstab query Results

I am passing a sql statement for a crosstab query, as such, in VB Code:
___________
strSQL = "TRANSFORM....
"SELECT ....
"FROM .....
"WHERE....
"GROUP BY ....
"PIVOT myField IN ('Apple', 'Orange', 'Banana')

CurrentDb.QueryDefs("myQuery").SQL = strSQL
____________

If I look at the ColumnHeading property in design, it's set to 'Apple', 'Orange', 'Banana'

The query in Access however is sorting as such... Banana, Orange, Total, Apple

Why isn't my PIVOT IN line sorting the column headings in the order I want!?!?

Thanks for your help!!

Being an amateur at Access, I've only recently realized that you can force column headings for crosstab queries, by typing those headings into the properties of the field selected as the column. But can you do the same for ROWS?

My data concerns patients of a specific type and the times of day they show up at our A&E (ER). I've grouped these by hour and now I want to crosstab them by day of week. But if there aren't any between say 3am and 5am, those hours don't show in the query, which is screwing up a pre-formatted report I have.

I've found some assistance on other threads.....
http://www.access-programmers.co.uk/...ad.php?t=92471
http://www.access-programmers.co.uk/...ad.php?t=83820
.....but to be honest I didn't really follow it.

Is there an easy solution? If not, it's not a huge problem and I can "fudge" it once a month, offensive as that must sound to you professional types!

Thanks as always.

Pat

My report is not working because of the crosstab query column headings. The crosstab query pulls the last three months of data. It labels the column headings as the date. When I update the crosstab query and try to run the report it is looking for column headings that are no longer there (it drops a month each month I run the report). How can I get the report to update automatically to only include the column headings available in the crosstab query instead of looking for the original ones that the report was designed on?

Under Access 2003, the simple expression, attached below, allowed me to include the day of the week in the date field (column heading) of a cross tab query (Sunday, 12/25/2010 versus simply 12/25/2010).

Under Access 2010, I get the "There is no message for this error" when trying to open the query.

Recreating the object and code does not solve the problem.

Is there some new syntax that is required?

Thank you. Attached Files Document.txt (55 Bytes, 13 views) Reply With Quote 02-02-2011, 07:51 AM #2 RuralGuy Administrator Windows 7 64bit Access 2010 32bit Join Date Mar 2007 Location 8300' in the Colorado Rocky Mountains Posts 9,484 It should work. Try recreating the query from scratch and see what happens.

I have created a reprt based on a crosstab query where the query totals the values within a certain field. So effectively that field becomes the column heading. The problem is that if there is one field missing ina table that I am analyzing, the report gives me an error saying that the field is not found. I can try to explain below:

Say that you have a field called "Callergroup" as the row heading and "Calltype" as the column heading.(it sums over a field called "TOTAL") Let us say assume that "Calltype" has three types of entries X,Y and Z.

Now when create the report from the crosstab query, X,Y, and Z will become the column headings. So assume that these will be the column headings for which I have designed and formatted the report for to be viewed by the management.

Now there are times that there may not be an entry, say Z, in the field "Calltype". Under these circumstances when I try to get the report it gives me an error saying that the field "Z" does not exist (since Z is one of the fields in the report previously created).
Is there a way to get around this so that even though the field does not exist the report assume that all TOTALS for that field will be zero but it does open the report without giving me an error. Thanks.

I need to generate reports that are based on crosstab queries.

In total, if they were manually created, I would need 180 crosstabs. This is impractical, inflexible and bad design. And insane.

There will be approx 18 different reports with 10 different crosstabs in each. The crosstabs have the same row headings, column headings and data, but will be sourced from different underlying queries. By which I mean the query on which the crosstab is based.

There must be a way to allow different queries or different crosstabs to be specified at runtime. I've found out that apparently you can't specify field-names at runtime without VBA...

I've looked at example VBA code that does something sort of similar (Dynamic Crosstab queries in reports), but it's beyond my understanding.

Failing that, is there some way to automatically write the necessary 180 crosstab queries without doing it the hard way?

I have created a crosstab query that shows file types as the column headings and names as the rows. The aggregate function sums the files per type for each name. I know how to fix the column order for the file types using the PIVOT statement shown below:
..."PIVOT TotalFileCountByNameAndType.FileType In (""Excel"",""Word"",""PowerPoint"",""PDF"",""Email "",""Other"");"

However, on my screen the user can hide or show the column using checkboxes. When a column is hidden and then shown, the column is displayed at the end of the list. I am careful to recreate the PIVOT statement above to keep the order of the columns without the one that is to be hidden.

After hidding and then showing all of the columns, I discovered that it is the aggregate, 'sum' column that is the problem. It is not in the list so Access wants to make it the first column. Once it is the first column, the other columns remain in the correct order.

So is there any way to list the aggregate function column in the list with my file types so that it can be at the end of the list?

Thanks!

Hi
I have created a crosstab query with column heading:

Day: Format([dtmDate],"ddd")

This gives me Mon, Tue, Wed etc for the days of the week which is what I currently want. When I first called up the query in view it had the days in an odd sequence so I moved them around so that they ran in sequence and that seemed to stick.
When I export the query to Excel (my user needs to use Excel for PocketPC) the day sequence has reverted to Fri, Mon, Sat, Thu, Tue, Wed which doesn't make any sense to me.
I tried adding a sequencing field using the "d" function in date format, and chose sort as ascending, but of course I can only have one column heading.
I do not think having 2,3,4,5,6,7 is very intuitive so cannot just leave things as that even though it makes it easier for me.
Anyone got any bright ideas?

BTW the reason I wanted days was partly ease for user and partly also to help me by having constant column headings when I re-import back into Access. This is OK since a separate export tells me which week we are playing with so I can translate the field into a date.

Hope someone can help!
Best wishes

Malcy

Background:

I work at a Help Desk; we have someone that daily calls back folks who called us to rate our service. The survey results are stored in a table containing the date of the call, the ticket #, the Employee's name, the skill for the call (application a, application b or technical) and the scores for the 5 questions. There are 2 other fields: Unavailable & Refused - Yes/No fields - if the caller can't get the person or the person doesn't want to complete a survey, then instead of scores the appropriate field is checked.

I've been working for the past few weeks (with someone else's help!!) on creating a query to load into a report to display the totals by day & for the month selected. There is a form that has the parameter dates on it to choose the month for the report - it's referenced in the PARAMETERS line of the query's SQL. They also helped me build column headings for the dates, because I need the report to be formatted with the dates across the top.

My problem: it seems that the WHERE part of the SQL is overriding the PARAMETERS, so I'm getting more than just the month selected.

Here's the SQL:

PARAMETERS [Forms]![frmQASurveyReports]![StartDateChoice] DateTime, [Forms]![frmQASurveyReports]![EndDateChoice] DateTime;
TRANSFORM Count([QA Survey Table].[Ticket#]) AS [CountOfTicket#]
SELECT [QA Survey Table].Employee, [QA Survey Table].Unavailable, [QA Survey Table].Refused, [QA Survey Table].[Ticket#], [QA Survey Table].Date
FROM [QA Survey Table]
WHERE ((([QA Survey Table].Date) Between DateAdd("d",-30,[Forms]![frmQASurveyReports]![EndDateChoice]) And [Forms]![frmQASurveyReports]![EndDateChoice]))
GROUP BY [QA Survey Table].Employee, [QA Survey Table].Unavailable, [QA Survey Table].Refused, [QA Survey Table].[Ticket#], [QA Survey Table].Date
PIVOT "D" & DateDiff("d",[Date],[Forms]![frmQASurveyReports]![EndDateChoice]) In ("D0","D1","D2","D3","D4","D5","D6","D7","D8","D9" ,"D10","D11","D12","D13","D14","D15","D16","D17"," D18","D19","D20","D21","D22","D23","D24","D25","D2 6","D27","D28","D29","D30");


Any ideas on how to fix the query? Once that works, I'll move on to fixing the report!!

Thanks.

hi

is it possible to have multiple columns within a crosstab query??

if so please specify how. at the moment i am not allowed to specify column heading for more than one item

thanks

In Access 2003, I have a crosstab query where the Column Headings are months found in a date field (formatted as "yyyy-mm"). The Row Headings are the Accounts. The Value column is the sum of currency values in the Paid field. The records are filtered to be those within a date range of another date field. The query returns columns for each month that has at least 1 sum. But my boss wants to see columns for every month in the date range, even if they are empty. How do I do that?

I have a crosstab query based on a single table. The query works as expected except for one problem: The column heading is report date from the record field report_date. In the table report_date is formatted mmm-yyyy. The date is properly formatted in a datasheet view of the table and in a simple select query based on the table and in a form based on the table. However, the columns in the crosstab query are formatted m/1/yyyy. I have tried using a format statement for the date and converting the resulting string to a date using the function CDate (the conversion is necessary to sort the date as a date and not a string). Nothing I have tried results in the desired format for the date columns in the crosstab query.

Please help me if you can. This may be a bug, but I don't know how to inform Microsoft about the problem.

Thanks!

I am having a time trying to figure out how to design a report that pulls its data from a Crosstab Query where the column headings change for every specific criteria defined. I am trying to make the report on a series of incremental QC test results that are performed a few thousand products. Some products might be tested at 1 hour, some at 10 hours, some at a week, etc... overall they is 12 different time increments... but they are not all used for every product!!!

I do not want to have to design 1700 different report depending on a product number (hahaahaha)... how can i just have the columns that are showing up through the crosstab query automatically go into the report... ie with out nameing a strict control source but letting it be more dynamic accordingly.

Thanks in advance.

I am trying to make a report that shows shipment information. I have a shipment table, a box table, and a box detail table. I have made a crosstab query that works well.

I've used the box number as a row heading, the item type as the column heading, and the item quantity for the value. The query shows how many of each item was sent in each box.

Since each shipment will have different items, I can't just convert this query to a report using wizard because the field names would be the same every time.

How can I make a dynamic report by using a crosstab query?

If I could have every item type as a column heading, even though some items' quantities would be zero, then that would be fine.

Thanks

-Joe

I have a crosstab query that new data is added for every month so the column headings change.

How can i create a report based off of this query without having to go in and update the design of the report every month?

thanks in advance

I have tasks in one table that are assigned a Priority (High, Med, Low) and a Status (10, 20, 30, 40). Priority and Status are separate tables.

In my report, I want a tabular output where Priority is the row heading and Status is the column heading.

I have made the crosstab query properly to get the desired data. In the report, I want to set it up such that it displays as many column headings as are attached to the tasks.

for instance, right now i have only Status 10 and 20 that are being used, so my outpout would display only those column headings. But if Status 30 gets assigned to a task I want that reflected in the report output.

How do I setup the report so that its agile in this manner?

Hello,

I have a report which is based on crosstab query. One of the fields in the query is "date" current range is "Between #7/1/2007# And #7/1/2008#"


Dates are not part of the column or row headings. The date field is there just to limit the range of data which is chosen.

I would like to do two things with this: (1) I would like to show the date range in the report header. However, the "date" field is not a choice in the pull down menu of available fields to include in the report.

(2) I would like the users of the report to be able to choose their own date range, which I have done with other reports by using "between.... and.." statements in the query. "Between [What is First Date] And [What is Final Date]."

I did try puting a text box in the report header, bound to the field in the query as follows: "=qry_rpt_dog_bike_violations]![Date]" but came up with an error message.

Thanks for any help!

Say I have the following table

Employee ID....Name....Gender...City


I want to know how many males work in each city and put it in a column chart.



So I create crosstab query as follows...

Row Heading = Gender - Where males only
Column Heading = City
Value = Count of EmployeeID
Row Heading = Count of TotalOfID:EmployeeID


I need to generate the report in the following format:

report to be in tablular form (single row for males, one column per city to give the number of males)

add a column graph to the report (horizontal-Axis=City, vertical-axis = Total males)


The report in tabular form works well the way I do it.

In order to generate the graph, I would intuitively use Value = SumOfTotalOfEmployeeID, Axis=City, Series=Gender

Unfortunately that does not work because the result of the crosstab query yields individual city names for the column headings rather than having them grouped as a [City] field...and I can only put one item in the chart wizard for the axis (and not a whole bunch of different cities)



I have a workaround with the crosstab query...as follows


Row Heading = City ***add dummy row heading to have cities grouped as a single field to generate the graph***
Row Heading = Gender - Where males only
Column Heading = City
Value = Count of EmployeeID
Row Heading = Count of TotalOfEmployeeID:PID

This allows me to enter the following data in the graph wizard as follows

AXIS = City
DATA = SumOfTotalofEmployeeID
Series=Gender

which works well


But now unfortunately the tablular report is screwed up as I do not have a single line with all males in each city....my tabular report now has as many lines as I had cities where males are living.


Is there anyway to get both the graph and single-line tabular data in the report. I do not like the graph option of adding the datatable to the graph.


Thanks for your help.

Bruno

Hi,
I have a crosstab query which generates 35 columns under column heading.
The report based on this query shows summary option for 20 columns only. How do I extend this to include all fields generated by crosstab query.
When I try to add a summary for one of the fields in report design, it does not recognise field name and shows error.

thanx

Hi everyone,

I'm trying to find a way to do something but I'm not even sure if it can be done. I'm trying to produce a simple presence/absence table for some species.

At the moment the data is entered into a table that has a field to hold the following: Site Number, Species. A Site_number (ideally forwarded from the site number autonumber field from the Site table) goes into the Site Number field, and the species present goes into the Species field. So, with this layout, you may have 3 records for Site#1, because there were 3 species present there.

What I've done is create a crosstab query with the row heading being site, the column heading being species, and the value being count of species. So this gives me a nice table presence of a species at a site is denoted as a 1 for that species field.

So at this point I have two issues. First...at this point the list of species across the top only includes the species present in the presence/absence table. Makes sense. So I then added our species look up table - which has a complete listing of all species we're interested in - and used the species name field from that table as the columns and set the relationship to include all from this table and only the one's that match from the presence/absence table. It works in that all the species are now listed across the top - however I end up with an extra record that has no Site Number value, and a bunch of zeros in the species fields where there are no count values in the records below.

Second issues - ideally in a presence absence table - presence is denoted by a 1, and absence a 0. So how do I get the blank fields to be a zero? I thought of setting the default value to zero - then realized that a query table is not the same as a table table? I also thought of using a simple If value is null, then give it a 0. Ideally if data entry is done correctly, there would be no count greater than one.

I'm by no means well versed in MS Access. I've managed to pick up enough along the way to get by - but I'd definitely be one of those people that doesn't know enough to be good, but knows enough to be dangerous!

Any suggestions would be more than welcome!


Not finding an answer? Try a Google search.