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!
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:
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?
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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;
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])
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.
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.
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:
/Gustav
Select
YourQuery.*,
tblWeekNumbers.WeekN
From
YourQuery,
tblWeekNumbers
Where
YourQuery.Start Between tblWeekNumbers.Start And tblWeekNumbers.End
/Gustav
Premium Content
You need an Expert Office subscription to comment.Start Free Trial