Rabu, 29 April 2015

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())));
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
 
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


__._,_.___

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)

.

__,_._,___

Tidak ada komentar:

Posting Komentar