Data with New Units - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Data with New Units

Tim Gilbert

5 years ago

Hello

TL;DR - how do I incorporate data with a new unit system into an existing database?

I have a database that tracks various sporting activities. Since many of the metrics for each activity are different and some have "split data", each activity is is "housed" in separate table(s). One metric that is common to several activities is "distance", as miles. Until recently this seemed to be of little concern - now I am getting data reported in "km" and in "m".

I know making a conversion table would be easy (similar to the currency conversion Richard did recently). My question is what are some good options for incorporating the new data? Should I add a field to indicate units? What suggestions are there for summing the data (or other operations) and reporting it back to users in a unit that makes sense to them? There is no hope for me to convince anyone to just use miles, I am compelled to juggle the various units (not complaining, this is another way for me learn).

Thank you - all the best to all in 2022

	Adam Schwanz @Reply 5 years ago
I would just make another field to track what the number is in, miles, km, etc. You can make a table storing these values if you want to. Using a Relational Combo. Then use an update query to set the value to mileage for all of the fields you've been using so far. Then just have the user enter the value and select the measurement unit when entering it.

	Adam Schwanz @Reply 5 years ago
You could do a conversion table too either separately or in conjunction with the setup I said above.

Tim Gilbert OP @Reply

5 years ago

Thank you. Fortunately or unfortunately, the only MS Access user is me. I get the data in CSV format, then import it into Access where I have Queries set up to get it to the appropriate tables. I'm thinking of adding a field for which unit is being used and modifying the queries to add that along with the data. I'm wondering about good ways to report the calculations back with a unit that is most familiar to the user - would it make sense (in a report or form) for distance to be a calculated value based on the unit selected? If so, the conversion would vary with the units used for each record and the units desired by the user - a bit complicated, but I could work something out.

Adam Schwanz @Reply

5 years ago

So does the information come in from the CSV in different units or is that always mileage? If it's always mileage that would be a pretty simple setup.

If you're getting it in different units and you need to show it in different units then you'll have some more work to do. I would probably work with a form using if/then statements and 2 combo boxes then, one with an input text field and a combo box for the input unit, and one text that is the calculation field with a second combo box to choose what unit to convert it to, and control every possible conversion. Otherwise it sounds like a nightmare of nested IIFs if you used a query.

	Tim Gilbert OP @Reply 5 years ago
Thanks - it's the 2nd option you note. I get the CSV data with distance in mi., km., or m. Since I import several records at once from one CSV (gladly always using the same unit for one CSV), would it make sense to assign a "UnitID" to the distance data and have a small table with conversion factors? I think that would work with your suggestion for separate input-output combos.

	Adam Schwanz @Reply 5 years ago
Yea that's probably what i would do is add a field that says what unit the value is. Then we can build a function to do the conversions.

	Richard Rost @Reply 5 years ago
Yeah, I think Adam's idea is good. I try to keep units the SAME in any database I'm building, but when you're dealing with other peoples' messes... er... I mean data... then you have to deal with what you're given. Converting between units shouldn't be that hard. It's just multiplication. Of course, we all know what can happen when you get your units wrong.

	Tim Gilbert OP @Reply 5 years ago
;-) Thanks, I figured that link went to a story about the Mars probe when I saw the comment. Thanks again. Have a great 2022.

	Richard Rost @Reply 5 years ago
Same to you.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View