Pie Chart advice

[mcclunyboy]

I have some data that I want to display in a chart:

Theme    Type       Count(Type) 
Blah1    1               5 
Blah1    2               5 
Blah1    3               8 
Blah2    1               1 
Blah3    2               5 
Blah3    3               10 
Blah4    1               111 
Blah4    2               222 
Blah4    3               333

I want to display that query data in a Pie Chart, one chart for each theme with a section of pie for each Type (with the value of the count determining the size). Unfortunately my mind has gone blank. Everything I try does not appear correctly, some queries also require an repeated entry of the parameters but even if I do that it doesn't display correctly. I think I need to write another query to the format:

Theme     Type1      Type2       Type3       Type4 
Blah1       5            5           8        1 
Blah2       1 
Blah3       5            10 
Blah4       111        222        333

Is that correct and does that make sense? If it isn't correct how else do I do it?
I haven't done charts before and I am struggling a bit - many thanks for any help.

 

[pr2-eugin]

Look at CrossTab query.

 

[mcclunyboy]

Thanks, I've done that and I've produced a query which looks as I expected.

Unfortunately the column names are Theme, SumType, 1,2,3,4 and when I go to create the chart I would like the chart to be displayed for each Theme with each section of pie determined by the Type (1,2,3 or 4) obviously equalling the SumType. When creating the chart I cannot get it to correclty utilise the combined Type1/2/3/4 fields....

I fear I maybe going down the wrong path?

 

[pr2-eugin]

It depends ! How many types are there in your table?

 

[mcclunyboy]

four types, but 30+ themes.

I have tried it a whole range of different ways, I did get it looking correctly but the queries were requesting the parameters for every theme. I am hoping to start from scratch and build a query slowly building the chart so I know it works.

 

[pr2-eugin]

If it is only 4 type, then you can go ahead with this,

SELECT yourTable.Theme, Sum(IIF(yourTable.Type = 'Type1', 1, 0)) As Type1, 
                        Sum(IIF(yourTable.Type = 'Type2', 1, 0)) As Type2, 
                        Sum(IIF(yourTable.Type = 'Type3', 1, 0)) As Type3, 
                        Sum(IIF(yourTable.Type = 'Type4', 1, 0)) As Type4
FROM yourTable
GROUP BY yourTable.Theme;

 

[mcclunyboy]

It basically has the same problem as the CrossTab Query. I have resorted to the following base query:

SELECT  theme., 
Type, 
Count(Type) AS CountOfType
FROM Type INNER JOIN [Action] ON ActionType.ActionTypeID = Type
GROUP BY Theme, Type;

Now that actually produces the charts but it produces four for each theme due to the four types. Does that help at all? I am fairly experienced with access but unfortunately I've never used charts before and to be honest perform pretty poorly when any statistics are involved Thanks for the advice.

 

[pr2-eugin]

Can you please upload a Stripped DB?

How to Upload a Stripped DB.

To create a Sample DB (to be uploaded for other users to examine); please follow the steps..

1. Create a backup of the file, before you proceed..
2. Delete all Forms/Queries/Reports that are not in Question (except the ones that are inter-related)
3. Delete auxiliary tables (that are hanging loose with no relationships).
4. If your table has 100,000 records, delete 99,990 records.
5. Replace the sensitive information like Telephone numbers/email with simple UPDATE queries.
6. Perform a 'Compact & Repair' it would have brought the Size down to measly KBs..
7. (If your Post count is less than 10 ZIP the file and) Upload it..

Finally, please include instructions of which Form/Query/Code we need to look at. The preferred Access version would be A2003-A2007 (.mdb files)

 

[mcclunyboy]

See attached, I've deleted everything but the very basics. I kept a few various queries I tried plus the horrible attempts. My head is elsewhere I think.

 

[JHB]

Does it meet your requirements, see attached database?

 

[mcclunyboy]

Hi,

It does - however if I add parameters to the query (in this case I will be using a District Code and start/end dates) it prompts for the parameters as it produces every chart?

I am starting to think simply running that query and exporting it to excel to produce the charts would be easier!