I have a query with two tables - a sale header table and a sale detail number. They are joined in the query by the sale number, common to both tables.
I need to count the number of sales, so in the query I am doing a count of the sale number in the header table. The result I am getting is the number of records in the DETAIL table.
What am I doing wrong? I am assuming that something in the join is causing a header record to be counted for each detail record, but I have tried all three types of joins, and still get the same result.

Robert

I assume the Header to Detail is 1:m. Why not just look at the Header table for your Count?

RG: I was collecting many columns of totals from the detail and five from the header, and tried to do that all at once. But the header got replicated with every line of detail, as I'm sure you know. So now I have a sub query for the detail and a sub query for the header. I feed those both into a query.
But now I get 'You tried to execute a query that does not include the specific expression 'APCOUNT' as part of an aggregate function.' APCOUNT is a count of the header records, and is passed up from the header sub-query. I have seen this message before, but cannot remember how to fix it.

Robert

Sorry but I can not help in this situation. Maybe one of the SQL guru's will drop by with a suggestion.

What going on is correct. If you think of the header as the parent and the detail being the child. Each child will have a parent so access will show you who the parent is for each child.

If you want to count the number of parents there are then you need to count the parent table only. You could do a group by query grouping by parent and counting by children this will tell you how many parents your have and how many children each parent has.

Remember each parent must have at least one child and no children can be orphans.

David

I've just replied to another thread on this topic, why go these people multi post to put it bluntly they are a pain, I shall delete my other post and not respond again.

Brian