Only include fields not equal 1

[surfbum3000]

I have 2 tables: tblReservedTime & tblAppts. I need to exclude the ReservedTime record if there is an appt. record in tblAppts. Both tables have the following fields in common:

Date
Location
EMP_ID
LastName
FirstName
StartTime
EndTime

I have tried the unmatched query wizard and then tried joining both tables on the above listed fields but I keep getting reserved time that has an appt. If the reserved time has an appt. in that slot, I want to exclude it.

 

[SkipVought]

Hi,

Could you post an example from both tables to illustrate which rows would be excluded?



[tt][highlight blue][white]
^{~ ~}[/white][/highlight][highlight red][white] To be ^~[/white][/highlight][highlight blue][white]
^~ BE ^~[/white][/highlight][highlight white][blue] safe on the 4^{th ~}[/blue][/highlight][highlight blue][white]
^~ ADVISED ^~[/white][/highlight][highlight red][white] Don't ^~[/white][/highlight][highlight blue][white]
^{~ ~}[/white][/highlight][highlight white][blue] get a ^~[/blue][/highlight]
[highlight red][white] 5^th on the 3^{rd ~}[/white][/highlight]
[highlight white][blue] Or you might not ^~[/blue][/highlight]
[highlight red][white] Come 4^th on the 5^th 4^thwith ^~[/white][/highlight]
[/tt]


Skip,

_[red][/red]

 

[surfbum3000]

What I need is a table that shows only the reserved time available. If the tables have the following fields that equal each other, then exclude it from the query. Here is an example:

tblReservedTime tblAppts
6/30/2006 6/30/2006
Location Location
EMP_ID EMP_ID
StaffLastName StaffLastName
StaffFirstName StaffFirstName
StartTime StartTime
EndTime EndTime

There are some other fields in the tblAppts not included in the tblReservedTime: ClientName, CaseNo, Subject,
service_id

"If you want to see the 6th, don't drink a 5th on the 4th

 

[PHV]

Something like this (SQL code)?
SELECT R.*
FROM tblReservedTime R LEFT JOIN tblAppts A
ON R.Date = A.Date AND R.Location = A.Location AND R.EMP_ID = A.EMP_ID AND R.StaffLastName = A.StaffLastName
AND R.StaffFirstName = A.StaffFirstName AND R.StartTime = A.StartTime AND R.EndTime = A.EndTime
WHERE A.EMP_ID IS NULL

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[surfbum3000]

I had to change some of the field names. The results still show reserved time but the reserved time has an appt. in it. Did I make an error?

tblxReservedTimeIntake (R) tblxAppts2Weeks (A)
STARTDATE STARTDATE
LOCATE_ID LOCATE_ID
EMP_ID EMP_ID
tblAnasaziStaff_LAST_NAME tblAnasaziStaff_LAST_NAME
tblAnasaziStaff_FIRST_NAME tblAnasaziStaff_FIRST_NAME
STARTTIME STARTTIME
ENDTIME ENDTIME


SELECT R.*
FROM tblxReservedTimeIntake AS R LEFT JOIN tblxAppts2Weeks AS A ON (R.ENDTIME = A.ENDTIME) AND (R.STARTTIME = A.STARTTIME) AND (R.tblAnasaziStaff_FIRST_NAME = A.tblAnasaziStaff_FIRST_NAME) AND (R.tblAnasaziStaff_LAST_NAME = A.tblAnasaziStaff_LAST_NAME) AND (R.EMP_ID = A.EMP_ID) AND (R.LOCATE_ID = A.LOCATE_ID) AND (R.STARTDATE = A.STARTDATE)
WHERE A.EMP_ID IS NULL;

 

[PHV]

The results still show reserved time but the reserved time has an appt. in it
Are the 7 joined fields equal for those unexpected records ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[surfbum3000]

Yes, the following fields are equal:

tblxReservedTimeIntake (R) tblxAppts2Weeks (A)
STARTDATE STARTDATE
LOCATE_ID LOCATE_ID
EMP_ID EMP_ID
tblAnasaziStaff_LAST_NAME tblAnasaziStaff_LAST_NAME
tblAnasaziStaff_FIRST_NAME tblAnasaziStaff_FIRST_NAME
STARTTIME STARTTIME
ENDTIME ENDTIME

There are some other fields in the tblxAppts2Weeks that are not in the tblxReservedTimeIntake table such as CaseNumber, ClientName, Subject, ServiceID since an appt. has been made in that time slot.

 

[PHV]

Hopefully no one of this 7 fields holds Null value, isn't it ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[surfbum3000]

Hopefully no one of this 7 fields holds Null value, isn't it ?"

That is correct. No null values in any field.

 

[PHV]

OK, so why not post some sample values ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[surfbum3000]

tblxReservedTimeIntake

STARTDATE: 7/3/2006
LOCATE_ID: NWMH
INTAKE_ID: ADICOC
EMP_ID: 10710
tblAnasaziStaff_LAST_NAME: DOE
tblAnasaziStaff_FIRST_NAME: JOHN
STARTTIME: 10:00
ENDTIME: 11:00

tblxAppts2Weeks

STARTDATE: 7/3/2006
LOCATE_ID: NWMH
SVC_ID: 1100
SUBJECT: This is the subject text area
STARTTIME: 10:00
ENDTIME: 11:00
CASE_NUM: 123456
tblCaseNos_LAST_NAME: Patient Last Name
tblCaseNos_FIRST_NAME: Patient First Name
MI: X
EMP_ID: 10710
tblAnasaziStaff_LAST_NAME: DOE
tblAnasaziStaff_FIRST_NAME: JOHN

 

[PHV]

And the SQL code ?

 

[surfbum3000]

SELECT R.*
FROM tblxReservedTimeIntake AS R LEFT JOIN tblxAppts2Weeks AS A ON (R.ENDTIME = A.ENDTIME) AND (R.STARTTIME = A.STARTTIME) AND (R.tblAnasaziStaff_FIRST_NAME = A.tblAnasaziStaff_FIRST_NAME) AND (R.tblAnasaziStaff_LAST_NAME = A.tblAnasaziStaff_LAST_NAME) AND (R.EMP_ID = A.EMP_ID) AND (R.LOCATE_ID = A.LOCATE_ID) AND (R.STARTDATE = A.STARTDATE)
WHERE A.EMP_ID IS NULL;

 

[surfbum3000]

The error seems to be WHERE A.EMP_ID IS NULL;

The EMP_ID is never null in both the tables. I have tried using SUBJECT, CASE_NUM, SVC_ID, tblCaseNos_LAST_NAME,
tblCaseNos_FIRST_NAME and MI since these are populated in the Appt. table but not in the Reserved Time table.

 

[PHV]

The EMP_ID is never null in both the tables
Seems you don't know how OUTER joins are working ...
Are the 7 fields EXACTLY the same type in BOTH tables ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[surfbum3000]

Are the 7 fields EXACTLY the same type in BOTH tables "
I verified all fields are data type text. My results continue to show reserved time even though there is an appt. scheduled at that time.

SELECT R.*
FROM tblxReservedTimeIntake AS R LEFT JOIN tblxAppts2Weeks AS A ON (R.ENDTIME = A.ENDTIME) AND (R.STARTTIME = A.STARTTIME) AND (R.tblAnasaziStaff_FIRST_NAME = A.tblAnasaziStaff_FIRST_NAME) AND (R.tblAnasaziStaff_LAST_NAME = A.tblAnasaziStaff_LAST_NAME) AND (R.EMP_ID = A.EMP_ID) AND (R.LOCATE_ID = A.LOCATE_ID) AND (R.STARTDATE = A.STARTDATE)
WHERE A.EMP_ID IS NULL;

 

[PHV]

What is the PrimaryKey of tblxAppts2Weeks ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[surfbum3000]

Neither table has an assigned Primary Key.

 

[PHV]

Neither table has an assigned Primary Key
Really ?
My last attempt:
SELECT R.*
FROM tblxReservedTimeIntake AS R LEFT JOIN tblxAppts2Weeks AS A ON (R.ENDTIME = A.ENDTIME) AND (R.STARTTIME = A.STARTTIME) AND (R.tblAnasaziStaff_FIRST_NAME = A.tblAnasaziStaff_FIRST_NAME) AND (R.tblAnasaziStaff_LAST_NAME = A.tblAnasaziStaff_LAST_NAME) AND (R.EMP_ID = A.EMP_ID) AND (R.LOCATE_ID = A.LOCATE_ID) AND (R.STARTDATE = A.STARTDATE)
WHERE A.ENDTIME IS NULL AND A.STARTTIME IS NULL AND A.tblAnasaziStaff_FIRST_NAME IS NULL AND A.tblAnasaziStaff_LAST_NAME IS NULL AND A.EMP_ID IS NULL AND A.LOCATE_ID IS NULL AND A.STARTDATE IS NULL;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

why would you NOT have a Primary Key? Additionally, why have duplicate data (storing the same information in multiple locations)? have you read the fundamentals document linked below?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi