Total Queries

[Jeff]

Hi

I'm rather new to totaling via queries, and I'm not very sure of what
I'm doing. I have a parent-child relationship between 2 records, where
the parent is literally information about the parent, and the child is
information about the child.

What I would like to do is find totals for several fields. One would be
the number of parents in the database, another would be totals for each
neighborhood in the city (on the parent records), a 3rd total would the
number of child under age 3 (on the child record), and a 4th that
totals the numbers of low birth weights (a checkbox on the child as
well.)

Is there a way to do all of this on the same query? I've read that this
is more friendly in Access2007, but I have 2003.

Thanks!

Jeff

 

[Duane Hookom]

There really isn't a good reason to try to do all of this in a single query.
Use multiple queries and display them in a main report or form using
subreports or subforms.

 

[Jeff]

Well, I'm trying now to do this in separate queries, but when using
data from the parent and child to locate the information, I end up with
multiple rows in my query results, each with a count of 1. What am I
doing wrong?

I've included the SQL for my query.

Thanks

Jeff

SELECT DISTINCTROW Demographics.[Program Type], Demographics.[Active
Home Visit Client], [Parenting Information].[Child Date of Birth],
DateDiff("yyyy",[Child Date of Birth],Date()) AS Age, Count([Parenting
Information].ParentID) AS CountOfParentID
FROM Demographics INNER JOIN [Parenting Information] ON
Demographics.DemogID = [Parenting Information].DemogID
GROUP BY Demographics.[Program Type], Demographics.[Active Home Visit
Client], [Parenting Information].[Child Date of Birth],
DateDiff("yyyy",[Child Date of Birth],Date())
HAVING (((Demographics.[Program Type])="HMG") AND
((Demographics.[Active Home Visit Client])=-1) AND
((DateDiff("yyyy",[Child Date of Birth],Date()))<3))
ORDER BY Demographics.[Program Type];

 

[David F Cox]

You appear to be trying to group on fields with unique values, like date of
birth.

 

[Jeff]

I know that the grouping comes from the "Grouping by" on the query;
however, I'm not clear on how to remove this. I need to include the
Child Date of Birth on the query to calculate age, since I don't want
children on the report who are older than 3. Is there another option to
use in conjunction with Child Date of Birth which would not group the
dates?

Thanks

 

[David F Cox]

Start from your output. What information do you want to see?

If you have something as specific as DOB in a grouping all you can hope to
see is aggregated information about children with that date of birth.

You can set an upper limit on the age of a child by putting the DOB in a
WHERE clause, and set the aggregate function to MAX(DOB), which will give
you useful extra information. For example if you are displaying information
on children up to three years old it is useful to know that your sample set
only actually includes children up to 2 yrs and 1 month.

 

[Jeff]

I just want 1 total count of children under age 3 who are a part of a
particular gov't program. I need to use the parent and child records
because the parent record has the gov't program information.

 

[David F Cox]

example:

SELECT Table1.name1
FROM Table1
WHERE (((Table1.mydate)<=#9/30/2005#))
GROUP BY Table1.name1;

The SELECT and GROUP BY show the information that you want to see.
The WHERE clause selects just the records that you want. Choose where in the
Total: row.

 

[John Spencer]

Pardon me,

If you are using the query grid, change GROUP BY to WHERE under the fields you
are using as critiera.

The SQL statement would look like the following.

SELECT Demographics.[Program Type]
, Count([Parenting Information].ParentID) AS CountOfParentID
FROM Demographics INNER JOIN [Parenting Information] ON
Demographics.DemogID = [Parenting Information].DemogID
WHERE (((Demographics.[Program Type])="HMG") AND
((Demographics.[Active Home Visit Client])=-1) AND
((DateDiff("yyyy",[Child Date of Birth],Date()))<3))
GROUP BY Demographics.[Program Type]
ORDER BY Demographics.[Program Type];

By the way, your age calculation will probably give you bad results. For
example, DateDiff will return 3 for #12/31/2004 to Jan 1, 2007. It basically
counts boundaries crossed to get the count. If you are doing years, the result
is the same as subtracting the year numbers.

For this query I would use

[Child Date of Birth] > DateAdd("yyyy",-3, Date())

That would get every record where the date of birth is after today's date three
years ago. If you are paranoid about the data, you could use Between
DateAdd("yyyy",-3, Date()) And Date().

As for low birth weights, that could be included in this query by adding the
field to the query and summing it.

Field: CountLow: IIF([Low Birthweight Field = True,1,Null)
Total: Sum

Or alternative using count. Counts counts whether or not a value is present,
Null is the only thing that count does not count.

Field: CountLow: IIF([Low Birthweight Field] = True, 1, Null)
Total: Count

Your stats on the parent table will be more complex since you want to get rid of
the duplicates. The easiest wat to handle this is to use DISTINCT to build a
query with only the needed fields in it. Then you can use that as a basis for
your other summary values.

Well, I'm trying now to do this in separate queries, but when using
data from the parent and child to locate the information, I end up with
multiple rows in my query results, each with a count of 1. What am I
doing wrong?

I've included the SQL for my query.

Thanks

Jeff

SELECT DISTINCTROW Demographics.[Program Type], Demographics.[Active
Home Visit Client], [Parenting Information].[Child Date of Birth],
DateDiff("yyyy",[Child Date of Birth],Date()) AS Age, Count([Parenting
Information].ParentID) AS CountOfParentID
FROM Demographics INNER JOIN [Parenting Information] ON
Demographics.DemogID = [Parenting Information].DemogID
GROUP BY Demographics.[Program Type], Demographics.[Active Home Visit
Client], [Parenting Information].[Child Date of Birth],
DateDiff("yyyy",[Child Date of Birth],Date())
HAVING (((Demographics.[Program Type])="HMG") AND
((Demographics.[Active Home Visit Client])=-1) AND
((DateDiff("yyyy",[Child Date of Birth],Date()))<3))
ORDER BY Demographics.[Program Type];