need query results showing Table 1 records both w/ and w/o matches in Table 2.

[Jesse]

I am trying to write a query in Access 2000 and would appreciate some
guidance as to how this should be approached. I am at a very basic
skill level.

My goal is to create a query that determines what type and size of
packaging should be used depending on the mix of products purchased.
We know that if 3 products from category X and 7 from category Y are
purchased then 'the medium corrugated box' should be used (and we have
reduced this to a grid), but we would like the process automated so
that a simple list can be printed.

Simplified (but hopefully adequate): I have 2 tables (Tb1 and Tb2)
with 4 related integer fields - Tb1.t1 - Tb1.t4 and Tb2.c1 - Tb2.c4
(respectively). Tb1 is a transaction table with a field holding a
unique order_ID number and totals for how many products were purchased
in 4 categories (t1 - t4).

Tb2 is a catalog with 4 fields (c1 - c4) showing permutations of what
someone might buy, by category. There are also 2 non-unique tag
fields, tg1 and tg2. tg1 holds a tag from a list of 6 tags (types
of shipping containers) and tg2 holds a tag from a different list of 4
tags (container size). (There are additional fields in each table.)

The query output should include the order_ID and t1-t4 fields from
Tb1, and 2 calculated fields (f1 and f2) based on tg1 and tg2 from
Tb2.

For every record in Tb1 I want a lookup done in Tb2. The first record
in Tb2 where t1=c1 AND t2=c2 AND t3=c3 AND t4=c4 match, the
corresponding tg1 and tg2 tags should be generated for f1 and f2.

However, if there is no record in Tb2 that matches a given record in
Tb1 (for all of the 't' and 'c' fields), then 'No tg1' and 'No tg2'
should appear in that record's f1 and f2 fields. In other words,
every record from Tb1 should appear in the query output.

My attempts so far have resulted in query results that either have
multiple appearances of records from Tb1 or that do not show the
records from Tb1 that have no match in Tb2. So, suggestions? Thank
you. -- Jesse

 

[pietlinden]

I am trying to write a query in Access 2000 and would appreciate some
guidance as to how this should be approached. I am at a very basic
skill level.

My goal is to create a query that determines what type and size of
packaging should be used depending on the mix of products purchased.
We know that if 3 products from category X and 7 from category Y are
purchased then 'the medium corrugated box' should be used (and we have
reduced this to a grid), but we would like the process automated so
that a simple list can be printed.

Simplified (but hopefully adequate): I have 2 tables (Tb1 and Tb2)
with 4 related integer fields - Tb1.t1 - Tb1.t4 and Tb2.c1 - Tb2.c4
(respectively). Tb1 is a transaction table with a field holding a
unique order_ID number and totals for how many products were purchased
in 4 categories (t1 - t4).

Tb2 is a catalog with 4 fields (c1 - c4) showing permutations of what
someone might buy, by category. There are also 2 non-unique tag
fields, tg1 and tg2. tg1 holds a tag from a list of 6 tags (types
of shipping containers) and tg2 holds a tag from a different list of 4
tags (container size). (There are additional fields in each table.)

The query output should include the order_ID and t1-t4 fields from
Tb1, and 2 calculated fields (f1 and f2) based on tg1 and tg2 from
Tb2.

For every record in Tb1 I want a lookup done in Tb2. The first record
in Tb2 where t1=c1 AND t2=c2 AND t3=c3 AND t4=c4 match, the
corresponding tg1 and tg2 tags should be generated for f1 and f2.

However, if there is no record in Tb2 that matches a given record in
Tb1 (for all of the 't' and 'c' fields), then 'No tg1' and 'No tg2'
should appear in that record's f1 and f2 fields. In other words,
every record from Tb1 should appear in the query output.

My attempts so far have resulted in query results that either have
multiple appearances of records from Tb1 or that do not show the
records from Tb1 that have no match in Tb2. So, suggestions? Thank
you. -- Jesse

use an outer join. Add the two tables to your query. Right-click
(could be double-click) the line (join) between teh two tables and
change it to Show All from Tb1. (Hopefully you didn't name your table
Tb1!! If so, I pity the person that has to make sense of the database
after it's no longer yours...)

 

[Jesse]

On Thu, 09 Aug 2007 20:55:15 -0700, "(e-mail address removed)"

[snip]

use an outer join. Add the two tables to your query. Right-click
(could be double-click) the line (join) between teh two tables and
change it to Show All from Tb1. (Hopefully you didn't name your table
Tb1!! If so, I pity the person that has to make sense of the database
after it's no longer yours...)

Wow. Not only were you right (which was likely in any event), but I
actually was able to follow your instructions (which is a comment
about me, not your instructions). This also means I must have posted
the question correctly -- which for newbies is a challenge all on its
own. Thank you very much.

And, thanks for the "subtle" hint that tables be given meaningful
names. As someone who once had to contend with DOS 8.3 file names, I
do like the luxury of being able to make up meaningful names. Yet,
the longer the table names the bigger the chance for typos in my query
statements. Again, thanks. -- Jesse.

 

[Jesse]

use an outer join. Add the two tables to your query. Right-click
(could be double-click) the line (join) between teh two tables and
change it to Show All from Tb1. (Hopefully you didn't name your table
Tb1!! If so, I pity the person that has to make sense of the database
after it's no longer yours...)

Thanks (see my earlier reply) but - whoops - now things have changed a
bit. Now there isn't a direct literal relationship between any fields
in Tb1 and Tb2. And, Tb2 now has 4 pairs of fields which establish
value ranges -- e.g., c1a and c1b.

So, for example, the query expression in field f1 for each record in
Tb1 is

exp1:IIF(t1 BETWEEN c1a AND c1b
AND t2 BETWEEN c2a AND c2b
AND t3 BETWEEN c3a AND csb
AND t4 BETWEEN c4a AND c4b,
tg1,"No tg1")

I set the query up with just two columns: Tb1.order_ID and exp1. At
the moment, Tb2 has 5 test records, and when I run this query I get 5
records for each order_ID number.

How can I fix this so that for each order_ID number I get 1 record in
the query results with (in the exp1 field) either a corresponding tg1
value or, if there isn't one at all, "No tg1"? Again, thanks for any
help with this. Jesse.

 

[pietlinden]

Sorry, subtlety was never my style <g>

 

[pietlinden]

Thanks (see my earlier reply) but - whoops - now things have changed a
bit. Now there isn't a direct literal relationship between any fields
in Tb1 and Tb2. And, Tb2 now has 4 pairs of fields which establish
value ranges -- e.g., c1a and c1b.

So, for example, the query expression in field f1 for each record in
Tb1 is

exp1:IIF(t1 BETWEEN c1a AND c1b
AND t2 BETWEEN c2a AND c2b
AND t3 BETWEEN c3a AND csb
AND t4 BETWEEN c4a AND c4b,
tg1,"No tg1")

I set the query up with just two columns: Tb1.order_ID and exp1. At
the moment, Tb2 has 5 test records, and when I run this query I get 5
records for each order_ID number.

How can I fix this so that for each order_ID number I get 1 record in
the query results with (in the exp1 field) either a corresponding tg1
value or, if there isn't one at all, "No tg1"? Again, thanks for any
help with this. Jesse.

sounds like you need a non-equijoin. Instead of creating a join on
two tables where there are *matching* values in two tables, you can
do this:

SELECT ...
FROM tblA LEFT JOIN tblB ON tblA.Field1 < tblB.Field2
WHERE...
how about trying this with normal table names that mean something and
the same with the field names. That way, it's much easier to have an
intuitive understanding of what your database actually describes.
Think of it this way.... is it easier to understand the relationship
between a mystery table X and another mystery table Y or between an
Invoice table and an Invoice Line Items table. You'll especially see
it if you diagram the relationships on paper. Especially when your
database gets complicated, this makes things MUCH easier in the long
run. You can check your logic with minimal effort.

Hope this helps. I've worked with databases that were good and some
that were bad... and the difference for me is how obvious the
structure and relationships between things are - could be tables or
code... the same holds. IF what something does is clear or obvious,
debugging things when stuff goes wrong (and it will!!) makes things
significantly simpler.

 

[Jesse]

sounds like you need a non-equijoin. Instead of creating a join on
two tables where there are *matching* values in two tables, you can
do this:

SELECT ...
FROM tblA LEFT JOIN tblB ON tblA.Field1 < tblB.Field2
WHERE...
how about trying this with normal table names that mean something and
the same with the field names. That way, it's much easier to have an
intuitive understanding of what your database actually describes.
Think of it this way.... is it easier to understand the relationship
between a mystery table X and another mystery table Y or between an
Invoice table and an Invoice Line Items table. You'll especially see
it if you diagram the relationships on paper. Especially when your
database gets complicated, this makes things MUCH easier in the long
run. You can check your logic with minimal effort.

Hope this helps. I've worked with databases that were good and some
that were bad... and the difference for me is how obvious the
structure and relationships between things are - could be tables or
code... the same holds. IF what something does is clear or obvious,
debugging things when stuff goes wrong (and it will!!) makes things
significantly simpler.

That worked great. Thanks. To help me with the coding, I actually
started by doing drags to create the joins and then edited the SQL to
change the joins from equal to non-equal.

There is a problem-for-newbies: Once non-equal joins are done, one
cannot do any work on the query via the Design View. I'm working
around this by doing a bare-bones number of fields in this query and
then doing fuller ones - making for easy editing via Design View -
based on it. OK, so that's manageable.

A tougher problem involves making manual entries if a record in Tb1
does not match any records in Tb2 -- thus generating "No tg1" in the
field which is a flag for manual intervention. My idea for that is to
have a manual "override" field and a "final_tg" field:

IIF([override] IS NULL, [tg1],[override])

But, because there is no common key between Tb1 and Tb2, (as I've
discovered) I cannot enter anything in the override field from within
a query. I've been able to work around that initially by using the
"Dynaset (Inconsistent Updates)" setting for the query. But, another
layer of queries will make even that no good. I've posted another
question here about this: "how to jump from form to related record in
table" for my next-level work-around.

I do realize that there may well be a better way to do this. What
puzzles me with the limits on editing data in a query is: What happens
when you're looking at a form with a customer's order and you want to,
for example, upgrade shipping to UPS Overnight from UPS Ground. This
seems like a rather standard kind of requirement. So, while I'd be
happy with a simple and effective way to do this, I'd also like to
know what is a "good" way to do this (if they are not the same). (At
least things are small enough here for now that I can, as required,
actually edit the underlying table.)

Again, thanks for your help with the non-equal joins. -- Jesse