Query from multiples table results in multiple records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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?
 
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.
 
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...
 
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"?
 
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!
 
Back
Top