Using Count

[Roger Bell]

I have a simple data base with several fields for
children: child1, child2 etc. I have created a Text box
(unbound) on the form to count the number of children in
a single record, but cannot seem to get it to work.
Could anyone please tell me the correct code for the
source control

Thanks for any assistance

 

[Rick Brandt]

I have a simple data base with several fields for
children: child1, child2 etc. I have created a Text box
(unbound) on the form to count the number of children in
a single record, but cannot seem to get it to work.
Could anyone please tell me the correct code for the
source control

Thanks for any assistance

Not a great design. You should have a separate table with a one-to-many
relationship so you can enter anywhere from zero to an indefinite number of
children into the related table with each being a record rather than a field.
Then a simple totals query can give you the count. Also with your current
design you have to modify the table design and anything referencing it anytime
you discover that you don't have enough fields (someone comes along with more
children than you have defined).

 

[Larry Daugherty]

Hi Roger,

You haven't yet bought into relational databases. The format you suggest
reflects "SpreadsheetThink", i.e. for repetitive attributes you just add
another column. You could make that work in Excel.

In a relational database, when you have repeating attributes, you create a
new table and each attribute has its own record in that table.

For purposes of discussion assume a table. tblParent and another table,
tblChild. tblParent has a Primary Key (ParentID - type Autonumber) and lots
of other attributes tblChild also has a Primary Key (ChildID - type
Autonumber) and it also includes a field called a Foreign Key - type Long
Integer - which has the Primary key of the parent of this child. tblChild
would also have lots of other fields for attributes..

If you've never worked with relational databases, it all seems
counterintuitive. You'd think the parent would maintain a list of all of
its children. Not so. That foreign key in the child table makes it all
work out. When you run a query to see which children belong to which parent
you'd sort on the parent Name but the important link is that between the
Parent's Primary Key and the number stored in the child's Foreign key field.

Now I'll make it worse. You've picked a situation that you can actually do
in a single table, say tblPerson by using recursion (usually forbidden). It
would start out like tblParent above but would definitely provide a field
for gender and would have PersonID, MomID, DadID, and everyone would have
the same list of attributes. Someone with NomID and DadID blank would be
the start of a new line. This will work for any number of generations.
Otherwise, MomID would have the Primary Key value for Mom's record and DadID
would have the Primary Key value for Dad's record.

Note that you can do the same kind of thing when you are showing employee
rankings except you replace MomID and DadID with MyBossID. The person at
the top of the heap would have the field "MyBossID" blank. Everyone else
would have a boss.

Every copy of Access comes with a sample application named "Northwind".
Open it and look at some of what's there in both run mode and design mode.
Also look in Tools|Relationships to see some interesting things.

HTH

 

[Jamie Collins]

The format you suggest
reflects "SpreadsheetThink", i.e. for repetitive attributes you just

add another column.

In a relational database, when you have repeating attributes, you
create a new table and each attribute has its own record in that
table.

If you take this too far and *always* create a new table in these
situations, you'll end up committing a database design flaw called
'attribute splitting' or 'orthogonal design'. You end up splitting your
database into too many parts, having to create numerous joins just to
get any meaning out.

Consider the alternatives first. Before creating a table of valid
values for Employees.sex with the associated overhead of a foreign key,
consider whether a CHECK constraint only allowing valid ISO sex code
value will suffice. Another common way to incorporate related
attributes is to use a single column of bitwise values, again with a
CHECK constraint to ensure only valid combinations are allowed.

Jamie.

--