Results 1 to 14 of 14
  1. #1
    Eirini_kap is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    13

    Create report form a crosstab query and group per week

    Hi!I want to create a report that will show the hours each person worked daily. I have created a crosstab query in order to have the dates as column headers. However, I would like to have a column at the end of every week where I will be able to sum the hours that people worked for the whole week. Any ideas how this can be done?Please keep in mind that I only started working with Access 2 days ago.. .

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Building stable report based on CROSSTAB query is not easy because of dynamic nature of fields.

    You will likely have to build 2 CROSSTAB queries and then join them.

    Review http://allenbrowne.com/ser-67.html

    Weekly grouping is even more complicated. Do you want a week to be 7 days Sun-Sat regardless of month or year? In other words, what do you want to do about weeks that cross months and years?

    Also, tables and queries have 255 fields limit. A year has 365/366 days and 52 weeks - that's 417/418 fields.

    What period do you want on report? 1 month? 1 year?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Eirini_kap is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    13
    Quote Originally Posted by June7 View Post
    Building stable report based on CROSSTAB query is not easy because of dynamic nature of fields.

    You will likely have to build 2 CROSSTAB queries and then join them.

    Review http://allenbrowne.com/ser-67.html

    Weekly grouping is even more complicated. Do you want a week to be 7 days Sun-Sat regardless of month or year? In other words, what do you want to do about weeks that cross months and years?

    Also, tables and queries have 255 fields limit. A year has 365/366 days and 52 weeks - that's 417/418 fields.

    What period do you want on report? 1 month? 1 year?
    Thank you for your reply!I have attached the way I want my report to look like and the way my data is structured when I import them in my DB. The link that you sent me was actually the one that helped me to create the crosstab but I am not getting how I can introduce a new column at the end of every week in order to sum the hours. I want the week to be 7 days starting from Monday and I want to do that for every quarter of the year. Each report will contain one quarter.Click image for larger version. 

Name:	Report Format.PNG 
Views:	22 
Size:	27.9 KB 
ID:	23383Click image for larger version. 

Name:	Table.PNG 
Views:	22 
Size:	2.0 KB 
ID:	23384

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Sorry, basic CROSSTAB query won't give you that output.

    You can use the CROSSTAB as report RecordSource and then in 4 or 5 unbound textboxes use calculations to sum the fields for each week. Example:

    =[4-Jan]+[5-Jan]+[6-Jan]+[7-Jan]+[8-Jan]

    Arrange the bound and unbound textboxes on report however you want.

    Problem is that will have to modify the report every quarter to edit the expressions.

    As I said, reports based on CROSSTAB not easy.

    An alternative would be lots of VBA code to write data to a temp table (the table is permanent, data is temporary) and base report on that table.

    Another might be with domain aggregate functions but they can be slow performers in query and report.

    These are getting into advanced and unconventional methods of data manipulation.

    I've never used CROSSTAB in any db I built.

    Didn't answer question about weeks crossing month or year. If a week crosses month or year you still want those 5 days kept together?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Eirini_kap is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    13
    Thank you so much!that was amazingly helpful! However, I have one more issue. I have created the unbound fields and added the calculations. I would also like to be able to sum those boxes as well. Is that possible? My goal is to have the total of both unbillable and billable hours for every week per last name (I hope my previous picture helps). As far as crossing a month is concerned, I still want to keep those 5 days together. It doesn't matter if my week contains days from another month. The only occasion I won't be doing this is at the end of the year. My 4th quarter will end at the last day of the year.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    In report, another textbox in report footer would have to repeat the expression with Sum().

    =Sum([4-Jan]+[5-Jan]+[6-Jan]+[7-Jan]+[8-Jan])

    If you want the week kept together regardless of crossing month/year, will need to calculate a week identifier and use that value to group records. Usual method is to calculate date of first day of week that a given date falls in. Fairly common topic.

    DateAdd("d", Weekday(datefield)+1, datefield)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Eirini_kap is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    13
    I am adding the text box in the task footer. (Billable and Non-billable hours are defined as a task in my table). However, this gives me the total for billable and non-billable hours separately.I would like to sum both for the full week. I am referring to the total on the left bottom corner of my image.Once again thank you!I think if I figure this out I will be good to go!
    Click image for larger version. 

Name:	Partial Report.PNG 
Views:	20 
Size:	5.8 KB 
ID:	23400

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    That Total row needs to be in a GROUP or REPORT footer. I don't enough about your report design. Looks you need two group levels: Department and LastName.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Eirini_kap is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    13
    Yes my report is grouped on department and on Last name and sorted on Task(Billable-Unbillable). I tried to group by Task in order to insert a footer and add the calculated field but when I do that it is giving me the total of Billable and the total of unbillable hours separately.. Also there is Totals option but it only allows me to Count and not sum tha values

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Totals from the ribbon? It is seeing data as text, not numbers.

    You need Total aggregate calc in the LastName group.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Eirini_kap is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    13
    Everything is being calculated correctly apart from the grand total in the right corner. Does it have to do with the fact that the totals in the full week column are being calculated with the unbound text box?
    Click image for larger version. 

Name:	Capture.PNG 
Views:	20 
Size:	3.5 KB 
ID:	23401

  12. #12
    Eirini_kap is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    13
    In general, do you think there would be an easier way for me to report the data the way I want without using a crosstab query? The only reason why I chose this was to make the dates column headers

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    As shown in post 6, the expression in the Week total needs to be:

    =Sum([4-Jan]+[5-Jan]+[6-Jan]+[7-Jan]+[8-Jan])

    Aggregate functions must refer to field names, not textbox names, so yes, the issue is with referencing UNBOUND textbox.

    I already stated in post 4 only alternatives would be lots of VBA code to write data to a temp table or domain aggregate functions. Whether or not these are easier is a point of view. I have used both. I have never used CROSSTAB.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Eirini_kap is offline Novice
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    13
    Thank you!I really appreciate your help.It worked!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-17-2017, 07:41 AM
  2. Trying to group items on a Crosstab Report
    By Nick Lingenfelter in forum Reports
    Replies: 2
    Last Post: 02-27-2013, 07:08 AM
  3. Replies: 5
    Last Post: 12-04-2012, 02:46 AM
  4. Create a form have 'group' like report
    By namserious in forum Forms
    Replies: 2
    Last Post: 06-28-2011, 02:47 AM
  5. Group by Week in a Crosstab Query
    By NMJones in forum Access
    Replies: 1
    Last Post: 06-24-2010, 04:09 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums