Query from multiples table results in multiple records

C

Carpie

I have one table 'Transactions' with fields (BuyerName, PONum, etc) and another table 'Buyers' with fields (BuyerName, Location, etc). What I want is a query that shows me all PONum's based on location (which is based on the buyer). If I do a query based just on 'Transactions' for Buyername and PONum I get the records I want but it is not showing location. If I add the Location field from the 'Buyers' table to the query, I am getting the same PONum multiple times for each different location. What am I doing wrong?
 
C

Carpie

I think I solved my own question by adding Locations and setting the following criteria:

[Transactions.Buyername=Buyers.Buyername)


Seems to work. Is it the right/best way of doing it?
 
J

John Vinson

I have one table 'Transactions' with fields (BuyerName, PONum, etc) and another table 'Buyers' with fields (BuyerName, Location, etc). What I want is a query that shows me all PONum's based on location (which is based on the buyer). If I do a query based just on 'Transactions' for Buyername and PONum I get the records I want but it is not showing location. If I add the Location field from the 'Buyers' table to the query, I am getting the same PONum multiple times for each different location. What am I doing wrong?
Click to expand...

Probably not joining the two tables appropriately. Try opening the
Query in SQL view and posting the SQL text here; someone should be
able to see the problem.
 
T

Tober

You bet, now you get the right data from both tables and
using Buyername as the primary key. You could also use the
SQL Statement JOIN
-----Original Message-----
I think I solved my own question by adding Locations and
Click to expand...
setting the following criteria:
[Transactions.Buyername=Buyers.Buyername)


Seems to work. Is it the right/best way of doing it?
.
Click to expand...
 
J

Jeff Boyce

Carpie

If you are using [BuyerName] as your join criteria for the two tables, what
will you do when you have two buyers named "John Smith"?
 
C

Carpie

Umm... I would have to say I would be in an awful predicament at that point. I would guess that I would be getting double results again? As long as I make the [Buyers.BuyerName] a primary key field, the second "John Smith" should not be able to be entered, correct? We're talking about less than 50 individuals so the chances are fairly slim.

If I had the time, I guess I could create a [BuyerID] as the primary key to avoid the problem. The trouble that I face now is that I have many forms pulling the [Transactions.BuyerName] field and that would all have to be switched to [BuyerID] which would then have to lookup the actual name (rather than ID number). More time than I can afford ATM.

Thanks for the help though!
 
Top