Report Gross Formula

[nightmaregenerator]

Hi all,

I have basically created a report for a truck company that outlines each driver’s “Weekly Load Outline” that summarizes through a query, all the below details:
Work Date
Truck Number
Single/Double
Destination - To - Return
Km Rate
Kms
Hourly Rate
Hours Worked
Gross and
Allowance

So what I tried to do was use a formula like this:

=Sum([kms]*[Charge])+Sum([HourlyRate]*[HoursWorked])

To work out the gross for each load, this does not work though as I learnt today.
I know that the tables are not like excel where they can hold your formals, but does anyone know, how to work out the gross for the load and allow me in the report to show the total gross as well?

Any help would be appreciated.

 

[Galaxiom]

Create a query with [Truck Number] and fields calculated from ([kms]*[Charge]) and ([HourlyRate]*[HoursWorked]).

Turn on Totals in the query designer.
Set the Totals of [Truck Number] to Group By and the others to Sum.

This will return a list of trucks and the total of the two derived fields by Truck Number.

 

[nightmaregenerator]

Sorry with query do I include the kms, charge, hourly rate and hours worked or should I have the totals of the formulas in separate fields? For the minute the Gross is an unbound text box in only the report.

 

[Galaxiom]

With Totals (AKA Aggregate) queries you can only include fields you want to process. Leave out the other fields or Access will complain or Group By all the other fields too. So if you want a report with daily totals then also inlude the Date as a Group By field.

If you need to do an aggregation and tie it back to other fields then perform the aggregation separately including the PK of the table and then use a Select query to add the other fields again.

Sometimes the best solution is to use a main form for the basic data and place the aggregate data on a subform with the Master/Child links set to reconnect the data. This avoids the problem with aggregate queries not being updateable which would prevent you from changing the main information where the aggregate query was combined with the main data as a single record source.

 

[Galaxiom]

For the minute the Gross is an unbound text box in only the report.

Maybe you should be recording the Gross in the table? Whatever way you do it, avoid storing figures that are calculated from others. Normally one might store the Gross and the Rates and calculate the product on the fly.

An alternative to storing the rate on every record is to store a series of rates in another table against a RateTypeID and a starting date. The Key of applicable rate type is stored in the main records and the date read from the main record to determine which rate should be applied.

You can refer to an unbound control on a form or report from a query like this.

Forms!formname.controlname
Reports!Reportname.controlname

or in the case of subforms:
Forms!formname!subformcontrolname.Form!controlname

 

[nightmaregenerator]

I'll explain the way I have it,

Basically I have a destination table, this keeps “From”, “To”, “Return” and the “kms” traveled.

Then there is the Driver Details which just stores the first and last name.

A Lode size which holds the load size and the rate at which they get paid for that load

A table with the truck rego numbers

And the weekly payroll, which holds many relationships to the above tables.
So in that table there is:
First Name - ref. to driver details
Last Name
Work Date
Truck number - ref. to truck rego table
Single Double - ref to Load Size
Local - Checkbox
Destination - ref. to Destination
Change over - Checkbox
Kms - ref. to Destination
KmsRate - Charge
Hours Worked
Hourly rate - Set at a default
Allowance - Checkbox
Nights - In the report this had a formula of to times the number by 30

I have only started using access, normally I would do flat file or databases with minimal relationships. So moving straight into something like this is a little difficult.

 

[Galaxiom]

I would expect a Job table which had relationships to Driver, Truck and Destination tables.
It would also have fields for LeaveDateTime, ReturnDateTime and Load.

LoadSize table should have a LoadSizeID (PK), LoadSizeDescription.

LoadRate should have LoadSizeID (FK), StartDate, PayRate. It maintains a complete historical record of the PayRates.

The payroll query would be based on the Job table with joins to find the correct PayRate by comparing the dates and load with the ranges stored in the LoadRate table.

You would also have a table of hourly rates which appear to be also a function of LoadSize so would include LoadSizeID as a foreign key.

Unless the hours are calculated directly from the jobs I imagine you would also have a TimeSheet.

You have certainly jumped in the deep end. The main thing to get right at the moment is the table structure and normalization. Once yo are happy with it I suggest you post it here for a look.

 

[nightmaregenerator]

The job table from your description would be the Weekly Payroll table I have set up, while load descriptions are too hard to organize because each truck can be carrying 6 to 7 different company loads.

The same as Start Dates and Start and Finish Times, as drivers may only do a week’s work for us and move on, while only local drives have start and finish times, but we work out there pay based on how many hours they work.

I am including the basics to the database if that helps you understand the relationship structure (if it's any help) what is in the tables is all the real information we use to organize these sheets for our drivers.

Thanks for all the help.

 

[Galaxiom]

If payroll object is a table you would record the Driver only by their DriverID which should be a number (the PK from the Driver table). Currently it looks like you are using the driver's first name as key for the relationship? What if two drivers have the same first name?

Although the driver is only in the table as an ID their full name can still appear on the report by using a join between the Payroll and driver tables on the DriverID in the Record Source query of the report.

The other ways to make these values appear but that is the easiest. But you definitely should not record the driver's name anywhere but in the Driver table.

 

[nightmaregenerator]

On the Report that is how the drivers name appears, so I need to change the fk for the payroll to the pk in the drivers details table?

With the way it's all layed out is there a way to solve the original problem of gross earned for the trip being added up into a total gross for the report?

Or can i just use the refrence that was mentioned for unbound controls?

 

[Galaxiom]

On the Report that is how the drivers name appears, so I need to change the fk for the payroll to the pk in the drivers details table?

Yes. that sounds right.

With the way it's all layed out is there a way to solve the original problem of gross earned for the trip being added up into a total gross for the report?

Or can i just use the refrence that was mentioned for unbound controls?

I am a little unclear on the exact problem. Does the folowing help?

The Control Source of a textbox in the header/footer of a form/report can be an expression based on fields from the Record Source.

So you can Sum the records on fields like this
=Sum([myfield])+Sum([yourfield])

Remember this expression is not based on the controls displayed on the form but on the fields in the Record Source.

 

[nightmaregenerator]

Basically the problem is that, when I created the formular in the repoert what ever the top gross was all other loads had that number appear.

I basically need a formula that can calculate unbound text boxes; but from what I have found this is impossible.
The reason I need it to refer to unbound text boxes is becasue I use the refrence =[Destination].[Column](4) to refer to the fourth column in my drop down box which stores the kms as I have been unable to find another way for the form to auto fill these details. Which is how many other less important details are filled out.