ACCESS FEED: [MS_AccessPros] Re: Monthly Roster report problem

Rabu, 06 Maret 2013

[MS_AccessPros] Re: Monthly Roster report problem

Unfortunately I've uploaded an incorrect db. I'm quickly working on the proper data and should get it uploaded in a few minutes.

--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" wrote:
>
> Russ
>
> I've taken a look at your database and it needs a lot of redesigning. As I suspected, you've made things much more complicated than need be. I'll work on it as much as I can this week and post back when things are looking better.
>
> Using Sunday's date as a kind of week indicator, we should be able to get your roster to work.
>
> In the meantime, please read Crystal's eBook "Access Basics". You can download a free copy from my website at:
> http://www.thatlldoit.com/Pages/AccessBasics.aspx
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
> My nothing-to-do-with-Access blog
> http://wrmosca.wordpress.com
>
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "ghsclass65" wrote:
> >
> > I believe I've made this simpler - at least for someone with more skill at creating reports than what I have. The initial db, "RosterReDo" is probably too confusing to work with. I have collected all the data for a 4 week month report and put it into one table in a file I've downloaded called, RosterReport.zip. The report I am trying unsucessfully to build should be layed out similarly to the blank report in the db zip file.
> >
> > Access typically runs fields from the detail section down into a column which would be tough to read for someone trying to find their monthly duty.
> >
> > In this table each week's assignment is indicated by the number in the week field - week one=1 week two=2 etc. The Time field data indicates if the duty is Monthly=M, AM=O, PM=P, and Wednesday=W.
> >
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "Clive" wrote:
> > >
> > > Hi ghsclass65,
> > >
> > > Try adding the Month field to the reports underlying
> > > query. You can then filter by month. I have also added
> > > a second month field to the query so that you can display
> > > the MonthName instead of the MonthNumber.
> > > the SQL is:
> > >
> > > SELECT tblAssignedRoles.AssDate, tblRoleList.RoleName,
> > > Names.FirstName, Names.LastName, Month([AssDate]) AS Mth,
> > > Format([AssDate],"mmmm") AS MthName
> > > FROM [Names] INNER JOIN (tblRoleList INNER JOIN
> > > tblAssignedRoles ON
> > > tblRoleList.RoleID = tblAssignedRoles.AssRoleID) ON
> > > Names.NameID = tblAssignedRoles.AssNameID
> > > WHERE (((tblAssignedRoles.AssDate)>=#1/1/2001#))
> > > ORDER BY tblAssignedRoles.AssDate;
> > >
> > > Hope that helps.
> > >
> > > Regards, Clive.
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "ghsclass65" wrote:
> > > >
> > > > I uploaded a file called RosterReDo.zip which can be located in Monthly Roster report problem.
> > > > I've got to make this monthly report and would like to do it on one report which is a sample in the zip file. These assignments should be selected on whether they wish to participate in this activity, which would be indicated by a Yes in the Active field, and Yes in each activity's field. Some people cannot do these duties in the evening or on Wednesday, and that can be indicated by Yes or No in those fields.
> > > >
> > > > Each person chosen should be chosen by the latest served date so no one has to accomplish a dudy too often. Also, no one should be chosen for weekly duties if they have been assigned monthly duties, and not more than one duty per week.
> > > >
> > > > I've accomplished this using blank reports filled with subreports over 2 pages, but I've had to manually choose whether it is a 4 week Sunday and 4 week Wednesday month or other combinations of those two days.
> > > > In my database which I've not uploaded I used somwhere around 300 queries, Appending chosen people to 5 (one for each week of the month) different tables, filtered that data and placed that into sub reports, then updated the served date of those individuals so they may not get chosen for the next week's duties.
> > > >
> > > > I was hoping for an easier method to accomplish all of this without using the sub reports.
> > > >
> > >
> >
>