Change a value in one table depending on the value(s) in another table

[Rex]

Hi,

I want to change a value in one table depending on the value(s) in
another table. I am trying to achieve this in a form.

to elaborate

I have a many-to-many relationship between tables Forms and Family and
the associate table is called Forms_Family. Forms_Family have fields
"dateSent" and "dateReceived". The Family table has a field called
"familyStatus". I want to change the value of "familyStatus" field
depending on the kind of form that has been sent and when it was
received back:

Forms_Family Table
FamilyID | FormsID | dateSent | dateReceived
------------------------------------------------------------------------
000 | form1 | 1/8/2003 |
001 | form2 | 5/9/2003 |

in the above table when I receive form1 and as soon as I enter a date
in "dateReceived" field I want the status of that particular family to
change in the Family table

Any help would be greatly appreciated..

Rex

[keri]

Rex,

I figure somebody else will probably ask this question too before
suggesting something;

How will the value in the dat field determine the status?

eg are all dates in december going to give one status and all dates in
january going to give another status etc
or
are all dates within the last 30 days going to give one status and all
dates between 30 and 60 days going to give another status etc
or
do you want one status if one set of forms has been sent, and another
status if the forms have been recieved etc

The answer to this question will determine the best way to get the
result you want. It may be you need a query on a table with a function
to calculate the status based on a date, or it may be you need a table
with dates and status defined or it may be something else - so more
info would be great!
Rex wrote:

Hi,

I want to change a value in one table depending on the value(s) in
another table. I am trying to achieve this in a form.

to elaborate

I have a many-to-many relationship between tables Forms and Family and
the associate table is called Forms_Family. Forms_Family have fields
"dateSent" and "dateReceived". The Family table has a field called
"familyStatus". I want to change the value of "familyStatus" field
depending on the kind of form that has been sent and when it was
received back:

Forms_Family Table
FamilyID | FormsID | dateSent | dateReceived
------------------------------------------------------------------------
000 | form1 | 1/8/2003 |
001 | form2 | 5/9/2003 |

in the above table when I receive form1 and as soon as I enter a date
in "dateReceived" field I want the status of that particular family to
change in the Family table

Any help would be greatly appreciated..

Rex

[Jason Lepack]

If the status of a family can be determined from a logical evaluation
of the data in forms_family table then you don't need to store that
information in a table. You can use a query find that information at
any time.

And then this is where Keri's statement comes into play. How does this
information affect the status? Do you have a list of 500 different
stati or is there only 5 or 6 different stati?

Cheers,
Jason Lepack

keri wrote:

Rex,

I figure somebody else will probably ask this question too before
suggesting something;

How will the value in the dat field determine the status?

eg are all dates in december going to give one status and all dates in
january going to give another status etc
or
are all dates within the last 30 days going to give one status and all
dates between 30 and 60 days going to give another status etc
or
do you want one status if one set of forms has been sent, and another
status if the forms have been recieved etc

The answer to this question will determine the best way to get the
result you want. It may be you need a query on a table with a function
to calculate the status based on a date, or it may be you need a table
with dates and status defined or it may be something else - so more
info would be great!
Rex wrote:

Hi,

I want to change a value in one table depending on the value(s) in
another table. I am trying to achieve this in a form.

to elaborate

I have a many-to-many relationship between tables Forms and Family and
the associate table is called Forms_Family. Forms_Family have fields
"dateSent" and "dateReceived". The Family table has a field called
"familyStatus". I want to change the value of "familyStatus" field
depending on the kind of form that has been sent and when it was
received back:

Forms_Family Table
FamilyID | FormsID | dateSent | dateReceived
------------------------------------------------------------------------
000 | form1 | 1/8/2003 |
001 | form2 | 5/9/2003 |

in the above table when I receive form1 and as soon as I enter a date
in "dateReceived" field I want the status of that particular family to
change in the Family table

Any help would be greatly appreciated..

Rex

[Rex]

It does not matter when I receive the form. The requirement is as soon
as I enter a date in the "dateReceived" field.. it should check for a
particular form (say form1) and should also check that the "dateSent"
is not NULL.. if this condition is met the status of that particular
family should change..

Thanks
Rex

On Jan 24, 2:02 am, "keri" <[e-mail]:

Rex,

I figure somebody else will probably ask this question too before
suggesting something;

How will the value in the dat field determine the status?

eg are all dates in december going to give one status and all dates in
january going to give another status etc
or
are all dates within the last 30 days going to give one status and all
dates between 30 and 60 days going to give another status etc
or
do you want one status if one set of forms has been sent, and another
status if the forms have been recieved etc

The answer to this question will determine the best way to get the
result you want. It may be you need a query on a table with a function
to calculate the status based on a date, or it may be you need a table
with dates and status defined or it may be something else - so more
info would be great!

Rex wrote:

Hi,

I want to change a value in one table depending on the value(s) in
another table. I am trying to achieve this in a form.

to elaborate

I have a many-to-many relationship between tables Forms and Family and
the associate table is called Forms_Family. Forms_Family have fields
"dateSent" and "dateReceived". The Family table has a field called
"familyStatus". I want to change the value of "familyStatus" field
depending on the kind of form that has been sent and when it was
received back:

Forms_Family Table
FamilyID | FormsID | dateSent | dateReceived
------------------------------------------------------------------------
000 | form1 | 1/8/2003 |
001 | form2 | 5/9/2003 |

in the above table when I receive form1 and as soon as I enter a date
in "dateReceived" field I want the status of that particular family to
change in the Family table

Any help would be greatly appreciated..

Rex- Hide quoted text -- Show quoted text -

[Rex]

I only have four status.. namely "Approached","Enrolled","Declined",
and "Removed"

cheers

On Jan 24, 3:19 am, "Jason Lepack" <[e-mail]:

If the status of a family can be determined from a logical evaluation
of the data in forms_family table then you don't need to store that
information in a table. You can use a query find that information at
any time.

And then this is where Keri's statement comes into play. How does this
information affect the status? Do you have a list of 500 different
stati or is there only 5 or 6 different stati?

Cheers,
Jason Lepack

keri wrote:

Rex,

I figure somebody else will probably ask this question too before
suggesting something;

How will the value in the dat field determine the status?

eg are all dates in december going to give one status and all dates in
january going to give another status etc
or
are all dates within the last 30 days going to give one status and all
dates between 30 and 60 days going to give another status etc
or
do you want one status if one set of forms has been sent, and another
status if the forms have been recieved etc

The answer to this question will determine the best way to get the
result you want. It may be you need a query on a table with a function
to calculate the status based on a date, or it may be you need a table
with dates and status defined or it may be something else - so more
info would be great!

Rex wrote:

Hi,

I want to change a value in one table depending on the value(s) in
another table. I am trying to achieve this in a form.

to elaborate

I have a many-to-many relationship between tables Forms and Family and
the associate table is called Forms_Family. Forms_Family have fields
"dateSent" and "dateReceived". The Family table has a field called
"familyStatus". I want to change the value of "familyStatus" field
depending on the kind of form that has been sent and when it was
received back:

Forms_Family Table
FamilyID | FormsID | dateSent | dateReceived
------------------------------------------------------------------------
000 | form1 | 1/8/2003 |
001 | form2 | 5/9/2003 |

in the above table when I receive form1 and as soon as I enter a date
in "dateReceived" field I want the status of that particular family to
change in the Family table

Any help would be greatly appreciated..

Rex- Hide quoted text -- Show quoted text -

[keri]

Here's what I think you are going to need to do provided I understand
you correctly. (I am confusing myself because you have a table named
Forms.....)

I think you are telling me that you are entering a date (recieved) on a
form. When you have a date in this field (presumably date recieved) in
your table you want the status to change. I am not so sure why you feel
it needs to search for a form - if this is needed then the solution
below won't help. If you could make this clearer it would be helpful.

However your solution will probably involve creating a query based on
the table that includes the date received field (and whatever other
fields the status is dependant on). Then you can create an expression
in your query to establish the status, eg

Status: IIf([Datercvd] Is Null,"Form sent","Form Recieved")

This would check if the field Datercvd is null. If it isn't the status
field will show "Form Received", if it is null then it will show "Form
Sent".

Another slightly more complex query;
Status: IIf([Datercvd] Is Not Null And [datesent] Is Not Null,"Form
Received",IIf([datercvd] Is Not Null And [datesent] Is Null,"Form
received but no sent date",IIf([datercvd] Is Null And [datesent] Is Not
Null,"Form sent","other status")))

So - if there is a date in the datercvd and datesent fields the status
is "Form Received"
if there is a date in datercvd but no date in date sent the status is
"Form received but no sent date"
If there is a date in datesent but no date in datercvd then the status
is "Form Sent"
If it is something other than this the status is "Other Status"

Obviously this is just to give you an idea of what can be done.
(Apologies for a messy query but I am in a rush!)