Solved CrossTab Percentages (1 Viewer)

Cliff67

Registered User.
Local time
Today, 01:34
Joined
Oct 16, 2018
Messages
175
Hello Everyone

My main customer repairs database has a table for cameras with information like serial number, series, model, sensor type, memory and any additional configuration.

I've made a crosstab query below (not got much experience in these) that gives me the result of the camera series and the total of the sensor types

QBESensorsXtab.JPG


the results are like:
XTABResults.JPG


what I want to do is get the percentage of the total number of cameras. I've got the start of a normal query with column which has Total: [COLOUR]+[MONO]. this gives me the total for each row
So for A series total is 296, B series =69 and C series = 258 together they make 623. I'm trying to get the percentage of each series in total.

So for A series 296/623 = 0.475 = 47.5%

Has anyone done similar I want the results to go in a bar chart with totals and percentages.

Would this be better as just a report? which I'm starting to lean towards

Any advice would be appreciated
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:34
Joined
Oct 29, 2018
Messages
21,357
That may be possible, but have you tried creating a new query based on your crosstab? That might be an easier way of getting what you want.
 

Cliff67

Registered User.
Local time
Today, 01:34
Joined
Oct 16, 2018
Messages
175
Hi DBGuy

I have created a second query based on the crosstab with just the row totals, but I really want the percentages to put on a bar chart - does the charting tool allow you to do that when you make a chart?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:34
Joined
Oct 29, 2018
Messages
21,357
Hi DBGuy

I have created a second query based on the crosstab with just the row totals, but I really want the percentages to put on a bar chart - does the charting tool allow you to do that when you make a chart?
I don't think so. I believe the chart can only read and represent data, not create them. So, have you tried adding the percentages in your new query though?
 

Cliff67

Registered User.
Local time
Today, 01:34
Joined
Oct 16, 2018
Messages
175
Hi DbGuy
Yes I've added them however my Maths is a bit messed up as I keep getting 100% or I'm referencing the field incorrectly. I'm going to be busy for a day or 2 doing image analysis so it doesn't mean I have forgotten you...
 

Cliff67

Registered User.
Local time
Today, 01:34
Joined
Oct 16, 2018
Messages
175
I will try to get an hour or tow over the next few days to have a real good think and see what I'm doing wrong
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:34
Joined
Feb 19, 2002
Messages
42,970
Create a query that calculates the percentage and use that query in the crosstab rather than the table.
 

Cliff67

Registered User.
Local time
Today, 01:34
Joined
Oct 16, 2018
Messages
175
Hi Guys

thank you for your suggestions. I had a bit of a brain wave insomuch as I created the percentages as a subreport and placed them at the bottom of the Chart with the totals in.

Once again thank you for your help

Cliff
 

Users who are viewing this thread

Top Bottom