Storing Calculations

[Neekos]

Ok so i know you shouldn't store calculations in a table, but what if i need to? i have calculations on one form, and i want to be able to show the value of that calculation on a different form based off a totally different table.

how would i go about doing that?

[NeoPa]

Base the second form on a query that includes input from the tableS you need.
I can't help any more for the moment I'm afraid, as your question is almost completely devoid of information I could use to help you :(

[nico5038]

Another option is to use a Dlookup() function, that will allow a calculation like:

dlookup("field1 * field2","your table","ID = " & [ID])
 

Nic;o)

[Neekos]

The calculation i need to look up is a long one. Would this still work in the DLookup?

=DLookup("Sum(([Grats]+[Insurance]+[Cruise Rate]+[Port Charges]+[Taxes]+[Air]+[Other])+([Grats2]+[Insurance2]+[Cruise Rate2]+[Port Charges2]+[Taxes2]+[Air2]+[Other2])+([Grats3]+[Insurance3]+[Cruise Rate3]+[Port Charges3]+[Taxes3]+[Air3]+[Other3])+([Grats4]+[Insurance4]+[Cruise Rate4]+[Port Charges4]+[Taxes4]+[Air4]+[Other4])+([Grats5]+[Insurance5]+[Cruise Rate5]+[Port Charges5]+[Taxes5]+[Air5]+[Other5]))", "tblManifest", "GroupNumber = '" & Me![GroupNumber] & "'")

i tried using it on the OnOpen event of the form like so:

Forms![frmGroupPayments]![GrossAmt] = DLookup(....same as above....)

but get an object required error.

[NeoPa]

As a full member now, you should know that we expect your code to be posted in [code] tags (See How to Ask a Question).
This makes it easier for our Experts to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.
Please use the tags in future.

ADMIN.

[NeoPa]

The calculation i need to look up is a long one. Would this still work in the DLookup?

=DLookup("Sum(([Grats]+[Insurance]+[Cruise Rate]+[Port Charges]+[Taxes]+[Air]+[Other])+([Grats2]+[Insurance2]+[Cruise Rate2]+[Port Charges2]+[Taxes2]+[Air2]+[Other2])+([Grats3]+[Insurance3]+[Cruise Rate3]+[Port Charges3]+[Taxes3]+[Air3]+[Other3])+([Grats4]+[Insurance4]+[Cruise Rate4]+[Port Charges4]+[Taxes4]+[Air4]+[Other4])+([Grats5]+[Insurance5]+[Cruise Rate5]+[Port Charges5]+[Taxes5]+[Air5]+[Other5]))", "tblManifest", "GroupNumber = '" & Me![GroupNumber] & "'")

i tried using it on the OnOpen event of the form like so:

Forms![frmGroupPayments]![GrossAmt] = DLookup(....same as above....)

but get an object required error.

That should work if there are no mistakes in the code (We can't tell as you're still posting the minimum you can get away with information-wise). This would be easier to do and more straightforward with a query though (as well as better optimised but that won't be a major issue I wouldn't think). If you don't share very much info we have to work hard to guess what you're about. My best guess is that the query would suit you better BUT the DLookup should work if it's done properly. If you post the code you've used and show which line the error occurrs on (the DLookup() in this case I expect) then we may be able to be more help.

[nico5038]

The calculation i need to look up is a long one. Would this still work in the DLookup?

=DLookup("Sum(([Grats]+[Insurance]+[Cruise Rate]+[Port Charges]+[Taxes]+[Air]+[Other])+([Grats2]+[Insurance2]+[Cruise Rate2]+[Port Charges2]+[Taxes2]+[Air2]+[Other2])+([Grats3]+[Insurance3]+[Cruise Rate3]+[Port Charges3]+[Taxes3]+[Air3]+[Other3])+([Grats4]+[Insurance4]+[Cruise Rate4]+[Port Charges4]+[Taxes4]+[Air4]+[Other4])+([Grats5]+[Insurance5]+[Cruise Rate5]+[Port Charges5]+[Taxes5]+[Air5]+[Other5]))", "tblManifest", "GroupNumber = '" & Me![GroupNumber] & "'")

i tried using it on the OnOpen event of the form like so:

Forms![frmGroupPayments]![GrossAmt] = DLookup(....same as above....)

but get an object required error.

Best to put into the Controlsource of [GrossAmt]:

=Dlookup("...","...","...")
 

I also suspect the SUM() not to work as it requires a groupby function.
When the summing is needed for all rows to one GroupNumber, then first define a GroupBy query to perform the summing per field and use only the "+" for summing these fields like:

([Grats]+[Insurance]+[Cruise Rate]+...
 

Getting the idea ?

Nic;o)

[Neekos]

i appologize for not using the code tags, i simply forgot.

As for the DLookup, you are right about the Sum function not working. So instead a built a query. When i run the query by itself, it is returning the correct value for the Sum of all of the values. However, when i add the query to the control source (i only added the field which has the totals) and then open my form - it is displaying a completly different value, and is also changing other calculations on the form that have nothing to do with the query.

What info would you need so that i can help you help me?

[Neekos]

Also i forgot to mention that when i added the query to the control source, it is adding the same record multiple times on my form - which is the reason why its changing the value of the calculations.

[Neekos]

My control source looks like this:

SELECT tblGroupPayments.*, qryGrossAmt.Expr1 FROM tblGroupPayments, qryGrossAmt; 

[nico5038]

Just keep the controlsource of the form as you had.

The query needs to be used in the DLOOKUP() function as the second parameter like:

=Dlookup("a+b+c....","qryGroupBy","...")
 

Nic;o)

[FishVal]

Hi, Neekos.

[tblManifest] by no means looks like a normalized table. I hope you quite aware of the fact that its the first (and maybe the last) reason of you present difficulties and future problems.

Plz, post [tblManifest] metadata. Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Also take a look on Database Normalisation and Table structures tutorial.

Regards,
Fish

[Neekos]

Just keep the controlsource of the form as you had.

The query needs to be used in the DLOOKUP() function as the second parameter like:

=Dlookup("a+b+c....","qryGroupBy","...")
 

Nic;o)

Ok i put the form's control source back to the orginal table.

And i put the control source of the txtbox to this:

= DLookup("Expr1", "qryGrossAmt", "GroupNumber = '" & Me!GroupNumber & "'")

but i get a #Name error.


Also, i realize my table isnt very normalized at all, i'm still very new to Access and this was the only way i could get it to display the info as i needed. You see, the purpose of my db is to display a manifest for a Group cruise booking. Every group will have multiple cabins, and each cabin can have up to 5 passengers. The only way i could figure out how to display the info as needed was to make each cabin its own record, and have a field for each passenger - including all of the charges associated with each passenger. There may have been an easier way of doing it, but im too far along to have to start over.

[Neekos]

ok i figured out that it doesnt like the Me!GroupNumber, because if i change that to the actual group number, it works.

what can i change it to so that it looks up the value associated with the current group number?

[nico5038]

Best to start reading the link FishVal provided you with.
Having the wrong table structure causes trouble like this and makes the database much harder to expand in the future.

I spend in general as much time (or even more) on the table design as on the creation of the forms and reports.

The solution for your problem is to change in the WHERE section of the DLOOKUP function the Me!GroupNumber into [GroupNumber].

Nic;o)

[Neekos]

That worked, thank you for all your help.

And i will read up on the table structure so that i dont run into further problems down the line! Thank you both!

[nico5038]

Glad we could help, and best to start changing the design of your tables now :-)

When you define the requirements, be sure we can help you.

Nic;o)

[NeoPa]

Neekos.

I'm sure you're new to this so it's understandable that you think that way, but you're making your own problems here.
Also, as you move away from the standard way of doing things you move away from where we can help you.
Most of the Experts here understand WHY normalised databases are better. In simple terms, it's because it works better and it makes it easier for you, the designer, to understand what's going on.
Clearly this is your choice, but when you come to look for who's responsible for it all getting horribly complicated you know where to start right?
Moving on.
DLookup() is only one of a whole group of Domain Aggregate functions available to you. Personally I would not use this method in your situation, but you can use DSum() to get the sum of the group of records that you're interested in. If you're only adding different fields from a single record then the + operator is sufficient.

[NeoPa]

It seems you guys were posting faster than I could keep up :D
If you're happy, then I'm happy.