Solved

Pivot table or auto-form?

Posted on 2009-05-13
9
350 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
 
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

948 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now