Trying to Understand and Use Relationships

When I create a query between tables and use relationships between the
tables (I am using an Access teach yourself book) the resulting datasheet
view is blank, even though I already have data entered into the different
tables. Shouldn't I be able to create a query that uses 2 tables and then
when I open the query datasheet view I would see all the query selected data
from both tables? I know I'm not understanding something. The end result
of what I want to do is create a data entry form from a query that has field
inputs from 2 separate tables. It's not working for me. From reading thru
many of the emails on these Access newsgroups my question just seems so
elementary. But, there it is. I know my way around Access but only as a
user dealing with single tables (I do make design changes on
tables/queries/forms/reports). Thanks

On Sun, 11 Jan 2009 18:04:07 -0600, Kathy Thornton wrote:

When I create a query between tables and use relationships between the
tables (I am using an Access teach yourself book) the resulting
datasheet view is blank, even though I already have data entered into
the different tables. Shouldn't I be able to create a query that uses 2
tables and then when I open the query datasheet view I would see all the
query selected data from both tables? I know I'm not understanding
something. The end result of what I want to do is create a data entry
form from a query that has field inputs from 2 separate tables. It's
not working for me. From reading thru many of the emails on these
Access newsgroups my question just seems so elementary. But, there it
is. I know my way around Access but only as a user dealing with single
tables (I do make design changes on tables/queries/forms/reports).
Thanks

As a point of terminology those lines in a query are "joins", not
"relationships". Only tables have relationships.

If there are any records that have the same value on both sides of a join
then you should get at least some rows in your output. However if you
create multiple joins they might cancel. That is there might be rows
with matching field values on the first join and there might be rows with
matching field values on the second join, but zero rows with matching
values on BOTH joins at the same time. The more joins you have the more
likely this becomes.

Also matters what kinds of fields you are joining. There might be fields
that appear to be equal because of formatting, but which have differences
under the covers. The query will only return rows on *exact* matches.

There are also different kinds of joins. Double-click one of the join
lines and look at the choices. One of those might be better suited to
what you need.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Hi Kathy,

When I create a query between tables and use relationships between the
tables (I am using an Access teach yourself book) the resulting datasheet
view is blank, even though I already have data entered into the different
tables.

The most common type of relationship is a "one-to-many" relationship,
abbreviated 1:M. Other types include "one-to-one" (1:1), and many-to-many
(M:N). The 1:1 relationship is the least common of the three; a M:N
relationship is created using two 1:M relationships, with a third table,
commonly known as a join or intersection table. Here is more information on
relationships:

Defining relationships between tables
http://support.microsoft.com/?id=304467

Do you have a field that has the same data in both tables? In the "one-side"
or parent table, this field should be uniquely indexed (ie. either set as a
primary key, or have an index set with No Duplicates allowed). In the
"many-side" or child table, you do not have a unique index set, and in fact
you don't need to index this field at all, as long as you choose the option
to Enforce Referential Integrity, when creating the relationship.

Assuming that you are working with the most common type of relationship,
1:M, you should have a form that displays the "one-side" information, and
includes a subform for displaying the data from the "many-side" table. You
mentioned a resulting datasheet, but it is not clear to me that this
datasheet is used as the subform in a main form.

I suspect that the reason you are not seeing any records is that you do not
have matching values between the primary key field (or uniquely indexed
field) in the "one-side" table, and the foreign key field in the "many-side"
table.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"Kathy Thornton" wrote:

When I create a query between tables and use relationships between the
tables (I am using an Access teach yourself book) the resulting datasheet
view is blank, even though I already have data entered into the different
tables. Shouldn't I be able to create a query that uses 2 tables and then
when I open the query datasheet view I would see all the query selected data
from both tables? I know I'm not understanding something. The end result
of what I want to do is create a data entry form from a query that has field
inputs from 2 separate tables. It's not working for me. From reading thru
many of the emails on these Access newsgroups my question just seems so
elementary. But, there it is. I know my way around Access but only as a
user dealing with single tables (I do make design changes on
tables/queries/forms/reports). Thanks

On Sun, 11 Jan 2009 18:04:07 -0600, "Kathy Thornton"
wrote:

When I create a query between tables and use relationships between the
tables (I am using an Access teach yourself book) the resulting datasheet
view is blank, even though I already have data entered into the different
tables. Shouldn't I be able to create a query that uses 2 tables and then
when I open the query datasheet view I would see all the query selected data
from both tables? I know I'm not understanding something. The end result
of what I want to do is create a data entry form from a query that has field
inputs from 2 separate tables.

It's *possible* to have a two (or even more) table query that allows
updating... but it's often not a good way to do so. You'll need to use the
right join type (a Left Join if there are no records in the "many" side
table), you'll see repeating data, you have to be finicky about the query to
have both tables updateable...

It's usually much easier to use a Form for the "one" side table, with a
Subform for the "many", with the defined relationship establishing the Master
link Field and Child Link Field properties of the subform.
--

John W. Vinson [MVP]

Kathy Thornton wrote:
When I create a query between tables and use relationships between the
tables (I am using an Access teach yourself book) the resulting
datasheet view is blank, even though I already have data entered into
the different tables. Shouldn't I be able to create a query that uses 2
tables and then when I open the query datasheet view I would see all the
query selected data from both tables? I know I'm not understanding
something. The end result of what I want to do is create a data entry
form from a query that has field inputs from 2 separate tables. It's
not working for me. From reading thru many of the emails on these
Access newsgroups my question just seems so elementary. But, there it
is. I know my way around Access but only as a user dealing with single
tables (I do make design changes on tables/queries/forms/reports). Thanks

You've had replies from some real experts but I'm wondering if the
confusion isn't more fundamental, so, with apologies (every-)where due:

Have you set up a simple example? Say you're running a very simple
library. You have a table of BOOKS, each with a unique id (the Primary
Key). The table might have a number (often an "Autonumber") for the id,
and the book's name and author.

You want to record where they are, so you have a table of LOCATIONS
(library branch one, library branch two, mobile van, repair shop, etc).
These locations also have a unique identifier (Primary Key). This
table might have an Autonumber for the location id, plus the location
name and (maybe) phone number.

The simplest (not necessarily the best) way of recording where the book
is currently is to add a column to the BOOKS table to store the Primary
Key value of the current location. (In the BOOKS table, this is
considered a Foreign Key). For any book, this field might be empty
(book lost!) or it should contain a valid location identifier.

So, you set up the Relationship in Access in the Relationships window.
Easy: you "add" both tables to the display, and drag the relevant field
over the corresponding field in the other table. Access remembers this,
and draws a line to show the relationship.

Then you create a query in Design View. Again, you "add" both tables,
and if the relationship is correctly set up Access will show the line
between the two corresponding fields.

You then drag (other) fields from either table onto the query grid, and
when you're done you can run it. You should see records showing all the
details you've chosen from your BOOKS table together with the details
you've chosen from the LOCATIONS table. If you'd only used fields from
the BOOKS table, you'd be limited to showing only the location number,
but because you've "joined" the tables, the query can show the location
name and any other details from the LOCATIONS table, for each book
displayed.

Does that help - or are you way ahead of me?

Tip: have a look at the SQL for your simple query - it'll help you
understand what's going on under the bonnet.

Phil, London

John W. Vinson wrote in
:

It's *possible* to have a two (or even more) table query that
allows updating... but it's often not a good way to do so. You'll
need to use the right join type (a Left Join if there are no
records in the "many" side table), you'll see repeating data, you
have to be finicky about the query to have both tables
updateable...

Don't forget Jet SQL's proprietary DISTINCTROW predicate, which can
force some otherwise non-editable query results to be editable. It
doesn't always work, but it's always something that's worth a try.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

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




Philip Herlihy wrote:
When I create a query between tables and use relationships between the
tables (I am using an Access teach yourself book) the resulting
[quoted text clipped - 8 lines]
is. I know my way around Access but only as a user dealing with single
tables (I do make design changes on tables/queries/forms/reports). Thanks

You've had replies from some real experts but I'm wondering if the
confusion isn't more fundamental, so, with apologies (every-)where due:

Have you set up a simple example? Say you're running a very simple
library. You have a table of BOOKS, each with a unique id (the Primary
Key). The table might have a number (often an "Autonumber") for the id,
and the book's name and author.

You want to record where they are, so you have a table of LOCATIONS
(library branch one, library branch two, mobile van, repair shop, etc).
These locations also have a unique identifier (Primary Key). This
table might have an Autonumber for the location id, plus the location
name and (maybe) phone number.

The simplest (not necessarily the best) way of recording where the book
is currently is to add a column to the BOOKS table to store the Primary
Key value of the current location. (In the BOOKS table, this is
considered a Foreign Key). For any book, this field might be empty
(book lost!) or it should contain a valid location identifier.

So, you set up the Relationship in Access in the Relationships window.
Easy: you "add" both tables to the display, and drag the relevant field
over the corresponding field in the other table. Access remembers this,
and draws a line to show the relationship.

Then you create a query in Design View. Again, you "add" both tables,
and if the relationship is correctly set up Access will show the line
between the two corresponding fields.

You then drag (other) fields from either table onto the query grid, and
when you're done you can run it. You should see records showing all the
details you've chosen from your BOOKS table together with the details
you've chosen from the LOCATIONS table. If you'd only used fields from
the BOOKS table, you'd be limited to showing only the location number,
but because you've "joined" the tables, the query can show the location
name and any other details from the LOCATIONS table, for each book
displayed.

Does that help - or are you way ahead of me?

Tip: have a look at the SQL for your simple query - it'll help you
understand what's going on under the bonnet.

Phil, London

--
Message posted via http://www.accessmonster.com

CK via AccessMonster.com wrote:
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

Hi Phil,

Fortunately I only have a few books and no-one to take them out and pour
coffee on them. Or leave them out for the dog to chew on. But I do have 500+
libraries. So following this epic tale through chapter by chapter, I have
made it work using a join table and queries as suggested.

Good work, thanks for the help. And the bedtime reading!

Cheers
CK

Philip Herlihy wrote:
Hi Phil and others

[quoted text clipped - 18 lines]
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200901/1

Finally, I know what was wrong with how I was doing it. I needed to use
many-to-many relationships. I made a join table and studied how it worked,
and found a decent example of one on the internet that helped me to
understand how to make the query work.

So, now it's working for me & I am entering data in a form like I wanted to.
Yeh.
Next step is to create the reports I need based on the combined tables.
But, I have more confidence now it being able to set them up.

Thanks everyone for your help.


"Kathy Thornton" wrote in message
...
When I create a query between tables and use relationships between the
tables (I am using an Access teach yourself book) the resulting datasheet
view is blank, even though I already have data entered into the different
tables. Shouldn't I be able to create a query that uses 2 tables and then
when I open the query datasheet view I would see all the query selected
data from both tables? I know I'm not understanding something. The end
result of what I want to do is create a data entry form from a query that
has field inputs from 2 separate tables. It's not working for me. From
reading thru many of the emails on these Access newsgroups my question
just seems so elementary. But, there it is. I know my way around Access
but only as a user dealing with single tables (I do make design changes on
tables/queries/forms/reports). Thanks