Counting not null values in 3 columns, storing each row count in a 4th column. (1 Viewer)

[Netassess]

Hi, I have a datasheet I use for checking weights of various trucks once loaded. I have a Date, Registration Number, 1st Weigh, 2nd Weigh, 3rd Weigh Column. I need to average the weigh data of each row into a 4th column. Sometimes there is only 1 or 2 weighs conducted so i can't simply Average the 3 Weigh columns by totalling the rows and dividing by 3. Is there a way I can count how many "is not nulls" there are in each row, and store those values in a 4th column?

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[Netassess]

Thanks Jon, have read the information on the link above.

 

[CJ_London]

If you normalised your data, the problem would be trivial to solve. Databases are not designed to work like excel.

you could try using the isnull function combined with the abs function

 

[Netassess]

Thanks for your reply. I understand that Access is not Excel. Can you give me a tip on where I haven't normalised the data? Thanks.

 

[CJ_London]

Your weigh columns should be in a separate table - one record per weigh, so a new record added on each weigh.

then you can use the average aggregate to get the value you want without the need for calculating an additional value or the calculation itself

 

[Netassess]

Thanks for that. So, with only the Weigh columns in a seperate table? I need to also record the Date of the Weighs and the Registration Number of each truck being weighed. Please forgive my ignorance on this...

 

[CJ_London]

you haven't explained what the app is required to do, the fields you have or (in this case), the frequency of weighing's, but looks like the answer for the date is yes, perhaps with a time element as well if a truck can be weighed more than once in a day, but you should have a PK (Primary Key) for the truck table and that is what you store in the weigh table as a FK (Foreign Key).

Your truck registration number may be sufficient as a unique identifier (it is called a natural key) but will be less efficient with a large set of data and requires more work to manage (since it can be changed) so better to use an autonumber PK with the registration as separate field that is indexed, no duplicates.

Your weigh table should also have its own PK as well as the FK (indexed, duplicates OK) back to the trucks table. There may be other fields as well such as driver details.

Recommend you research 'database normalisation'

 

[Netassess]

Thanks so much. The app is to record up to 3 random weighs out of 15-20 loads each day for each of 12 trucks. The objective is to ascertain the average load weight for all 12 trucks over a period of approx. 6 months. This average is then used to calculate approx. how much product has been transported by multiplying the number of loads multiplied by the avearage weight. I do have the truck details set up with rego # as the PK, and a PK (Autonumber) for each row in the weigh table. In the weign table I put in the date, rego, and the 3 weighs in each row. The rego is via a combo box using the truck details table. Im starting to see my mistake, big thanks for that!!

 

[Pat Hartman]

The weight table needs a composite PK or an autonumber PK with a composite unique index..

fld1 = TruckID
fld2 = Date()
fld3 = LoadNum

Then a single weight for each row. In a relational database, tables are tall and thin. Spreadsheets are short and wide.

In a relational database, there are NO functions that work "across" columns in a single row (unless you choose to write them). All functions work on a set of rows.

 

[Netassess]

Thanks heaps, I am re-doing the structure to suit. Really appreciate your advice..