Quick Queries #74
By Richard Rost
7 days ago
Access Time Fields Might Not Work the Way You Expect
In this video, we answer viewer questions about Microsoft Access, including troubleshooting time and duration calculations in tables, discussing the use of class modules versus procedural code for workflow development, and handling required foreign key fields in table design. We also cover best practices for moving Access databases to SharePoint or SQL Server for multi-user environments, clarify basic database terminology, and talk about managing table relationships and the differences between formatting and updating date/time fields. As always, there are shout-outs for helpful community members and tips for database project requests. This is Quick Queries 74.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, format time fields, sum durations, short time vs duration, hh:nn:ss format, minutes seconds entry, table design, class modules, foreign key required, SharePoint migration, SQL Server backend, league database, ad hoc joins, global relationships, referential integrity, remove time from date
Intro
In this video, we answer viewer questions about Microsoft Access, including troubleshooting time and duration calculations in tables, discussing the use of class modules versus procedural code for workflow development, and handling required foreign key fields in table design. We also cover best practices for moving Access databases to SharePoint or SQL Server for multi-user environments, clarify basic database terminology, and talk about managing table relationships and the differences between formatting and updating date/time fields. As always, there are shout-outs for helpful community members and tips for database project requests. This is Quick Queries 74.
Transcript
All right, leading off today, we've got a question that's been in the forums on my website for a while now from a Toyna from the Netherlands. I hope I pronounced that right, one of my Silver members. He's been a member for 10 years and he's built the database that he's tracking songs and their durations.
If he puts in the first five records here, he gets 22 minutes 15 seconds, or so he thinks. As soon as he adds in the next one, it flips around and it says two minutes 20 seconds. So the math appears to be wrong. Can any of you figure out what it is? A few users on the website forums already figured it out, and I figured I'd share this with everybody here.
Did you get it? What's happening is Toyna thinks he's entering minutes and seconds, but in reality, Access is storing that as a time of day. So Access is storing that internally as hours and minutes and looks the same. What happens is down here he's getting to 22:15, which is 10:15 p.m., and then he's adding four hours and 14 minutes to it. That's wrapping around at 2 a.m. That's why. Let me show you an example.
All right, here in the TechHelp database, we've got - let's just use the contact table. I got a contact date. Let's delete all these records out of here. Goodbye. Get ready, and let's get rid of that default value contact date. We'll get rid of that and we'll just pretend contact date is the field he's using, our song track title and duration.
Okay, so in the contact form, this is empty now. Let's set this so it's a tab stop, and we're going to format this now. We're going to format it - actually, let's just leave the format as it is. Let's just leave it with no format.
Okay, so come in here now. Let's put in some of the dates that he's got here or the times. So 4:04. Okay, see, all right. It's coming in with a date format. So I'm assuming that he put a format in here of short time.
Let's see here. I bet you he did, that short time. We didn't see the design of his table or this form, but I bet you that's what he's got - short time. So if you do that with short time, it's gonna look like that. 4:04, what else has he got? 4:04, 4:32.
Whoops, I can't type today! 4:32. Right, now let's put a sum on the bottom like he's got. Just delete notes there, we'll copy this, paste it down here and we'll open you up and we'll set your control source equals the sum of contact dates. We'll get a sum of all those fields.
All right, so there we go. 4:32. What else you got in? 3:02 and then 4:33 and then 6:04.
All right, we're good so far. We're in 22:15, which is exactly what you have. Now you put the next record in and it's 4:14, and it jumps to 2:29. That's exactly what you had here, 2:29.
Right, so that's definitely what the problem is. You're entering these in and you're typing them in like this into a short time field, but that's hours and minutes, so it's gonna wrap around. Now, there are a couple of ways you could fix this.
Oh, and by the way, I got to give props to Donald. He's the one that figured this out first on the website, and there are a few other people that chimed in, but Donald gets credit for that one.
Now, the easiest way to fix this is to simply come in here and instead of using - where we at - instead of using short time, you want to put in here hh:nn - remember n is minutes - :ss. Now you have to put your data back in there. Copy this format down here too.
Okay, now when you come back in here and look at it, you'll see that it's properly giving you the data that you want: hours, minutes, and seconds. If you type these in correctly the other way, let's see what we got. Yeah, you have to put it in 03:02 like that or 3:02.
Right, and you could probably just come in here, well, you could do this - if you got a ton of records, you could do it with some math, but I'm not going through all that right now. Just do it like this and just put a zero in front of that, right, and there. Okay, I went through and fixed them all and now you can see it's giving you the proper 26 minutes and 29 seconds.
Okay, you got it - format it like this.
Now, Donald also mentioned I do cover this in one of my videos: Time as a Duration. I'll put a link to this down below. Go watch this. It should explain a little bit better.
I also have to give props to Kevin, one of my moderators, because he did post this. He's got a similar database and his solution here is correct. Right, he's got the sum and all that, but notice he is using this for the format: minutes and seconds. Right, so he knows you have to enter these as minutes and seconds.
Okay, so I hope that answers your question and shows you what you did. If you want to see a more in-depth solution, let me know. Post a comment in the forum and maybe if I get some time, I'll do some more with this.
All right, next up, Robert, one of my Gold members and Developer students, is building a workflow system using class modules and object oriented patterns and he's wondering if that's how I would build something like that in my Access security database. Then he follows up asking what I mean when I say I don't really need class modules because I'm a disciplined developer.
That's a great question and I want to be clear about this. Class modules are powerful and if you enjoy object oriented design and it helps you stay organized, that's totally fine. But, and I've said this a few times in my 20 plus years of consulting, I've almost never needed to use class modules to build solid Access applications.
To be a solid, disciplined developer, I focus on simple, predictable structure, clear table design, consistent naming, small focused procedures, keeping business logic out of forms when possible, and not trying to be clever just for the sake of being clever. Make my code readable - that kind of stuff. That discipline replaces what a lot of people hope class modules will somehow magically fix.
So no, you're not doing anything wrong using class modules, and there's some really cool stuff you can do with it. I cover them in my Access Developer 51. Actually, it isn't quite finished yet. My students get access to them before they're fully finished, but I start covering them in Access Developer 50 and then we got some more in 51, a little bit more in 52.
They're really cool and you can do some really cool stuff with them and you're not doing anything wrong for not using them. For most Access projects, especially security workflow systems and stuff like that, you want a clean procedural approach and it's usually easier to build normally that way. It's easier to debug and it's easier to maintain long-term.
But there's certainly nothing wrong with using class modules. Whatever works for you is what works.
All right, let's head over to the YouTubes, and before we jump into the questions today, I want to take a quick moment to say thank you to everyone who reached out with a Happy New Year message to me on YouTube and on my website. I had just a small sample here, there were lots more, and I appreciate every single one of them.
I should mention that I usually record these quick queries a little bit ahead of time sometimes. Sometimes I know we have a weekend edition if I'm running behind, but the January second episode was actually recorded before New Year's Eve. So this one right here is the first quick queries I've recorded since the new year officially started. So welcome to 2026, and thank you again all for the kind words, the encouragement, the good wishes. I'm really looking forward to what we're going to build and learn together this year.
All right, let's get into today's questions.
All right, we've got - I don't know how to pronounce this now - someone says, I got a table, has a field as a foreign key and it's required and I want to set all the records to null but the query does not accept it because the field is required. I don't want to change it to required equals off, what's the solution?
This is a kind of a weird one. If a field is a foreign key and it's marked required, then by definition you can't set it to null. Access isn't being stubborn, it's doing exactly what you told it to do.
The real question isn't why won't the query work; the real question is why do you want to set a required foreign key to null in the first place? Foreign keys usually point to parent records. It's the child side of a parent-child relationship, so setting it to null means you're intentionally creating an orphan.
If you don't know what widows and orphans are, I've got a whole separate video on that. Go watch this guy.
If the relationship is optional, then the solution is simple: turn required off. If the relationship is not optional, then you shouldn't be setting it to null in the first place. In that case, you either need to delete the record, reassign it to a different parent, or rethink your design. You can't have it both ways.
A field can't be required and null at the same time, so you have to take a step back and revisit the table design, not fight the query. Without seeing more about what you're trying to do, I have no idea, so your hypothetical is understood, but you can't do it. Tell me more or no.
All right, next up we've got Hey Hoves. He says, my company has expanded and we've added more employees. We need to modify our database for multiple users. Are your recommendations in this video still valid and up to date? Our IT manager is moving us to SharePoint and I am concerned about moving the database.
Here's the video that he's talking about for everybody else who wants to go watch it. That video's from 2022, so it's about four years old now, but this is a really common question that I get, and the short answer is yes, my recommendations in that video are still valid today. The technology has not changed that much, and of course, everything I said: don't use file sharing services like OneDrive, Dropbox, you already know that.
As for SharePoint, I only recommend it if your company is already using SharePoint and fully committed to it. If your IT department is already all in on SharePoint, then yes, you can move your tables to SharePoint lists and keep Access as a front end. Just understand that it's a compromise and SharePoint isn't a true database server.
I've got a whole seminar on migrating your Access database to SharePoint and it's okay, I mean it'll work, it's fine, but there is a much better solution. That of course is SQL Server.
Now, if you're not already on SharePoint, I don't recommend moving to it just for Access. SQL Server is a much better long-term solution. It's much more reliable, it's more scalable, it's designed for multi-user environments. I've already got this online seminar available now and I've got a full SQL Server course coming very soon.
So if your company is growing and you expect more users and you want to, you know, worry about security and scalability and all that stuff and speed, then SQL Server is the better choice. SharePoint is a reasonable option if you're already there and your company's already using it, everybody's already locked into it, and you've got support for it, and that's fine. There's nothing wrong with it, but SQL Server is better. That's my opinion and I'm sticking to it.
All right, this next one made me smile because the comment was posted on the video that literally explains the answer. Purple 9-rain posted, I need help with this question that I got wrong on a quiz and I'm studying to find the correct answer for my knowledge now, but I can't find it in my textbook nor online.
It says: A collection of related records in a database is known as a
A. file
B. relationship
C. record
D. table
E. field
I chose table and it was marked wrong. Anyone knows? It would be a great help. Thank you. Is the answer relationship?
No, you are correct. The answer is table. A record is a single row in a table. A field is a single column of that row. A relationship doesn't store records at all; it just defines how tables connect to each other. A file is just a file.
So if you chose table and your teacher marked it wrong, your answer is correct, your teacher is wrong. Either the quiz was poorly worded or the answer key is wrong. Purple rain, you're good. If your teacher wants to argue about it, you tell them they can come talk to me after class.
This one comes up a lot. I mentioned it back in quick queries 69. A lot of people want to build things like sports leagues or tournament systems in Access: soccer, football, whatever you want to call it, baseball, whatever flavor you prefer.
There really aren't that many complete tutorials out there that I've seen, but I really haven't looked, so I don't know. I'll be honest. I've got very limited real-world experience running leagues. I've played in tournaments, I used to play a lot of softball, bowling. Managing schedules, standings, playoffs, tiebreakers - that stuff, that's a full-time job in and out of itself. Access is the right tool for it, but that's a lot of work.
From a database perspective, it's totally doable. Teams, players, matches, scores, standings - it's all just tables and relationships. The hard part is deciding the rules. In a lot of tournaments that I played in, if you get a weird number of teams or if you've got custom rules for your tournament or for your league, that's where the problems start. Just putting together a blanket basic tournament schedule, that's not a big deal, but it's like, okay, well now we need to do this and if it's a Thursday and the moon is shining, it's all those weird edge cases - we call them in software development.
Here's what I'll say again: If there are enough people out there who really want to see a league or tournament-style project like this built in Access, let me know. Post a comment down below. As you guys can see, I do read through all these comments. This was three weeks old already. I might not be right on the ball with them, but I try to read them all and I at least try to respond to everybody as best I can.
If there's enough interest, I might put something together as a learning example. No promises, but if I see demand, I'll consider it. The squeaky wheel gets the grease. The more comments I see on a particular topic, the more likely I am to make a video on it. So yeah, post a comment down below.
Next up we got Rafael, who says about my invoicing video, where do you establish the relationships between the tables created in this example? I don't see any relationships established between them. Thank you.
Great question. I talked about this briefly back in quick queries 29. I think we're going back now like a year or so, but it's worth a quick recap because I know everybody doesn't watch every video.
I don't always use system-level relationships in Access and that's intentional. You don't actually need global relationships for Access to work correctly most of the time. I use what are called ad hoc joins directly in queries: customers to orders, orders to order details, joined where and when I need them.
One big reason is that system-level relationships don't survive well once you start working with multiple backends or you start moving things to SQL Server. You've got to set up a whole separate, different set of relationships there. Access can't enforce them across multiple backends anyway, so I don't rely on them. I try to handle that stuff with my form design and in my code. I usually enforce the referential integrity in code by controlling what users are allowed to delete, for example: you can't delete an order if it has order details, you can't delete a customer if they have orders. You can do that with referential integrity, but you can also do it very simply in your forms too, in your form code.
I don't use cascade updates because my primary keys are all autonumbers, so there's nothing to update. Cascade deletes are very dangerous, so I have to be very careful with those. So I almost never use those at the table level either. I'll control that in my button: "Are you sure you want to do this?" and then I'll delete the stuff myself. Oh, someone's beaming in.
If you like global relationships and they work for you, that's fine. I do teach them and I've got several videos that cover relationships in detail, but they're optional and Access gives you more than one way to solve that problem. Yes, this is on my list for a full video because there's a lot of nuance here that deserves a deeper dive. I've been wanting to do a TechHelp video for global relationships versus ad hoc joins and what the options and the alternatives are. Look for that pretty soon.
But great question and the bottom line is you don't need global relationships. There's nothing wrong with them; use them if you like them, but I tend not to use them that often.
Oh, and plus, I forgot part of the reason why I stopped using them in the first place is because it's hard to get good error messages out of them. You get "The record really requires a related record in this other table." I'm like, what?
Rather than trying to change all the error messages and intercept those, I just handle that stuff myself. If you're going to delete a customer for example, instead of using referential integrity, I like to control when my users delete stuff. I make them use my button. I don't let them use the form-level delete: select the record, hit delete. I turn that off. I don't allow deletes. I make them use my delete button and the first thing I do is check to make sure that they don't have any related records in any other tables.
So, lots of ways to do this stuff.
All right, finally today we got Peg talking about my remove time video. Peg says, after trying to add this query, it's an update query, I realize you just have to change the short date format in either the table or the report and the time is removed easier than the update query.
Well, you're not actually fixing the problem, you're just hiding the problem. In the original video, the user that I was dealing with was importing a bunch of records from Excel that's got date and time portions and her database doesn't like that. She's got queries and reports and stuff already built that don't like that time portion because if you want to do a report, let's say, from January 1 to January 3, and your database is already built assuming it's just dates with no times, you're not going to get records that say January 3 at 6 p.m. because that's after your end date. What she wanted to do is chop all the dates off.
If you've got date time values in a table and you just don't want to see the times, that's fine - switch to short date format and problem solved. But if you've got calculations and equations and stuff that rely on it just being a date at midnight and you don't want that time value in the field, you've got to remove it and I show how to remove it in that video. It's not just not displaying the time, it's physically removing the time from the field and that's the difference and I hope that makes it understandable.
All right, changing the format is easier, but it only changes how the date looks, and the update query actually fixes the underlying issue by removing them all in one shot.
All righty, okay.
Quiz
Q1. What was the main issue Toyna encountered with tracking song durations in Access?
A. Entering durations as text instead of numbers
B. Entering durations as dates and times, causing Access to wrap around after 24 hours
C. Sorting records incorrectly
D. Forgetting to save records before summing
Q2. When Toyna added a song duration that pushed the total to 22:15 and then added another, why did the sum change to a much lower value?
A. The Access sum function cannot handle values over 24
B. The duration field was formatted incorrectly, causing time of day wrapping
C. Short time format only supports minutes and seconds
D. The field type was set to number instead of date/time
Q3. What is the correct approach to fix duration fields so Access sums durations correctly?
A. Change the field to currency format
B. Use the format hh:nn:ss for hours, minutes, and seconds
C. Change the field to a text type and retype all values
D. Remove the time format from the field
Q4. In Access, what does the short time format (such as 4:32) represent?
A. Four hours and 32 seconds
B. Four hours and 32 minutes
C. Four minutes and 32 seconds
D. Four hours and 32 milliseconds
Q5. What is the limitation of the short time format when summing durations in Access?
A. It does not allow negative times
B. It wraps around at 24 hours, treating data as time of day
C. It only supports seconds, not minutes
D. It cannot be used in calculated fields
Q6. According to the instructor, what is NOT a substitute for disciplined development practices in Access?
A. Using class modules
B. Consistent naming conventions
C. Clear table design
D. Focused, simple procedures
Q7. Why might a developer choose not to use class modules in an Access project?
A. They are not supported in Access 2019
B. Simple procedural approaches are often easier to maintain and debug
C. Class modules run more slowly
D. Class modules do not work with forms
Q8. What is the main reason the instructor sometimes avoids system-level (global) relationships in Access tables?
A. System-level relationships cause frequent data loss
B. They cannot be used with local tables
C. They do not survive multiple backends such as SQL Server or SharePoint
D. They increase file size significantly
Q9. What is recommended if a field is a required foreign key in Access and you want to set its values to null?
A. Change the Required property to No if the relationship is optional
B. Use an update query to set the values to zero instead
C. Remove the foreign key constraint temporarily
D. Change the field type to text
Q10. What is the main drawback of using SharePoint as your backend for Access in a multi-user environment?
A. SharePoint cannot be accessed from Access at all
B. SharePoint is not a true database server and is less robust than SQL Server
C. SharePoint requires every user to have administrator rights
D. SharePoint cannot support more than five users
Q11. According to the instructor, what is a "table" in a database?
A. A collection of records
B. A single row of data
C. A column within a record
D. The connection between records
Q12. What does "referential integrity" help prevent in a relational database?
A. Duplicate records in a table
B. Orphaned child records when parent records are deleted
C. Incomplete updates of records
D. Overflow errors during summing
Q13. Why does the instructor prefer handling deletions and data integrity via custom code/buttons instead of relying solely on table-level referential integrity?
A. Access cannot enforce referential integrity at all
B. Custom code allows for precise control and better user messages
C. Table-level enforcement is always slower
D. It reduces the need for data backups
Q14. Why is simply changing the display format (e.g., to short date) not always sufficient when dealing with date/time fields containing time values?
A. It permanently deletes time data
B. It only changes the appearance, not the stored data, which may cause logic or report issues
C. It causes calculation errors with lookup fields
D. It prevents sorting by date
Answers: 1-B; 2-B; 3-B; 4-B; 5-B; 6-A; 7-B; 8-C; 9-A; 10-B; 11-A; 12-B; 13-B; 14-B
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
In today's Quick Queries video, we have a variety of Access-related questions that have come up in the forums and from YouTube comments. Let me walk you through each one, explaining some of the common issues and solutions that I've discussed with students and members over the years.
First up, there's a long-running question from a Silver member in the Netherlands who has been building a database to track songs and their durations. He noticed that when entering song durations, things seem fine at first. For the first several records, he gets a total like 22 minutes and 15 seconds, but as soon as he adds another song, the total jumps to just a couple of minutes. Many users have struggled with this, so let's break down what's happening.
The problem is that he is entering durations thinking in terms of minutes and seconds, such as 4:04. However, Access is interpreting this entry as a time of day, not a duration. Internally, Access stores this as hours and minutes, like 4:04 AM, not 4 minutes and 4 seconds. So, as he sums taller numbers, the calculation wraps around the 24-hour clock. For example, adding enough durations to go past midnight makes the total start over, which looks wrong if you are tracking duration.
The real fix is to change how the field is formatted and how data is entered. Rather than using a "short time" format that reads hours and minutes, you need to specify a format that clearly uses hours, minutes, and seconds. I recommend formatting the field using "hh:nn:ss" so that Access treats the value as a time interval. You may need to re-enter your data, making sure to add leading zeros and the right structure, such as "00:04:04" for four minutes and four seconds. If you have a lot of data, you might be able to use code to update it, but for small datasets, just correct them manually.
I have to give credit to Donald on the website for pointing out this solution first, and also to Kevin, one of my moderators, who demonstrated the correct formatting for durations in his own system. There's also a full video called "Time as a Duration" that deals with this issue in more detail, and I highly recommend checking that out if you want even more explanation.
Next, I had a question from one of my Gold members, Robert, who is working with class modules and object-oriented design patterns in his workflow system. He wanted to know if that's how I would approach building a security database in Access, and what I mean when I say I do not use class modules very often.
Class modules are definitely powerful and provide a solid framework if you like object-oriented structures. However, in my consulting career, I've found that for most Access applications, you can get away with disciplined, procedural design instead. That means focusing on solid table structures, consistent naming, keeping business logic out of forms, and writing clear, focused code. Class modules can be great, but you do not need them for most database projects, even ones with robust business logic or security needs. I do cover more advanced class module use in my Access Developer series, especially in the later lessons, and those interested in a deep dive should check those out.
Turning to some YouTube questions, I want to thank everyone who sent Happy New Year messages. I really do appreciate the kind words, and I'm looking forward to all the projects and learning we'll share this year.
One viewer asked about required foreign key fields. He has a table where a foreign key is set as required, but wants to update records to set that field to null. If a field is marked as required, then Access will not let you set it to null because that would violate referential integrity. The real question here is design-related: why do you want to nullify a required field? Usually, required foreign keys are there because records should always be linked to a parent. If the relationship might be optional, set the "required" property to false. Otherwise, you either need to reassign or delete the record, but you can't have a required field also allow nulls. If you want to understand more about "widows and orphans" in database design, I have a separate video explaining those concepts.
Another viewer named Hey Hoves wanted an update on whether my previous recommendations for moving to multi-user environments in Access are still valid, especially since his company is considering SharePoint. The advice in my previous video from 2022 still holds up today. In general, avoid file-sharing services like OneDrive or Dropbox for Access backends. If your company already uses SharePoint, it is possible to migrate your tables to SharePoint lists, but SharePoint is not a true database server. The best solution is still SQL Server; it's much more scalable, secure, and reliable for multi-user Access applications. So, my guidance remains the same: If your IT setup is already committed to SharePoint, go ahead, but if you are looking for a future-proof back end, SQL Server is the way to go.
Next, I saw a comment from a YouTube viewer who got a quiz question wrong. The question asked what a collection of related records is called, and they answered "table," which is correct. A table is a collection of records. Fields are the individual columns, records are the rows, a relationship defines how tables connect, and files are just storage. If your teacher marked "table" wrong, then there is an error in the answer key or wording.
There was also a question about creating tournament or league management systems in Access. Many people try to build applications for sports leagues. While this is absolutely possible in Access since it is just a matter of structuring your tables around teams, players, matches, etc., the real challenge is the business logic. Every league has unique rules and exceptions, which makes building a generic solution difficult. If there is enough demand, I can put together a learning example, but let me know in the comments if this is something you'd like to see.
Rafael asked about establishing relationships between tables in an invoicing database. While you can set system-level relationships in Access, I usually rely on ad hoc joins within my queries instead. System-level relationships don't always migrate well if you split your database or move to SQL Server, so I prefer to enforce referential integrity with my form designs and in code. For example, preventing a user from deleting a customer who still has orders can be handled in your form logic rather than enforced at the system level. Using cascade updates and deletes at the table level can be risky, so manage these in your application logic for better control.
Lastly, Peg commented on my video about removing time from date fields. She found that just changing the format of the field to "short date" makes the time disappear. While this is true for how the data looks, the underlying problem may not be solved. If you need to actually remove the time portion because of aggregate queries or filters that depend on the data being a pure date, you have to update the actual values, not just the display format. Changing the format only hides the problem.
If you'd like to see a complete step-by-step tutorial covering any of the topics above, you can find detailed videos on my website at the link below. Live long and prosper, my friends.
Topic List
Identifying and fixing time field wrapping in Access
Understanding time storage in Access (time of day vs duration)
Formatting Access fields for hours, minutes, and seconds
Correcting data entry for time durations in Access
Summing time values correctly in Access forms
Setting appropriate field formats for duration tracking
Implications of using required foreign key fields in Access
Handling null values in required relationship fields
Rethinking table design for foreign keys and required fields
Recommendations for multi-user Access environments
Accessing and migrating data to SharePoint vs SQL Server
Considerations when moving Access tables to SharePoint
Advantages of SQL Server for multi-user Access databases
Understanding tables, records, fields, and relationships in Access
Handling database quiz and terminology confusion
General table and relationship design recommendations in Access
Why and when to use system-level relationships in Access
Using ad hoc joins in queries versus global relationships
Enforcing referential integrity in forms and code
Dangers of cascade updates and deletes in Access
Controlling user deletions with custom form buttons
Difference between formatting and stripping time data in tables
Updating queries to remove unwanted time portions from date fields
Correctly handling date and time values for queries and reports
Article
Let's dive into a few frequently asked questions and common stumbling blocks for Access users, especially those building databases for tracking things like song durations, managing relationships between tables, or moving to multi-user setups.
Suppose you are tracking song lengths in Microsoft Access and you notice that your calculated totals for song duration do not add up as expected. You input times like 4:04 and 4:32, expecting those to be minutes and seconds, but after a certain point, your total appears to "wrap around," suddenly displaying a much smaller number than expected. The confusion here is that Access stores short time fields as time-of-day, meaning what you enter as 4:04 (expecting 4 minutes and 4 seconds) is actually stored as 4 hours and 4 minutes AM. When you total up entries past 24:00, Access wraps the time around to the next day, just like a clock.
For example, if you enter a few tracks as 4:04, 4:32, 3:02, 4:33, and 6:04, the sum shows 22:15. Add another song of 4:14, and suddenly your sum shows only 2:29. This happens because the way Access sums time fields is by counting hours and minutes as if they were clock times instead of durations. Once the total surpasses 24 hours, or midnight, it wraps around.
To fix this, you should format your duration field to store and display elapsed time, not time of day. In Access, use a custom format like hh:nn:ss for your duration fields. Remember that in Access formatting, 'hh' is hours, 'nn' is minutes, and 'ss' is seconds (not 'mm', since 'mm' represents months). You may have to re-enter your data in the updated format, especially if you have a lot of existing records. For durations over an hour or with seconds, enter the value as, for instance, "00:04:04" for 4 minutes and 4 seconds. If you have many existing records, you may consider writing some code or using an update query to correct them in bulk, but for small numbers, manual correction works fine.
Once your data uses the appropriate duration format and the field is formatted with hh:nn:ss, your sum calculation will work as expected and display the correct total duration.
Switching topics, let's talk about class modules and object-oriented programming in Access. Class modules can be powerful tools for organizing database logic and supporting object-oriented design. Although class modules allow for encapsulation, code reuse, and cleaner management of complex systems, many Access applications are well-served by disciplined procedural programming and well-structured table designs. Keeping your procedures small, using clear table and field names, and handling business logic in central locations makes your code easier to understand and maintain. You are not required to use class modules, and many seasoned Access developers build robust systems without them. That said, there's nothing wrong with using class modules if they match your development style or project needs. Use whatever approach helps you build reliable, maintainable systems.
Another scenario you might confront involves trying to set a required foreign key field to null. In Access, if a field is marked as required, you cannot set it to null—this is fundamental to database integrity. A foreign key typically creates a link to a parent table, representing that each record in your table relates to a record in another table. If the foreign key is required, every record must have a parent; if you set it to null, you break that relationship, creating what is known as an orphaned record. If you need a field to allow nulls, simply set the 'Required' property to No. Otherwise, you must provide a valid foreign key value. If your business process calls for making a relationship optional, adjust the table design accordingly.
For those working in multi-user environments, upgrading from a single-user database to support multiple users is a common step. Still, you might wonder if previously published recommendations are still up to date. Generally, the advice remains valid: don't use file-sharing services like OneDrive or Dropbox to host your main database files for multi-user setups. If your company uses SharePoint and is committed to it, moving your tables to SharePoint lists can work with Access as a front end, but SharePoint isn't a true relational database server. For the best performance, reliability, and scalability, SQL Server is a better backend for Access databases in a multi-user environment. Only opt for SharePoint if your company is already using it extensively, otherwise, SQL Server is the professional solution for supporting numerous users and heavier workloads.
If you're learning the basics and encounter a quiz question about database structure, such as "A collection of related records in a database is known as a ____," you should know that the answer is table. Each record is a row in a table, a field is a column, and a relationship just connects tables but does not itself store records. If a teacher or quiz marks "table" as incorrect for this question, then the mistake is in the answer key, not your understanding.
Some users want to design systems like sports leagues or tournament schedules in Access. This is possible, as all of the core elements—teams, players, matches, scores—can be expressed as tables and relationships. However, the real complexity comes from handling the custom rules, schedules, and exceptions that sports leagues often entail. If you are interested in building such a system, start by outlining your entities and relationships, but be prepared to handle a lot of detail in business logic for various competition formats and rules.
Another question often asked is about building relationships between tables in Access. While Access offers a relationships window to create global (system-level) relationships, you do not have to rely on these for your database to work. Many developers, particularly those who plan to split their database or move to different backends like SQL Server, use ad hoc joins inside queries rather than global relationships. Referential integrity and cascade rules can be enforced at the form or code level. For example, you can prevent users from deleting a customer if there are related orders by including that logic in your form's delete button code, rather than depending on cascade delete at the table level, which can be dangerous. Using ad hoc joins makes your Access database more portable and easier to manage during upgrades or back-end transitions. However, if you prefer global relationships and they fit your workflow, there's no harm in using them; it's a matter of preference and project requirements.
Finally, when working with date-time fields, you might want to prevent time portions from interfering with queries or reports—such as when running a date-based report and including all entries for a given day. Suppose you have a field that contains both date and time (e.g., 1/3/2024 6:00 PM) but your queries are only supposed to check dates (e.g., everything from 1/1/2024 to 1/3/2024). If you only set the field or report to use the "short date" format, it hides the time but doesn't remove it. This can exclude records you might expect to see, because comparisons with date ranges are actually checking against the full date and time value. To truly remove the time portion, you must run an update query that sets the value to just the date, at midnight. For example, in an update query you might use:
Update MyTable set MyDateField = Int([MyDateField])
This code strips out the time portion, leaving the date at midnight. Changing the display format only changes how the data looks, not the underlying value, so use update queries to actually remove time parts when necessary.
I hope this clarifies these common issues in Access database management, from handling times and durations correctly, to table relationships, to structuring your tables and using appropriate techniques for multi-user scenarios. Taking care with your table definitions, data formats, and workflow logic will help you avoid many subtle problems as your databases grow. If you have a specific question or want a deeper dive into a particular problem, feel free to reach out and I'll do my best to help!
|
