Too many records

[Guest]

Hi,
I have two tables I'm trying to pull info from. "Table1" has RecordNumber
(auto), InvoiceNumber, Customer (customer code), CustomerName, ShipTo, and
other fields that don't pertain to this isssue. The second table is
"ShipToAddresses" which has ID (auto), CustomerCode, CustomerName, ShipTo,
Address, City, State, Zip.

I have put these two tables on a query and included CustomerCode,
CustomerName, ShipTo, InvoiceNumber from either table in different
combinations and have changed the join properties to each selection available
and it is still giving the wrong set of records.

The query should return a specific invoice with ShipTo #3. It will return
ShipTo #3 if I use the ship to from Table 1. If I use ShipTo from
ShipToAddresses table, it will give the invoice number with ALL ship to's for
that customer with the same invoice number on all entries with all different
zip codes.

I've included the latest combination in SQL below:

SELECT Table1.InvoiceNumber, Table1.Customer, Table1.ShipTo,
SHIPTOADDRESSES.Zip
FROM Table1 INNER JOIN SHIPTOADDRESSES ON Table1.Customer =
SHIPTOADDRESSES.CustomerCode
WHERE (((Table1.InvoiceNumber)=78298));

Invoice Number 78298 returns the correct CustomerCode, CustomerName, and
ShipTo, but it lists every zip code associated with this customer (in other
words, for every ship to they have).

I'm not sure what I'm doing wrong, I've never run across this before and I
must say it has me stumped. If anyone can point me in the right direction to
correct this, it would be greatly appreciated.
Thanks in advance for any help,
Pam

 

[David F Cox]

Join on a group by query so that you only get the relevent fields listed
once.

 

[bmarland]

It looks like you just need another join. Assuming "shipto" represents
a unique customer address, "shipto" would also need to be joined to
result in a single row.

SELECT Table1.InvoiceNumber, Table1.Customer, Table1.ShipTo,
SHIPTOADDRESSES.Zip
FROM Table1 INNER JOIN SHIPTOADDRESSES ON Table1.Customer =
SHIPTOADDRESSES.CustomerCode

AND table1.customer.shipto = shiptoaddresses.shipto

WHERE (((Table1.InvoiceNumber)=78298));

 

[Guest]

Thanks to both for the info.
bmarland - The second join did the trick. I have quite a few queries set
up, but have never had one with two joins on the design grid. It works
perfect!!

Thanks again for the help,
Pam