Help with a query 1

[strangeryet]

Hello,

I need some assistance with a query that i am trying to build.

A table contains records which constitute an employee's shift.

There are 7 'Default' records for each employee, along with any number of additional records which will override these default records if the field "XDate" in the record equals the date the form is displaying.

For instance: today is Friday 7/28 and the employee name is Joe; if there are no additional records out in the table for Joe for 7/28, then we will grab his default record for the Day (being Friday). If there is a record for Joe for 7/28, then we will use this record to get his shift start and end times rather than his default record.

Here is an example of what some of the records for Joe or anyone else might look like:

Name: Joe, XDate:"7/28/2006", XDay:Fri, StartTime:xxxx, EndTime:xxxx

Name: Joe, XDate:"Default", XDay:Fri, StartTime:xxxxx, EndTime:xxxxx

Name: Joe, xDate:"Default", XDay:Thr, Startime:xxxxx, EndTime:xxxxx

Name: Joe, XDate:"Default", XDay:Wed, StartTime:xxxx, EndTime:xxxx

etc. four more default records for tue, mon, sat, sun.
And this situation can occur for serveral employees, so I don't want to just grab Joe.

So I need a query that will grab the record with an XDate that matches todays date, if that does not exist then I need to grab the record with the XDate that has the word "Default" in it for the day being displayed, in this case it would be Friday.

By the way, XDate is a text field and not a Date datatype field.

The query below gets what I need, but it includes the Date record and the default record:

SELECT tblPhotographerShifts.Name, tblPhotographerShifts.xDate, tblPhotographerShifts.StartTime, tblPhotographerShifts.EndTime
FROM tblPhotographerShifts
WHERE (((tblPhotographerShifts.xDate)=[@ByDate])) OR (((tblPhotographerShifts.xDate)="Default") AND ((tblPhotographerShifts.Day)=[@Day]))
ORDER BY tblPhotographerShifts.Name, tblPhotographerShifts.xDate;

Thanks for your help!

 

[Golom]

Try this

SELECT P.Name, P.xDate, P.StartTime, P.EndTime
FROM tblPhotographerShifts P
WHERE P.xDate=[@ByDate] 
  AND NOT EXISTS (SELECT 1 FROM tblPhotographerShifts X 
                  WHERE X.Name = P.Name 
                    AND X.xDate="Default" AND X.Day=[@Day])

UNION

SELECT P.Name, P.xDate, P.StartTime, P.EndTime
FROM tblPhotographerShifts P
WHERE P.xDate="Default" AND P.Day=[@Day]
  AND NOT EXISTS (SELECT 1 FROM tblPhotographerShifts X 
                  WHERE X.Name = P.Name AND xDate=[@ByDate])

ORDER BY P.Name, P.xDate;

 

[Golom]

That wasn't quite right was it?

SELECT P.Name, P.xDate, P.StartTime, P.EndTime
FROM tblPhotographerShifts P
WHERE P.xDate=[@ByDate] 
  
UNION

SELECT P.Name, P.xDate, P.StartTime, P.EndTime
FROM tblPhotographerShifts P
WHERE P.xDate="Default" AND P.Day=[@Day]
  AND NOT EXISTS (SELECT 1 FROM tblPhotographerShifts X 
                  WHERE X.Name = P.Name AND xDate=[@ByDate])

ORDER BY P.Name, P.xDate;

 

[strangeryet]

Golom, I think you got it! Thanks very much. I have been working on this for four days! Thank you.