Crosstab query oddity

  • Thread starter Thread starter Tman
  • Start date Start date
T

Tman

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
 
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 said:
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
Click to expand...
 
I expect this is a rounding error. What happens if you try:
UNION SELECT ADate, DateAdd("h",1, [ATime]), AName, ALoc
Click to expand...

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?
Click to expand...

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 said:
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
Click to expand...
Click to expand...
 
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 said:
I expect this is a rounding error. What happens if you try:
UNION SELECT ADate, DateAdd("h",1, [ATime]), AName, ALoc
Click to expand...

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?
Click to expand...

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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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];
Click to expand...

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.
 
Thanks for the nice comment. I'm happy to be of assistance...

--
Duane Hookom
MS Access MVP


Tman said:
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];
Click to expand...

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.
Click to expand...
 
Back
Top