Need SUMMARY VALUE of costs

[zkrucz]

I have a question regarding how to create a summary of costs TABLE by Job# of: LABOR and MATERIALS from a Table that has multiple entries for the same Job#. I need that Summary value to be able to compare to the Quoted amounts (in another Table).

What I am looking for is similar to the CrossTab summary but not a static value which would require running the CrossTab query every time to get an answer. And, I need it to be in a Table form so that I can create Forms using that value.

[June7]

Aggregate data should not be saved to table but calculated when needed. Forms can be built from CROSSTAB but data in CROSSTAB is not editable.

Why can't you use Sum() function on form? Can use DLookup to pull quoted value from other table.

Not really clear what you need.

[zkrucz]

@VIP
Thanks for the feedback. Let me explain why I have these issues.
We use iPads for our field staff to send in data. Unfortunately (for Access DB), the service provider uses horizontal style (spreadsheet) format. The incoming data is imported/append into a jobs Table which results in duplicate Job#s because any job may be completed in in pieces/phases. I want to be able to aggregate key costs (Labor, Materials, Inventory, Equipment) in order to figure out if the Quoted Job# resulted in profit or loss, using a Form.
I tried using the Sum() function in a Form an I get an error: (#Name?)
The Fields which I described above are formatted as Currency data type.
So, can you steer me in the right direction for me to get aggregate values in my Form. As an example, the Sum(J_Materials) aggregate should return a value of $519.00

[NoellaG]

Try a DSUM : = Dsum("Fieldname","Tablename", "Crit")
example
= DSum("J_Labor", "Table_Jobs", "[Job #* = """ & me.txtJobNr & """")
given the control name for Job # on your form is txtJobNr, if not state there the name of the control on your form that holds the critirium

[zkrucz]

@NoellaG

Thanks for your feedback, I modified your example to match the Materials example above:

=DSum("J_Materials","Table_J","[Job # = """ & [me].[txtJobNr] & """")

However, I still get the same error: #Name?

Did I mess up my statement (above)?

[kd2017]

=DSum("J_Materials","Table_J","[Job #] = """ & [me].[txtJobNr] & """")

Looks like NoellaG had a typo and you omitted the closing square bracket after [Job #

You'll need to make sure the field names and text box names in the dsum match what they actually are in your project.

[zkrucz]

@NoellaG

I tried anothe rapproach by creating a Summary query and trying to turn it into a table, but I am getting null values. When running the query to create the Table, I am being asked to populate all the cost fields. And even when I try to populate them with random values to see the imapct (say I entered "1" in all cases), the resulting Table sill has null values.

[Gicu]

Try "SELECT Table_J.[Job #], Sum(Table_J.J_Materials) AS JobMAterials, Sum(Table_J.J_Eguipment) AS Job_Equipment, Sum(Table_J.J_Labor) AS Job_LaborFROM Table_J
GROUP BY Table_J.[Job #];"

Please see attached.

Cheers,

[June7]

Can't use Me. in textbox expression. This is a VBA alias for object code is behind. Drop Me. and expression should work

I prefer apostrophe to doubled quotes.

=DSum("J_Labor", "Table_Jobs", "[Job #] = '" & [txtJobNr] & "'")

Advise not to use spaces nor punctuation/special characters in naming convention.

[zkrucz]

@Gicu VIP
Hey, thank you! I got the aggregate Query to work (see below). Now, how can I get this to create a Table for me so that I can use the Table fields for my form?

[zkrucz]

@June7 VIP

Hmm, I tried your expression and I still get the #Name? error....

[June7]

Then Access cannot find control named txtJobNr. What is actual name of control? Is it same as field name? Try the field name.

=DSum("J_Labor", "Table_Jobs", "[Job #] = '" & [Job #] & "'")

There is no need to create a table. Use query like a table, except cannot edit data in an aggregate query.

[Gicu]

Sorry, you tried what expression where? You don't need to create another table to use the totals query in a form, just set it as the form's recordsource then in the form use the query's fields as the control source for the text boxes.

Cheers,
Vlad

[zkrucz]

@Gicu VIP

I guess I don't know how to access Queries for my Forms (design), then. When I open FORMS/Design i can only access existing Tables, there are no existing queries. What am i missing here?

[Gicu]

You have a queries tab on the Show Tables pop up window, slick on that and you will see it.