1:M with queries

[kimberly.shaffer]

This may be a dumb question, but can you create a 1:M relationship with
queries instead of tables? thanks!

[MGFoster]

ki**************@gmail.com wrote:

This may be a dumb question, but can you create a 1:M relationship with
queries instead of tables? thanks!

The relationships are always between tables not queries.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[MGFoster]

ki**************@gmail.com wrote:

This may be a dumb question, but can you create a 1:M relationship with
queries instead of tables? thanks!

Relationships are always between tables. What are you trying to do?

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[kimberly.shaffer]

Well, I bought a whole bunch of Access Database books and I couldn't
find the answer in any of them. I got what I needed to do, which is to
create a 1:M relationship with two tables, but I was hoping to use my
queries because the databases I wanted to use had some null values in
the fields I wanted to join on the many side and so I had ended up
filtering out the nulls and recreating tables. It was kind of a pain.

[MGFoster]

ki**************@gmail.com wrote:

Well, I bought a whole bunch of Access Database books and I couldn't
find the answer in any of them. I got what I needed to do, which is to
create a 1:M relationship with two tables, but I was hoping to use my
queries because the databases I wanted to use had some null values in
the fields I wanted to join on the many side and so I had ended up
filtering out the nulls and recreating tables. It was kind of a pain.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If you just want to show all the rows in the ONE table try a LEFT JOIN
query. The the table w/ the ONE relationship on the LEFT side and the
many relationship on the right side. E.g.:

SELECT one_table.column, <column list>
FROM one_table LEFT JOIN many_table
ON one_table.join_column = many_table.join_column
WHERE < criteria>

This should return all rows in the left table and all rows in the right
table. Those rows in the right table that don't have a value in the
join_column will not have anything (NULL).

Perhaps if you stated your goal we could help you better.

Also, a good read of a decent database design book would be in order. I
usually recommend _Database Design for Mere Mortals_ by Hernandez.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQls42oechKqOuFEgEQIx8ACfUkB2Cbuev65gggrYZ/5uBa9dHD8An2gP
UUdrwT1ZS5nsZyRDHIFtVcZ0
=i7Fq
-----END PGP SIGNATURE-----

[Alan Webb]

Kimberly,
It's not a dumb question. The word relationships is used in Access to refer
to two different things. I think this confuses some people--it confused me
for a while. There are constraints that limit what can be added to a table
based on a value stored in another table. These are called referential
integrity relationships and are what you set up in the Relationships Window
when you enable referential integrity for a relationship. Joins are
instructions in a query to match values in one or more columns in two
different tables or queries and return any rows in the result that have
matches. An INNER join requires that the values in the contributing
tables/columns match. An OUTER join (LEFT, RIGHT or FULL) return all the
rows in one table or query and allow nulls in the column being matched.
From your other posts it sounds like what you need is to do an OUTER join in
your query.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

<ki**************@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

This may be a dumb question, but can you create a 1:M relationship with
queries instead of tables? thanks!