Query Conversion

  • Thread starter NeonSky via AccessMonster.com
  • Start date
N

NeonSky via AccessMonster.com

Hello,

The below query works just fine with my sample dataset, though with my live
dataset (100k records) it takes far to long to crunch. I was curious if
anyone might be able to offer insight into a way to re-write the below query
to improve efficiency? Thank you for your time and consideration!

SELECT TempB.Name, TempB.Address, TempB.Arrival, TempB.Departure, TempB.
RoomType, TempB.RCODE, TempB.Confo, 1<>(SELECT COUNT (*)
FROM tblRawReservationData As TempA
WHERE TempA.Name = TempB.Name
AND TempA.Address = TempB.Address
AND TempA.Arrival = TempB.Arrival
AND TempA.Departure = TempB.Departure
AND TempA.RoomType = TempB.RoomType
AND TempA.RCODE = TempB.RCODE) AS ConcurrentCode INTO tblReservationData
FROM tblRawReservationData AS TempB;
 
N

NeonSky via AccessMonster.com

Hello everyone, I actually figured out a workaround which seems to be working
just fine. (Instead of self referencing I created a duplicate record set)
 
J

John Spencer

PERHAPS the following will work for you

SELECT TempB.Name, TempB.Address, TempB.Arrival, TempB.Departure, TempB.
RoomType, TempB.RCODE, TempB.Confo, ConcurrentCode
INTO tblReservationData
FROM
(SELECT True as ConcurrentCode
, T.Name, T.Address, Arrival
, Departure, RoomType, RCode
FROM tblRawReservationData as T
GROUP BY True, T.Name, T.Address, Arrival
, Departure, RoomType, RCode
HAVING Count(*) > 1) as TempA
INNER JOIN tblRawReservationData AS TempB
ON TempA.Name = TempB.Name
AND TempA.Address = TempB.Address
AND TempA.Arrival = TempB.Arrival
AND TempA.Departure = TempB.Departure
AND TempA.RoomType = TempB.RoomType
AND TempA.RCODE = TempB.RCODE

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
N

NeonSky via AccessMonster.com

Hello John, certainly a well thought out solution. Thank you.
John said:
PERHAPS the following will work for you

SELECT TempB.Name, TempB.Address, TempB.Arrival, TempB.Departure, TempB.
RoomType, TempB.RCODE, TempB.Confo, ConcurrentCode
INTO tblReservationData
FROM
(SELECT True as ConcurrentCode
, T.Name, T.Address, Arrival
, Departure, RoomType, RCode
FROM tblRawReservationData as T
GROUP BY True, T.Name, T.Address, Arrival
, Departure, RoomType, RCode
HAVING Count(*) > 1) as TempA
INNER JOIN tblRawReservationData AS TempB
ON TempA.Name = TempB.Name
AND TempA.Address = TempB.Address
AND TempA.Arrival = TempB.Arrival
AND TempA.Departure = TempB.Departure
AND TempA.RoomType = TempB.RoomType
AND TempA.RCODE = TempB.RCODE

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Hello,
Click to expand...
[quoted text clipped - 13 lines]
AND TempA.RCODE = TempB.RCODE) AS ConcurrentCode INTO tblReservationData
FROM tblRawReservationData AS TempB;
Click to expand...
Click to expand...
 
Top