Solved

Pivot table or auto-form?

Posted on 2009-05-13
9
358 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

626 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