CK said:
Hi Phil and others
I've been reading this thread as I'm trying to run a vaguely similar (?)
query but also stymied by the relationships. Perhaps you could shed some
light on this?
Using your example, I have two tables of locations and books. Copies of books
can be in several locations (the fact that they are copies is not relevant).
As a new book arrives, I need to assign it to an unused location (or two). I
want to look at a list of locations in datasheet view and see which books are
in each location and also which locations have no books. I have set up a
query (and a form based on it) in datasheet view showing locations and books.
Currently it shows me all locations but book fields are all empty. Knowing
the new book exists in the Book Table, how can I look it up in the datasheet
and assign it to an empty location?
(Books table has BookID as primary key, Location table has LocationID as
primary key and BookID as foreign key.)
Thanks for your help
Cheers
CK
Several issues here (including the show-stopper!).
Firstly, you say you're going to assign "a book" to a "..location (or
two)". So your "book" isn't a physical copy of a book, it's (hang on!)
the class of books having (say) the same title, author and publication
date, or (better) the same ISBN. Call it an "edition".
Puzzled? If I tell you to go and read "Pride and Prejudice" by Jane
Austen, I'm not directing you to any particular copy! So let's refine
things: you can have a book-edition, and a book-copy which is an
"instance" of the book-edition in question. A given book-edition of
"Pride and Prejudice" may have 522 pages, but only my copy has a coffee
stain on p32.
One way of representing this arrangement is to have two tables for
books. "Book-Edition" will contain the stuff which every copy must have
in common: title, publisher, page-count, author (etc). A second table,
call it "Book-Volume", will have information that relates only to one
particular copy, and can simply refer (via Foreign key) to the relevant
book-edition. Book-Volume could contain a field for LocationID, and
stuff like "condition", "acquisition date", etc. Of course, you'd only
need to do this is multiple copies are significant in your situation
(this is only an example!).
Do you see the error in your setup yet?
You have BookID as a field within your Location table. That could be
made to work, but it's bad design. The principle, often stated here, is
"one fact in one place". (You can read this up if you read articles
on "normalisation" - which you are urged to do.) If you have to have a
new record in your Locations table for every new book, you'll end up
repeating all the other Location fields (like address1, address2, City,
postcode, etc) for every book. Very wasteful, and what do you do when
the Cambridge library moves to a new building in the city? You'd have
to update the address once for every book at that branch, and (worse)
you also could end up with inconsistencies. No thanks...
Instead, the simplest approach would be to include a column for
LocationID in the Book-Volume table.
When you update the record for a specific volume with the ID for a
specific Location, you're recording the fact that this volume is
associated with that Location. It's relatively straightforward to
devise a form that will do that by allowing you to select from a
drop-down (combo box) of Locations and, when you select one, the
relevant ID will be written into the record, ready to be updated when
you move to a new record.
You'd see which books were at which location by writing a query which
"joins" the two tables. Set up a relationship first, and Access will
recognise it when you add both tables to the query builder. Normally a
query will show only those records where there is a link via the
relationship, and if you write a query to show books/locations you won't
see libraries which have no books.
To show locations with NO books, you'd need to change the properties of
the relationship to be an "Outer" join - one which includes records in
one or other table where there is no corresponding record in the other
table - here including libraries whose IDs do not appear in the record
of any volume. To do this, just double-click the line joining the
tables, and Access will offer you options.
What if you wanted to record borrowers? What if you wanted to know the
last borrower of that copy of P&P (the one who left the coffee stain on
p32)? For this you'd want a further table, of "Loans". This table
would have its own Primary Key, plus foreign keys for a "Borrower" table
and the "book-volume" table, and there could be columns for loan-date
and return date. The you could scan that table to see who had it before
me...
Phil
