New to Access, please help!

[octatyan]

I have two tables : 1. Adults 2. Kids

I want my report to list all the Adults (from the Adults table) and also list the kids they have if any (from the kids table).

Currently, my report only shows the adults that HAVE kids. I want the report to show ALL the adults (even if they don't have kids) and also any kids those adults may have. I have used the Reprt Wizard and also tried designing it from scratch. But I cant seem to get my report to show the information the way I want it. Please help! Thanks!

 

[bradcccs]

Sounds like a relationship problem in your query setup.

Check the link between the Adult Table and Kid Table (by double clicking on the link), and change the selection to:

Include ALL records from table Adult and only those from table Kids where the joined fields are equal (From memory - may not be verbatim)

HTH

Brad.

 

[saintsman]

Have you tried to produce your report direct from the table? If you have , produce a query first and design your report from the query.

If your query shows the right result so will your report.

If the query does not work, what is the link between the two tables that puts parents with children? Your problem may lie there.

 

[octatyan]

I changed the relationship and that worked! Thanks!

But I have another question...
I am trying to add up all their ages (adults+their kids). But it will not add up the ages of the adults with no kids because for example, if the adult is 45 yrs old and he has no kids, then it seems that Access gives the kids age as a NULL value. So the 'Total Age' column is a NULL value as well (45+NULL=NULL?)

How do I change it so that Access changes all NULL values to zeroes. (for example, is their a certain code I can use in the 'Build' or 'Zoom' option?)

By the way, this is all being done in a Query.

 

[Matthew Snook]

From Access help on "About working with blank fields in queries":

"When a field contains no values, it contains a Null value or, for Text, Memo, or Hyperlink fields, a Null value or a zero-length string. If Null values exist in a field, they can affect the query's results. Following are some guidelines for working with Null values and zero-length strings in queries"...

"Converting Null values to zero

When you have fields that contain Null values, you can create an expression that converts the Null values to zero. You might do this if you want the records containing Null values to be included in an aggregate calculation, or if you want to prevent an expression from resulting in a Null value when a field that's referenced in the expression contains Null values. Use the Nz function, for example, to convert Null values to zero:

Nz([Subtotal],0+Nz([Freight],0)"...

From a different approach, you can also build a default value into your table so that fields are populated with zeros instead of nulls from the time of record creation.

Matt