Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Report format to show new person for each week of each month? 1

Status
Not open for further replies.

angelandgreg

Technical User
Sep 20, 2006
106
US
I have a query that shows all the people that are marked to volunteer to HelpAtHome.

Which is linked to a table with the days that the HelpAtHome people are also scheduled to volunteer on specific days each month.

I want to create a report, based on the query, but to output something like the following:


Oct06-Week1 Oct06-@eek2 ... Nov06-Week1
Person1 Person2 Person3


Current query result:
Name ScheduledDate
Cathy Filippone 10/2/2006
Nicole Meyer 10/3/2006
Shawn Kiesel 10/4/2006
Karen Singley 10/9/2006
Marlene Huckabone 10/12/2006
Cathy Filippone 10/23/2006
Shawn Kiesel 10/25/2006
Cathy Filippone 11/6/2006
Marlene Huckabone 11/9/2006
Karen Singley 11/13/2006
Marlene Huckabone 11/14/2006
Shawn Kiesel 11/15/2006
Cathy Filippone 11/27/2006
 
There are some sample calendar reports at
Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
thanks but unfortunately nothing in there look like it can help with what I'm trying to do.

1. What will change the dates in the ScheduledDate field to output as Oct06-Week1 (mmmyy-Week1) and so on for the 4 weeks in October then restart Nov06-Week1 when the dates fall in November and so on?
2. Then be able to display the Name(s) listed for the scheduled "Week(s)"?

 
Your original post lacks clarity.
I think the following contains two 'column heading' errors/typos and doesn't describe how to handle weeks that straddle two different months. Is the week of Oct 29th the 5th week of Oct or the 1st week of Nov?
Oct06-Week1 Oct06-@eek2 ... Nov06-Week1
Person1 Person2 Person3

The following might all be correct but doesn't relate to persons in the above "output something like the following"
Current query result:
Name ScheduledDate
Cathy Filippone 10/2/2006
Nicole Meyer 10/3/2006
Shawn Kiesel 10/4/2006
Karen Singley 10/9/2006
Marlene Huckabone 10/12/2006
Cathy Filippone 10/23/2006
Shawn Kiesel 10/25/2006
Cathy Filippone 11/6/2006
Marlene Huckabone 11/9/2006
Karen Singley 11/13/2006
Marlene Huckabone 11/14/2006
Shawn Kiesel 11/15/2006
Cathy Filippone 11/27/2006

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
Yes, on the week of Oct 29 is Week 5.

Sorry, Person = Name
 
You aren't really understanding my cry for help. You are showing single place holders with "person1", "Person2",... in the first display and then show actual name like "Cathy Filippone" in the second display. Why don't you take the time to type in real people names rather than "Person1"? This would tie the two displays together to make sense.

It also looks like you might have several names per week but your first display shows only one person per week. Again, this is confusing and it seems would only take a few minutes to provide a better display and specifications.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
I guess I was trying to show an example with my actual data result.

Yes, there are more then one name per week as a person is scheduled for each day of the week.

So is there a way that will list all the names for each week of the month. I suppose the order can be by ascending date.

So in my query result I show 3 people in the week 1 of Octoboer.

All 3 names would show under the Oct06-Week1, I think that would work.

Maybe like this:
Oct06-Week1 Cathy Filippone Nicole Meyer Shawn Kiesel
Oct06-Week2 Karen Singley Marlene Huckabone

or is that not a good idea?
 
Hi Duane. How goes it?

Angelandgreg, you need to make a table that defines which dates belong in which "Weeks". The computer doesn't know how you are defining that, you have to tell it.

TableName: DateWeeks
Field: CalendarDate
Field: DisplayMonth
Field: DisplayWeek

SampleData (I'm making this up):

10/26/06 10 4
10/27/06 10 4
10/28/06 10 4
10/29/06 10 5
10/30/06 10 5
10/31/06 10 5
11/01/06 10 5

See? Is November 1st in "OCT WEEK 5"? It depends on how you define your "weeks" at your company.

So, I suggest making a table in Excel with all dates and months and weeks, then just pasting into a table in Access. Then use that to join to your data table.

Ginger

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at
 
Hi Ginger,
Good to see you out and about. I hope this is a good sign...

angelandgreg,
You mention "a person is scheduled for each day of the week" and yet your desired reports are by week. Does it make a difference in the display of your report if Cathy Filippone is scheduled 10/2/2006 and Nicole Meyer is scheduled 10/3/2006?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
no it does not, the specific date of 10/2 and 10/3 is not important. could just be used for sorting order so that Cathy Filippone comes up before Nicole Meyer.

 
I'm only able to get this far:

"W" & DateDiff("ww",IIf([ScheduledDate] Is Null,"NA",[ScheduledDate]),MonthEnd()+1)

WHERE (((tblSchedule.ScheduledDate) Between MonthEnd()+1 And MonthEnd()+31))


using:
Function MonthEnd()
MonthEnd = DateAdd("m", 1, (Month(Date) & "/1/" & Year(Date))) - 1
End Function


I am getting:
Name Week
Cathy Filippone Week-1
Nicole Meyer Week-1
Shawn Kiesel Week-1
Karen Singley Week-2
Cathy Filippone Week-4
Shawn Kiesel Week-4


Need to be able to show more then one month where the Week restarts for the change in months??
 
I made it into a cross-tab and am getting:

Month Week Cathy Filippone Karen Singley Nicole Meyer Shawn Kiesel
Oct-06 Week-1 1 1 1
Oct-06 Week-2 1
Oct-06 Week-4 1 1


Don't know how to have it restart with Week-1 when the date range is changed or left out to look at all the dates so when Nov-06 comes up, instead of the Week just following the order it changes back to Week-1 and continues until it's Dec-06 and changes to Week-1 again, etc ...

what do you think? and how do I do that?
 
Month Week Cathy Filippone Karen Singley Nicole Meyer Shawn Kiesel
Oct-06 Week-1 Cathy Filippone Nicole Meyer Shawn Kiesel
Oct-06 Week-2 Karen Singley
Oct-06 Week-4 Cathy Filippone Shawn Kiesel


this is where I would like Week-6 to actually say Week-1 again because it's another month.
Nov-06 Week-6 Cathy Filippone Nicole Meyer
Nov-06 Week-7 Karen Singley Shawn Kiesel
Nov-06 Week-9 Cathy Filippone
 
I'm not sure what you crosstab sql looks like but you might use an expression like:
WK: "Week" & DateDiff("ww",DateSerial(Year([Date]),Month([Date]),1),[Date])+1

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top