Using form calculations elsewere

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am making a strange database heres a quick summary

There is a parts list linked to a list of who we get them from
There is a module list which has a form for creating them linked to a
subform of components showing which components and adding up totals * buy
markup facrots and yield rates

now i need to make an equipment set which is like the module one but should
which modules it uses and totals them up

The problem in having is how do i get the Total for each module i
calculated. how can i get that total from my module form and put it into an
equipment query? can i take it from each module's form and somehow reference
it?

please Im totaly stumped, i can provide more info if needed
 
I am making a strange database heres a quick summary

There is a parts list linked to a list of who we get them from
There is a module list which has a form for creating them linked to a
subform of components showing which components and adding up totals * buy
markup facrots and yield rates

now i need to make an equipment set which is like the module one but should
which modules it uses and totals them up

The problem in having is how do i get the Total for each module i
calculated. how can i get that total from my module form and put it into an
equipment query? can i take it from each module's form and somehow reference
it?

please Im totaly stumped, i can provide more info if needed
Click to expand...

Just do the total in the Query *instead* of on the Form.

You can create a Totals query by clicking the Greek Sigma icon (looks
like a sideways M) on the query design window. Any total that you can
calculate in a Form can be calculated in a Query; and you can use that
query wherever you wish.

John W. Vinson[MVP]
 
ok ill try that, but the total on each part sub form is the total of all the
parts it use's (like making an order for certian components) its set up like
the demo database's order form how do i get each module (order's) sum of
components out of the form?
 
ok ill try that, but the total on each part sub form is the total of all the
parts it use's (like making an order for certian components) its set up like
the demo database's order form how do i get each module (order's) sum of
components out of the form?
Click to expand...

You can answer that much better than I, since you can see your
database and I cannot. You would use a Query on the table being
displayed on the subform, using criteria which select just the records
for that order.

John W. Vinson[MVP]
 
John Vinson said:
You can answer that much better than I, since you can see your
database and I cannot. You would use a Query on the table being
displayed on the subform, using criteria which select just the records
for that order.

John W. Vinson[MVP]
Click to expand...

Ok, so i made a Query based off my ModuleQuery (this query is the sub form
of the module form, like an order it adds part's to that "orders' list of
components)

This new query totals up the Fianl total of all of the individual
components for each order, giving me a grand total, it then dose the
calculations i had on my form

Now the problem is i create a new Query based off my Equipment and Modules
tables, this is basicly the same as my ModuleQuery, the idea is that it will
be the sub form of a peice of Equipment and you can add modules (which is
like an order composed of all my previous orders) this works fine i can now
select my previos modules and add them to each peice of equipment

But i need the Final total of each module to be in this new query, if i try
to add the Final totals query to its makeup i lose the ability to select
modules and can only see the ones already added, is there a way to link that
field into my new query withough actualy adding it into this new query?
 
What would make this easyer was if i could somehow record the final totals
into the module table, is it posible to create an equasion in a table? or
make a table value that uses a querys value?
 
What would make this easyer was if i could somehow record the final totals
into the module table, is it posible to create an equasion in a table? or
make a table value that uses a querys value?
Click to expand...

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson[MVP]
 
Thanks for all the info so far, what I just need to know is how I get a
value from one query to another without actually adding the Query to the list
of shown tables/querys it uses

I think DLookup is what im after but im not to sure on how to use it

I has this DLookup(“[Totalfield]â€,â€[Queryname]â€,) that gets me a total but
not the correct one

Basicly I have two querys

Queryname, with a CODE field and at Total Field, this query is based of
another query and shows the SUM of the Total Field for the combined CODE
reference’s (the query its based off has muitaple CODE’s

And the one im trying to get the “Total Field†into has a the CODE field
aswell lets call this one EquipQuery, what I want to do is have the Dlookup
get the correct Final Total for the CODE I have

I thought it was like this DLookup(“[Totalfield]â€,â€[Queryname]â€,â€CODE =†&
CODE) but that dosent seem to work? How do I make it so that the CODE looks
for the form like

DLookup(“[Totalfield]â€,â€[Queryname]â€,â€CODE.Queryname =†& CODE.EquipQuery)

I couldent get that to work either, can you plese tell me what im doing wrong
 
Also the CODE field is TEXT it is 2 letter snd 4 numbers but is shown as text

EG.. DS1002, it this the reason im having a problem?
 
Also the CODE field is TEXT it is 2 letter snd 4 numbers but is shown as text

EG.. DS1002, it this the reason im having a problem?
Click to expand...

Yes: a text field requires quotemarks around the criterion. Try

DLookup(“[Totalfield]”,”[Queryname]”,”Queryname.CODE ='” &
EquipQuery.CODE & "'")

This will evaluate to

queryname.CODE = 'DS1002'

and work correctly.

Note that you had swapped the query name and the field name - it's
query name first, *then* the field name.

John W. Vinson [MVP]
 
Back
Top