Calculating weight & showing this in stones & pounds

[Terri H]

Hi, I need to calculate CUMULATIVE weight loss over a number of weeks, and show the result in stones and pounds (that is "st" and "lbs" added as text).



Here's a scenario:
In Week 1, the starting weight is 13 st 6 lbs
In Week 2, the new weight is 13 st 2 lbs, so the loss = 4 lbs
In Week 3, the new weight is 12st 12 lbs, so the cumulative loss, from Week 1 to Week 3 = 8 lbs
In Week 4, the new weight is 12st 5 lbs, so the cumulative loss, from Week 1 to Week 4 = 15 lbs... or 1st 1 lb

Whilst I can manage the basic maths bit, I just can't seem to find a way to include concatenation as well, so that the cumulative total for Week 4 shows as a loss of "1 st 1 lb".

I am more proficient in Excel and, there, I am using this formula: =CONCATENATE(ROUNDDOWN(A1/14,0)," st ",MOD(A1,14)," lbs") where A1 is the weekly weight loss in pounds.

Can anyone help?
Many thanks
Terri

[Bob Fitz]

Can you tell us about what tables and the fields they have. Perhaps even post a copy of the db.

[Terri H]

Thank you, Bob. I've only just started writing the database, so it's a bit of a testbed mess at the moment. I can take an extract as an example, then post that here, if that would help.

[Bob Fitz]

Thank you, Bob. I've only just started writing the database, so it's a bit of a testbed mess at the moment. I can take an extract as an example, then post that here, if that would help.

Come Terri. Don't be shy. Show us what you've got.

[Terri H]

Thank you, Bob. I've only just started writing the database, so it's a bit of a testbed mess at the moment. I can take an extract as an example, then post that here, if that would help.

[Terri H]

I'm trying to upload a test db, but I'm getting a message that it's too large to post? Max file size 500 kb? It's less than 800 kb. How to I attach it?

[Terri H]

Thanks Bob, I've zipped it. TH_WeightTrackerTest1.zip

[Bob Fitz]

I'm trying to upload a test db, but I'm getting a message that it's too large to post? Max file size 500 kb? It's less than 800 kb. How to I attach it?

Do a "Compact and Repair" then Zip the db and attach the zipped file.

[Bob Fitz]

Thanks Bob, I've zipped it. TH_WeightTrackerTest1.zip

Terri
I've had the briefest of looks at your db. Access is not "another kind" of spreadsheet or vice versa.
Before you go any further with your project I would recommend that you normalise your tables and read a little on database design.
I'm sure that there will be countless posts here on the subject but please feel free to post back if you need further help.

[Welshgasman]

Too new for me to look at form wise, but I would have thought you would need to convert to lbs and recalc.
Simple enough as

intStone & " Stone " & intLbs & "lbs"
You are doing no more than you did in Excel

[Welshgasman]

Terri
I've had the briefest of looks at your db. Access is not "another kind" of spreadsheet or vice versa.
Before you go any further with your project I would recommend that you normalise your tables and read a little on database design.
I'm sure that there will be countless posts here on the subject but please feel free to post back if you need further help.

Sounds like you may as well just leave it in Excel?, better graphing etc? and you are familiar with it?

[CJ_London]

agree with others, your table design is not appropriate for a database

would expect something like

tblWeighings
PK....wDate....Weight

where weight is in lbs.

the equivalent of your excel calculation

would be

15\14 & " St " & 15 mod 14 & " lbs"

which will return '1 St 1 lbs'


rather than trying to add all the individual losses, just deduct the first week from the current week

[ssanfu]

@Terri
I agree with the others about the db design, so I started making some changes and all of a sudden several hours went by.

I don't know if you want more than 4 weeks and I don't know if the dB is for many more people, so I added a person table, changed the Weight Table to be able to add a million more weight readings and changed the form to be a main form/sub form arraignment. I had to write a couple of UDFs to do the calcs (and the numbers even match yours!).

On the form if you see controls with a yellow backcolor (in design view).... that is my way of having PK/FK controls/fields on the form.

Maybe this will help you a little....


And Welcome to the forum....

[Terri H]

Thank you, Steve. This has certainly helped to unmuddle my thinking! I'm new to Access, just self-learning really, and enjoying exploring a new approach. Your suggestions are very helpful and they've enabled me to step away from the more linear process I've been used to with Excel. Thanks for taking the time and trouble.

[ssanfu]

Happy to help.

I know it is hard to get out of an Excel brain and into a RDBMS brain.

15 - 20 years ago I was using/coding Excel and a little scripting in FileMaker Pro (mac ); now I've been using Access for so long that I struggle when I have to code in Excel.



Good luck with your project....