Inner join problem

J

JME

hello experts,

i have table Transaction like below;
TransID ProductID Cost Quantity Resale
1 5 $250 10 $300
2 7 $1000 25 $1240
3 7 $1000 25 $1240
4 2 $450 15 $600
5 1 $1400 5 $1600

i created qryAveCost:
SELECT Transaction.TransID, [Transaction]![Cost]/
[Transaction]![Quantity] AS AveCost
FROM Transaction;

now i create new query qryAveMargin:
SELECT Transaction.TransID, ([Transaction]![Resale]/
[Transaction]![Quantity])-[qryAveCost]![AveCost] AS
AveMargin
FROM Transaction INNER JOIN qryAveCost ON
Transaction.Quantity = qryAveCost.Quantity;

It turn out to give 7 records instead of 5 which some
records are repeated twice. Where is my mistake?
 
L

Laurie Eaton

Hi JME,

because you have mulitple entries of the same value (25) the query looks to join these several times on each table - hence the multiple results. Its not a good idea to JOIN on fields that are not unique. You should try to JOIN in a unique key such as ProductID - but this may mean you will have to design yr tables. Have a look at using the 'DISTINCT' word - such as 'SELECT DISTINCT...' but please check the result are correct - just because the computer says it, doesn't make it right.

regards

Laurie Eaton
 
K

Ken Snell

I think you want to join the tables on TransID, not on Quantity.

Also, change ! to . in your queries.
 
V

Van T. Dinh

Because you joined the Table & the Query by non-unique values.

Try:

SELECT Transaction.TransID, ([Transaction]![Resale]/
[Transaction]![Quantity])-[qryAveCost]![AveCost] AS AveMargin
FROM Transaction INNER JOIN qryAveCost ON
Transaction.TransID = qryAveCost.TransID;
 
G

Guest

may i know what is purpose of using . instead of !
-----Original Message-----
I think you want to join the tables on TransID, not on Quantity.

Also, change ! to . in your queries.

--

Ken Snell
<MS ACCESS MVP>

hello experts,

i have table Transaction like below;
TransID ProductID Cost Quantity Resale
1 5 $250 10 $300
2 7 $1000 25 $1240
3 7 $1000 25 $1240
4 2 $450 15 $600
5 1 $1400 5 $1600

i created qryAveCost:
SELECT Transaction.TransID, [Transaction]![Cost]/
[Transaction]![Quantity] AS AveCost
FROM Transaction;

now i create new query qryAveMargin:
SELECT Transaction.TransID, ([Transaction]![Resale]/
[Transaction]![Quantity])-[qryAveCost]![AveCost] AS
AveMargin
FROM Transaction INNER JOIN qryAveCost ON
Transaction.Quantity = qryAveCost.Quantity;

It turn out to give 7 records instead of 5 which some
records are repeated twice. Where is my mistake?
Click to expand...


.
Click to expand...
 
K

Ken Snell

! is the bang operator, which is used as a shortcut for referencing
collections for forms and reports. Tables use a . operator to separate table
names and field names.

--

Ken Snell
<MS ACCESS MVP>

may i know what is purpose of using . instead of !
-----Original Message-----
I think you want to join the tables on TransID, not on Quantity.

Also, change ! to . in your queries.

--

Ken Snell
<MS ACCESS MVP>

hello experts,

i have table Transaction like below;
TransID ProductID Cost Quantity Resale
1 5 $250 10 $300
2 7 $1000 25 $1240
3 7 $1000 25 $1240
4 2 $450 15 $600
5 1 $1400 5 $1600

i created qryAveCost:
SELECT Transaction.TransID, [Transaction]![Cost]/
[Transaction]![Quantity] AS AveCost
FROM Transaction;

now i create new query qryAveMargin:
SELECT Transaction.TransID, ([Transaction]![Resale]/
[Transaction]![Quantity])-[qryAveCost]![AveCost] AS
AveMargin
FROM Transaction INNER JOIN qryAveCost ON
Transaction.Quantity = qryAveCost.Quantity;

It turn out to give 7 records instead of 5 which some
records are repeated twice. Where is my mistake?
Click to expand...


.
Click to expand...
Click to expand...
 
Top