MS Access Weekly Report

Good afternoon,

I'm looking for a solution to this issue and I hope someone can help.
I have an MS query that returns Employee Sales Performance per day. The field look as follows:

- Date (ex: 2015-Aug-08)
- The Employee ID
- The Hours Worked
- The Number of Transactions
- Total Units per Sale
- The Total Sales.

The query also has calculated field like:
- Sales Per Hour
- Avg Units per Sale
- Avg Sale
- Transactions per hour

I would like to create report for any given week and fill in the information from the query. The problem I face is doing it in columns such that The first column are the titles, the next 7 columns are each day of a given week and the last one is a totals column.

I've played around with crosstab but it's not working as desired. I've attached an Excel spreadsheet with the way in which I would like the report to output.

WeeklyReport.xls

I'm not sure if I should create columns in the report or use a different method.

Any help would be greatly appreciated.

Regards,
Lawrence
LVL 1
TSIsolutionsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access doesn't do "vertical columns" reports, which is essentially what you're after. I can suggest two approaches:

You can create subreports for each data item (like Day1_HoursWorked, Day2_HoursWorked, etc) and then create a master report that uses all those subreports, Align the subreports in the "grid" fashion from your Excel mockup, and you would be able to see a report that's similar to what you're asking. IMO, however, this is a huge maintenance nightmare, and can be a real performance dog.

A better solution, in my opinion, is to create a temporary table that reflects the way you want your report to look, then fill that temp table, and then use that temp table as the source for your report. In your example, I'd create a temp table with 9 Columns:

Type
Day1
Day2
Day3
Day4
Day5
Day6
Day7
Totals

Then add Rows to the table for each of the items in your ColumnB. For example, I'd add a row where Type = "Hours Worked". For the Day1 column in that row, I'd run code that would determine the value I should have for 2015-08-02, and add that data to my Day1 column in that row. I'd do the same for 2015-08-03, 08-04, etc etc.

I'd then add a row for "Sales $", and compute the values for each column.

I'd then continue doing that for all other rows (# Transactions, # Items, etc).

The only tricky part is the column names (on the report). You can set those through code, or you can add a row to the temp table with blank values in the Type column, and the actual verbiage you want to see in the Day1, Day2, etc columns. Either way will work.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Agree with Scott.

Some other notes...
Compounding the complexity here is that you will probably need/want a way to filter for various combinations of: Store, Employee, and/or Week.

I know it is not what you want, ...but you could create Report in Access; grouped by Emp/Week/Store.
At least here this would be easy to create and no maintenance would be required...

Or, why not leave this particular report in Excel?
At least in Excel you have access to Pivot tables/PowerPivot, ...where you may be even able to have more flexibility...

JeffCoachman
TSIsolutionsAuthor Commented:
Thanks for your help guys,

As you mentioned there is no way to pivot the data properly so we decided to leave the data in the format we had it in originally.
It's too bad there's no way in Access to have a proper vertical/pivot layout.

Thanks Again for you help.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
So why the B grade?

I'm not Microsoft, and I have no control over what or how Access works, or what features it provides (or does not provide). The methods I suggested are two common ways to make Access do what you're after. The fact that you decided not to use them should not warrant a lower grade.
Jeffrey CoachmanMIS LiasonCommented:
FWIW,

Scott actuality posted two valid workarounds.
He even added a full explanation on the second.

Making anything in Access look like a Weekly, (or Monthly) "Calendar" has never been easy.
TSIsolutionsAuthor Commented:
My apologies for the grading situation. I was under the impression that the grading was about how good the solution would be in regards to the question. I understand that Scott gave the best possible answer and I thank him for that however I thought that the grading was towards how well does the answer solve the question so that if someone else were to have the same question, they would know that there is no easy solution ergo the "B". I have now switched it to an "A".
My apologies for the confusion once again however there should be a mechanism that shows that the question has a positive (and feasible) response. In this case the workaround is too complicated for what it's worth.

Cheers,
Lawrence
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.