DSum?

[Sergio Moreira]

Hi Guys,
Look, I have a table to record supplier invoices for vehicle maintenance services.

The fields at this table are:

1. [Vehicle_License_Plate] - text - this is to input the vehicle ID, or registration
2. [Supplier_ID] - text - this is to input the supplier ID, or supplier code
3. [Invoice] - text - this is to input the supplier invoice number
4. [Invoice_Date] - date/time - this is to input the invoice date
5. [Service] - text - this is to input the type of service, from a pre-determined list
6. [Amount] - number - this is to input the invoice amount
7. [Accepted] - yes/no - this is to input whether the invoice has been accepted by our services or not
8. [Accounting] - date/time - this is to input the date when invoice has been booked in the accounting system

Furthermore, at my main vehicle table (the one where all the information respective to a vehicle is recorded), I have some fields where I need to have the total amount spent per service type (one field for each type of service).

In my intention to have an update query calculating the values for these fields, based on the supplier invoices table, I'm having a problem...

At the update query, I'm saying that I want to update i. e. field [Cost_Transportation], on my main vehicle table to the result of the following formula:

DSum("[Amount]";"DB_Supplier_Invoices";"[Service]='TRA'")

Problem is that, instead of having the total "Transportation" amount for the each vehicle well calculated and updated at the main vehicle database field, I'm getting the overall total transportation amount from the supplier invoices table, and this total amount is then updated on all [Cost_Transportation] fields at my main vehicle table.

Istead, what I would need was a formula that could calculate, from the "DB_Supplier_Invoices" table, the total [Amount] per service AND per vehicle, and then update the fields at the main vehicle table accordingly.

Any help will be highly appreciated!!
Should you require further clarification, please advise.
Many thanks and best regards.

 

[Sergio Moreira]

Hi Guys,
I'm sorry for being a "pain", but can anyone give some hints on this problem I'm having?
Many thanks to all of you anyway.
Kindest regards.
Sérgio

 

[stopher]

DSum("[Amount]";"DB_Supplier_Invoices";"[Service]='TRA'")

If you want DSUM to return the total for a specific vehicle then the format will be:

DSum("[Amount]";"DB_Supplier_Invoices";"[Service]='TRA' AND [Vehicle_License_Plate]='required license plate'")

This is fine if you only want to update one record. However, if you are updating all you records then using DSUM will be inefficient. Instead, create an aggregate query based on your invoices table that sums the total invoices by Vehicle_LP, by Service. Then join this query with your update query using Vehicle_LP as the join key and Service=TRA.

Having said all that, what you are effectively doing is storing a calculated field. This is considered bad practice in relational database design for lots of reasons. Instead, you simply calculate the value as and when you need it.

hth
Chris

 

[Sergio Moreira]

Chris,
Many thanks for your time and support! ;-)
I understand what you say about storing calculated values, so, I tried to make this an auto-calculated field in my main form.
Basically, I open the properties control on the form field [Cost_Transportation] and placed the following expression on the Control Source:

DSum("[Amount]";"DB_Supplier_Invoices";"[Service]='TRA' AND [Vehicle_License_Plate]=Form![Vehicle_License_Plate]") - so that I coud have the correct calcs for each vehicle record displayed in this form.

However, I get a blank/null result in the field when seen in the form...

Is there something wrong in the expression? I'm sure there is... Maybe in the part of the expression where I try to put a second criteria where the vehicle to DSum at the DB_Supplier_Invoices table must be same as the one currently displayed in the form, aka current record...

I should be getting back to my very simple abacus, as this is just to complex for me... sorry and thanks

Best regards.
Sérgio

 

[stopher]

DSum("[Amount]";"DB_Supplier_Invoices";"[Service]='TRA' AND [Vehicle_License_Plate]=Form![Vehicle_License_Plate]") - so that I coud have the correct calcs for each vehicle record displayed in this form.

I assume you are displaying continuous forms i.e. multiple records. So you should really do this using a query as I explained earlier.

Nevertheless, you can use DSUM and the expression should be something like:

DSum("[Amount]";"DB_Supplier_Invoices";"[Service]='TRA' AND [Vehicle_License_Plate]='" & [Vehicle_License_Plate] & "'")

[Vehicle_License_Plate] should be the field in your datasource (not the name of the textbox you are using to display it)

Note the use on '" (single then double quote) and "'" (double,single,double quotes). This basically creates single quotes round your variable field.

hth
Chris