Summing like fields across tables

  • Thread starter Thread starter lars
  • Start date Start date
L

lars

I thought that this would be a very simple setup when I started, but I am
having problems.
Using Access 2000 in Windows 2000.

I have a number of Agencies who provide child care. I have a table that
holds Agency information, using a autonumber for each Agency as a unique
identifier. Each Agency may take care of any number of ages of children.
At each age group the same information is needed, therefore, I created a
separate table for each age group. Each table has an Agency ID which is
linked to the Agency info table.

Agency
ID (autonumber)
Street Address
City
State
Telephone

Infant
Agency ID
Number enrolled
Number per Adult
Number per Teacher

Toddler
Agency ID
Number enrolled
Number per Adult
Number per Teacher

PreSchool
Agency ID
Number enrolled
Number per Adult
Number per Teacher

Now, when all the Agencies have registered the number of children by age
group, I want to be able to go in and get a sum of total Number enrolled,
total Number per Adult, total Number per Teacher per Agency. (All Agencies
do not all have all children). The age group tables are joined to the
Agency table by ID and Agency ID.

I created a query that lets me see each Agency and each specific
field.(i.e.- Number enrolled at each age group). The problem is that I
cannot get it to sum the total number from each field. The freaky part is
that it was working one day and quit working the next (just as I was going
into production). I built an expression (what I thought was a simple
expression) and it will not display a total number. I get absolutely
nothing. I have tried =Sum(
![field]+
![field]),
=Sum((
![field]+
![field])),
DSum((
![field]+
![field])) and still no results. I've done
Groupby with Expression, groupby with Sum, still nothing. I just can't
figure out what went wrong.

Please help.

Leslie
 
The first issue that I see is using multiple enrollment tables where a
single table would be better. Infant, Toddler, and PreSchool should be
values in a field rather than table names. If you can't change your table
structure then start by creating a union query:


SELECT "Infant" as AgencyType, [Agency ID], [Number enrolled], [Number per
Adult], [Number per Teacher]
FROM Infant
UNION ALL
SELECT "Toddler", [Agency ID], [Number enrolled], [Number per Adult],
[Number per Teacher]
FROM Toddler
UNION ALL
SELECT "PreSchool", [Agency ID], [Number enrolled], [Number per Adult],
[Number per Teacher]
FROM PreSchool;

You can then use a totals query on the results of the union query. I kinda
question three "number" fields also and would consider normalizing this
further but I don't know why there are three separate fields.


--
Duane Hookom
MS Access MVP


lars said:
I thought that this would be a very simple setup when I started, but I am
having problems.
Using Access 2000 in Windows 2000.

I have a number of Agencies who provide child care. I have a table that
holds Agency information, using a autonumber for each Agency as a unique
identifier. Each Agency may take care of any number of ages of children.
At each age group the same information is needed, therefore, I created a
separate table for each age group. Each table has an Agency ID which is
linked to the Agency info table.

Agency
ID (autonumber)
Street Address
City
State
Telephone

Infant
Agency ID
Number enrolled
Number per Adult
Number per Teacher

Toddler
Agency ID
Number enrolled
Number per Adult
Number per Teacher

PreSchool
Agency ID
Number enrolled
Number per Adult
Number per Teacher

Now, when all the Agencies have registered the number of children by age
group, I want to be able to go in and get a sum of total Number enrolled,
total Number per Adult, total Number per Teacher per Agency. (All Agencies
do not all have all children). The age group tables are joined to the
Agency table by ID and Agency ID.

I created a query that lets me see each Agency and each specific
field.(i.e.- Number enrolled at each age group). The problem is that I
cannot get it to sum the total number from each field. The freaky part is
that it was working one day and quit working the next (just as I was going
into production). I built an expression (what I thought was a simple
expression) and it will not display a total number. I get absolutely
nothing. I have tried =Sum(
![field]+
![field]),
=Sum((
![field]+
![field])),
DSum((
![field]+
![field])) and still no results. I've done
Groupby with Expression, groupby with Sum, still nothing. I just can't
figure out what went wrong.

Please help.

Leslie
Click to expand...
 
At each age group the same information is needed, therefore, I created a
separate table for each age group.
Click to expand...

That's a very good reason to NOT create a separate table for each age
group! Instead, have all the children in one table, with an additional
AgeGroup field!

You can use a UNION query to string your current non-normalized tables
together to be searched or summed - see the online help for UNION. But
I'd really recommend combining the tables into one.
 
Thanks for your help.
The reason I have separate tables is because I am collecting information
from over 200 agencies. I have seven fields at each age group. I thought
that keeping the information separate would allow me to run queries against
each age group. i.e.- what are the total number of infants cared for by all
agencies.
I can't imagine what a single table containing seven age groups for 200
agencies would look like.

Duane Hookom said:
The first issue that I see is using multiple enrollment tables where a
single table would be better. Infant, Toddler, and PreSchool should be
values in a field rather than table names. If you can't change your table
structure then start by creating a union query:


SELECT "Infant" as AgencyType, [Agency ID], [Number enrolled], [Number per
Adult], [Number per Teacher]
FROM Infant
UNION ALL
SELECT "Toddler", [Agency ID], [Number enrolled], [Number per Adult],
[Number per Teacher]
FROM Toddler
UNION ALL
SELECT "PreSchool", [Agency ID], [Number enrolled], [Number per Adult],
[Number per Teacher]
FROM PreSchool;

You can then use a totals query on the results of the union query. I kinda
question three "number" fields also and would consider normalizing this
further but I don't know why there are three separate fields.


--
Duane Hookom
MS Access MVP


lars said:
I thought that this would be a very simple setup when I started, but I am
having problems.
Using Access 2000 in Windows 2000.

I have a number of Agencies who provide child care. I have a table that
holds Agency information, using a autonumber for each Agency as a unique
identifier. Each Agency may take care of any number of ages of children.
At each age group the same information is needed, therefore, I created a
separate table for each age group. Each table has an Agency ID which is
linked to the Agency info table.

Agency
ID (autonumber)
Street Address
City
State
Telephone

Infant
Agency ID
Number enrolled
Number per Adult
Number per Teacher

Toddler
Agency ID
Number enrolled
Number per Adult
Number per Teacher

PreSchool
Agency ID
Number enrolled
Number per Adult
Number per Teacher

Now, when all the Agencies have registered the number of children by age
group, I want to be able to go in and get a sum of total Number enrolled,
total Number per Adult, total Number per Teacher per Agency. (All Agencies
do not all have all children). The age group tables are joined to the
Agency table by ID and Agency ID.

I created a query that lets me see each Agency and each specific
field.(i.e.- Number enrolled at each age group). The problem is that I
cannot get it to sum the total number from each field. The freaky part is
that it was working one day and quit working the next (just as I was going
into production). I built an expression (what I thought was a simple
expression) and it will not display a total number. I get absolutely
nothing. I have tried =Sum(
![field]+
![field]),
=Sum((
![field]+
![field])),
DSum((
![field]+
![field])) and still no results. I've done
Groupby with Expression, groupby with Sum, still nothing. I just can't
figure out what went wrong.

Please help.

Leslie
Click to expand...
Click to expand...
 
It took me an hour of thinking about what you meant, and I finally see the
error of my ways.
Thanks for setting me straight- I know what I have to do now.

lars said:
Thanks for your help.
The reason I have separate tables is because I am collecting information
from over 200 agencies. I have seven fields at each age group. I thought
that keeping the information separate would allow me to run queries against
each age group. i.e.- what are the total number of infants cared for by all
agencies.
I can't imagine what a single table containing seven age groups for 200
agencies would look like.

Duane Hookom said:
The first issue that I see is using multiple enrollment tables where a
single table would be better. Infant, Toddler, and PreSchool should be
values in a field rather than table names. If you can't change your table
structure then start by creating a union query:


SELECT "Infant" as AgencyType, [Agency ID], [Number enrolled], [Number per
Adult], [Number per Teacher]
FROM Infant
UNION ALL
SELECT "Toddler", [Agency ID], [Number enrolled], [Number per Adult],
[Number per Teacher]
FROM Toddler
UNION ALL
SELECT "PreSchool", [Agency ID], [Number enrolled], [Number per Adult],
[Number per Teacher]
FROM PreSchool;

You can then use a totals query on the results of the union query. I kinda
question three "number" fields also and would consider normalizing this
further but I don't know why there are three separate fields.


--
Duane Hookom
MS Access MVP


lars said:
I thought that this would be a very simple setup when I started, but I am
having problems.
Using Access 2000 in Windows 2000.

I have a number of Agencies who provide child care. I have a table that
holds Agency information, using a autonumber for each Agency as a unique
identifier. Each Agency may take care of any number of ages of children.
At each age group the same information is needed, therefore, I created a
separate table for each age group. Each table has an Agency ID which is
linked to the Agency info table.

Agency
ID (autonumber)
Street Address
City
State
Telephone

Infant
Agency ID
Number enrolled
Number per Adult
Number per Teacher

Toddler
Agency ID
Number enrolled
Number per Adult
Number per Teacher

PreSchool
Agency ID
Number enrolled
Number per Adult
Number per Teacher

Now, when all the Agencies have registered the number of children by age
group, I want to be able to go in and get a sum of total Number enrolled,
total Number per Adult, total Number per Teacher per Agency. (All Agencies
do not all have all children). The age group tables are joined to the
Agency table by ID and Agency ID.

I created a query that lets me see each Agency and each specific
field.(i.e.- Number enrolled at each age group). The problem is that I
cannot get it to sum the total number from each field. The freaky
Click to expand...
Click to expand...
part
is
that it was working one day and quit working the next (just as I was going
into production). I built an expression (what I thought was a simple
expression) and it will not display a total number. I get absolutely
nothing. I have tried =Sum(
![field]+
![field]),
=Sum((
![field]+
![field])),
DSum((
![field]+
![field])) and still no results. I've done
Groupby with Expression, groupby with Sum, still nothing. I just can't
figure out what went wrong.

Please help.

Leslie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top