Query Question

SteveL13 used Ask the Experts™
I have a query that has a field named "Start".  I also have a separate table named tblWeekNumbers.  This table looks like this:

Note that week 1 is from 8/16/2015 through 8/22/2015, Week 2 is from 8/23/2015 through 8/29/2015, etc.  The dates and the PK number in this table will never change.

Now in the query I need to add a field named "WeeK"  If the date in the field "Start" in the query is between 8/16/2015 and 8/22/2015 then I want the new field in the query named "Week" to show "1".  If the date in the field "Start" in the query is between 8/23/2015 and 8/29/2015 then I want the new field in the query named "Week" to show "2", etc.

How can I add this field and value to the query?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
You have to use a non equi-join.  Once you do this, you will only be able to view the query in SQL view since QBE view can only show equi-joins.

Start with the queyr in QBE view and draw join lines from the date in table1 to the  start date in the week table and also from the date in table1 to the end date in the week table.

Switch to SQL view and change the relational operators from "=" to ".=" for the Start date and to "<=" for the end date.


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;

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])
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017

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.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

It would look something like:

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.
Distinguished Expert 2017

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.
Most Valuable Expert 2015
Distinguished Expert 2018

You need no join at all, and you can easily build a query using the GUI designer using this skeleton:
    YourQuery.Start Between tblWeekNumbers.Start And tblWeekNumbers.End

Open in new window

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



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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial