MS Access Need help with weekly report

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

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?
Question by:DJPr0
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
LVL 40

Expert Comment

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

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

Expert Comment

ID: 39241007
Look at sample
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.


Author Comment

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?
LVL 48

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?

Author Comment

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

Accepted Solution

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.
LVL 48

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.

Author Closing Comment

ID: 39241993
Thanks als315 & fyed!

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

710 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