Query Question

Here is what I have so far.  But when I try to run the query I get an invalid bracketing error:

SELECT tblProjectPurchaseOrders.ProjectID, tblProjectPurchaseOrders.POnumber, tblProjectPurchaseOrders.Code, tblProjectPurchaseOrders.POValue, tblProjectPurchaseOrders.Distribution, [POValue]-[Distribution] AS Result, Min(tblProjectPurchaseOrders.Start) AS MinOfStart, Max(tblProjectPurchaseOrders.End) AS MaxOfEnd, Round(([End]-[Start])/7,0)+1 AS Duration, tblProjectPurchaseOrders.[Split?], ([POvalue]-[Distribution])/Nz(Round(([End]-[Start])/7,0)+1,0) AS Calculation
FROM tblProjectPurchaseOrders INNER JOIN tblWeekNumbers ON (tblProjectPurchaseOrders.End <= [tblWeekNumbers.End]) AND ([tblProjectPurchaseOrders.Start] >= [tblWeekNumbers.Start])
GROUP BY tblProjectPurchaseOrders.ProjectID, tblProjectPurchaseOrders.POnumber, tblProjectPurchaseOrders.Code, tblProjectPurchaseOrders.POValue, tblProjectPurchaseOrders.Distribution, [POValue]-[Distribution], Round(([tblProjectPurchaseOrders.End]-[Start])/7,0)+1, tblProjectPurchaseOrders.[Split?], ([POvalue]-[Distribution])/Nz(Round(([tblProjectPurchaseOrders.End]-[Start])/7,0)+1,0), (Int(DateDiff("d",[End],[tblProjectPurchaseOrders.Start])/7)*-1)+1, ([POvalue]-[Distribution])/((Int(DateDiff("d",[tblProjectPurchaseOrders.End],[Start])/7)*-1)+1)
HAVING (((tblProjectPurchaseOrders.POValue)>0))
ORDER BY tblProjectPurchaseOrders.POnumber;

Select all Open in new window

Is there a way to do this with an expression field in the query?  Something like this (which of course is "syntaxly" incorrect?

Week: = DLookup("[WeekN]", "tblWeekNumbers", "[Start] = Between [Start] and [End])

Yes, you can use a where clause rather than a join but then you would be doing a cross join which produces a Cartesian Product.  All the rows of tblA joined to every row of tblB  and then the resultset is whittled down by the where clause.  This query would be not updateable and depending on the row count could take substantially longer.  So, 1000 rows in tblA and 365 rows in tblB = 365,000 rows in the result set to which the where clause would be applied.

I would not under any condition use a domain function as in your example.  The domain function forces Access to run a separate query for every row of tblA and it gives the query optimizer no way to optimize.  So, 1567 rows in tblA would require running 1567 queries to do the lookup.

WHERE vs HAVING.  Where is applied BEFORE any aggregation so it reduces the rows that go into the query whereas HAVING is applied AFTER the aggregation and so reduces the rows that come out of the query.  HAVING is only used on aggregated data.  It doesn't make sense to use it on non-aggregated data so it is typically used with counts or averages and the like.  In your case, your query should be using a WHERE and not a HAVING.

It would look something like:

SELECT T.*, W.WeekN
FROM yourTable as T
LEFT JOIN WeekNumbers as W
ON T.Start >= W.Start AND T.Start < DateAdd("d", 1, W.End)

Not this is a non-equi JOIN, so it will not show up in the query grid and if you attempt to view the query grid, it may break the join.  At the very least, it will display a message that indicates it cannot display the join criteria.

This was my first suggestion (although I see now that there was a type).  Steve asked for a different method and I was telling him why this was best.

You need no join at all, and you can easily build a query using the GUI designer using this skeleton:

Select 
    YourQuery.*,
    tblWeekNumbers.WeekN
From
    YourQuery,
    tblWeekNumbers
Where
    YourQuery.Start Between tblWeekNumbers.Start And tblWeekNumbers.End

Select all Open in new window

Of course, you may be able to integrate this in YourQuery so you don't need an additional query.

/Gustav

Sorry for the delay responding to the suggestions.  I'm going to try to get back to this tomorrow.