Numbering

  • Thread starter Thread starter Brigham Siton
  • Start date Start date
B

Brigham Siton

We have a table that contains demographic info of people.

Each family has the Father's SSN as the UniqueID : [ssn]
Each member of the family has the person code: [pcode]

pcode = 1 Father
pcode = 2 Mother
pcode = 3 Child 1
pcode = 4 Child 2
and so on.

We can identify the father, mother. Having able to identify these two, the
rest are marked as a child.

Now our question: If the family have more than 1 child, how can we make the
pcode for the child to show 3, 4, 5, 6 and so on?

Let me know if theres a way to do this.

Thank you very much in advance.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What is the significance of the pcode? If you just want to number the
children in chronological order use birth dates instead of numbers. If
you just want to count the number of people in a family use the Count(*)
function in a query, group on the father's SSN.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmQKjYechKqOuFEgEQL2nACeNx2iXybQg72Sw0JkXUt4p5ViY3UAn2++
izYZfYipTWPW7K2nsB6bdp+8
=VeGh
-----END PGP SIGNATURE-----
 
Thank you very much for your response.

This pcode if combined with the SSN of the employee, will be the person's
personal ID. We are converting the data from msaccess to another system and
these SSN and PCODE are required to specifically determine who is what in
the family.

1 = Father
2 = Mother
3 = Child 1
4 = Child 2
5 = Child 3
and so on

On the children, it does not matter who is the oldest or youngest. We just
need to mark them from 3 and up.

It is not about counting the number of members in the family. The new
system we are going to load the file to requires this field. Otherwise, it
will reject the file.

If there is no other other way, we would need to go through 135,000 records
and do this manually.

Let me know what you think.

Thanks again.



MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What is the significance of the pcode? If you just want to number the
children in chronological order use birth dates instead of numbers. If
you just want to count the number of people in a family use the Count(*)
function in a query, group on the father's SSN.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmQKjYechKqOuFEgEQL2nACeNx2iXybQg72Sw0JkXUt4p5ViY3UAn2++
izYZfYipTWPW7K2nsB6bdp+8
=VeGh
-----END PGP SIGNATURE-----

Brigham said:
We have a table that contains demographic info of people.

Each family has the Father's SSN as the UniqueID : [ssn]
Each member of the family has the person code: [pcode]

pcode = 1 Father
pcode = 2 Mother
pcode = 3 Child 1
pcode = 4 Child 2
and so on.

We can identify the father, mother. Having able to identify these two,
the rest are marked as a child.

Now our question: If the family have more than 1 child, how can we make
the pcode for the child to show 3, 4, 5, 6 and so on?

Let me know if theres a way to do this.
Click to expand...
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you just need a sequential number for all rows (records) you can use
a AutoNumber data type column named 'pcode.'

If you need a family sequentially numbered you'd have to know how to ID
the father & mother record to assign the 1 & 2 pcodes, respectively.
For the child rows you could use a "running count" query. Then use the
query to load the external table instead of using the table as the data
source.

I don't have enough info about your tables to show an example of a
running count query. See this MS site for info on how to create a
running count query:

http://support.microsoft.com/default.aspx?scid=kb;en-us;290136

In the examples: instead of using DSum() use the DCount() function.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmQimoechKqOuFEgEQK69wCeMbSBgh0LLeiCh7Tu+WTgfSBABoIAn12v
N2s54xNkctjhNoEqQg4W8t/m
=/YA9
-----END PGP SIGNATURE-----

Brigham said:
Thank you very much for your response.

This pcode if combined with the SSN of the employee, will be the person's
personal ID. We are converting the data from msaccess to another system and
these SSN and PCODE are required to specifically determine who is what in
the family.

1 = Father
2 = Mother
3 = Child 1
4 = Child 2
5 = Child 3
and so on

On the children, it does not matter who is the oldest or youngest. We just
need to mark them from 3 and up.

It is not about counting the number of members in the family. The new
system we are going to load the file to requires this field. Otherwise, it
will reject the file.

If there is no other other way, we would need to go through 135,000 records
and do this manually.

Let me know what you think.

Thanks again.



-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What is the significance of the pcode? If you just want to number the
children in chronological order use birth dates instead of numbers. If
you just want to count the number of people in a family use the Count(*)
function in a query, group on the father's SSN.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmQKjYechKqOuFEgEQL2nACeNx2iXybQg72Sw0JkXUt4p5ViY3UAn2++
izYZfYipTWPW7K2nsB6bdp+8
=VeGh
-----END PGP SIGNATURE-----

Brigham said:
We have a table that contains demographic info of people.

Each family has the Father's SSN as the UniqueID : [ssn]
Each member of the family has the person code: [pcode]

pcode = 1 Father
pcode = 2 Mother
pcode = 3 Child 1
pcode = 4 Child 2
and so on.

We can identify the father, mother. Having able to identify these two,
the rest are marked as a child.

Now our question: If the family have more than 1 child, how can we make
the pcode for the child to show 3, 4, 5, 6 and so on?

Let me know if theres a way to do this.
Click to expand...
Click to expand...
Click to expand...
 
Let me try to experiment on the running count function you mentioned.

Thank you very much.


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you just need a sequential number for all rows (records) you can use
a AutoNumber data type column named 'pcode.'

If you need a family sequentially numbered you'd have to know how to ID
the father & mother record to assign the 1 & 2 pcodes, respectively.
For the child rows you could use a "running count" query. Then use the
query to load the external table instead of using the table as the data
source.

I don't have enough info about your tables to show an example of a
running count query. See this MS site for info on how to create a
running count query:

http://support.microsoft.com/default.aspx?scid=kb;en-us;290136

In the examples: instead of using DSum() use the DCount() function.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmQimoechKqOuFEgEQK69wCeMbSBgh0LLeiCh7Tu+WTgfSBABoIAn12v
N2s54xNkctjhNoEqQg4W8t/m
=/YA9
-----END PGP SIGNATURE-----

Brigham said:
Thank you very much for your response.

This pcode if combined with the SSN of the employee, will be the person's
personal ID. We are converting the data from msaccess to another system
and these SSN and PCODE are required to specifically determine who is
what in the family.

1 = Father
2 = Mother
3 = Child 1
4 = Child 2
5 = Child 3
and so on

On the children, it does not matter who is the oldest or youngest. We
just need to mark them from 3 and up.

It is not about counting the number of members in the family. The new
system we are going to load the file to requires this field. Otherwise,
it will reject the file.

If there is no other other way, we would need to go through 135,000
records and do this manually.

Let me know what you think.

Thanks again.



-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What is the significance of the pcode? If you just want to number the
children in chronological order use birth dates instead of numbers. If
you just want to count the number of people in a family use the Count(*)
function in a query, group on the father's SSN.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmQKjYechKqOuFEgEQL2nACeNx2iXybQg72Sw0JkXUt4p5ViY3UAn2++
izYZfYipTWPW7K2nsB6bdp+8
=VeGh
-----END PGP SIGNATURE-----

Brigham Siton wrote:

We have a table that contains demographic info of people.

Each family has the Father's SSN as the UniqueID : [ssn]
Each member of the family has the person code: [pcode]

pcode = 1 Father
pcode = 2 Mother
pcode = 3 Child 1
pcode = 4 Child 2
and so on.

We can identify the father, mother. Having able to identify these two,
the rest are marked as a child.

Now our question: If the family have more than 1 child, how can we make
the pcode for the child to show 3, 4, 5, 6 and so on?

Let me know if theres a way to do this.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top