Fitness 8
By Richard Rost
4 months ago
Importing Data From Excel, Calculated Query Fields In this Microsoft Access tutorial I will show you how to add a manual save button to your form, import data from Excel into your tables, calculate a protein-to-calorie ratio using a query with the IIf function, add and format calculated fields on your form, set up a requery button, and fix inner and outer joins to make sure all your records are displayed. This is part 8. MembersThere is no extended cut, but here is the file download: Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, fitness database, database save button, Me.Dirty False, copy paste Excel data, protein to calorie ratio, calculated query field, IIf function, category auto fill, requery button, outer join FoodT FoodGroupT, percent format query, delete button form
Subscribe to Fitness 8
Get notifications when this page is updated
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost.
This is part eight of my fitness database series. If you haven't watched parts one through seven yet, go watch those first.
As a reminder, I'm building a fitness database for myself because it's something I'm going to personally use. But if you don't care about fitness at all, that's fine. The real value here is in the techniques that I'm going to show you, everything from building tables and forms and queries to all the VBA. These apply to any Access database, whether you're managing customers, inventory, orders, Pokemon cards, or whatever you're tracking. It doesn't matter--all these techniques are for every database that you want to build. Fitness is just the example.
Let's get into it.
The next thing I want to put in here is: if you make a change over here, like if you change this to 96, and if you move to a different record, that should update over here. See that? But sometimes users can get confused if they don't see it update right away. Now, you could put after update events in all of these fields, but I think that gets a little disruptive. So what I want to do is just put a little save button down here, and that's mostly for people that don't know how to use Access databases properly.
We know this record is dirty, and it will get updated as soon as you close the form, leave the record, or whatever. In that case--actually, it doesn't always update when you close the form. Let's see: 96, close it. Okay, it did update that time. But I think just putting a little save button down on the bottom saves a lot of heartache and a lot of headache--just ache in general. So I'm going to put a save button over here, and again, this is just for people to give them peace of mind.
Let's open up that button. Let's call this the save button, and under Events, the On Click event is going to be literally just Me.Dirty = False. I've been using Me.Refresh for years and years. I think I mentioned this before: Me.Dirty = False is faster. It just commits the record to the table. It doesn't reload anything. It doesn't recalculate anything. It just saves the record.
Save that. Bug, compile of course. I have a whole video coming out on Me.Dirty = False versus Me.Refresh.
So now if you come over here, you change it back to 95, you hit your save button, that's it. It's saved, and it updates over here.
Like I said before, I have a whole bunch of data that I've been keeping in Excel--my meal plans and my calorie charts and all that stuff. I have data like all the different bars that I've been trying. My favorite, of course, so far is the Barebells, but I've got a lot of these and some of them I've noted on here, "don't buy again." I want to keep their information because I want to know in the future, "hey, don't buy this one again" in case I see it advertised or something, but I have a box of them. So I'm going to eat them all eventually, either they're going to expire or I'm going to eat them--one or the other.
But I want to get this data right here into my database. We're going to calculate this too. I like to calculate the protein-to-calorie ratio, so the higher that is, the more protein in it for the calories that you're eating. As you can see, Barebells is pretty good too--that's pretty high on the list. These other ones, David Bars, aren't bad. Quest Bars just taste like chalk. I just don't like them. You can find the Quest Bars everywhere, though. You can find these in a 7-11 or a gas station if you're on the road and you need something quick. They're not bad, they're just not my favorite.
What I want to do is get all of this into the database without having to copy and paste, or without having to type it all over again. So we're going to copy and paste it. Now, the key is either line your table fields up like this so that they're in this exact order, or make a query so the fields are in this order--either one--and then we can copy and paste this stuff, just like I showed you before.
I'm just going to create a query. We'll do this nice and easy, very design, and--oh, we also need to get the category. We haven't made a form for this yet, but this food group, T, my category 13, is protein bars. We'll fix that too. That's easy to just come over here in Excel and put a 13 over here. We'll make this the first column and just auto fill that down, and I can copy that in there as well.
Bring in your FoodT, and then just match up the columns that you have in your Excel spreadsheet: the Food Group ID, then we've got the Description, then the Calories, what's next? I've got Protein next, then Carbs, then Sugar (I go total sugar), and then Notes. Even though added sugar is usually more important (because not all sugars are created equal).
No need to save this, just go into datasheet view because we're not going to save this guy. I'm going to go back over here to Excel and select all the fields that I want to put into my database. Copy that and come back over here. We're going to do that trick I showed you before: select the same fields that we had selected in Excel, just out to there, and then hit paste--boom!
See, as long as this matches up field for field with what's in your Excel spreadsheet, it comes over just like a tab-delimited file did before. Now we don't have to save this; let's plan on doing more with it. I'm not going to save it.
If I refresh--we should put a requery on this here. I'll hit F5--there we go, put a requery button down here. See, it's these little things that you start discovering that you need once you start actually using it. You can call it requery, you can call it refresh.
As far as newbies go, I'm kind of torn. I like to use the word "requery" because it actually is a requery, which in Access is different from refresh, but sometimes people don't get that. That's okay. Requery button--that's a teaching moment there. Right click, build event, and this will simply be Me.Requery. Save it, close it, close it, open it, and that should--okay.
Let's do that little calculation next in our FoodWithGroup query. I'm going to design this guy, and I'm going to make a new column over here, a new field, and a calculated query field. We're going to call this ProteinPercent, and that's going to be Protein divided by Calories.
I can tell you right now we're going to need to fix this with an IIf function, because if Calories is zero, we're going to get a divided by zero error. We're going to say if Calories equals zero, then put a zero here; otherwise put that calculation. If you're not familiar with the IIf function, go watch this guy.
Hit OK, and if you take a peek at it you can see there it is. You can format it here in the query, but we're also going to have to format it in the form too. Go to Design View, bring up this guy's properties (right click, go to Properties), set the format to Percent, and for Decimal, I'm just going to go zero. I don't care if it's 1.3 percent. Save it, close it, you can run it, and there we go. That looks good: 19% of a chicken breast is protein, that's fantastic.
Close that, and then we'll add it in here. Close this guy, right click, Design View. Now that it's in the underlying query, it should show up in here. I'm just going to copy and paste this guy--copy, paste--slide this up here, double click. This is going to be ProteinPercent now, copy, paste. While we're at it, format: percent and then zero decimal places. Notice our ShowFoodF, copy it over. That's one of the benefits of it being an On Click event procedure; you don't have to keep adding new code to each one of these fields.
Members are just going to copy this label--copy, paste--and now with our new code that we wrote last time, all we have to do is change the On Click here to ProteinPercent and then give it a proper name, which is going to be ProteinPercentLabel. Save it. Oh, we have to fix this. Save that, close it, close it, open it, and you have to change what the label says in it, but you can see it's working.
There we go. Now we've got to slide this over a little bit and save it there. There we go.
That's pretty cool. Let's change that label. Let's make it Pro % like that. That looks good. I use that a lot. You know how much protein percentage, so if I can come over here now, I can say, "show me all the protein..." Well, I only got one. We've got to add the rest of my stuff in here. Show me all the--what do I got? Fruits. Okay, what fruit has the best protein percentage? It's a tangerine. I don't have a lot of stuff in here yet.
Another thing I discovered when I started actually working with this: go to Add New, if I put in here, like, ham, and then I just save it and close it, and even if I requery it, it's not showing up. Even if I get rid of this here, let's clear that, you don't see ham on here anywhere. Anybody know why that is?
Well, that's because our query is an inner join, and that means you have to have a matching record over here for every matching record over here. If you put a food item in that doesn't have a food group, you won't see it in the list. To fix that, we're going to make this an outer join. I want to show all records from FoodT and the matching records from FoodGroupT. So if you have a food item that's not in a group, you'll still see it. You get the little arrow there--that's called an outer join. Go watch this video if you want to learn more about those.
Now save it, close it, close it, open it--let me shrink that guy up--and you can see right here, I've got two items now, in fact, that don't have it. So, Fish Can, salmon, Chicken of the Sea--that should be protein. And this guy, well, it's just ham. Let's delete it.
I'm of the mindset that I don't usually like users to be able to just delete stuff without using my buttons, so in the next video we're going to make a button for that. But for now, let's just hit delete and you can see how messy it is now. It's all gone over here. Our button will ask them if they're sure, we'll delete it, and then we'll requery this list so that it goes away.
We'll start with that in the next video.
All right folks, that's going to do it for part eight. That's the TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I will see you on Monday for part nine. Tomorrow is Quick Queries Friday.
TOPICS:
Adding a save button using Me.Dirty = False
Copying and pasting data from Excel to Access
Aligning table or query fields for data import
Creating a query for structured data import
Assigning food categories using Food Group ID
Adding a requery button to refresh data
Creating calculated fields in queries
Calculating protein to calorie ratio in queries
Using the IIf function to avoid divide by zero errors
Formatting calculated fields as percent
Adding calculated fields to Access forms
Formatting controls for percentage display
Using outer joins to display all records in a query
COMMERCIAL:
In today's video, we're continuing with part eight of the fitness database series. I will show you how to add a save button to help users commit records easily, copy and paste data from Excel into your Access tables using matching field order or queries, and add a requery button for refreshing your form. You'll learn how to calculate and display protein percentage, avoid errors with the IIf function, and fix issues caused by inner joins by switching to an outer join so every food item appears, even if it is not in a group. Plus, we'll talk about formatting calculated fields, organizing your forms, and planning for adding features like safe deletion buttons in the future. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is the main purpose of the fitness database being built in this series?
A. To teach techniques that apply to all Access databases
B. To track only Richard's personal fitness goals
C. To show how to use Access specifically for gyms
D. To manage customer information for a retail store
Q2. Why does Richard recommend adding a save button to the form?
A. To help users unfamiliar with Access save changes manually
B. Because records never save automatically in Access
C. So that records can update other tables automatically
D. To allow users to save records to external files
Q3. What is the difference mentioned between Me.Dirty = False and Me.Refresh in Access?
A. Me.Dirty = False saves the record without refreshing or recalculating
B. Me.Refresh is faster than Me.Dirty = False
C. Me.Refresh only works for forms, not tables
D. Me.Dirty = False reloads all data in the form
Q4. When copying data from Excel into Access, what is most important to ensure a successful paste?
A. The table or query fields must be in the same order as the Excel columns
B. The Excel data must have no empty cells
C. The Access table should be empty before pasting
D. The Access form must be in Design View
Q5. Why does Richard suggest using a query to paste Excel data instead of pasting directly into a table?
A. Fields can easily be arranged to match the Excel columns
B. Queries do not require field names
C. Tables cannot be edited once created
D. Tables do not allow copy and paste operations
Q6. What is the purpose of the ProteinPercent calculated field added in the FoodWithGroup query?
A. To show the ratio of protein to calories for each food item
B. To calculate how many grams of protein are in a serving
C. To display the total calories for each food group
D. To compare total carbs to sugar content
Q7. Why does the ProteinPercent calculation use an IIf function?
A. To avoid division by zero errors when the calories field is zero
B. To check if protein content is missing
C. To ensure all percentages are formatted as whole numbers
D. To highlight high-protein foods
Q8. What is the effect of formatting the ProteinPercent field as Percent with zero decimal places?
A. It displays the result as an integer percentage
B. It rounds all numbers to the nearest ten
C. It hides the percentage symbol
D. It prevents editing the field in datasheet view
Q9. Why does Richard decide to use a requery button rather than a refresh button, especially for learners?
A. Requery reloads data from the data source, which is different from refresh
B. Refresh is obsolete in newer versions of Access
C. Requery is faster than refresh for saving data
D. Refresh does a full restart of the database
Q10. What problem does Richard encounter when adding a new food entry that is not assigned to a food group?
A. The entry does not appear in the results due to an inner join
B. The entry duplicates all other records in the table
C. The entry overwrites the first record in the table
D. The entry automatically assigns itself to the first group
Q11. How does Richard solve the issue of missing entries that lack a food group?
A. By changing the query join type to an outer join
B. By manually adding a food group to every entry
C. By sorting the data alphabetically
D. By deleting all records without a food group
Q12. According to Richard, why should users not be able to delete data directly in the datasheet view?
A. It can lead to messy or unintended deletions
B. It prevents updating of calculated fields
C. It locks the database for all other users
D. It causes errors when pasting new Excel data
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from Access Learning Zone is part eight of my fitness database series. Before getting started with this lesson, I recommend watching parts one through seven first, as this tutorial builds on the previous ones.
The project I am working on is a fitness database that I am designing to use myself. However, you do not need to be interested in fitness to benefit from these lessons. The techniques I cover are broadly applicable, whether you are building a database for customers, inventory, orders, collections, or anything else. Fitness serves only as the example; what matters are the skills and methods you will pick up as we go along.
Today I want to focus on improving how records are updated in the database. If you change a value and move to another record, you will see that the information updates as expected. However, users might feel uncertain if changes are not immediately visible. Technically, Access commits changes when you leave the record or close the form. Although generally reliable, this process can sometimes confuse users.
Rather than placing AfterUpdate events in every field, which would add unnecessary complexity, I have decided to add a simple save button to the form. This gives users the reassurance that their data is saved without disrupting their workflow. The save button will execute a command to save the current record immediately, ensuring any modifications are safely stored in the database. For this, I prefer using the Me.Dirty = False command over Me.Refresh, since it is quicker and more efficient. Me.Dirty = False simply commits the current record without reloading or recalculating any data. I will cover the differences between these commands in greater detail in a separate video.
With this save button in place, users can change values, click save, and know for certain that their data has been stored. The change will immediately reflect elsewhere in the database.
Next, I want to address importing large amounts of data from Excel. Personally, I track things like meal plans and nutrition data in spreadsheets. For example, I have lists of different protein bars I have tried, including personal notes about which ones are worth purchasing again. Some are marked "do not buy again," but I still want to include them for reference. My goal is to bring all this data into the database efficiently, rather than retyping it all by hand.
The most reliable way to do this is to match the order of your table or query fields in Access with the columns in Excel. This alignment allows for straightforward copying and pasting. If necessary, create a query in Access that lines up the fields precisely as they appear in your spreadsheet – for instance, Food Group ID, Description, Calories, Protein, Carbs, Sugar, and Notes. In Excel, make sure every entry has the correct Food Group ID (such as 13 for protein bars) before copying.
Once aligned, switch to the datasheet view of your query, select your Excel data, copy it, and paste it directly into the datasheet in Access. As long as the columns match, this process is simple and reliable.
I also recommend adding a requery button to refresh your data view after making changes. In Access, "requery" is more accurate than "refresh," as it truly reruns the underlying query and fetches the latest data. This is especially useful after adding, editing, or importing records.
For those interested in data analysis, you can add calculated fields to your queries. In my case, I like to see the protein-to-calorie ratio for each food item, which helps identify foods with the best nutritional profile. To do this, add a calculated query field that divides protein by calories. It is important to account for cases where the calorie value is zero, or you will encounter a divide-by-zero error. Use the IIf function to handle these scenarios safely. You can format this calculated field as a percentage, either directly in the query or on your form, depending on where you want it shown.
Once the calculated field is part of your query, you can add it to your forms. Copy an existing text box and label, assign them to the new protein percentage field, and apply your formatting preferences. Labels can be updated as needed — for example, abbreviate to "Pro %" for clarity.
During actual use, you may notice records not appearing in certain queries. This often happens if a record is missing a required matching value in another table, for instance, a food item without a food group. By default, queries use inner joins, which show only records with matches in both tables. If you want to ensure all your records are visible, even if some fields are missing, change the join type to an outer join. This allows all food items to display, regardless of whether they have an assigned group. If you want to understand outer joins better, I have another video that explores them.
Finally, when it comes to deleting records, I generally prefer not to allow users to delete items directly from the datasheet or in a way that could cause confusion. In a future lesson, we will add a custom delete button that ensures removals are carried out intentionally, with a confirmation prompt and automatic list refresh afterward.
That is all for part eight of the series. For a complete video walkthrough with step-by-step instructions covering everything discussed here, visit my website at the link below.
Live long and prosper, my friends.
Topic List
Adding a save button using Me.Dirty = False
Copying and pasting data from Excel to Access
Aligning table or query fields for data import
Creating a query for structured data import
Assigning food categories using Food Group ID
Adding a requery button to refresh data
Creating calculated fields in queries
Calculating protein to calorie ratio in queries
Using the IIf function to avoid divide by zero errors
Formatting calculated fields as percent
Adding calculated fields to Access forms
Formatting controls for percentage display
Using outer joins to display all records in a query
|
