473,289 Members | 1,884 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

How to group data by date in crosstab query? Access 2000

48
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:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum([Duration]/12) AS TimeLost
  2.  
  3. SELECT dbo_Roster.[Service Type], Sum([TimeLost]) AS [Total Of TimeLost]
  4.  
  5. FROM dbo_Roster
  6.  
  7. WHERE (((dbo_Roster.[Service Type]) Like "RA*" Or (dbo_Roster.[Service Type]) Like "RA-*"))
  8.  
  9. GROUP BY dbo_Roster.[Service Type]
  10.  
  11. PIVOT IIf(dbo_Roster.Date Between 2008/1/1 And 2008/1/31,"Jan08",
  12. IIf(dbo_Roster.Date Between 2008/2/1 And 2008/2/28,"Feb08",
  13. IIf(dbo_Roster.Date Between 2008/3/1 And 2008/3/31,"Mar08",
  14. IIf(dbo_Roster.Date Between 2008/4/1 And 2008/4/30, "Apr08",
  15. IIf(dbo_Roster.Date Between 2008/5/1 And 2008/05/31, "May08",
  16. 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?
Apr 29 '08 #1
5 5008
Stewart Ross
2,545 Expert Mod 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Count(BranchEntries.[Branch#]) AS [CountOfBranch#]
  2. SELECT BranchEntries.[Branch#]
  3. FROM BranchEntries
  4. GROUP BY BranchEntries.[Branch#]
  5. 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.
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM CLng(Nz(Count([Branch#]),0)) AS Expr2
Hope this helps.

-Stewart
Attached Images
File Type: jpg ScreenHunter_20.jpg (9.3 KB, 290 views)
File Type: jpg ScreenHunter_21.jpg (8.9 KB, 298 views)
Apr 30 '08 #2
HowHow
48
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
May 1 '08 #3
Stewart Ross
2,545 Expert Mod 2GB
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
May 1 '08 #4
Stewart Ross
2,545 Expert Mod 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. 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...
May 1 '08 #5
HowHow
48
Hi Stewart,

Thank you for the answeres and tips.
May 2 '08 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

3
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 |...
4
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...
12
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...
5
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...
3
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...
27
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...
2
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....
1
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...
2
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....
2
isladogs
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...
0
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...
0
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...
0
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 =...
0
isladogs
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...
0
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...
1
isladogs
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...
0
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...
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.