Solved

MS Access Need help with weekly report

Posted on 2013-06-12
9
359 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

840 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