If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab query oddity
I expect this is a rounding error. What happens if you try:
UNION SELECT ADate, DateAdd("h",1, [ATime]), AName, ALoc Have you looked at the Week at a glance type calendar reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4? -- Duane Hookom MS Access MVP -- "Tman" wrote in message ... I'm stumped and baffled by a strange problem I'm having with a crosstab query. My crosstab query creates a grid showing appointments for the following week. The result shows seven columns: Appointment Time followed by Day0 through Day6 (Monday through Saturday for the following week). The values in the rows represent the start times of the appointments. At the column and row intersections, the name of the person with the appointment is displayed. ATime Day0 Day1 Day2 Day3 Day4 Day5 Day6 8:00:00am 9:00:00am 10:00:00am etc. I start with the following union query (Query: Union1): SELECT ADate, ATime, AName, ALoc FROM [Table: AData] WHERE [ALoc] = "VProd1" UNION SELECT ADate, [ATime]+(1/24), AName, ALoc FROM [Table: AData] WHERE [ALoc] = "VProd1" UNION SELECT ADate, ATime, AName, ALoc FROM [Table: TimeFrames]; The field ATime is the start time of the appointment. Appointments are in two-hour blocks, so the first union in the query creates an additional row for the second hour of the appointment. In other words, if Smith has an appointment at 2pm, the resulting crosstab query datasheet will show Smith in the 2pm row and the 3pm row. The second union adds data from Table: TimeFrames, which is simply a list of all possible appointment times. This allows the crosstab query datasheet to display rows for those times when there are no appointments scheduled and allows the user to easily see when appointments are available. The crosstab query datasheet should always have exactly 14 rows (8am through 9pm). Here's the crosstab query that's based on the union query: TRANSFORM Last([Query: Union1].AName) AS LastOfAName SELECT [Query: Union1].ATime FROM [Query: Union1] GROUP BY [Query: Union1].ATime ORDER BY [Query: Union1].ATime, "Day" & DateDiff("d",DateAdd("d",1-Weekday(Date()),Date())+8,[ADate]) PIVOT "Day" & DateDiff("d",DateAdd("d",1-Weekday(Date()),Date())+8,[ADate]) In ("Day0","Day1","Day2","Day3","Day4","Day5"); Everything works great EXCEPT if someone has an appointment that starts at 10am, 1pm, 4pm, or 7pm. If an appointment is scheduled for any of those four times, an extra row appears. For example, if someone has a 10am appointment, the datasheet will display two rows for 11am, one with the name of the person with the appointment and a blank row. A 1pm appointment creates two 2pm rows. A 4pm appointment creates two 5pm rows. A 7pm appointment creates two 8pm rows. In an effort to track this down, I am only using one record in Table: AData. I've been changing the appointment time around for that one record and then viewing the crosstab datasheet and only get that extra row when the record's appointment time (ATime) is 10am, 1pm, 4pm, or 7pm. Any other appointment start time provides the expected results with no extra rows. I'd greatly appreciate any ideas on what might be going on. Thanks. Tman |
#2
|
|||
|
|||
Crosstab query oddity
I don't usually format in a query but you could try
SELECT ADate, Format(ATime,"hh:nn") , AName, ALoc FROM [Table: AData] WHERE [ALoc] = "VProd1" UNION SELECT ADate, Format([ATime]+(1/24),"hh:nn"), AName, ALoc FROM [Table: AData] WHERE [ALoc] = "VProd1" UNION SELECT ADate, Format(ATime,"hh:nn") , AName, ALoc FROM [Table: TimeFrames]; -- Duane Hookom MS Access MVP -- "Tman" wrote in message ... On Sat, 3 Jul 2004 22:46:25 -0500, "Duane Hookom" wrote: I expect this is a rounding error. What happens if you try: UNION SELECT ADate, DateAdd("h",1, [ATime]), AName, ALoc Yes, I did try that along the way and still had the same problem. During the countless hours of staring at the query, I kept wondering if the calculation of adding one hour was causing the problem and tried the DateAdd method with the same results. It seems as though the calculated 11:00:00am does not exactly equal 11:00:00am stored in the TimeFrames table, so I get the extra row. An appointment at 9:59:59am or 10:00:01am works fine, an appointment at exactly 10:00:00am doesn't. Have you looked at the Week at a glance type calendar reports at http://www.invisibleinc.com/divFiles.cfm?divDivID=4? Yes, I have. Quite fascinating indeed. Ultimately, I'm going to need to display my crosstab data as well as two other sets of crosstab data as subforms on a main form that will include several other things including buttons to requery the subforms data to show appointments this week or two weeks from now, etc. Thanks. Duane Hookom MS Access MVP -- "Tman" wrote in message .. . I'm stumped and baffled by a strange problem I'm having with a crosstab query. My crosstab query creates a grid showing appointments for the following week. The result shows seven columns: Appointment Time followed by Day0 through Day6 (Monday through Saturday for the following week). The values in the rows represent the start times of the appointments. At the column and row intersections, the name of the person with the appointment is displayed. ATime Day0 Day1 Day2 Day3 Day4 Day5 Day6 8:00:00am 9:00:00am 10:00:00am etc. I start with the following union query (Query: Union1): SELECT ADate, ATime, AName, ALoc FROM [Table: AData] WHERE [ALoc] = "VProd1" UNION SELECT ADate, [ATime]+(1/24), AName, ALoc FROM [Table: AData] WHERE [ALoc] = "VProd1" UNION SELECT ADate, ATime, AName, ALoc FROM [Table: TimeFrames]; The field ATime is the start time of the appointment. Appointments are in two-hour blocks, so the first union in the query creates an additional row for the second hour of the appointment. In other words, if Smith has an appointment at 2pm, the resulting crosstab query datasheet will show Smith in the 2pm row and the 3pm row. The second union adds data from Table: TimeFrames, which is simply a list of all possible appointment times. This allows the crosstab query datasheet to display rows for those times when there are no appointments scheduled and allows the user to easily see when appointments are available. The crosstab query datasheet should always have exactly 14 rows (8am through 9pm). Here's the crosstab query that's based on the union query: TRANSFORM Last([Query: Union1].AName) AS LastOfAName SELECT [Query: Union1].ATime FROM [Query: Union1] GROUP BY [Query: Union1].ATime ORDER BY [Query: Union1].ATime, "Day" & DateDiff("d",DateAdd("d",1-Weekday(Date()),Date())+8,[ADate]) PIVOT "Day" & DateDiff("d",DateAdd("d",1-Weekday(Date()),Date())+8,[ADate]) In ("Day0","Day1","Day2","Day3","Day4","Day5"); Everything works great EXCEPT if someone has an appointment that starts at 10am, 1pm, 4pm, or 7pm. If an appointment is scheduled for any of those four times, an extra row appears. For example, if someone has a 10am appointment, the datasheet will display two rows for 11am, one with the name of the person with the appointment and a blank row. A 1pm appointment creates two 2pm rows. A 4pm appointment creates two 5pm rows. A 7pm appointment creates two 8pm rows. In an effort to track this down, I am only using one record in Table: AData. I've been changing the appointment time around for that one record and then viewing the crosstab datasheet and only get that extra row when the record's appointment time (ATime) is 10am, 1pm, 4pm, or 7pm. Any other appointment start time provides the expected results with no extra rows. I'd greatly appreciate any ideas on what might be going on. Thanks. Tman |
#3
|
|||
|
|||
Crosstab query oddity
Thanks for the nice comment. I'm happy to be of assistance...
-- Duane Hookom MS Access MVP "Tman" wrote in message ... On Sat, 3 Jul 2004 23:46:10 -0500, "Duane Hookom" wrote: I don't usually format in a query but you could try SELECT ADate, Format(ATime,"hh:nn") , AName, ALoc FROM [Table: AData] WHERE [ALoc] = "VProd1" UNION SELECT ADate, Format([ATime]+(1/24),"hh:nn"), AName, ALoc FROM [Table: AData] WHERE [ALoc] = "VProd1" UNION SELECT ADate, Format(ATime,"hh:nn") , AName, ALoc FROM [Table: TimeFrames]; -- Duane Hookom MS Access MVP Well, it looks like you got me over the hump with the idea of formatting the ATime field. That eliminated the problem with the extra rows but it also messed with the sort order of the appointment times in the left column (sorted the appointment times alpha-numerically), so I took it a step further using CDate and now all appears to be working properly: SELECT ADate, CDate(Format([ATime],"h:nn am/pm")) as ATime2, AName, ALoc FROM [Table: AData] WHERE [ALoc] = "VProd1" UNION SELECT ADate, CDate(Format([ATime]+(1/24),"h:nn am/pm")) as ATime2, AName, ALoc FROM [Table: AData] WHERE [ALoc] = "VProd1" UNION SELECT ADate, CDate(Format([ATime],"h:nn am/pm")) as ATime2, AName, ALoc FROM [Table: TimeFrames]; Thanks for your help and I should also thank you for the direction on handling dates as crosstab columns you have provided others in the past. That helped me big time as well. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Crosstab query with same VALUE in multiple columns | ChrisJ | Running & Setting Up Queries | 1 | June 20th, 2004 10:41 PM |
Crosstab query showing months | Jeff Gilstrap | Running & Setting Up Queries | 2 | June 19th, 2004 03:02 PM |
Reference Main Report Crosstab Query from Subreport | blinton25 | Setting Up & Running Reports | 0 | June 7th, 2004 11:42 PM |
Showing all subrows in crosstab query | Ragnar Midtskogen | Running & Setting Up Queries | 3 | May 26th, 2004 08:16 PM |
Dates In a CrossTab query | Box 666 | New Users | 1 | May 21st, 2004 04:01 AM |