criteria/expression wording

  • Thread starter Thread starter Jessica E
  • Start date Start date
J

Jessica E

If anyone can help, i'd appreciate it. i have two tables
that are being joined together. i need all matching and
non-matching data from both. how do i word my query
criteria or expression to show me if X in table 1 does not
have a match in table 2, then still show it? also, vice
versa, if its in table 2 but not in table 1, still show
it.

thanks in advance!!!
Jessica E :)
 
If anyone can help, i'd appreciate it. i have two tables
that are being joined together. i need all matching and
non-matching data from both. how do i word my query
criteria or expression to show me if X in table 1 does not
have a match in table 2, then still show it? also, vice
versa, if its in table 2 but not in table 1, still show
it.
Click to expand...

This is called a "Full Outer Join" and unfortunately it isn't
supported in Access as a single query.

You can get around this limitation with a query based on two other
queries. First create a query joining Table1 to Table2; select the
join line and choose Option 2 - "Show all records in Table1 and
matching records in Table2", a Left Outer Join. Save this query as
(say) qryLeft.

Now create a second query, using Option 3 - a Right Outer Join. Save
it as qryRight.

Finally go into the SQL window and type

SELECT * FROM qryLeft
UNION
SELECT * FROM qryRight

to string together the two queries, eliminating duplicates
automatically.
 
turns out i only need one outer join. without getting into
too much detail, for the field i'm using, i'll never have
data in Table 2 with no match in Table 1.

however, i do need to create a join on 4 other fields
between the two tables. in the same query i created the
first outer join, can i create the inner joins for the
other 4 fields within that query or will i end up with
duplications and/or errors? do i need to create all joins
within that query as outer joins? if so, will that result
in any duplications?

thanks again!
jessica
 
turns out i only need one outer join. without getting into
too much detail, for the field i'm using, i'll never have
data in Table 2 with no match in Table 1.

however, i do need to create a join on 4 other fields
between the two tables. in the same query i created the
first outer join, can i create the inner joins for the
other 4 fields within that query or will i end up with
duplications and/or errors? do i need to create all joins
within that query as outer joins? if so, will that result
in any duplications?
Click to expand...


I don't understand.

A given query can have a Join between two tables on up to ten fields.

That join can be either an inner join or an outer join.

It can't be an inner join on some fields, and an outer join on some
other fields!

You can use an Inner Join on the four fields, and a criterion such as

=Table1.[A] OR IS NULL

on Table2.A in order to select only records which either match or have
no value in field A; but if you need an inner join on other fields,
then you must have a record in Table2.

Could you give an example of the kind of data you're dealing with, and
what results you would like to see?
 
i'm so sorry i worded that so poorly now that i reread
it. okay, here's what i'm doing... i'm comparing dollars
on resources. the dollars in one table (table 1) are the
prime bottom line dollars. the dollars in the other table
(table 2) are all of the overhead costs associated with
the resource and dollars in table 1. so, i could have a
resource that has no overhead cost, but i won't have
overhead costs wihtout a resource. hence the outer join
on the resource field.

here's where it gets fun... each resource is associated
with a date in time, a project and an account within that
project. all of those fields could have an inner join on
them, but access gives me an error message if i have both
types of joins in the query. then there's problem is the
amount of time it takes to process all of the data
(millions of records).

i hope that makes more sense about what i'm trying to do.
i'm gonna try your suggestion too because it sounds like
that may work very well.
i really appreciate your help.

happy holidays!!
Jessica :)
-----Original Message-----
turns out i only need one outer join. without getting into
too much detail, for the field i'm using, i'll never have
data in Table 2 with no match in Table 1.

however, i do need to create a join on 4 other fields
between the two tables. in the same query i created the
first outer join, can i create the inner joins for the
other 4 fields within that query or will i end up with
duplications and/or errors? do i need to create all joins
within that query as outer joins? if so, will that result
in any duplications?
Click to expand...


I don't understand.

A given query can have a Join between two tables on up to ten fields.

That join can be either an inner join or an outer join.

It can't be an inner join on some fields, and an outer join on some
other fields!

You can use an Inner Join on the four fields, and a criterion such as

=Table1.[A] OR IS NULL

on Table2.A in order to select only records which either match or have
no value in field A; but if you need an inner join on other fields,
then you must have a record in Table2.

Could you give an example of the kind of data you're dealing with, and
what results you would like to see?


.
Click to expand...
 
-----Original Message-----
i'm so sorry i worded that so poorly now that i reread
it. okay, here's what i'm doing... i'm comparing dollars
on resources. the dollars in one table (table 1) are the
prime bottom line dollars. the dollars in the other table
(table 2) are all of the overhead costs associated with
the resource and dollars in table 1. so, i could have a
resource that has no overhead cost, but i won't have
overhead costs wihtout a resource. hence the outer join
on the resource field.

here's where it gets fun... each resource is associated
with a date in time, a project and an account within that
project. all of those fields could have an inner join on
them, but access gives me an error message if i have both
types of joins in the query. then there's problem is the
amount of time it takes to process all of the data
(millions of records).

i hope that makes more sense about what i'm trying to do.
i'm gonna try your suggestion too because it sounds like
that may work very well.
i really appreciate your help.

happy holidays!!
Jessica :)
-----Original Message-----



I don't understand.

A given query can have a Join between two tables on up
Click to expand...
to
ten fields.
That join can be either an inner join or an outer join.

It can't be an inner join on some fields, and an outer join on some
other fields!

You can use an Inner Join on the four fields, and a criterion such as

=Table1.[A] OR IS NULL

on Table2.A in order to select only records which either match or have
no value in field A; but if you need an inner join on other fields,
then you must have a record in Table2.

Could you give an example of the kind of data you're dealing with, and
what results you would like to see?


.
Click to expand...
.
Click to expand...
 
Back
Top