Solved

MS Access Need help with weekly report

Posted on 2013-06-12
9
361 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 40

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 40

Expert Comment

by:als315
ID: 39241007
Look at sample
DBWeeks.accdb
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 
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 40

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

680 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