Doyce,
You shouldn't need to "create a week number table". Can't you create a group by/totals query based on tblWeekStartDates that would function as a week number table?
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 11:54:14 -0700
Subject: RE: [MS_AccessPros] How to link to a table with start dates
Duane,
Just hit a snag. I'm going to use this query to populate a graph. I want include the weeks that have zero records to show since it will be a line graph. I'm grouping on WStartDate and that looks good except for the weeks where the count would be zero. I think I can create a week number table and link to it with a left join but I wonder if there is a better way other than creating and linking another table?
Doyce
---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :
You shouldn't need to "create a week number table". Can't you create a group by/totals query based on tblWeekStartDates that would function as a week number table?
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 11:54:14 -0700
Subject: RE: [MS_AccessPros] How to link to a table with start dates
Duane,
Just hit a snag. I'm going to use this query to populate a graph. I want include the weeks that have zero records to show since it will be a line graph. I'm grouping on WStartDate and that looks good except for the weeks where the count would be zero. I think I can create a week number table and link to it with a left join but I wonder if there is a better way other than creating and linking another table?
Doyce
---In MS_Access_Professionals@yahoogroups.com, <winberry.doyce@...> wrote :
Duane,
No I didn't. Just week no's and week start dates. Now I understand what you mean. Great idea and simple!
Thanks!!
Doyce
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
No I didn't. Just week no's and week start dates. Now I understand what you mean. Great idea and simple!
Thanks!!
Doyce
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
Doyce,
Did you add a date field to tblWeekStartDates so there is a record for every date? If so, just join the unique date field from tblWeekStartDates to PICKUPDATE.
Duane Hookom MVP
MS Access
Did you add a date field to tblWeekStartDates so there is a record for every date? If so, just join the unique date field from tblWeekStartDates to PICKUPDATE.
Duane Hookom MVP
MS Access
To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 09:53:46 -0700
Subject: RE: [MS_AccessPros] How to link to a table with start dates
Duane,
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
From: MS_Access_Professionals@yahoogroups.com
Date: Wed, 29 Apr 2015 09:53:46 -0700
Subject: RE: [MS_AccessPros] How to link to a table with start dates
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: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (7) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar