Summing from subreports - Help!!!

[mitchy1111]

Hi All,



I need some help please. See attached file.

Basically, we use the database to produce quotes for ramp kits. Each quote will have a number of different kits which make up the ramp. When the quote is accepted, its status is changed to ordered and I need to extract data regarding stock commitment for all accepted quotes

So Firstly I have the Table 'Quotes' which stores the client info, discounts, quote no, status etc.

Then I have 'Quotes Line Items METAL' which stores each item line from the quote linked to the 'Quotes' Table via QuoteNo and Autonumber.

Finally I have the table 'KITSMETALtbl' which stores the bill of materials (all parts required to make up the kit) and is linked to Quotes Line Items METAL table via ItemID.

The StockCommitment report then pulls out the top line info from the Quotes table. This has a subreport (StockCommitmentKITOverview) from Quotes Line Items METAL listing all the KITS on the quote. A further subreport (StockCommitmentBreakdown) then lists the required parts for each quote item line.

I am trying to sum up the breakdown of all these parts so we can assess all the stock that is committed.

Spent hours trying to work out how to sum these up. I wondered if there is a way using VBA or by changing the structure. Help much appreciated.

Cheers

[June7]

I give up trying to figure out aggregate calcs by references to report/subreport/subsubreport objects and controls. Do another subreport on the main form for just aggregate calcs.

Here is alternative. Doesn't use subreports. Change main report RecordSource to be a join of all three tables.
SELECT KITSMETALtbl.*, [Quotes Line Items METAL].*, Quotes.*
FROM KITSMETALtbl RIGHT JOIN ([Quotes Line Items METAL] INNER JOIN Quotes ON [Quotes Line Items METAL].QuoteNo = Quotes.Autonumber) ON KITSMETALtbl.ID = [Quotes Line Items METAL].ItemID
WHERE ((Not (KITSMETALtbl.ID) Is Null))
ORDER BY Quotes.Client, KITSMETALtbl.ID;

Then use report Grouping/Sorting with summary calcs in footers. Makes the aggregate calcs easier although having some frustration with sorting.

[Stingaway]

Often times I will create a separate query (especially for summary info of detail that exists in a subform) and then use that query as a separate subform on the main form. The only catch is when information changes and the requery or refresh that has to take place, but for the most part the whole ship in a bottle in a bottle approach can get dicey (for me). I try to do as much from the main form as I can.. bah. just realized you were talking sub reports not sub forms. Ignore this... ha.

[mitchy1111]

Thanks June7, I'll have to brush up on my SQL