Crosstab Problem

  • Thread starter Thread starter Smurf
  • Start date Start date
S

Smurf

Hi

For some reason or another I can't seem to generate a Crosstab from this
query
SELECT 'Monday' AS DayName, ttable.[period#], [period_time], description
FROM ttable, l_room
WHERE (((ttable.[period#]) Not In (select [period#] from sub_l_room where
day = 'Monday' and [l_room#] = l_room.[l_room#])))
and l_room.[l_room#] In (Select [l_room#] from l_room)

Access keeps telling me that it doesn't recognize l_room.[l_room#] as a
valid name or field.

Anyone know what the problem is?
 
Yes I realised that after I posted it. The original query didn't have the
subquery in it, and I forgot to remove that part of the where clause. It
still doesn't work though...

Duane Hookom said:
Wouldn't l_room.[l_room#] In (Select [l_room#] from l_room) always be true?

--
Duane Hookom
MS Access MVP


Smurf said:
Hi

For some reason or another I can't seem to generate a Crosstab from this
query
SELECT 'Monday' AS DayName, ttable.[period#], [period_time], description
FROM ttable, l_room
WHERE (((ttable.[period#]) Not In (select [period#] from sub_l_room where
day = 'Monday' and [l_room#] = l_room.[l_room#])))
and l_room.[l_room#] In (Select [l_room#] from l_room)

Access keeps telling me that it doesn't recognize l_room.[l_room#] as a
valid name or field.

Anyone know what the problem is?
Click to expand...
Click to expand...
 
Please re-post your current query sql. It would help if you posted something
like a crosstab since your original query wasn't a crosstab.

--
Duane Hookom
MS Access MVP


Smurf said:
Yes I realised that after I posted it. The original query didn't have the
subquery in it, and I forgot to remove that part of the where clause. It
still doesn't work though...

Duane Hookom said:
Wouldn't l_room.[l_room#] In (Select [l_room#] from l_room) always be true?

--
Duane Hookom
MS Access MVP


Smurf said:
Hi

For some reason or another I can't seem to generate a Crosstab from this
query
SELECT 'Monday' AS DayName, ttable.[period#], [period_time], description
FROM ttable, l_room
WHERE (((ttable.[period#]) Not In (select [period#] from sub_l_room where
day = 'Monday' and [l_room#] = l_room.[l_room#])))
and l_room.[l_room#] In (Select [l_room#] from l_room)

Access keeps telling me that it doesn't recognize l_room.[l_room#] as a
valid name or field.

Anyone know what the problem is?
Click to expand...
Click to expand...
Click to expand...
 
Original:
SELECT 'Monday' AS DayName, [ttable].[period#],
[ttable].[period_time], [l_room].[description]
FROM ttable, l_room
WHERE (((ttable.[period#]) Not In (select [period#] from sub_l_room
where day = 'Monday' and [l_room#] = l_room.[l_room#])));

Generated Crosstab
TRANSFORM Count([Room Availability].[period#]) AS [CountOfperiod#]
SELECT [Room Availability].period_time, Count([Room
Availability].[period#]) AS [Total Of period#]
FROM [Room Availability]
GROUP BY [Room Availability].period_time
PIVOT [Room Availability].DayName;
 
Apparently the select query works but the crosstab doesn't. You may have to
change the SQL of the original to use an outer join.

--
Duane Hookom
MS Access MVP


Joe said:
Original:
SELECT 'Monday' AS DayName, [ttable].[period#],
[ttable].[period_time], [l_room].[description]
FROM ttable, l_room
WHERE (((ttable.[period#]) Not In (select [period#] from sub_l_room
where day = 'Monday' and [l_room#] = l_room.[l_room#])));

Generated Crosstab
TRANSFORM Count([Room Availability].[period#]) AS [CountOfperiod#]
SELECT [Room Availability].period_time, Count([Room
Availability].[period#]) AS [Total Of period#]
FROM [Room Availability]
GROUP BY [Room Availability].period_time
PIVOT [Room Availability].DayName;


"Duane Hookom" <duanehookom@NO_SPAMhotmail.com> wrote in message
Please re-post your current query sql. It would help if you posted something
like a crosstab since your original query wasn't a crosstab.
Click to expand...
Click to expand...
 
Back
Top