Simple question that's driving me nutzoid!

[frosttr]

I looked at the excerpt you sent - not sure what you are referring to in as far as 'normalizing to get rid of repeating groups' though. When I viewed the associated form, there are only 2 that calculated, the other 4 were zero value. In the form, anything in description that is all in caps is a room designation, everything else is equipment that uses power and needs to be calculated. The equation for calculations is based off of the PH factor...anything that is a PH of 3 is WATTHRS=QTY*Volts*AMPS*HRS*UF*DF*1.73. If PH=1 (or in rare cases 2), then the equation would be the same, except for the *1.73 at the end. Have I succeeded in total confusion for you? This is all the data I have to work with. The prior programmer did this in DOS back in 1988, and can't be contacted for help, so I'm trying to get this converted into Access and to be as user friendly as possible, since several people will be using it. The calculation for this particular segment has me totally stumped - if I can ever figure it out, or even a work-around, I can do the rest of the calculations, reports, etc, with switchboarding. I haven't worked in DOS for more than 20 years, and because of locks, can't get into the programming to see how it was done. Plus, all company records for the past 30 years are in there, and I don't want to jeopardize corrupting it and losing everything. I'm just hoping someone out there can help me with a solution to this. I've worked in Access enough to know that "nothing is impossible", just a matter of "tricking" it to give the results you want. I've just not found that trick yet for this equation. I had thought as a last resort of doing this portion in Excel, converting formulae to number, then importing to Access. But that's an "iffy" solution, not to mention somewhat time consuming.


Thanks for any input you can give me!

[mike60smart]

Hi Sorry I set the query to only show those records where PH = 3

If you remove the filter in the query you should be ok

[frosttr]

Did you receive my response with a copy of the dBase?

[frosttr]

When you say 'remove the filter', are you referring to the 3 in the PH? This is critical in calculations, as not all equipment calculated are a PH3, so if I remove the 3, then everything will be calculated as PH1, which would give incorrect results.

[mike60smart]

Hi

The Repeating Group in the Client table is as shown below:-

From Date

To Date

Quantity

To Date2

Quantity2

To Date3

Quantity3

To Date4

Quantity4

To Date5

Quantity5

To Date6

Quantity6

To Date7

Quantity7

To Date8

Quantity8

To Date9

Quantity9

To Date10

Quantity10

To Date11

Quantity11

To Date12

Quantity12 This has to be dealt with in a separate table

[mike60smart]

Hi
In the Record Source of the Form that displays the data you just remove the Criteria of 3 from the Criteria Row

[mike60smart]

In Form Design View.

Double Click on the small square - shown in Black to the Left of the Ruler

This will display the Property Sheet for the Form

Click on the Data Tab and the record Source will be displayed.

Click in this Row and the Elipse (...) will display at the end of the Row

Click in the Elipse and the Select Query will display

Then just remove the 3 for the criteria Row

[mike60smart]

Here is the Form with the 3 removed

[frosttr]

Ah, gotcha. Actually, this has no significance in the calculated form and is not shown there in any way. It is also not showing in the reports for the calculated. There are 2 separate forms/reports which print out results based on data from different aspects of the table. So, while I understand (I think) of what you're saying, the last row of the table with associated fields are not calculated with the forms usage calculation.

[Gicu]

Sorry but can you please tell us what the result should be for this line?

Most of the components are 0, what would you expect to see in the yellow area?
Cheers,

[davegri]

See attached. It will handle either blank or zero as the multipliers. It converts blanks to zero, then omits zero fields from the calculation.
You might want to have the total as zero when the quantity is blank or zero? I didn't address that.

WATTS = VOLTS * AMPS
Also, you calculations are a bit too simple to cover all situations.
For example, WATTS is not included in the calculations at all. If VOLTS or AMPS are missing, then WATTS should have a value and be in the equation, eliminating both VOLTS and AMPS.
If all three are present, Then WATTS should closely equal AMPS * VOLTS. If it doesn't, there is a data input integrity problem.
Also, if all three are present, either WATTS should be included or VOLTS * AMPS included, but not all three. Including all three would really skew the total as WATTS would be squared.

If WATTS is not present, and either VOLTS or AMPS is also not present, the total cannot be calculated as meaningful.

I didn't address those issues in the code either.

frost-davegri-v01.zip

[accesstos]

Can't read the data of table Client, so I didn't test it but you can give a try to that way:

=QTY * ((VOLTS * AMPS * HRS * IIf(Nz(UF, 0) = 0, 1, UF) * IIf(Nz(DF, 0) = 0, 1, DF)) * IIf(PH = 3, 1.73, 1))

Volts, Amps and Hrs can't be zero or Null.
I suppose that UF and DF are factors with values between 0% and 100%, isn't it?

[frosttr]

For this specific line entry, the total should be 136,080. The calculation should be anything that is a PH of 3 is WATTHRS=QTY*Volts*AMPS*HRS*UF*DF*1.73. If PH=1 (or in rare cases 2), then the equation would be the same, except for the *1.73 at the end. Rooms are not calculated, so those entries would show blank, or zero. I actually went back and removed the default of "0" from any field that does not have a value, but that still did not help the equation, even using the nz. I had thought about going back and any of the calculated fields that have a null value, entering a 1, so that I could get results...but that would entail me doing script coding so that when the report was printed that 1 would not show. The finalized report goes to the state, and they specify "blank" for any field that does not have a value. I'm not good at scripting, so avoiding that as a last option.
Thanks for any input/suggestions you may have!

[frosttr]

Watts cannot equal volts*amps. For instance, if I have a 1000 watt microwave, volts and amps are not taken into account in the calculation as it would be a phase 1 product, whereas a 4' flourscent tube would be phase 3 product, and watts*volts*amps*hrs*uf*df*tf*1.73 would enter into the calculation. I know that sounds complex, but it's the way the state specifies calculations be done.

[frosttr]

You are correct, in the UF, DF and TF are percentage between 0 and 100%. As far as the other fields being null/zero, that is my problem in getting this equation to work. I'm thinking that perhaps entering a default value of 1, then somehow going back once the report is pulled and hiding or eliminating the 1 so that it doesn't show. This report goes to the state, and it can't show any value other than actual, which they consider zero & 1 to not be a valid value.