Inner join problem

  • Thread starter Thread starter JME
  • Start date Start date
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?
 
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
 
I think you want to join the tables on TransID, not on Quantity.

Also, change ! to . in your queries.
 
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;
 
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...
 
! 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...
 
Back
Top