Count Fields and Perform Calculation

[hotrodsue]

In my query there are 9 fields that record weight of truck loads, Load1, Load2,...through Load9. Some fields may have no entry. I need to count ONLY the number of loads that have entries.

Once loads are counted I then need to perform the following calculation:

Gross Weight - (Tare Weight x Number of Loads) = Net Weight

Example:

Gross Weight of 256,080 - (Tare Weight of 23,680 x 6 Loads) = Net Weight of 114,000

Gross Weight and Tare Weight field values are in the query. Once I determine how to count the number of loads, I believe I can get the above calucation to work.

Any suggesetions would be so appreciated.

 

[namliam]

This is not designed properly... Loads shouldnt be distrubuted across columns, but rather in records. In that case making the calculation is easy....

Using IIF though you could do something like:
IIF (Isnull(Load1),0,1) + IIF (Isnull(Load2),0,1) + etc

to count the loads, but seriously what is going to happen if somehow some way someone makes 10 loads? You really need to rethink this design!

 

[Scooterbug]

I have a sneaking suspicion that the load 1-9 are all stored in the same table instead of having their own table. If that is the case, you might want to rethink your table design.

If they are in a separate table, a simple count can solve the issue. On the query builder, there is a symbol on the top that looks like a giant E. Clicking on that will allow you to count records. Just select the field you want counted (Load), set the criteria to >0, and in the Total field, select Count.

If they are in the same table, it gets a bit trickier in my opinion. I would use code to do a dcount. You would have to use the dcount on each load field, then total them all up.

 

[hotrodsue]

This is my second database. My first database was completed with a lot of study and video tutorial, along with excellent tips on this forum.

You both have made a good observation. I do have all nine loads in one table. The customer brought his Excel spreadsheet for me to review and create a database.

So, if I create only one field titled "Load" then I would be able to count the total loads and not worry about having blank "Load" fields or not enought "Load" fields.

I'm going back to the drawing board, wish me luck. Thanks so much for your input.

 

[namliam]

Your table should look something like:
CustomerID, LoadDate, Weight

Then per customer (or truck or whatever is your main record) you can count easy per date how many load there were.
There is no ABSOLUTE need to change this... However if you have an excel sheet why not calculate this in excel?? Probably because you need to do "bigger and badder" things to the data than you can/want to do in Excel. If that is the case, then keeping it in Excel format is not a smart thing to do and redesign is required/advisable.

 

[hotrodsue]

There is so much data with a variety of calcuations, so we need to go to a database. At first I was concerned about changing the table, but, I can see the need to get this designed properly.

The database is for a dairy that has various fields for growing feed. The data is input to determine the amount of feed harvested. For one field (of corn) there are about 10 trucks harvesting in one day. In re-working my table, I believe the following will work:

HarvestID (main record), HarvestDate, FieldID (which field is being harvested), VehicleID (which truck), Load(Weight), and ForageID (type of grain).

I'm going to get back to it, and will check back in for other posts or suggestions.

 

[namliam]

Re: Design

Sounds to me like a Harvest can have multiple fields harvested at one time??
And one field can have many trucks
Each truck may have multiple loads...
Not this last one I am not sure about Or if this level is needed or not.

Which to me means you want a tblHarvest, to contain your harvest info
Then a tblField to hold the field info
Then depending on the detial you need... either just make a tblTruckLoad
or make a tblTruck and a tblLoad

I hope this gets you started on the road... *badam bam*

 

[hotrodsue]

Re: Design

Sounds to me like a Harvest can have multiple fields harvested at one time??
And one field can have many trucks
Each truck may have multiple loads...
Not this last one I am not sure about Or if this level is needed or not.

Yes, on all accounts above. Yesterday I was a little overwhelmed trying to redesign. Since my first database, 6 months has passed. I need to be in Access regularly to keep it fresh.

Which to me means you want a tblHarvest, to contain your harvest info
Then a tblField to hold the field info
Then depending on the detial you need... either just make a tblTruckLoad
or make a tblTruck and a tblLoad

In my table re-design for one "Load" per record, rather than Load1, Load2, etc., I studied forum posts, tutorial, Google. Once I re-designed the table, I worked hard to figure query calculations. I got confused because there’s more than one way to do most things. Eventually, I went back to my first table and tried the following calculation you gave me and it worked.

Using IIF though you could do something like:
IIF (Isnull(Load1),0,1) + IIF (Isnull(Load2),0,1) + etc

However, I see the logic and importance of proper design. That loads shouldn’t be distributed across columns. Truck typically can do no more than 6 loads in a day, so 9 columns should cover a rare instance of an extra load. Still, I know this is not the most efficient design.

Tables I have:

HarvestID
HarvestDate
FieldID
VehicleID
ForageID
Load1 (Thru Load9)
Tare1 (Thru Tare3)

FieldID
Field (Identifies the Field Harvested)

VehicleID
Vehicle (Identifies the Truck Used)

ForageID
Forage (Identifies the type of grain harvested, corn, soybean)

Load1 thru Load9 (Weight of each Load)

Tare1 thru Tare3 (Weight of Empty Truck)

Thank you very much for your input. What I have is working, but I'm concerned it isn't properly designed. I’m willing to keep trying. If I go back to table re-design again, I can then post a more specific question about where I got stuck on query calculations. What do you think?

 

[namliam]

Why have Tare1 thru 3 but have load 1 thru 9?? Shouldnt there be a Tare for each load?
Or is the Tare averaged? or what?

And wont a Vehicle have a "default" Tare?? Or does it change becuase of different vehicle configurations that need to be made depending on the cargo?

As long as your solution works that is the most important thing... Just know its limitations and know the problems you may/will run into. You will be good to go.

 

[hotrodsue]

You're right, Tare weight does change because of different vehicle confirgurations and it IS averaged for the 3 most recent.

Thanks so much. Since, the solution worked, I'll go forward with what I have. I'm also encouraged that I can keep working, learning more. Thanks to great folks like you at the forum.

I'm good to go. Again, many thanks.