Pivot table or auto-form?

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
LizzJAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
LizzJAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LizzJAuthor Commented:
anyone please...
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
LizzJAuthor Commented:
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
 
LizzJAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
LizzJAuthor Commented:
Okie.. I'll post a related quesion then
0
All Courses

From novice to tech pro — start learning today.