Display Calculated Value in Subform text box based on value from Combo selection.

[joel009]

Windows 7, Access 2010.
On the Subform I have a combo that displays values 1-4 based on table CalcType. Previously we have been storing calculated values in a table, not a temp table, and using the values. The problem is updating the 4 values involves a few calculation sub's and slows the db down.
I developed a query that pulls all 4 calculation values without the intermediate tables. Great I thought, I can save all the time calcualting values.
Subform is continuous. Subform query is such that I can update fields. The problem is displaying the calculated value in a locked text box based on the value in the combo box. If I enhance the subform query to include the calculated values (I can do that) the rest of the subform fields are not updateable.

Anyone have any idea how I can display the calculated value (in a locked txt box) in a subform and still keep the rest of the subform updateable.

Subform Query:
SELECT tblPartRouteAssignment.MassUpdate, tblPartRouteAssignment.DefinedInKanban, tblPartRouteAssignment.PartNumber, tblPartInfo.Description, tblPartRouteAssignment.Station, tblPartRouteAssignment.PartStation, tblPartRouteAssignment.CalculationTypeFK, tblPartInfo.PackDensity, tblPartInfo.Container, tblPartRouteAssignment.ReceivingDock, tblPartRouteAssignment.LineFeedDeliveryType, tblPartRouteAssignment.DockUnloadType, tblPartRouteAssignment.LocFullStorage, tblPartRouteAssignment.TravDistLineFeedVA, tblPartRouteAssignment.TravDistLineFeedNVA, tblPartRouteAssignment.TravDistDockVA, tblPartRouteAssignment.TravDistDockNVA, tblPartRouteAssignment.StackHtReceiving, tblPartRouteAssignment.StackHtLFDelivery, tblPartRouteAssignment.StackHtTrainLen, tblPartRouteAssignment.StackHtEmpties, tblPartRouteAssignment.EmptyTrailer
FROM (tblDockHandling RIGHT JOIN ((((tblPartRouteAssignment LEFT JOIN tblPartStation ON (tblPartRouteAssignment.PlantCode = tblPartStation.PlantCode) AND (tblPartRouteAssignment.PartNumber = tblPartStation.PartNumber) AND (tblPartRouteAssignment.Station = tblPartStation.Station) AND (tblPartRouteAssignment.ModelYear = tblPartStation.ModelYear)) LEFT JOIN tblPartInfo ON (tblPartRouteAssignment.PlantCode = tblPartInfo.PlantCode) AND (tblPartRouteAssignment.PartNumber = tblPartInfo.PartNumber) AND (tblPartRouteAssignment.ModelYear = tblPartInfo.ModelYear)) LEFT JOIN tblRouteInfo ON (tblPartRouteAssignment.PlantCode = tblRouteInfo.PlantCode) AND (tblPartRouteAssignment.Route = tblRouteInfo.Route) AND (tblPartRouteAssignment.ModelYear = tblRouteInfo.ModelYear)) LEFT JOIN tblLinefeedHandling ON tblPartRouteAssignment.LineFeedDeliveryType = tblLinefeedHandling.LineFeedHandlingID) ON tblDockHandling.DockID = tblPartRouteAssignment.DockUnloadType) LEFT JOIN tblEfficiencyModelType ON tblRouteInfo.EffModelTypeFK = tblEfficiencyModelType.EffModelTypePK
WHERE (((tblPartRouteAssignment.PlantCode)=[Forms]![frmBaseRouteAssignment]![txtPlantCode]) AND ((tblPartRouteAssignment.ModelYear)=[Forms]![frmBaseRouteAssignment]![txtModelYear]))
ORDER BY tblPartRouteAssignment.Route, tblPartRouteAssignment.PartNumber, tblPartInfo.Description;

Calculation Query:
SELECT tblRouteInfo.PlantCode, tblRouteInfo.ModelYear, tblRouteInfo.Route, tblRouteInfo.Department, tblRouteInfo.JobType, tblRouteInfo.Validated, tblRouteInfo.NumberofDrivers, tblRouteInfo.BattChange, tblRouteInfo.SafetyChk, tblRouteInfo.AddlMinPerShiftRt, tblRouteInfo.TravelSpeed, tblRouteInfo.SmallLotNumStops, tblRouteInfo.SmallLotDelyCycles, tblRouteInfo.RouteComment1, tblRouteInfo.RouteComment2, tblEfficiencyModelType.EfficiencyModelType, tblPartRouteAssignment.PartNumber, tblPartRouteAssignment.Station, tblPlantInfo.WorkingMinPerShift, tblPlantInfo.ShiftsPerDay, tblPartRouteAssignment.LineFeedDeliveryType, tblPartRouteAssignment.DockUnloadType, tblPartRouteAssignment.ReceivingDock, tblPartRouteAssignment.LocFullStorage, tblPartRouteAssignment.LocLineFeedStaging, tblPartRouteAssignment.LocEmptyStorage, tblPartRouteAssignment.LocDockReturn, tblPartInfo.Description, tblPartRouteAssignment.CalculationTypeFK, Nz((IIf([tblPartRouteAssignment]![CalculationTypeFK]=1,[CalcType1BldRt]) & IIf([tblPartRouteAssignment]![CalculationTypeFK]=2,[CalcType2BldRt]) & IIf([tblPartRouteAssignment]![CalculationTypeFK]=3,[CalcType3BldRt]) & IIf([tblPartRouteAssignment]![CalculationTypeFK]=4,[CalcType4BldRt])),0) AS PSBld_Rate, tblPartInfo.PackDensity, tblPartInfo.Container, tblPartInfo.Length, tblPartInfo.Width, tblPartInfo.Height, IIf(Nz([PackDensity],0)<=0,0,[PSBld_Rate]/[PackDensity]) AS COPD, tblLinefeedHandling.LineFeedType, tblLinefeedHandling.LineFeedHandlingVA, tblLinefeedHandling.LineFeedHandlingNVA, tblLinefeedHandling.StagingHandlingVA, tblLinefeedHandling.StagingHandlingNVA, tblLinefeedHandling.EmptyReturnHandlingVA, tblLinefeedHandling.EmptyReturnHandlingNVA, tblDockHandling.UnloadType, tblDockHandling.DockHandlingVA, tblDockHandling.DockHandlingNVA, tblPartRouteAssignment.StackHtReceiving, tblPartRouteAssignment.StackHtLFDelivery, tblPartRouteAssignment.StackHtEmpties, IIf([EfficiencyModelType]="FORKLIFT",(([LineFeedHandlingVA]+[LineFeedHandlingNVA])/[StackHtLFDelivery])+(([StagingHandlingVA]+[StagingHandlingNVA])/[StackHtReceiving])+(([EmptyReturnHandlingVA]+[EmptyReturnHandlingNVA])/[StackHtEmpties]),IIf([EfficiencyModelType]="SMALLPARTS",(([LineFeedHandlingVA]+[LineFeedHandlingNVA])/[StackHtLFDelivery]),IIf([EfficiencyModelType]="TUGGER",([LineFeedHandlingVA]+[LineFeedHandlingNVA])+(([StagingHandlingVA]+[StagingHandlingNVA])/[StackHtTrainlen]),(([LineFeedHandlingVA]+[LineFeedHandlingNVA])/[StackHtTrainlen])+(([StagingHandlingVA]+[StagingHandlingNVA])/[StackHtTrainlen]))))+[AddlMinPerShiftPt] AS HdlgPerCont, tblPartRouteAssignment.TravDistLineFeedVA, tblPartRouteAssignment.TravDistLineFeedNVA, tblPartRouteAssignment.TravDistStagingVA, tblPartRouteAssignment.TravDistStagingNVA, tblPartRouteAssignment.TravDistDockVA, tblPartRouteAssignment.TravDistDockNVA, tblPartRouteAssignment.TravDistEmptyVA, tblPartRouteAssignment.TravDistEmptyNVA, IIf([EfficiencyModelType]="FORKLIFT",(([TravDistLineFeedVA]+[TravDistLineFeedNVA])/[StackHtLFDelivery])+(([TravDistStagingVA]+[TravDistStagingNVA])/[StackHtReceiving])+(([TravDistEmptyVA]+[TravDistEmptyNVA])/[StackHtEmpties]),IIf([EfficiencyModelType]="SMALLPARTS",[SmallLotTravelVA]+[SmallLotTravelNVA],IIf([EfficiencyModelType]="TUGGER",(([TravDistLineFeedVA]+[TravDistLineFeedNVA])/[StackHtTrainLen])+(([TravDistStagingVA]+[TravDistStagingNVA])/[StackHtTrainLen]),(([TravDistLineFeedVA]+[TravDistLineFeedNVA])/[StackHtTrainLen])))) AS TravelPerTrip, [TravelPerTrip]*(60/(5280*[TravelSpeed])) AS TravelMinPerTrip, IIf([EfficiencyModelType]<>"SMALLPARTS",[HdlgPerCont]+[TravelMinPerTrip],[HdlgPerCont]) AS AllocPerCont, IIf(Nz([PackDensity],0)=0,0,IIf([tblLinefeedHandling].[LineFeedType]="0",0,([AllocPerContVA]+[AllocPerContNVA])*[COPD])) AS LFAllocation, IIf(Nz([PackDensity],0)=0,0,IIf([tblLinefeedHandling].[LineFeedType]="0",0,[AllocPerContVA]*[COPD])) AS LFAllocationVA, IIf(Nz([PackDensity],0)=0,0,IIf([tblLinefeedHandling].[LineFeedType]="0",0,[AllocPerContNVA]*[COPD])) AS LFAllocationNVA, IIf(Nz([PackDensity],0)=0,0,IIf([tblDockHandling].[UnloadType]="0",0,([DockAllocPerContVA]+[DockAllocPerContNVA])*[COPD])) AS DockAllocation, IIf(Nz([PackDensity],0)=0,0,IIf([tblDockHandling].[UnloadType]="0",0,[DockAllocPerContVA]*[COPD])) AS DockAllocationVA, IIf(Nz([PackDensity],0)=0,0,IIf([tblDockHandling].[UnloadType]="0",0,[DockAllocPerContNVA]*[COPD])) AS DockAllocationNVA, [LFAllocation]+[DockAllocation] AS TotalAllocation, tblPartRouteAssignment.LocPickNPack, tblPartRouteAssignment.LocLineFeedEmptyStaging, tblPartRouteAssignment.AddlMinPerShiftPt, tblRouteInfo.SmallLotTravelVA, tblRouteInfo.SmallLotTravelNVA, IIf([EfficiencyModelType]="FORKLIFT",(([TravDistLineFeedVA]/[StackHtLFDelivery])+([TravDistStagingVA]/[StackHtReceiving])+([TravDistEmptyVA]/[StackHtEmpties])),IIf([EfficiencyModelType]="SMALLPARTS",[SmallLotTravelVA],IIf([EfficiencyModelType]="TUGGER",(([TravDistLineFeedVA]/[StackHtTrainLen])+([TravDistStagingVA]/[StackHtTrainLen])),([TravDistLineFeedVA]/[StackHtTrainLen])))) AS TravelPerTripVA, IIf([EfficiencyModelType]="FORKLIFT",(([TravDistLineFeedNVA]/[StackHtLFDelivery])+([TravDistStagingNVA]/[StackHtReceiving])+([TravDistEmptyNVA]/[StackHtEmpties])),IIf([EfficiencyModelType]="SMALLPARTS",[SmallLotTravelNVA],IIf([EfficiencyModelType]="TUGGER",(([TravDistLineFeedNVA]/[StackHtTrainLen])+([TravDistStagingNVA]/[StackHtTrainLen])),([TravDistLineFeedNVA]/[StackHtTrainLen])))) AS TravelPerTripNVA, IIf([EfficiencyModelType]="FORKLIFT",([LineFeedHandlingVA]/[StackHtLFDelivery])+([StagingHandlingVA]/[StackHtReceiving])+([EmptyReturnHandlingVA]/[StackHtEmpties]),IIf([EfficiencyModelType]="SMALLPARTS",([LineFeedHandlingVA]/[StackHtLFDelivery]),IIf([EfficiencyModelType]="TUGGER",([LineFeedHandlingVA])+([StagingHandlingVA]/[StackHtTrainlen]),([LineFeedHandlingVA]/[StackHtTrainlen])+([StagingHandlingVA]/[StackHtTrainlen])))) AS HdlgPerContVA, IIf([EfficiencyModelType]="FORKLIFT",([LineFeedHandlingNVA]/[StackHtLFDelivery])+([StagingHandlingNVA]/[StackHtReceiving])+([EmptyReturnHandlingNVA]/[StackHtEmpties]),IIf([EfficiencyModelType]="SMALLPARTS",([LineFeedHandlingNVA]/[StackHtLFDelivery]),IIf([EfficiencyModelType]="TUGGER",([LineFeedHandlingNVA])+([StagingHandlingNVA]/[StackHtTrainlen]),([LineFeedHandlingNVA]/[StackHtTrainlen])+([StagingHandlingNVA]/[StackHtTrainlen]))))+[AddlMinPerShiftPt] AS HdlgPerContNVA, [TravelPerTripVA]*(60/(5280*[TravelSpeed])) AS TravelMinPerTripVA, [TravelPerTripNVA]*(60/(5280*[TravelSpeed])) AS TravelMinPerTripNVA, IIf([EfficiencyModelType]<>"SMALLPARTS",[HdlgPerContVA]+[TravelMinPerTripVA],[HdlgPerContVA]) AS AllocPerContVA, IIf([EfficiencyModelType]<>"SMALLPARTS",[HdlgPerContNVA]+[TravelMinPerTripNVA],[HdlgPerContNVA]) AS AllocPerContNVA, [TravDistDockVA]/[StackHtReceiving] AS DockTravelPerTripVA, [TravDistDockNVA]/[StackHtReceiving] AS DockTravelPerTripNVA, IIf(Nz([StackHtReceiving],0)<=0,0,[DockHandlingVA]/[StackHtReceiving]) AS DockHdlgPerContVA, IIf(Nz([StackHtReceiving],0)<=0,0,[DockHandlingNVA]/[StackHtReceiving]) AS DockHdlgPerContNVA, [DockTravelPerTripVA]*(60/(5280*[TravelSpeed])) AS DockTravelMinPerTripVA, [DockTravelPerTripNVA]*(60/(5280*[TravelSpeed])) AS DockTravelMinPerTripNVA, [DockHdlgPerContVA]+[DockTravelMinPerTripVA] AS DockAllocPerContVA, [DockHdlgPerContNVA]+[DockTravelMinPerTripNVA] AS DockAllocPerContNVA, (([SmallLotTravelVA]+[SmallLotTravelNVA])*(60/(5280*[TravelSpeed])))*[SmallLotDelyCycles] AS SLFPM, [SmallLotNumStops]*0.33*[SmallLotDelyCycles] AS SLSST, ([SLSST]+[SLFPM]) AS SLTravelandStops, IIf(Nz([StackHtLFDelivery],0)<=0,0,[LineFeedHandlingVA]/[StackHtLFDelivery]) AS LFHdlgPerContVA, IIf(Nz([StackHtReceiving],0)<=0,0,[StagingHandlingVA]/[StackHtReceiving]) AS StgngHdlgPerContVA, IIf(Nz([StackHtEmpties],0)<=0,0,[EmptyReturnHandlingVA]/[StackHtEmpties]) AS EmptyHdlgPerContVA, IIf(Nz([StackHtLFDelivery],0)<=0,0,[LineFeedHandlingNVA]/[StackHtLFDelivery]) AS LFHdlgPerContNVA, IIf(Nz([StackHtReceiving],0)<=0,0,[StagingHandlingNVA]/[StackHtReceiving]) AS StgngHdlgPerContNVA, IIf(Nz([StackHtEmpties],0)<=0,0,[EmptyReturnHandlingNVA]/[StackHtEmpties]) AS EmptyHdlgPerContNVA, tblPartRouteAssignment.StackHtTrainLen, tblPartStation.StationBld_Rate AS StationBldRt, tblPartStation.PlantUsage, Nz([sel_CalcType3_Count_DNR].[CountOfPartNumber],0) AS CountOfCalcType3, sel_CalcType3_BldRt_DNR.CalcType3BldRtRaw, sel_PartStationCount_DNR.CountPartStation, tblPartInfo.Bld_Rate, Format(CDbl(IIf([CountOfCalcType3]=3,((([tblPartinfo].[Bld_Rate]-[CalcType3BldRt]/[sel_PartStationCount_DNR].[CountPartStation])*([tblPlantInfo].[JobsPerDay]/[tblPlantInfo].[PartDumpMultiplier])/[tblPlantInfo].[ShiftsPerDay])),((([tblPartInfo].[Bld_Rate]/[sel_PartStationCount_DNR].[CountPartStation])*([tblPlantInfo].[JobsPerDay]/[tblPlantInfo].[PartDumpMultiplier])/[tblPlantInfo].[ShiftsPerDay])))),'0.0') AS CalcType1BldRt, tblPartStation.VerifiedBld_Rate AS CalcType2BldRt, sel_CalcType3_BldRt_DNR.CalcType3BldRtRaw AS CalcType3BldRt, Format(CDbl((([tblPartInfo].[Bld_Rate]*([tblPlantInfo].[JobsPerDay]/[tblPlantInfo].[PartDumpMultiplier])/[tblPlantInfo].[ShiftsPerDay]))),'0.0') AS CalcType4BldRt
FROM ((((tblDockHandling INNER JOIN (((((tblPartRouteAssignment INNER JOIN tblPlantInfo ON tblPartRouteAssignment.PlantCode = tblPlantInfo.PlantCode) INNER JOIN tblPartStation ON (tblPartRouteAssignment.ModelYear = tblPartStation.ModelYear) AND (tblPartRouteAssignment.Station = tblPartStation.Station) AND (tblPartRouteAssignment.PartNumber = tblPartStation.PartNumber) AND (tblPartRouteAssignment.PlantCode = tblPartStation.PlantCode)) INNER JOIN tblPartInfo ON (tblPartRouteAssignment.ModelYear = tblPartInfo.ModelYear) AND (tblPartRouteAssignment.PartNumber = tblPartInfo.PartNumber) AND (tblPartRouteAssignment.PlantCode = tblPartInfo.PlantCode)) INNER JOIN tblRouteInfo ON (tblPartRouteAssignment.ModelYear = tblRouteInfo.ModelYear) AND (tblPartRouteAssignment.Route = tblRouteInfo.Route) AND (tblPartRouteAssignment.PlantCode = tblRouteInfo.PlantCode)) INNER JOIN tblLinefeedHandling ON tblPartRouteAssignment.LineFeedDeliveryType = tblLinefeedHandling.LineFeedHandlingID) ON tblDockHandling.DockID = tblPartRouteAssignment.DockUnloadType) INNER JOIN tblEfficiencyModelType ON tblRouteInfo.EffModelTypeFK = tblEfficiencyModelType.EffModelTypePK) LEFT JOIN sel_CalcType3_Count_DNR ON (tblPartRouteAssignment.CalculationTypeFK = sel_CalcType3_Count_DNR.CalculationTypeFK) AND (tblPartRouteAssignment.Station = sel_CalcType3_Count_DNR.Station) AND (tblPartRouteAssignment.ModelYear = sel_CalcType3_Count_DNR.ModelYear) AND (tblPartRouteAssignment.PlantCode = sel_CalcType3_Count_DNR.PlantCode) AND (tblPartRouteAssignment.PartNumber = sel_CalcType3_Count_DNR.PartNumber)) LEFT JOIN sel_CalcType3_BldRt_DNR ON (tblPartRouteAssignment.PartNumber = sel_CalcType3_BldRt_DNR.PartNumber) AND (tblPartRouteAssignment.Station = sel_CalcType3_BldRt_DNR.Station) AND (tblPartRouteAssignment.ModelYear = sel_CalcType3_BldRt_DNR.ModelYear) AND (tblPartRouteAssignment.PlantCode = sel_CalcType3_BldRt_DNR.PlantCode)) LEFT JOIN sel_PartStationCount_DNR ON (tblPartRouteAssignment.ModelYear = sel_PartStationCount_DNR.ModelYear) AND (tblPartRouteAssignment.PartNumber = sel_PartStationCount_DNR.PartNumber) AND (tblPartRouteAssignment.PlantCode = sel_PartStationCount_DNR.PlantCode)
ORDER BY tblRouteInfo.Route DESC , tblPartRouteAssignment.PartNumber DESC;


Joel

 

[Katerine]

I don't understand the story behind the query or what exactly you wanted displayed in the text box, so I don't fully understand the question, but it does look quite a bit more complicated than it needs to be.

Don't do the calculation inside the query for this purpose. Do it inside the textbox. Pick the option that's most relevant for your needs...

Option #1:
I don't really know what the setup for the combo box is (or the text box, for that matter), and that's actually one of the most pertinent things, but let's say that you have a combo box called cboPickCalcType, and it's based just on CalcType, which is a table with 4 fields in it, and you want the textbox to display Column 2 (of the table) * 100 / Column 3. Since the textbox and the combo box are in the same subform, all you need to do is make sure that the column count of the combo box is set to 4 (the number of columns in the table), and then set the control source of the text box to this:

=[cboPickCalcType].[Column](1)*100/Nz([cboPickCalcType].[Column](2),.00001)

(Note that columns start at 0, so what a human would think of as column 2 is actually column 1, and so on).

Option #2:
If you need it to do something a little more complicated (like show a column that differs depending on what type is picked)... normally I would say to use VBA, but since it's a continuous form, that might return weird results. Instead, set the control source of the text box to something like this (based on what I see in your query):

=Nz((IIf([cboPickCalcType]=1,[CalcType1BldRt]) & IIf([cboPickCalcType]=2,[CalcType2BldRt]) & IIf([cboPickCalcType]=3,[CalcType3BldRt]) & IIf([cboPickCalcType]=4,[CalcType4BldRt])),0)

(Note that CalcType1BldRt, CalcType2BldRt, CalcType3BldRt, and CalcType4BldRt would all have to be bound controls on the form for this to work. They can be invisible and tiny, but they must be present).

Option #3:
Now let's say you need the textbox to do calculations based on a different table than the primary table you're editing. One option is to have all of those fields (that the calculation is based on) in the underlying query, add controls to the form for all of the needed fields (visible or invisible), and proceed with Option #2. That's definitely the fastest option.

But let's say you can't get the recordsource query to return the fields that you need to make the calculations AND be updateable, no matter how much you play with the join types (easiest way to find out, btw, is to just save the query, then run it and make modifications until you have something that you can edit in the query view, and fits what you need). Most common cause for this is grouping in the query. You can't have grouping in the query and have it be updateable - it's actually not possible. In that case, you'll most likely need to use a dlookup in the textbox. Try everything else first, though, because this option is a major pain, and makes the form much, much slower. ;-)

HTH!

Katie

 

[Katerine]

Oh, small correction under Option #2: this is what you really want for the code (since the combo box can't have more than one picked option):

=Nz(IIf([cboPickCalcType]=1,[CalcType1BldRt],IIf([cboPickCalcType]=2,[CalcType2BldRt],IIf([cboPickCalcType]=3,[CalcType3BldRt],IIf([cboPickCalcType]=4,[CalcType4BldRt],Null)))),0)

It's just a little more efficient. Plus, it'll not give you null-related errors and actually return 0 if nothing is picked in the combo box yet.

Katie

 

[joel009]

Thanks Katie, I have been beating my brains out trying the 3 solutions you recomend. Your solution #2 worked very well when i was storing the calculated values in a table which I am trying to get away from. The one part of the calculations I am having problems bringing in is counts which have to be done on the same table 2 different ways. Trying to obtain the counts outside of the subform main query results in the subfrom displaying the same value for every record in the subform or problems because the value the calculation is based on is not loaded/available when it tries to populate the calculated field.

I'll look at the joins again.
Joel

Joel

 

[Katerine]

Hi joel009,
Unfortunately, if you want the end result to be updateable, there cannot be any aggregate queries in there. If you're doing any record counts, or sums, or anything that involves aggregate queries, I'm afraid these are your options:

[ol 1][li]Resolve yourself to not having the recordset be updateable.

[/li][li]Redesign the form to a single form... maybe with a listbox in the header that you can use to see all the information for all records, and then double-click an item in the listbox to go to that record and edit. It doesn't matter if a listbox rowsource is updateable or not - listboxes can't be edited directly anyway. Just set the bound field to the id from the main table that you want to edit, then set the form's control source to something like:

SELECT * FROM [InsertNameOfMainTableToEditHere] WHERE ID = " & lstChooseRecord

In the listbox's double-click event:

Me.Refresh

If all of the calculated values are in the listbox, then you can set a bunch of display-only (not enabled) textboxes in the form to show columns from the listbox, which would be easiest.

This is probably what I would do, btw. Please let me know if you need any further clarification.

[/li][li]Change the query to use DCounts or DLookups or DMax or whatever, instead of aggregate queries. If you remove all aggregate queries, then the result should once again become updateable... but fair warning, the query will be very, very, very slow.

[/li][li]Continue to use a temp table to store the results of the aggregate queries, and update the table prior to the query being run. This is the most-often used option for reports and queries which must be updateable, because it actually considerably speeds things up vs. trying to run the aggregate functions in the same query, because the database has fewer complicated joins to deal with.

[/li][li]Move to SQL Server (which will probably wind up necessitating Option #2 anyway).[/li]
[/ol]

And... that's it. Those are the options I can think of. Sorry I don't have better news, but the fact is, in Jet, you just can't have a query that both has aggregate functions and is updateable.

Katie

 

[joel009]

Thanks Katie, The requirements are that the subform display multiple rows, there are 5 subfroms in total are all continuous so I can display multiple rows. The header section of the form displays the summary data. I am trying to enhance performance and slowing anything down does not sound attractive.
So there goes suggestions 2 & 3. We are using your suggestion 4 and it just does not sit well with me to store a calculated value, but it works smoothly that way.
The way the subform works has it's limitations.

Thanks again,
Joel


Joel