Relationships - Showing Text

[Dos Equis]

I am creating a database which tracks Newspaper subscribers, carriers
and areas delivered. I have 3 tables: Subscriber Data, Carriers &
Areas.

The Carrier table has 3 fields: First Name, Last Name & Carrier ID

The Area table has 4 fields: Area, CarrierID, Homes and Subscribers
(Free paper - we saturate high demographic areas without making
recipients pay)

In the Area table the CarrierID field is a number as in the Carrier
table but shows as a name.

The Subscriber table has many fields but the 2 of concern right now are
Area & Carrier.

I have biult relationships between the three tables with referintial
integrity enforced along with cascade updates.

My problem is when I change an areas carrier, the change is not
reflected in the subscribers table. In the subscriber table the
carrier field is text not numerical and I think this has something to
do with my problem. It has also occured to me that I may not need to
keep the carrier info in the subscriber data table but not sure if that
is correct.

ANY help would be appreciated.

Thanks,

Byron

 

[jacksonmacd]

I am creating a database which tracks Newspaper subscribers, carriers
and areas delivered. I have 3 tables: Subscriber Data, Carriers &
Areas.

The Carrier table has 3 fields: First Name, Last Name & Carrier ID

The Area table has 4 fields: Area, CarrierID, Homes and Subscribers
(Free paper - we saturate high demographic areas without making
recipients pay)

In the Area table the CarrierID field is a number as in the Carrier
table but shows as a name.

The Subscriber table has many fields but the 2 of concern right now are
Area & Carrier.

I have biult relationships between the three tables with referintial
integrity enforced along with cascade updates.

My problem is when I change an areas carrier, the change is not
reflected in the subscribers table. In the subscriber table the
carrier field is text not numerical and I think this has something to
do with my problem. It has also occured to me that I may not need to
keep the carrier info in the subscriber data table but not sure if that
is correct.

You should remove Carrier from the Subscriber table because a
particular subscriber is *in* a particular Area, and each Area has a
particular Carrier. To know which Carrier services each Subscriber,
you will need to build a query with all three tables.

I never use cascading updates, so I am not 100% certain, but I would
not expect it to make the change that you described...

 

[Dos Equis]

Ok,

Took carriers out of subscriber data and it looks like everything is
working fine. Next issue:

I have two fields that need to balance each other and would like to
automate them.
One is the 'Area' field and the other is 'Mailed'. Area is a text
entry/lookup field - Mailed is a yes/no box.

If 'Area' = Mail Mailed = yes or box checked. Is there and If/then
statement that would automate this through the validation property or
is it time to code? (If you say code, include at least a reference
please)

Thanks,

Byron

 

[jacksonmacd]

In your form that you use for data entry, place some VBA code that
sets the value. In particular, the code will be in the AfterUpdate()
event of the txtArea textbox. Something like this should work (check
for correct control names):

Private Sub txtArea_AfterUpdate()
If txtArea = "Mail" Then
chkMail = True
Else
chkMailed = False
End If
End Sub