M:M Relationship and Report Sum Problem?

[CHTHOMAS]

I have got 4 tables with the following fields.

Standart Table -> Standard Id, Description, Discipline, Price, Currency
Specification Table -> Spec id, Spec Title
Currency Table -> Currency id, Currency Name, Conversion Rate
Join Table -> Standard id, Spec id

with the following links and relationships

"Price" of standard table linked to "Currency id" of Currency table(M:1)
"Standard id" of standard table linked to "Standard id" of Join Table(1:M)
"Spec id" of Specification table linked to "spec id" of Join Table(1:M)

ie One standard can belong to Many specifications. One specification can belong to many standards.

I have a select query which selects the fields from these tables and the same query is used for a report.
The select query select "Standardid" from the join table and the other details of Standards from the StandardTable, Specificationid from the Join table and other details of Specification from the Specification Table.
The report tries to group Discipines first then the Standards belonging to that discipline followed by the specifications of each standards and then sum the prices of the standards.

My problem.

If i include only tables "Standard" and "Currency" in the query then i am getting the sum of standards correctly.
If i include "Join" and "Specification" tables along with the above tables in the query, then report takes into account the number of specifications and sums the prices of standards accordingly.
ie if a standard has the price $25 and there 2 specifcations belonging to it, then instead of showing the sum as $25, it shows as $50.


I tried to Pick the Standardid from the standard table, but the result is same. I am sure i am doing something stupid with the query.

Can anybody help me to solve the above problem. any ideas or suggestions or solutions will be highly appreciated. If my explanation is difficult to understand i can email the sample db (very small in size)

Regards and Thanks

Charley

 

[RickSpr]

Open your query and look at the data. You're getting one row for each spec for each standard, and each row has the corresponding standard's price in it. In other words, you're adding up the price for each spec when what you want is the price for each standard.

Try creating a subreport. The subreport's recordsource should be a query that contains your Join and Specification tables. The main report's query should contain only Standard and Currency tables, which gives you the correct price totals. The linking field from the main report to the subreport is StandardId. For each Standard on the main report, the subreport will list each Specification title.