from n records become n-square records??

  • Thread starter Thread starter kayak
  • Start date Start date
K

kayak

i have table "Transaction detail" consist of 5 records and
fields are
transactionID,DistrCost,ResaleAmount,ResaleQuantity. i
created query "qry DistrCost" to calculate AveDC.
Now i want to recall that 5 records under "AveDC" field to
calculate in another query which is "qry
ResaleAmount".Below is SQL term.


SELECT TransactionDetail.ResaleAmount, [ResaleAmount]/
[TransactionDetail]![ResaleQuantity] AS AveResale, 0.8*
[AveResale] AS [AveResale@80%], IIf(([AveResale@80%]<[qry
DistrCost]![AveDC]),1,0) AS [Resale@80%-AveDC]
FROM TransactionDetail, [qry DistrCost]
WHERE ((([qry DistrCost].AveDC) Is Not Null));


after executed it i got 25 records instead of 5.i believe
the mistake whould be in WHERE statement but i dont know
what to replace it. any suggestion to make correct?
 
Hi,


If you have 5 records in TransactionDetail and 25 records in the result,
it is very probably because you have 5 records in [qry DistrCost] that have
a not null value under their field AveDC.

You have to express the relation between TransactionDetail and [qry
DistrCost] that would link the records in a one to one relation (I imagine)
for the purpose of that query. That kind of temporary relation is a join.

It is preferable to use a dot, not a ! between a table name and its
field.



Hoping it may help,
Vanderghast, Access MVP
 
after executed it i got 25 records instead of 5.i believe
the mistake whould be in WHERE statement but i dont know
what to replace it. any suggestion to make correct?
Click to expand...

As Michel suggests, you're not joining the table to the query; Access
is giving you a 'Cartesian product', every possible combination or
records between the two. Try

SELECT TransactionDetail.ResaleAmount, [ResaleAmount]/
[TransactionDetail].[ResaleQuantity] AS AveResale, 0.8*
[AveResale] AS [AveResale@80%], IIf(([AveResale@80%]<[qry
DistrCost].[AveDC]),1,0) AS [Resale@80%-AveDC]
FROM TransactionDetail INNER JOIN [qry DistrCost]
ON TransactionDetail.[ID]= qryDistrCost.[ID]
WHERE ((([qry DistrCost].AveDC) Is Not Null));

replacing ID with the name of the field which uniquely identifies an
item.
 
Back
Top