Summary Reports

[Matthew]

I have a fairly extensive military personnel database. I
have countless queries and reports which handle all my
needs. However, what I am missing is a one-page type
summary report which brings all the various queries
together. Not knowing how to accomplish this in ACCESS,
I currently use an EXCEL spreadsheet which automatically
imports the data from my dbase (it's easy to count totals
this way from various queries and fit on one page). But
I would love to do this from ACCESS. Help.

 

[Fons Ponsioen]

Can you give some specifics? it is rather easy to combine
various summarizing queries into a single query and create
report(s) based on this (these) queries. But to answer
such a broad question with any usable suggestion is rather
difficult.
Post back with some specifics.
Fons

 

[Guest]

I basically need to COUNT totals from my various
established queries and include them into one report. I
have tried DSUM, DCOUNT, COUNT as part of CONTROL SOURCE
of TEXT BOXES. But have not been successful. Since I am
basically counting totals, did not want to use another
query. If I have to use Query, I would need help to
count totals. Does this explain better? Thank you.

 

[Fons Ponsioen]

Without further detail, the simplest in my opinion would
be to use some totals queries, this allows you to group on
most any field and caluclate numeric fields such as sum,
avg, mean,etc.
Hope this helps.
Fons

 

[Guest]

Not sure how to do the totals queries. I've tried, and
can't get anything to work. Here's details: main table
is military personel. i have one query that selects
enlisted personnel. another query that select officer
personnel. for my report, I want the number of enlisted
and number of officer. I figured I could pull the total
from each of the established queries by using a control
source function. thanks.

 

[Jamie Collins]

Not sure how to do the totals queries. I've tried, and
can't get anything to work. Here's details: main table
is military personel. i have one query that selects
enlisted personnel. another query that select officer
personnel. for my report, I want the number of enlisted
and number of officer. I figured I could pull the total
from each of the established queries by using a control
source function.

It is nettique to post details, for example your DDL schema:

CREATE TABLE Personnel
(
personnel_nbr CHAR(10) NOT NULL,
last_name VARCHAR(35) NOT NULL,
personnel_type CHAR(8) NOT NULL,
CONSTRAINT pk__personnel PRIMARY KEY (personnel_nbr),
CONSTRAINT ch__personnel_type CHECK(personnel_type IN ('Enlisted',
'Officer')),
CONSTRAINT un__personnel_nbr_type UNIQUE (personnel_nbr,
personnel_type)
)
;

some sample data:

INSERT INTO Personnel
(personnel_nbr, last_name, personnel_type)
VALUES ('9226599520', 'Norarules', 'Officer')
;
INSERT INTO Personnel
(personnel_nbr, last_name, personnel_type)
VALUES ('1179537015', 'Hevitoxic', 'Officer')
;
INSERT INTO Personnel
(personnel_nbr, last_name, personnel_type)
VALUES ('2857727233', 'Regisaver', 'Enlisted')
;
INSERT INTO Personnel
(personnel_nbr, last_name, personnel_type)
VALUES ('6929845415', 'Livehulas', 'Enlisted')
;

and the DDL for your existing queries would've been a nice touch:

CREATE VIEW EnlistedPersonnel AS
SELECT personnel_nbr, last_name
FROM Personnel
WHERE personnel_type = 'Enlisted'
;

CREATE VIEW OfficerPersonnel AS
SELECT personnel_nbr, last_name
FROM Personnel
WHERE personnel_type = 'Officer' ;
;

Do you expect the reader to do all that work for you as well as some
up with a solution? <g>

You seem to want to do something like this:

CREATE VIEW TotalsByPersonnelType_Err AS
SELECT 'Officer' AS personnel_type,
COUNT(personnel_nbr) AS [Count]
FROM OfficerPersonnel
UNION
SELECT 'Enlisted' AS personnel_type,
COUNT(personnel_nbr) AS [Count]
FROM EnlistedPersonnel
;

However, it would be easier to just use the Personnel table:

CREATE VIEW TotalsByPersonnelType AS
SELECT personnel_type, COUNT(personnel_nbr) AS [Count]
FROM Personnel
GROUP BY personnel_type
;

Jamie.

--

 

[Guest]

Have you tried adding Footers in the report, then set a Text box for each
total you want such as =Sum([YourFieldName])

Add Sorting & Grouping, select the field you want totals for such as the
Consultant field, then select Footer. Add the total text box as shown above.

Mary