query matching more than one field (driving me mad!)

[eleanor b]

this is probably very simple but I cannot for the life of me work it out...

I have an address book. I then have a 'manuscript' form containing info from many different tables. Each manuscript has up to five different names from the same field in the address book (author1, author2, referee1, referee2, referee3).
I can easily create a command on the address book form linking the name with any ONE of the entries on the manuscript form (for example: list manuscripts for which this person is author1) but what I want is to list all manuscripts where this person is author1 OR author2. Or list all manuscripts for which this person is referee - whether their names is on the manuscript as referee1, 2, or 3.

I hope this makes sense.

any tips, advice very much appreciated as I am stuck at this point in developing the database.

thank you, Eleanor

 

[Fizzio]

Looking at your description it really depends on your data structure.

Rather than storing Author1 Author2 etc, use a junction table to create a many to many relationship which makes querying soooooooo much easier. Have a look at the (very) ruf and ready example I have attached as an example.

 

[eleanor b]

thank you so much for this. I will look properly tomorrow as I have to go home now - I live in france and we are not allowed to work more than 35 hours a week - such a hard life!

The problem is firstly that I inherited the database as is, and secondly that author1 author2 etc are not random labels but refer to the amount of work they have done on the manuscript, so it does matter that they keep their labels.

cheers

 

[The_Doc_Man]

so it does matter that they keep their labels.

No it doesn't. It matters that you know their ordinal position in the credits, which is a different statement. You limit yourself when you require specific labels in this case because you have a non-normalized database if you do that.

Author1, Author2, Author3.... are what are called a Repeating Group. This violates first-normal form for a normalized DB. This leads to two instant problems.

1. Waste of space when less than 3 contributors.
2. No way to indicate a 4th or later contributor.

The correct structure to omit this problem is (maybe) like this:

tblManuscript
fldManuID, primary key, possibly autonumber field
fldTitle, text
fldSize, long (pick your favorite units: size of file, words, paragraphs, pages... in fact, you can have more than one such field and store EACH of these, depending on what your statistical needs are.)
... other fields associated with the manuscript but not the authors

tblAuthor
fldAuthID, prime key, possibly autonumber
fldFName
fldMName
fldLName
fldSuffix
fldPrefix
... other fields describing known authors

tblManuAuth
fldManuID, foreign key, long
fldAuthID, foreign key, long
fldAuthOrd, integer, position of this author in the manuscript's list of contributors
In this table, your foreign keys should have separate indexes (Dup OK) but you could ALSO define a compound prime key if you wished. If you did so, the manuscript and author ID fields together would be that key. (The same author cannot be listed twice as a contributor to the same manuscript, so the combination should be unique.)

Now, to find an author, you seach the tblAuthor to get the ID, then search tblManuAuth to find ANY WORK to which that author contributed.

To see the authors in the correct order, sort tblManuAuth by ManuID then by fldAuthOrd, then look up the names using fldAuthID. Look up the manuscript name using fldManuID.

 

[Fizzio]

Thanks Doc Man - better explanation than I could muster up!

Sample Db is as you say except has an additional field for reference ie Author / Referee etc. with compound primary key.

I found it easier to create than explain - now what does that make me again - a visual teacher (something like that anyway )