Cross-Midnight Shift Problem

[daniel.ru92]

Query is supposed to pull all the records from that shift. Each work shift begins at 8 am and ends at 8 the next morning. At any time during the shift in which the query runs, it pull all the records from that day and if the current time after midnight, the records yesterday.


After I enabled query design error:
"The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement."

Here is the SQL of query:
SELECT Deliveries.ReceptionTime, Customers.FullName, [StreetName] & " " & [Deliveries.ApartNumber] & " / " & [Deliveries.HouseNumber] & [Deliveries.EntryNumber] & ", " & IIf([Deliveries.IsUnit]=True,"unit, ","") & [CityName] AS Address, Deliveries.PayByCreditCard, Deliveries.IsPrepaid, Deliveries.TotalPrice, Deliveries.Comments
FROM Cities LEFT JOIN ((Customers LEFT JOIN Deliveries ON Customers.[CustomerID] = Deliveries.[CustomerID]) INNER JOIN Streets ON Deliveries.StreetID = Streets.StreetID) ON Cities.CityID = Streets.CityID
WHERE (((Deliveries.ReceptionTime)="Date()") AND ((DatePart([h],Now()))>8) AND ((DatePart([h],[ReceptionTime]))>8)) OR (((Deliveries.ReceptionTime)="Date()") AND ((DatePart([h],Now()))<8) AND ((DatePart([h],[ReceptionTime]))<8)) OR (((Deliveries.ReceptionTime)=DateAdd("d",-1,Date())) AND ((DatePart([h],Now()))<8) AND ((DatePart([h],[ReceptionTime]))>8));

Here is Printscreen of the query:



BIG THANKS GUYS!!!

[June7]

Try changing the INNER join between Streets and Deliveries to outer type (LEFT or RIGHT).

[daniel.ru92]

Nope...
"Syntax error in JOIN operation."

[June7]

You clicked on the line that represents the link and opened the dialog box to change the join type?

[daniel.ru92]

I could not figure out how to do it ... Quite new in SQL...

[June7]

Open query in design view. Click on the line that connects the tables. Make choice "Include ALL records from Deliveries..."

[daniel.ru92]

I did it and still the same problem ...
"The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement."

SQL Code:
SELECT Deliveries.ReceptionTime, Customers.FullName, [StreetName] & " " & [Deliveries.ApartNumber] & " / " & [Deliveries.HouseNumber] & [Deliveries.EntryNumber] & ", " & IIf([Deliveries.IsUnit]=True,"unit, ","") & [CityName] AS Address, Deliveries.PayByCreditCard, Deliveries.IsPrepaid, Deliveries.TotalPrice, Deliveries.Comments
FROM Cities LEFT JOIN ((Customers LEFT JOIN Deliveries ON Customers.[CustomerID] = Deliveries.[CustomerID]) LEFT JOIN Streets ON Deliveries.StreetID = Streets.StreetID) ON Cities.CityID = Streets.CityID
WHERE (((Deliveries.ReceptionTime)=Date()) AND ((DatePart([h],Now()))>8) AND ((DatePart([h],[ReceptionTime]))>8)) OR (((Deliveries.ReceptionTime)=Date()) AND ((DatePart([h],Now()))<8) AND ((DatePart([h],[ReceptionTime]))<8)) OR (((Deliveries.ReceptionTime)=DateAdd("d",-1,Date())) AND ((DatePart([h],Now()))<8) AND ((DatePart([h],[ReceptionTime]))>8));

[June7]

The arrow direction between Deliveries and Streets looks backwards.

Did you select "Include ALL records from Deliveries ..."?

Select and delete the line.

Click on StreetID in Streets and drag to StreetID in Deliveries.

Then select the jointype.

[daniel.ru92]

I did so, I did the opposite, I did every possible way and it keeps the same error. There could be a problem that I set up two columns in a query with one of them even not up on tables (up on date), and on these columns I run the filter of rows??

[June7]

Sorry, I hadn't even looked at the filter criteria. Does the query open if there are no criteria? I really can't see any reason all these tables cannot be joined in query.

Filter criteria can be applied to calculated fields.

If you want to provide db for analysis, follow instructions at bottom of my post.

[jwreding]

Is it just me, or do the joins on Customers/Deliveries and Cities/Streets look like they're going in the wrong direction?

First, is there a need for an outer join between Customers and Deliveries? Do you ever have any records in Deliveries that don't have a corresponding Customer record? Is your entry form for Deliveries based on a query that only displays Customer records already in the table? If so, then you can change this to an inner join by double clicking the join line and choosing option 1.

The error message is telling you it doesn't like the outer joins you have, so if you take care of that one then maybe it will cooperate. If not, you may need to look at the Cities/Streets join and either change its direction (which I'd do anyway), or convert it also to an inner join.