Duane,
I've created the table with the dates but I still need help linking the SQL. Here is the SQL of the Query now:
SELECT "Doubles" AS Type, Count([New Primary].UNITID) AS CountOfUNITID, DatePart("ww",[PICKUPDATE],1) AS ProdWeek, Year([PICKUPDATE]) AS ProdYear
FROM [New Primary] LEFT JOIN TAG ON [New Primary].UNITID = TAG.[UNIT #]
WHERE (((TAG.TagException)=False)) OR (((TAG.TagException)=False Or (TAG.TagException) Is Null) AND ((TAG.[DATE TAG]) Is Null))
GROUP BY DatePart("ww",[PICKUPDATE],1), Year([PICKUPDATE])
HAVING (((Year([PICKUPDATE]))=Year(Now()))) OR (((Year([PICKUPDATE]))=Year(Now())));
SELECT "Doubles" AS Type, Count([New Primary].UNITID) AS CountOfUNITID, DatePart("ww",[PICKUPDATE],1) AS ProdWeek, Year([PICKUPDATE]) AS ProdYear
FROM [New Primary] LEFT JOIN TAG ON [New Primary].UNITID = TAG.[UNIT #]
WHERE (((TAG.TagException)=False)) OR (((TAG.TagException)=False Or (TAG.TagException) Is Null) AND ((TAG.[DATE TAG]) Is Null))
GROUP BY DatePart("ww",[PICKUPDATE],1), Year([PICKUPDATE])
HAVING (((Year([PICKUPDATE]))=Year(Now()))) OR (((Year([PICKUPDATE]))=Year(Now())));
The fields in tblWeekStartDates are ID, WeekNo, PYear and WStartDate. ProdWeek would link to WeekNo, ProdYear to PYear and I would like to add WStartDate to the query.
Doyce
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
I would keep this super simple by creating the tblWeekStartDates with all dates. You can add columns like [IsHoliday] etc.
If you view the SQL of a query, you can modify the join to use expressions rather than just field names.
Duane Hookom, MVP
MS Access
If you view the SQL of a query, you can modify the join to use expressions rather than just field names.
Duane Hookom, MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 09:31:41 -0700
Subject: [MS_AccessPros] How to link to a table with start dates
Hello friends,
I have created a table with Week Numbers, Years, and a Weekly Start Date. It is tblWeekStartDates. I want to create a query that uses datepart to extract a week number and year from a production date in order to group by week and then link it back to the week number table to get a weekly start date for that week's production. I know I can do this by creating a query and extracting the week number and year and then use that query as the record source for another query to link to tblWeekStartDates to get the start date but I wonder if that is the best way or if there is a way to link tblWeekStartDates to the original query?
Doyce
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 09:31:41 -0700
Subject: [MS_AccessPros] How to link to a table with start dates
Hello friends,
I have created a table with Week Numbers, Years, and a Weekly Start Date. It is tblWeekStartDates. I want to create a query that uses datepart to extract a week number and year from a production date in order to group by week and then link it back to the week number table to get a weekly start date for that week's production. I know I can do this by creating a query and extracting the week number and year and then use that query as the record source for another query to link to tblWeekStartDates to get the start date but I wonder if that is the best way or if there is a way to link tblWeekStartDates to the original query?
Doyce
__._,_.___
Posted by: winberry.doyce@roadsysinc.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
.
__,_._,___
