I have many "sub" tables and corresponding forms that I am using to generate a total in a main form. However, when I generate more than one expression in the main form query (one expression to sum the data from each of the "subs"), I am getting what I guess you could call duplicate sums. The query is returning a result for the first record in the first sub with the first record from the second sub, then the first record of the first sub with the second record of the second sub, and so on. I also created queries to do the work (as opposed to creating the expressions in the main form query), but I am getting the same result.

I am assuming this is a relationship issue, or maybe I can't use multiple "detail" items to create a total, unless there is something else I'm am missing. All subs are linked to the main with a QuoteID. I have tried different combinations of relationship types, but with the same results. I'm not completely new to this but I'm certainly not an expert. Any help I could get would be great.

Thanks,


Brian

Hi Brian

Can you upload your Db?

Mike

It sounds like a cross-product to me. The queries are not related in any way to each other and one or more of them returns more than 1 record. Since the queries are not related, Access has no way to match a record from query1 with its corresponding record in query2, etc. Therefore it is producing a recordset that includes all possible combinations.

If the queries have a common field, then you can join them on that field and get an intelligently-matched recordset.

If each query only returned one record, then you would have exactly what you want.