Counting in Queries Ends Up Multiplying

[MikeP]

Having a problem counting with queries. I'm pulling data in several
different queries. When I go to put the data in a report (using the souce
record query builder to tie the queires to the report) the counts multiply by
each other. I've tried it several different way but the count totals keep
multiplying by each other. Any one know what I'm doing wrong??

Thanks.

 

[Duane Hookom]

I expect your query is set up wrong. It's difficult to determine since you
haven't provided any information about your table structures or queries.

 

[Lord Kelvan]

you are getting a cartesian product because query1 and query2 etc are
not linked together and most likly cannot. what you sould do is
create a sub report for each query in the main report.

when you do a query one multiple tables if you dotn link them you get
the cartesian product

ie

table1
name
john
frank
bob

table2
occupation
builder
plumber
bum

if you did the query
select name,occupation from table1,table2
you will get the results

john builder
frank builder
bob builder
john plumber
frank plumber
bob plumber
john bum
frank bum
bob bum

so you add forigen and primary keys into the tables so you dont get
this

table1
table1id name
1 john
2 frank
3 bob

table2
table2id occupation table1id
1 builder 2
2 plumber 3
3 bum 1

(of course you woudl create a table inbetween for this type of
information but that is just too much to type)

so then your query would be

select name,occupation
from table1,table2
where table1.table1id = table2.table1id

and you will get

john bum
frank builder
bob plumber

now when you have two queries you and you want to link them together
into one query you need some way fo linking them just as you do with
the tables else you get the same problem

and because you most likly wont be able to do this you need as stated
above to create a sub report for each query in the main one.

hope this helps

Regards
Kelvan