Solved

Find missing records in one table excluding cancelled items

Posted on 2016-08-25
4
33 Views
Last Modified: 2016-08-25
Hi, I need to find all entries in table Hotelogix which are not existing in table Reservations but excluding cancelled records in Reservations. I have tried the following statement:

SELECT HotelogixReservations.[Group Id], HotelogixReservations.[Guest Name]
FROM HotelogixReservations LEFT JOIN QueryFull ON Ltrim(Rtrim(HotelogixReservations.[Group ID])) = QueryFull.[Group Reservation No]
WHERE (((QueryFull.[Group Reservation No]) Is Null)) OR QueryFull.Cancel=TRUE;

This almost works but gives an error in the case where I have the same reservation in Reservations table twice (once as cancelled and once as an open one). Not sure how to exclude any cancelled records in reservation? Thank you for any help..
0
Comment
Question by:mpim
4 Comments
 
LVL 1

Accepted Solution

by:
rquaglia earned 500 total points
Comment Utility
Hi!
looking at you question I suggest to try something like:
SELECT HotelogixReservations.[Group Id], HotelogixReservations.[Guest Name]
FROM HotelogixReservations
WHERE Ltrim(Rtrim(HotelogixReservations.[Group ID])) NOT IN (select [Group Reservation No] from QueryFull where Cancel<>TRUE);

Sorry, I can't try this but anyway I hope this helps.
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
Can you provide some sample data and expected result?
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
The problem with your initial query is the criteria against the "right" table.  The subselect solves that problem.  However, if you are running the query in Access subselects are not well optimized so you could make a query that selects the "right" table and applies the criteria.  Then left join to that query.  Access would then apply the criteria to the "right" table PRIOR to performing the left join and that solves the problem.
1
 

Author Closing Comment

by:mpim
Comment Utility
Great help! Thank you Michael
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now