First time using crosstab query because tired of creating queries and put them together in one query (such a stupid thing to do :D). Not sure how it works still.
I have link table called dbo_Roster to access 2000, I try to group data by Service and dates, for example 01/01 to 01/31 is Jan08, 01/02 to 28/2 is Feb08 and so on. The result should be like below :
[HTML]Service Jan08 Feb08 Mar08 Apr08 May08 Jun08 Total
Service1 # # # # # # #
Service2 # # # # # # #
Service3 # # # # # # #[/HTML]
However, it shows me this only:
[HTML]Service Total Feb08 Jan08
Service1 # # #
Service2 # # #
Service3 # # #[/HTML]
My code is as below: - TRANSFORM Sum([Duration]/12) AS TimeLost
-
-
SELECT dbo_Roster.[Service Type], Sum([TimeLost]) AS [Total Of TimeLost]
-
-
FROM dbo_Roster
-
-
WHERE (((dbo_Roster.[Service Type]) Like "RA*" Or (dbo_Roster.[Service Type]) Like "RA-*"))
-
-
GROUP BY dbo_Roster.[Service Type]
-
-
PIVOT IIf(dbo_Roster.Date Between 2008/1/1 And 2008/1/31,"Jan08",
-
IIf(dbo_Roster.Date Between 2008/2/1 And 2008/2/28,"Feb08",
-
IIf(dbo_Roster.Date Between 2008/3/1 And 2008/3/31,"Mar08",
-
IIf(dbo_Roster.Date Between 2008/4/1 And 2008/4/30, "Apr08",
-
IIf(dbo_Roster.Date Between 2008/5/1 And 2008/05/31, "May08",
-
IIf (dbo_Roster.Date Between 2008/6/1 And 2008/6/30, "Jun08"))))));
I think I must have get the crosstab concept wrong. Can anyone show me how to use it correctly?
5 5008
Hi. The date comparisons in your IIF statements are unlikely to work as you expect them to, as you are comparing date literal values that are non-standard in SQL (and not delimited by # characters either). Also, the monthly headings will be ordered alphabetically (hence Feb08 before Jan08 and so on).
I would suggest using the Format function to return the year and month as a numeric combination year-first (to order correctly on year changes such as December to January), using a Pivot statement like this in place of your IIFs: - PIVOT Format(dboRoster.Date,"yyyy-mm")
Please note that a crosstab query will leave out columns that have no matching rows in the source data, so it is possible to skip months and so on unless the column headers have been explicitly defined for the query concerned. Also, if for any row group summarised there is no data for a particular pivot column a null value is recorded in that column, not a zero. The example crosstab below demonstrates both flaws of crosstab queries, as shown in one of the attached screenshots. - TRANSFORM Count(BranchEntries.[Branch#]) AS [CountOfBranch#]
-
SELECT BranchEntries.[Branch#]
-
FROM BranchEntries
-
GROUP BY BranchEntries.[Branch#]
-
PIVOT Format([EntryDate],"yyyy-mm");
Use of Nz (and CLng to transform the result back to a number) resolves the null value issue, as shown in the other screenshot. - TRANSFORM CLng(Nz(Count([Branch#]),0)) AS Expr2
Hope this helps.
-Stewart
Hi,
Thanks...I was using "#" to represent numbers without explaining in the previous post..sorry for the confusion....few more questions though...
1. When I use clng, the numbers behind decimal is gone. For example, 1.5 become 2...etc. If I am not using clng, some of the figures has lots of numbers behind the decimal. What should I do if I only need two decimal only?
2.
a.Total column always goes in front of the "year and month" column. Is there anyway to put them behind? I tried to move them to the back in design view in query but it didn't work.
b.If total is needed every 3 months, or 6 months, can it be done? a total in between the "month column". For example, Jan, Feb, Mar, Total (Jan to Mar), Apr, May, June, Total (Apr to June), Grand Total (Total from Jan to June)
3.How to define months explicitly in query so that the month column still show even though there is no figure in it? Maybe can replace by zero if there is nothing in it?
Many thanks for your time
Hi again. In answer to your questions:
1. Use CDbl in place of CLng - converts to double-precision floating point values, whereas CLng is a long integer (whole number) conversion.
2. (a) There is no way you can place row summary data (such as your totals) after the pivoted columns. Such data is always displayed before the pivoted columns.
2 (b) Can't be done using an Access crosstab query.
3. It is possible to define specific column headings for the crosstab query, but I find this a very inflexible approach. By their nature, the headings are static. But the row data is not static, and there is a conflict between defining a set of headings and the moving months of real data.
There are more complex approaches which could be taken using a combination of SQL and VBA code to provide summary totals and so on, but it is simpler in many ways to export the data (or summaries of it) to Excel, which is designed for such data manipulation (which is not what databases do best).
-Stewart
Hi,
Thanks...I was using "#" to represent numbers without explaining in the previous post..sorry for the confusion....few more questions though...
1. When I use clng, the numbers behind decimal is gone. For example, 1.5 become 2...etc. If I am not using clng, some of the figures has lots of numbers behind the decimal. What should I do if I only need two decimal only?
2.
a.Total column always goes in front of the "year and month" column. Is there anyway to put them behind? I tried to move them to the back in design view in query but it didn't work.
b.If total is needed every 3 months, or 6 months, can it be done? a total in between the "month column". For example, Jan, Feb, Mar, Total (Jan to Mar), Apr, May, June, Total (Apr to June), Grand Total (Total from Jan to June)
3.How to define months explicitly in query so that the month column still show even though there is no figure in it? Maybe can replace by zero if there is nothing in it?
Many thanks for your time
Hi again. Just to add to my previous reply, the # characters I was referring to were what should have been delimiting the dates in your IIF statements (post 1). If you include literal dates in Access SQL statements these are delimited by # characters like this: - Where Table.SomeDate = #01/31/2008#
The SQL standard requires that dates listed as literals be presented in the American convention of month/day/year. It was for these two reasons I was mentioning that your crosstab was unlikely to return the values you would expect.
There is an excellent HowTo article by our Admin contributor NeoPa on this topic linked here for reference.
-Stewart
I was using "#" to represent numbers without explaining in the previous post..sorry for the confusion....few more questions though...
Hi Stewart,
Thank you for the answeres and tips.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Kaczorek |
last post by:
Hello.
I have one serious problem with COUNT in TSQL.
I use MS SQL Server 2000.
I would like to count rows depending on data in it.
What I meat is:
I have fields like:
region | month | year |...
|
by: Tony |
last post by:
Hey guys,
I use Google Groups quite a bit as it is an enormous wealth of
information, and now I need some help. I have created a query using
parameters to capture a range of date, the date is...
|
by: Steve Elliott |
last post by:
I have a query set up to gather together data between two specified dates.
Shown in the query column as:
Between #24/09/2004# And #01/10/2004#
Is it possible to enter several different date...
|
by: Sami |
last post by:
I can create queries and reports based on info from one table. How do
I create one using information from multiple tables. What do I need
to make sure the information from one table will be...
|
by: Lee |
last post by:
I have a crosstab query that lists the number of patients(DECnum)by
County. County is my Row Heading. Currently, the Column heading is Age
in months, which is set at one month intervals. I would...
|
by: Bob |
last post by:
running access 2k; mdb w/ linked tables to another mdb
(front/back-end);
trying to run a query that updates a table FROM information from a
crosstab query.
I AM NOTT trying to update the...
|
by: Katie |
last post by:
I have created a report which sorts data by a time frame.
However, if there is no data for a particular time frame then that row
does not show.
I need it to show with a blank for the value, e.g....
|
by: greg |
last post by:
I have a table that I want to left join with crosstab query and I want
to be able to edit data in the table (only). However, once I perform
the join, the entire query result cannot be edited. Is...
|
by: Coxmg |
last post by:
I have several related tables. One table lists orders with due dates for SKUs while another table lists components of a SKU (diamonds). A third related table lists diamond IDs and diamond inventory....
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM).
In this month's session, the creator of the excellent VBE...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: marcoviolo |
last post by:
Dear all,
I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
| |