Solved

MS Access Need help with weekly report

Posted on 2013-06-12
9
353 Views
Last Modified: 2013-06-12
Users input projects they are working on every day with a date.

How can I produce a report that looks like the following:                                                            (user would select a date range)

Week of        User                               Project worked on
June 2 - 8
                     Spongebob                       Ordered krabby patties
                     Patrick                              Jelly Fishing
                     Mr. Crabs                         Payroll
                                                               Employee of the Month
June 9 - 15
Etc.

Would like all the weeks displayed, even if no projects are displayed.
I would rather not have a table with all the weeks of the year if possible.
Can VBA spit out the weeks (June 2 - 8) by it self and display users and projects if the date falls in the week range?
0
Comment
Question by:DJPr0
  • 3
  • 3
  • 3
9 Comments
 
LVL 39

Expert Comment

by:als315
ID: 39240924
Can you show expected output if week end is in the next month? For example, for week starting from 30 June.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39240962
What does your table structure look like?

I generally do this with a table (tbl_Numbers), which contains a single field (lngNumber) and only 10 records (the values 0-9).  From this table, I create a query that allows me to generate a sequence of numbers as large (or small) as I want.  My qry_Numbers usually looks like:

SELECT Tens.lngNumber * 10 + Ones.lngNumber as lngNumber
FROM tbl_Numbers as Tens, tbl_Numbers as Ones

This generates numbers from 0-99.  Starting with this table and query, I then create a query that generates date ranges.  For example, to get a set of records defining the dates for Monday through Friday for 10 weeks, you would use:

SELECT qry_Numbers.lngNumber
           , Date()-Weekday(Date())+2+([lngNumber]*7) AS Monday
           , Date()-Weekday(Date())+6+([lngNumber]*7) AS Friday
FROM qry_Numbers
WHERE qry_Numbers.lngNumber <=9

Save that query, then use it in conjunction with your employee project dates table to generate the output you are looking for.  This latter part will depend on your particular table structure.  If you could provide a sample data table, that would make it easier.
0
 
LVL 39

Expert Comment

by:als315
ID: 39241007
Look at sample
DBWeeks.accdb
0
 

Author Comment

by:DJPr0
ID: 39241135
als315 - Date would be:
June 30 - July 6


fyed - Is there an easier way just to output week dates (June 30 - July 6) for a report?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39241363
Yes, but no guarantee that you will get all combination of weeks.  You specifically said, "Would like all the weeks displayed, even if no projects are displayed."

In order to get weeks where there are no projects displayed, you must have an external source for your dates, not necessarily present in your other table.

As I said, can you please provide us with some sample data?
0
 

Author Comment

by:DJPr0
ID: 39241367
als315 - Thanks for the sample database!

Why can't the report go beyond one page? I.E. If I input 1-1-13 to 6-12-13 I receive a report with 1-1-13 to 5-18-13

How can I have the report display any range I provide?
0
 
LVL 39

Accepted Solution

by:
als315 earned 400 total points
ID: 39241535
tblNums should have so many numbers as possible number of weeks you like to see in report. I 've filled it up to 20, fill it to 53 and it will be enough for one year.
DBWeeks.accdb
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 100 total points
ID: 39241663
That is why I use the table and query to generate the date range.  The table only contains 10 records but you can generate as many numbers as you need, from any date range.
0
 

Author Closing Comment

by:DJPr0
ID: 39241993
Thanks als315 & fyed!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now