Solved

MS Access Need help with weekly report

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

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
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 stored procedures 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 Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

863 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

25 Experts available now in Live!

Get 1:1 Help Now