Page 1 of 1

"cross referencing" between two tables

Posted: Sun Jan 18, 2004 1:19 pm
by glennn3
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

Posted: Sun Jan 18, 2004 4:07 pm
by microthick
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

Posted: Sun Jan 18, 2004 4:25 pm
by glennn3
yes, thanks very much....