One to one relationship

[ngan]

I have Access XP with link tables to SQL server 2000. There is a read only
view that I linked to called NoShow. This lists all the times a rider was not
at their location when they are to be picked up (therefore they are flagged
noshow). Fields include clientid, pickup date, PU and DO addresses, etc.

Riders with only one noshow during this period gets no letter. Riders with
more than one, we have to send out a notification letter every time the rider
is no showed. In a floating 60 day period, if the rider hits the 6th no
show, a letter of suspension is sent out. We do these batch letters every
day.

Since the view is read only, I was thinking of creating a table called
tblLetter with fields LetterID (PK), ClientID, NoShowID, LetterSentDate,
LetterSentBy. So every time a letter is sent out, a new record is entered in
by the program and timestamped.

1. If the batch is running every day, noshow records that already had a
letter sent should be flagged so we don't send out a letter for that same
noshow the next day.

2. when the client reaches the 6th, the batch would send out a different
type of letter (suspension).

I'm at a stump on how to do both figure out which record has a letter sent
and the number of noshow the rider has.

Anyone can point me in the right direction?

Thanks.

 

[tina]

well, you could run a Totals query on your linked read-only table to pull
all clients with more than one ( > 1) no-show record. make sure you include
the ClientID field in this query.

create a Totals query based on tblLetters and the read-only table, that
counts the number of letters sent for each client. again, include ClientID
in this query.

use a third query to link the previous two, on the Client ID field, with
criteria where the NoShow count is greater than the LettersSent count, but
less than 6. use this query to generate your no-show notification letters.

use a fourth query to again link the first two queries on the Client ID
field, this time with criteria where the NoShow count is greater than the
LettersSent count, and equal to 6. use this query to generate your
suspension letters. (presumably you only send one suspension letter, at the
6th offense.)

btw, assuming your read-only table includes a primary key value for each
NoShow record, all you need to track in your native table is

tblLetters
LetterID
NoShowID (foreign key from read-only table)
LetterType (no-show notification, suspension notification)
DateSent
SentBy
(you don't need to include the ClientID, because that value is available
through the linked NoShow record in the read-only table. remember the basic
normalization rule: don't duplicate data, except for foreign key values.)

hth