Rabu, 29 April 2015

RE: [MS_AccessPros] How to link to a table with start dates

 

Duane,

Yes. Got it! Thanlks for your help today!! 
Doyce



---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

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 :

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 :

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
 
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,
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 (8)

.

__,_._,___

Tidak ada komentar:

Posting Komentar