trouble calculating sums in a query

[Guest]

I am a new Access user and am having trouble getting my query to calculate
the correct sum when drawing from two databases. The situation is:

Tables: I have one table that contains information about circulation
(mailing list codes, circulation counts for each mailing list code, etc.). I
have one table that says how many items were ordered from a mailing list
code. Each line of this database represents an order placed. The mailing list
codes (my field that joins these two tables) repeat itself throughout the
report.

Query: I can make one query to find total circulation counts for each
mailing list code (drawing only from one table) and I can make a query that
finds total orders for each mailing list code (drawing from both tables), but
when I try to make a query that will find both totals, my circulation count
totals is way how--way to high. I think it is adding circulation counts extra
times whenever it finds the mailing code repeated in the orders table. Make
sense to anyone?

I've tried setting the Unique Values property to Yes, but still am having a
problem.

Any help would be greatly appreciated. The reason I am trying to get these
totals together in one query is because my next step will be to find response
rates for mailing lists and will need to divide the total orders by the total
circulation counts.

Thanks!

 

[Baz]

OK, you've got two working queries, create a third query to bring them
together.

Let's suppose that your working queries are:

qryCirculationCount, which returns columns mailing_list_code and circ_count
qryOrderCount, which returns columns mailing_list_code and order_count.

Let's also suppose that you have a table which lists all of the valid
mailing list codes, let's call it mailing_list_codes, primary key
mailing_list_code.

So, your third query would be as follows:

SELECT
mailing_list_codes.mailing_list_code, circ_count, order_count
FROM
(mailing_list_codes LEFT JOIN
qryCirculationCount ON mailing_list_codes.mailing_list_code =
qryCirculationCount.mailing_list_code) LEFT JOIN
qryOrderCount ON mailing_list_codes.mailing_list_code =
qryOrderCount.mailing_list_code

 

[Guest]

Thanks for your help Baz. I am working on trying to create the query as you
suggested. Will hopefully be able to share results soon...

 

[Guest]

This is still not working for me. I tried running the query as you suggested,
but my counts are still way off. Should I be trying to do something to select
distinct rows from the circulation table? Any other ideas?

 

[Baz]

No, not without some examples of your data and the corresponding results
returned by the query.