Display the count of a subset of ages in database 1

[sicvic]

Hi
I have a simple question.

I have setup a research database that has ages of kids (Field name, "Age") within an age bracket (from 0 to 6).

On my analysis layout page, A data point I am interested in displaying is a count of the number of kids with ages < 1

I have looked at other post with similar problems but I have been unable to replicate it with any success

Database characteristics

1 file, 1 table

Thks

 

[JeanW]

Depending how your age calc is, you could make a calculation to flag all the records with a 'year' value < 1.

Something along these lines:
Case(
Yearfield < 1;1;0
)

Through a relationship based upon the value of this field, you make a count.

This will give you the number of records < 1 year.

HTH

 

[sicvic]

Appreciate the response

I tried the calculations

Define Age 1 = Case(Yearfield < 1;1;0)

Display count Age 1 = Count (Yearfield)

I thought this calculation should give me a total of the ages <1 in the "Display count Age 1" field. However, it only flags that the records with 1 or 0.

What is wrong with my counting calculation?

 

[JeanW]

That's right. All your records with age < 1 wil have a value '1' in the flagfield.

Now make a count on your 'Display count Age 1' field (not the Yearfield), which will have a 1 or a 0 value.

It will give you the total of all the '1' = total records with age < 1.

HTH

 

[JeanW]

Sorry, make the count on your Define Age 1 field....not your age field...and you have to make the count based on a relationship (self join) for the whole file.

 

[sicvic]

How would I go about the self join

Create another table - Count and what fields do I create and to what Fields in the main table do I set a relationship?

 

[JeanW]

Make sure you have a 'constant' field in your file = calc = number = auto-enter 1.

Make a table occurence 'Constant', with a relationship (Constant) to the same file where constant = constant.

This will give you a constant 'open' channel for all your records to all your records in the table.

You already have Age 1, that can have a value 1 or 0.
Make a countLowerOne field, calc Count(Constant::Age1), where Age 1 is based upon the relationship.
This will take all the Age 1 field into account in your file.

HTH

 

[sicvic]

Thanks for the Guide - that was awesome

For others who may be have been following this post: Lets us says you recorded all ages of the kids in your neighbourhood.

In your database you want to count how many kids are ages <1

This is my setup

you will need two tables

Table 1: Each record in this table is a kid
a. Ages: number
b. Define Age 1 = case (Age < "1"; "1";"0")
Table 2
a. Define Age 1 = auto serial
b. Count Age 1 = count (Table 1:efine Age 1)

Create a relationship btw the fields Define Age 1 in Table 1 and table 2. Clink on the create records.

My next mission is to figure how to do it for all age bracket. Jean would I be able to use Table 2 to make calculations for other age brackets?

 

[JeanW]

Several ways to ge here, depend of the outcome.

What do you want as final information, why and when.

You could use a simple calculation as a one time sort of display, but if you want it as part of a 'process' there will be other techniques to use.

I think about something like the display in a portal and from there a navigation to each record in the found set, or a direct navigation to a record within a foundset, and the rest of the records in a portal etc.

Could you give more info about what you want to acomplish ?

 

[JeanW]

A possible way to go is a setup along these lnes:

Make the calculation of the age in years dynamic.

Make a script that wil search and isolate the records with a value '0' in the year calculation field.

To have a found set of children age < 1 push the button to activate that script.

HTH

JW