Solved

Pivot table or auto-form?

Posted on 2009-05-13
9
354 Views
Last Modified: 2013-11-29
Hi Experts,

I'm looking for help on this:

I have a table that contains following data fields (divided by |):

         Date | 1 day | 2 days | 3 days | ... ...| 30 days | more than 30 days
e.g 01/02/09    3          0            0                     1                 2

I need to come up with a chart (see attached) based on data from this table. E.g. a sum of numbers for Feb-09 will be calculated for each bracket of days. However, I want to build in the feature such that user could choose the brackets (i.e. it could be 1-3 days or 1-5 days, so on). And also the months (i.e. jan-09, may09, so on).

Hope the sample is self-explanatory. I don't need the format to be exactly the same. But it's important to capture the essential figures in a presentable way.

Can anyone tell me how to do it, maybe using a form for user input, a query to do the sum and a pivot chart to display the figures?


sample.xls
0
Comment
Question by:LizzJ
  • 5
  • 4
9 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24382194
As far as I can see, all you really need is a GroupBy Query.

Here is a sample.

See the query named: Query1
Access-EEQ-24407197CrosTabPivotT.mdb
0
 

Author Comment

by:LizzJ
ID: 24382527
errrrrrrrr.

Maybe I didn't make it clear. But my fields are not like 1-3 days, it is day by day.
What I need is to get user to specify which days they want to group together. E.g. 1 day to 7 days, 8 to 11 days, 12 to 30 days, more than 30 days. Then my query should be able to get the sum of those days.

What can I do in such a case?
0
 

Author Comment

by:LizzJ
ID: 24383342
anyone please...
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24387128
My sample was based on the sample you provide.

If you want an exact sample, then please post "Exactly" what you have.
(Your raw data)
And also post a valid representation of the "exact" output you want, based on your sample data.
0
 

Author Comment

by:LizzJ
ID: 24391706
Sorry if I didn't give a clear sample.
I've attached the sample below. Hope it is self-explanatory enough.
Raw data is in table [myData]
Refer to form "user" to see what I need.

Thanks a lot.
sample.mdb
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 24392134
Try the GroupBy queries in this sample

I created my own set of data, with more values as well.

JeffCoachman
Access-EEQ-24407197-GroupByTotal.mdb
0
 

Author Comment

by:LizzJ
ID: 24392261
Jeff,

Have you looked at the form "user" and saw my notes there? The difficulty is I need user to specify what are the days they want to group together. Please refer to my sample again...

Thanks~
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24392798
LizzJ,

Well that requires a *whole* different line of logic.

The main question here was how to create the summary:
<I need to come up with a chart (see attached) based on data from this table. E.g. a sum of numbers for Feb-09 will be calculated for each bracket of days. >
...This required a fair amount of work on its own.

I suggest we button up this question and create a new question for the "Search" interface.

JeffCoachman
0
 

Author Comment

by:LizzJ
ID: 24392917
Okie.. I'll post a related quesion then
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

792 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