I'm not sure what to call this...

[Scott Crews]

Okay, I'm not really sure how to express this, or the best
way to do this really, but here's what I need:

I have a database of sponsored children. When children
move away and are no longer involved, I need
to "unsponsor" them.

In table "Child", each child(record) has a field (Yes/no
box) "Click to Add/Remove". If the box is checked, they
are considered not in the program. Also, in table "Child
Sponsor Matchup", each child has a "Sponsor Number".

What I need is some kind of script or something where (if
this is possible) at any point if the "Click to
Add/Remove" box in the "Child" table is checked, then
the "Sponsor Number" field in the "Child Sponsor Matchup"
table is set to 1.

Is this possible?

Thanks,

Scott

 

[TC]

Scott, several points.

1. "Click to Add/Remove" is a terrible name for a field in a table! The name
of the field should be an adjective describing some aspect of that
particular record. If the checkbox field is True (ticked) when the child is
here/active/involved, and False (blank) when the child is moved
away/inactive/no longer involved, call the field something like Current,
Active, or Involved. Then you can add a >label< such as "click to
add/remove" when you put that field on a form. You will get hopelessly
confused if you use "labels" for "field names" in the way that you seem to
have done with that checkbox.

2. Why would you reset the Sponsor Number to 1 when a child moves away? A
child is either current/active/involved, in which case it has a sponsior
number (which in general will not be 1), or, it is noncurrent/inactive/no
longer involved, in which case it presumeably has >no< sponsor number - not
number 1!

3. Can a child have several sponsors simultaneously? The answer to this has
a critical affect upon the structure of your database tables. Further to
that, I suspect that you have not read-up on the topic of "normalization".
This is a critical topic to understand. Start here for a dry but instructive
read:

http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC

 

[Guest]

Okay, maybe this will help clarify..

Using an adjective and not a phrase is a good point--it probably will cross me up at some point. I'll correct that

There is only one sponsor for each child. And you're right, when a child moves away, he does need to have no sponsor number. I didn't think I could just clear a field out like that, so I created Sponsor Number 1 as a "null sponsor" so that it could collect all the children that left the program and didn't have sponsors otherwise. But still, I don't know how to do that either. Does anyone know how

As far as normalization goes, I've read that document before, and I think I did a pretty good job of normalizing the data. I was handed this database as one table with about 50-60 different fields, and broke it up into 19 tables...according to how I thought normalization applied to that database. When it came time to add on the Sponsor Number part, I probably didn't do that completely kosher. If you know of a more detailed explanation of normalization though, I'd be glad to read it, as I will readily admit I'm not very capable in the art of normalization

So if you're just tuning in, heres the situation
When checkbox "Click to Add/Remove" in table Child is true, I need for field "Sponsor Number" in related table "Child Sponsor Matchup" to be set to 1, or just nothing, if that's possible

Thanks for trying to understand my incredibly odd plight

Scott

 

[TC]

Hi Scott

Not to sound picky, but, when you reply to a post, please hit the reply
button, then retain the whole text of the previous posts, in your reply.
This is because many of us answer quite a few questions in these newsgroups.
It is difficult to remember what we are discussing with each person, unless
each person retains the whole text of the previous conversation, in each new
reply. Understand that no-one has time to manually search back to previous
posts, to see what was said in those previous posts.

You can add your reply at the top of the previous posts ("top posting"), or
at the bottom ("bottom posting"), or under each individual point in the
post. Try to use a single one of those three styles, for each reply you make
in a thread. This makes it easier for everyone else to follow along.

I'll use the "after each point" style, to reply to your comments & questions
below.

Okay, maybe this will help clarify...

Using an adjective and not a phrase is a good point--it probably will

cross me up at some point. I'll correct that.

It >definitely< will cause you >huge confusion< down the track! Clean it up
before you proceed

There is only one sponsor for each child. And you're right, when a child

moves away, he does need to have no sponsor number.

Then, this is what you need in terms of table structure (your names may
differ):

tblChild - a table containing details for each known child:
ChildID (primary key)
SponsorID (see below)
forname, surname, date of birth, etc.

tblSponsor - a table containing details for each known sponsor:
SponsorID (primary key)
forname, surname, address, etc.

When a child is sponsored, you simply edit the appropriate Sponsor ID into
the SponsirID field of the appropriate record in tblChild. This "connects"
that sponsor to that child. We say that SponsirID is a "foreign key", to the
sponsior table, from the child table.

To "unsponsor" a child, you simply set the SponsorID field of the
appropriate record in tblChild, to Null.

There are other ways that you could do this. For example, since children &
sponsors are both people, you could maybe have a Person table, where each
record was a child, or a sponsor, as shown by a flag within the table. But
this would lead to certain other complications. I would go with what I
suggested above.

I didn't think I could just clear a field out like that, so I created

Sponsor Number 1 as a "null sponsor" so that it could collect all the
children that left the program and didn't have sponsors otherwise. But
still, I don't know how to do that either. Does anyone know how?

This gives children who do not have a sponsor:

SELECT * FROM tblChild WHERE SponsorID IS NULL

This gives children who >do< have a sponsor:

SELECT * FROM tblChild WHERE SponsorID IS NOT NULL

This gives the full child >and sponsor< details of children who do have a
sponsor:

SELECT *
FROM tblChild AS c, tblSponsor AS s
WHERE s.SponsorID = c.SponsorID

As far as normalization goes, I've read that document before, and I think

I did a pretty good job of normalizing the data. I was handed this database
as one table with about 50-60 different fields, and broke it up into 19
tables...according to how I thought normalization applied to that database.

I can't see any way that you would need 19 for what you have described so
far!

When it came time to add on the Sponsor Number part, I probably didn't do

that completely kosher. If you know of a more detailed explanation of
normalization though, I'd be glad to read it, as I will readily admit I'm
not very capable in the art of normalization.

Understanding normalization is a critical part of designing a comptent
database. Do some more reseatrch on the web. There are hundreds of documents
& tutorials out there!

So if you're just tuning in, heres the situation:
When checkbox "Click to Add/Remove" in table Child is true, I need for

field "Sponsor Number" in related table "Child Sponsor Matchup" to be set to
1, or just nothing, if that's possible.

No - that is not what you need, at all.

Thanks for trying to understand my incredibly odd plight,

Your plight is typical of new developers coming to grips with normalization


Cheers & hope this helps - not trying to lecture!
TC