The basic issue I am trying to solve here is
1) How many equipments per V-name -calculated value (there are over 16K of
V-names)
2) which V-names has the most number of equipments
3) What is the distribution of the "V-names with equipments" i.e.
calculated
value.
The reason I use a pivot table is so that I can sort on the Calculated
value
to see the edge cases, and also to reduce the number of records to fall
under
the Excel record limits (Worksheet size 65,536 rows by 256 columns ). One
of
my Access tables that I need to analyze has over 325K records, and with
Pivot
table, I can pick and choose on the various fields to do the analysis
needed.
So, I then export the Query data(records ~ to Excel and run the add-tool
"Data Analysis: Descriptive Statistics", as well as create further pivot
tables as needed to create histograms, distribution graphs etc. If I can
do
all this in MS-Access, that would be great. Unfortunately, I am more
familiar
with Excel. Is there a way to do this in Access?
Duane Hookom said:
You don't want to store a value that can be calculated. Why do you think
you
need a pivot table to count the number of records in a group?
Find your query that calculates your counts. Add this query to the Record
Source query of your report. Join the fields used to GROUP BY in the
counts
query. Add the CountOf field to the grid so that you can sort by it in
your
report.
--
Duane Hookom
MS Access MVP
message
I used the Query designer to do find the count in the Pivot table.
My table is named Table1
It has two fields, "V-Name" (text) "EQUIPMENT" (text).
The table looks something like this:
V-Name Euipment
alpha atc
alpha tgv
alpha xihyn
alpha asdfsdf
beta asdfsdf
beta tgv
gamma tgv
gamma atc
Incidently, in the table, there are over 16K records.
To find the count of equipment per V-Name, I applied your instruction
as
follows:
In the Query designer,
First column
Field= "V-Name"
Table = "Table1"
Total = Groupby
Sort =Descending
Second column
Field= "Equipment"
Table = "Table1"
Total = Count
Sort =Descending
I then created a pivot table with this query and I got the total count
of
equipments for each of the V-Names.
I now want to add this total count to their corresponding records in
Table1.
So, in Table1, I have created a new field called "EquipTotal", which is
a
number field.
Can you please give me step by step instructions with the exact syntax
on
how to populate this empty field with the total count per V-Name as
derived
in the query?
The result should look something like this:
V-Name Euipment EquipTotal
alpha atc 4
alpha tgv 4
alpha xihyn 4
alpha asdfsdf 4
beta asdfsdf 2
beta tgv 2
gamma tgv 2
gamma atc 2
Thanks for your help and your patience with a novice.
:
Create a totals query that is similar to your report's record source.
Group
by EQUIPMENT and count EQUIPMENT. Remove more detailed information
from
the
query so your query calculates the proper values.
Save this totals query and then add it to your report's record source
join
at least the EQUIPMENT field. Add the count of EQUIPMENT to the query
grid
so that you can use it in your sorting and grouping in your report.
--
Duane Hookom
MS Access MVP
message
I am rather weak in SQL. I will need step by step instructions.
Is there a way to get the result using Pivot tables? By doing a
Autocalc:
Count on the Equipment field? I have gotten that far, but I don't
know
how
to
sort the pivot table based on the calculated count...
Help!!!
:
You have to add the counted value to your report's record source.
You
can
do
this with either a subquery, DCount(), or creating another totals
query
and
joining it to the record source query.
--
Duane Hookom
MS Access MVP
in
message
In a report, I have a calculated field called "CountEquip", which
counts
the
total number of records of [EQUIPMENT] field in a table.
I have set the controlSource for the
"CountEquip"=Count([EQUIPMENT])
This calculated field "CountEquip", is placed in a grouping
header
in
Report
design view, while the [EQUIPMENT] field is placed in the details
view.
In Print preview of the report, the 1st grouping result has 10
equipment
records total, the next one has 50 equipment records total, the
next
one
has
20 equipment records total, then 60, then 20, then 1, then 100.
I want the report to be in descending order of the total count
value
of
"CountEquip". In other words, I want to first display the group
with
100,
then 60, then 50 , then 30, then 20, then 10, then 1 record
total.
If I go to "Sorting and Grouping", there is no option to select
the
calculated field, since its NOT a column item.
How do I sort a report on a calculated value, which is not in a
table?
Thanks for your help!
