I just want a report summary

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that has a number of children in various age groups I want to
make a report that simply has totals for each of field. I do not want to
show all the records. I want to make it split into two groups found in a
another field. One of the fields is a yes/no field. I want to run a total
on this field also. I don't even know where to start.
 
My psychic abilities are not functioning well do to the cold weather up here
in Wisconsin. Can you please type some sample records with table and field
names? Then treat us to how you would expect to display these records in a
report.
 
I'm sorry, I thought I had explained sufficiently. Here is an example of the
table structure.

name address phone 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 00 01 02
03 04 05 nochildren update source

each field identified by numbers is a numeric field that represents the year
the child was born.

for each address we are logging in how many children born in each year. If
there are no children the no children field is checked

the update field determines whether the entry for that address has been made

the source field identifies the source of the update ie mass mailing, public
schools, private schools, web site . . . etc

I would like a report that totals how many children are in each age group,
how many addresses have no children
how many records came from each data source
an overall total of children


I would appreciate any help you could give me.
 
Is there any way that you can normalize your table structure? Each child
should create a record in a related table that stores either the child's
birthdate or at least the year. You would then be able to summarize the
information.

If you can't normalize the tables, I would try to create a union query that
would normalize the table. Do you have a primary key field? Do you
understand the problems with naming a field name? Every object in Access has
a name. Naming a field name can be very confusing.

BTW: I don't know how you would have expected anyone to answer your first
question based on the information provided. When you ask a question, pretend
you don't know anything about your tables or requirements and then read your
question prior to posting.
 
I am not creating a record for each child. I am not storing birth dates. My
table has a record for each address. The name field (which I will rename
RNAME) contains the surname of the resident. I want to count the number of
children at each address according to the year they were born. My address
field is the key field. There are no duplicate addresses, there is no
redundant data. I want a report that doesn't show each record, but a total
of the number of children reported in each year field. That would be a great
start. It would be nice if I could have a subtotal of each number for each
source. I have a field that identifies each source. I would like something
that would look like:

source 05 04 03 02 01 00 99 98 97 96 95
.. . .

first source 20 1 5 32 4 26 9 28 40 30
28 . . .

2nd source 5 20 30 40 5 32 54 48 55 60
43. . .

3rd source 2 5 7 8 7 8 5 7 9
5 1 . . .

total 27 26 42 80 16 66 68 83 104 95
72. . .

I don't know if it's possible but I would also like to include a column to
show the total of all the addresses which reported no children. That field I
have set up as a yes-no field. Does this pose a problem?

The cherry on top would be to show a comparison of the total number of
records in the table and the number of records which have been updated with
the child count. The source field is a good indicator of whether or not the
record has been updated. That field I have set up as a text field.
 
You can sum your "number" columns and group by Source.
SELECT Source, Sum(Abs([NoChildren])) As NoKids, Sum([05]) as Num05,
Sum([04]) as Num04,...
FROM tblExcel
GROUP BY Source;

Your table is very un-normalized which creates the need to build long sql
views.
 
Back
Top