Query Link Question

  • Thread starter Thread starter Cal Monroe
  • Start date Start date
C

Cal Monroe

I have 2 tables. One has a large number of records, many
with the same customer ID in a field. The other table
only a few records. I want to link the two tables based
on the customerID field, BUT I only want the smaller
table to link once for each customerID it matches.
If table 1 has 5 records for the same customerID, I only
want table 2 to link to the first of the 5 records with a
matching ID, not 5 to 1 link, more link 1 to 1 with the
other 4 not having a link in table 2.
Is this possible???
 
Dear Cal:

Can you write a query on just Table 1 (the "small table") so that it
has only one row for each CustomerID, along with whatever other
information from that table you want to see for that CustomerID? If
you can do this, then you can JOIN (that's a specific technical term
for what I think you mean by "link") that query to your Table 2 (the
one with a large number of records).

The difficulty in doing this may be in defining what you mean by "the
first of the 5 records." You must have an unambituous definition for
this in order to implement it.

A major consideration is how you will do this. Assume for the moment
there is only one other column in Table 1 (besides CustomerID) that is
of interest. In the 5 rows with that same CustomerID, this other
column may have 5 different values. Just looking at the 5 rows, how
can you tell which one is the one you want.

Finally, there is no such meaningful term as "first" among the 5 rows
with the same CustomerID. Unless you impose a sorting order, the rows
are in a "Bag". Consider a set of Legos. Some are red, some white,
blue, green, etc. Put them all in a big sack and draw them out one at
a time till you get the first red one. What made that particular red
Lego the first one? Pure chance. Repeat the experiment any you will
probably obtain a different "first red Lego."

If you just want one of the 5 rows at random then you don't need a
specific definition of "First" but if there is some meaning to "first"
other than just random chance, then you must define and implement this
unambiguously.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks for your reply.
I will try to better explain my problem. I need all five
rows of data in the larger table, say they are customer
orders cost $. The smaller table has a cost that is only
linked to a customer, not an order. I only want to
capture the $ value from the smaller table once when
reporting, but I still need the detail of the larger
table. If I simply join the two tables, I get all the
data from the larger table and the data from the smaller
table 5 times.
Table A: 50, 25, 35, 40, 90 (5 records)
Table B: 33, 33, 33, 33, 33 (1 record matched 5 times)

I need:
Query to show Table B data only once,so that when summed
in reports, I get totals for the detail and only the
amount for the Table B data, example: 240 and 33.

thanks,
Cal
 
Dear Cal:

The example you show indicates a considerable table design problem.
But I'll stick to solving the problem at hand first.

If you "assume" that the value of Cost will be the same in EVERY row
of Table B, then you could write a Totals query GROUPed BY the key on
which you join to this table, showing the "Minimum" value (or the
"Maximum" value, or the "Average") of the Cost column.

If you have any facility that allows a user to change this Cost value
in Table B, it would have to be able to update all 5 (or more, or
fewer) rows of Table B for that Key value. The table design problem
is that this should not be the case. There should (and may already)
be another table which has just one record for this key, and the Cost
should go in there.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top