If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
One to one relationship
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. |
#2
|
|||
|
|||
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 "ngan" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Impossible? Relationship / Join Quandary | SteveTyco | Database Design | 1 | May 5th, 2005 01:58 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
Deleting a foreign key relationship in SQL | Stevio | Running & Setting Up Queries | 2 | December 22nd, 2004 02:51 PM |
Re-establishing a broken relationship | David McKnight | Database Design | 2 | December 1st, 2004 10:49 AM |
Setting dual relationship with tool connector | Carlos | Visio | 0 | May 20th, 2004 12:51 AM |