ok - i have clients submitting (uploading) documents to a folder server side, at which time i can put the Title of the piece in a field in his record... these submissions are to be reviewed and evaluated, with a corresponding field, "status" updated - the member will need to be able to view his status at a later time -
I don't have a problem showing the contents of the particular fields in the db; my issue is that persons will be submitting more than one piece and each submission will require its own status, of course. obviously i don't want to insert multiple titles into one field, so i probably need to have a second table just for a member's submission titles, but i don't know how to integrate this so that a person can have many titles with a status avaiable for each...
clues, brilliant people? :o)
thanks so much,
glenn
"cross referencing" between two tables
Moderator: General Moderators
-
microthick
- Forum Regular
- Posts: 543
- Joined: Wed Sep 24, 2003 2:15 pm
- Location: Vancouver, BC
So, you make two tables and create a one-to-many relationship between them.
Table 1: Clients
ClientID (Make ClientID the primary key)
ClientName
ClientAddress
Etc, etc.
Table 2: Documents
DocID (Make DocumentID the primary key)
ClientID
DocName
UploadDate
Status
Etc, etc.
Now, when you want to get all the documents via a particular client, your query would look something like:
SELECT Clients.*, Documents.* FROM Clients, Documents WHERE Documents.ClientID = Clients.ClientID AND Clients.ClientID = $SomeClientID
Table 1: Clients
ClientID (Make ClientID the primary key)
ClientName
ClientAddress
Etc, etc.
Table 2: Documents
DocID (Make DocumentID the primary key)
ClientID
DocName
UploadDate
Status
Etc, etc.
Now, when you want to get all the documents via a particular client, your query would look something like:
SELECT Clients.*, Documents.* FROM Clients, Documents WHERE Documents.ClientID = Clients.ClientID AND Clients.ClientID = $SomeClientID