Solved

Pivot table or auto-form?

Posted on 2009-05-13
9
356 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server functions 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 Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

752 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