Query Question

I have a query that has a field named "Start".  I also have a separate table named tblWeekNumbers.  This table looks like this:

WeekNumbers
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?
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
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

SteveL13Author Commented:
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])
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

PatHartmanCommented:
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 LLCCommented:
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.
PatHartmanCommented:
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.
Gustav BrockCIOCommented:
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

Open in new window

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

/Gustav
SteveL13Author Commented:
Sorry for the delay responding to the suggestions.  I'm going to try to get back to this tomorrow.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.