why does this querry multiply the rows by 9

[Bob H]

I have just tried building a querry to give me some values, and then try
some totals, but adding 2 other querries to it and selecting the
required feilds, I get 9 times the amount of data back than really is
there. There are only 4 records that I want, but the querry returns 36
records!

SELECT qryPartValue.DescriptionofCargo, qryPartValue.CommercialValue,
qryTeessideValues.DescriptionofCargo, qryTeessideValues.CommercialValue,
qryTeessideValues.ShippingDate
FROM qryPartValue, qryTeessideValues;


Thanks

 

[XPS350]

I have just tried building a querry to give me some values, and then try
some totals, but adding 2 other querries to it and selecting the
required feilds, I get 9 times the amount of data back than really is
there. There are only 4 records that I want, but the querry returns 36
records!

SELECT qryPartValue.DescriptionofCargo, qryPartValue.CommercialValue,
qryTeessideValues.DescriptionofCargo, qryTeessideValues.CommercialValue,
qryTeessideValues.ShippingDate
FROM qryPartValue, qryTeessideValues;

Thanks

I guess one of the queries you select from returns 9 rows and the
other 4. You get 4 * 9 rows as a result because you don't "join" the
queries. If there is is a common column in both queries, joining them
on that columns will reduce the result to 0 to 9 rows. If there is no
such field, there is no way you can do it.

Groeten,

Peter
http://access.xps350.com

 

[Bob H]

I guess one of the queries you select from returns 9 rows and the
other 4. You get 4 * 9 rows as a result because you don't "join" the
queries. If there is is a common column in both queries, joining them
on that columns will reduce the result to 0 to 9 rows. If there is no
such field, there is no way you can do it.

Groeten,

Peter
http://access.xps350.com

Ok, thanks, but after I joined DescriptionOfCargo from both querries, I
now getonly 8 records. There are 4 records on one qurry and 9 on the
other, so I would have expected a return of both the 4 and 9 records.
Obviously I am not doing something right.

Thanks

 

[Clifford Bass via AccessMonster.com]

Hi Bob,

I would not expect you to get more than four records. Joining on a
description field may be problematic unless the values are unique. For
instance you might have two separate shipments of "Automobiles", one on Jan.
5 and the other on Jan 10. Something like that could cause an erroneous
return of eight records from the query. How about you post your data and
what you are expecting to get from it with your query? Use fake data if your
data is sensitive.

Clifford Bass

 

[XPS350]

Ok, thanks, but after I joined DescriptionOfCargo from both querries, I
now getonly 8 records. There are 4 records on one qurry and 9 on the
other, so I would have expected a return of both the 4 and 9 records.
Obviously I am not doing something right.

Thanks

Getting 8 rows means that there are 8 rows in the 9 row query that
have a matching Description in the 4 row query. The number of rows
returned depends on the values in your queries. Without knowing the
values I predicted 0 to 9 rows in my previous post.

Groeten,

Peter
http://access.xps350.com

 

[Bob H]

Hi Clifford,
here is the SQL for the querry, which returns 8 records:

SELECT qryPartValue.DescriptionofCargo, qryPartValue.CargoQty,
qryPartValue.CommercialValue, qryTeessideValues.DescriptionofCargo,
qryTeessideValues.CargoQty, qryTeessideValues.CommercialValue INTO
tblPartValues
FROM qryPartValue INNER JOIN qryTeessideValues ON
qryPartValue.DescriptionofCargo = qryTeessideValues.DescriptionofCargo;


The 2 querries, qryPartValue and qryValueTeesside, have a total of 13
records, 4 in one and 9 in the other. If I selected Description, qty and
values in the querry, I would like to get all 13 records, the 4 and the
9. At the moment, both the querries list the records side by side, ie,
one set of records is next to the other set of records, 8 in each

Really I should expect only see 4 in one and 9 in the other.

Thanks

 

[XPS350]

Hi Clifford,
                here is the SQL for the querry, which returns 8 records:

SELECT qryPartValue.DescriptionofCargo, qryPartValue.CargoQty,
qryPartValue.CommercialValue, qryTeessideValues.DescriptionofCargo,
qryTeessideValues.CargoQty, qryTeessideValues.CommercialValue INTO
tblPartValues
FROM qryPartValue INNER JOIN qryTeessideValues ON
qryPartValue.DescriptionofCargo = qryTeessideValues.DescriptionofCargo;

The 2 querries, qryPartValue and qryValueTeesside, have a total of 13
records, 4 in one and 9 in the other. If I selected Description, qty and
values in the querry, I would like to get all 13 records, the 4 and the
9. At the moment, both the querries list the records side by side, ie,
one set of records is next to the other set of records, 8 in each

Really I should expect only see 4 in one and 9 in the other.

Thanks

If you want the 13 rows of both queries, you need to make a UNION
query:
SELECT DescriptionofCargo, CargoQty, CommercialValue FROM qryPartValue
UNION ALL
SELECT DescriptionofCargo, CargoQty, CommercialValue FROM
qryTeessideValues

I am not sure about making a create query of it directly. Maybe u need
to save the above select query and use that to create tblPartValues.

Groeten,

Peter
http://access.xps350.com

 

[Bob H]

If you want the 13 rows of both queries, you need to make a UNION
query:
SELECT DescriptionofCargo, CargoQty, CommercialValue FROM qryPartValue
UNION ALL
SELECT DescriptionofCargo, CargoQty, CommercialValue FROM
qryTeessideValues

I am not sure about making a create query of it directly. Maybe u need
to save the above select query and use that to create tblPartValues.

Groeten,

Peter
http://access.xps350.com

Hi peter, thanks for the explanation and use of a Union querry.
I have ran that and it does give 13 rows of data.
the
Presently on my main form, I have had to add 2 cmdButtons to open up 2
other forms based on the 2 said querries, to give me the vcalues required.
I was hoping for some method of just either having one extra/subform to
give the values returned from the 2 querries, or if possible get those
values into a text box on the main form, but I can't see how to do it yet.

Thanks