Quick Queries #8
By Richard Rost
3 years ago
Too Many Tables! "Yes" or Yes. Copy Record w Children
Welcome to another Quick Queries video brought to you by Access Learning Zone. I am your instructor, Richard Rost. Quick Queries videos are designed to let me answer as many of your questions as possible! In today's video...
Topics Covered
- Too many tables and queries
- "Yes" or Yes or -1
- Copy Record with Child Records
- Difference Between Two Records (Salaries)
- Running Access on Different Platforms
- Going Over 2 GB Database Limit
- Regional Syntax Semicolons
Previous Quick Queries
Links
Keywords
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, quick queries, qq, Too many tables and queries, "Yes" or Yes or -1, Copy Record with Child Records, Difference Between Two Records (Salaries), Running Access on Different Platforms, Going Over 2 GB Database Limit, Regional Syntax Semicolons
Intro In this video, we talk about several Microsoft Access questions, including managing large databases with many tables and queries, setting up conditional formatting for combo boxes, copying complex nested tables, calculating differences between records using DLookup, handling Access file size limits and storing images, dealing with regional differences like separator characters in functions, and troubleshooting issues caused by Windows updates.Transcript Welcome to another Quick Queries video brought to you by AccessLearningZone.com. I am Richard Rost. Quick Queries is me answering all the questions that I get via YouTube comments or posts, some of my forums or emails, or whatever that don't necessarily need a full video on their own, so I gather them all up and we talk about them here.
This is number 8, so if you like this, there are 7 before this. You'll find links down below.
This question comes from Jack. Jack says, I recently inherited a database from my predecessor and it's a mess. There are close to 100 tables and well over 200 queries. Is this normal and what should I do about it?
Well, first of all, Jack, if all of those tables and queries serve a purpose, then there's nothing wrong with that. I've built some pretty monstrous databases myself in the past, and as long as all of those tables have a purpose and they're all in use and they're all properly normalized and their relationships are good, then that's okay.
I would go through them and just make sure they're all set up properly, and there is a proper relationship between them. That's just something that you have to have the experience to know how to do. I've got several videos on relationships. I'll put a link down below. Also, go watch my normalization video. That will teach you how to make sure the right stuff is in the right tables.
A lot of times when I see people with tons and tons of tables, they're putting stuff in multiple tables that really should just be in one. So watch out for that, like customer address all over the place.
Another thing I like to do is use a helper table. What that basically is, let's say you've got lots of little tables that have tiny lists in them, such as a list of genders, a list of states, or a list of cities. All of those different tables usually have maybe a handful or a couple dozen records in them, maybe 50 or 60 records. You can consolidate all of those into one big table so you don't have 50 tables; you can put all that stuff into one table. I call it a helper table, and I've got a video that explains how to do that too. I use this a lot.
As far as all those queries go, I try to reduce the number of queries that I have in my database, especially ones that I'm calling from VB code, by writing SQL statements for them. If you have a real simple query that's just doing a simple select statement or whatever, or even some action queries, append queries, or update queries, if you can move those into VB code, like if you're running them from a button, that will eliminate some of the mess too.
But be very careful when you're deleting these objects because you might delete something you need. So make sure you back up your database first. Backup your database. It's very important before you start messing with stuff.
Jim wants to know how to fix what looks like a conditional formatting problem. He says, I've got a form that is a paid option combo box. So you have a combo box trying to say yes to green, no to red. You have your conditional formatting set up there. After the rules, on opening the form and navigating through the records, there's no color change.
Now, the question is, if you're using a combo box, is it a value list combo box where you have the text in there of the words yes and no, or is it a bound combo box, a relational combo box where you've got the values yes and no in there? Because remember, in Access, yes is negative one and no is zero.
So if that's the case, you have to set your conditional formatting to look for zero and negative one, or zero and not zero. I like this because some other database systems, like SQL Server, use one for true. So that's probably your problem. You're probably confusing text with the actual true/false values. So look for that.
Next up, we've got Simone asking how to copy nested tables. In other words, to copy a project with all of the stuff underneath it. So you have project one, it's got a sheet, it's got elements, it's got all this stuff underneath it.
Unfortunately, Simone, there's no easy way to do this. This involves a little bit of programming, actually a lot of programming, and something called a recordset. I do cover this in my Access Developer Level 24 class. The example that I use is copying an order. You have to copy the order record and then all of its detail items underneath that. We'll use two recordsets to loop through the items and copy them over to another record.
This lesson is about two hours long. I spend a good hour of that lesson just covering copying an order with the details. I wish there was an easy way to do it aside from manually copying and pasting stuff. If you want to do it with code, with one button click, it involves some programming. So there you go.
Mecelly Ann asks, how do you calculate the difference between two rows in Access? Now remember, in Access we call them records. Rows is an Excel term. The difference between two cells is the same column. I get what you mean. For example, the difference in salary between two workers.
First, you're going to need a way to select which two workers you're talking about. So I would do this on a form. I would make two fields where you can pick the employee from a combo box. Then, once you've got that employee ID in each combo box, use a DLookup statement and have another text box next to the combo box where you DLookup their salary from the employee table or wherever you have it stored. Then, once you've got that done, it's easy to subtract them. So that's how I would do it.
So here's DLookup. There's DLookup that looks up a specific record from a table. If you want to do a whole bunch of records, like you want to add up a bunch of records, you can use DSum. You can use the average. All the D functions are called domain aggregate functions. In other words, you can use them to perform calculations on multiple records in the same table. So there you go.
Next up is Blake, who is referring to a video that I posted last year for my April Fools joke. It's called Access on Almost Any Platform. In that video, I joke that we've got a tool available that'll let you run an Access database on your Mac, on your watch, on your tablet, even on your old Atari 2600, whatever. It was a joke. Most, I'd say 90 percent of people, took it in stride. But a few of you were a little hurt that I posted something like that.
I'll keep this year's April Fools joke a little more lighthearted and sensitive. However, and I do mention this at the end of the April Fools joke video, there is a way you can run Access on several different platforms. For example, if you want your database on your Android phone or your Mac or whatever, there are always different alternatives. I cover a lot of those alternatives in this video.
For example, one of my favorite services is Access Database Cloud, where they will run your Access database in a virtual PC. What that means is you can connect to it from another PC, from your Mac, from your Android phone, your iPhone, whatever. You'll see the screen that your Access database is displaying right over the web, right on your device. You can lay out your forms and stuff to fit the profile of that device.
So there are lots of different ways you can do this. You can put your database in SQL Server, then build a web platform for it. There are tons of different ways you can get your databases online. So check this video out.
Next up, David says, I talk about getting past the two gigabyte file size limit in my split database video by stringing together tables that have a two gigabyte max file size. How do you do that? I'd like to put our agency into an Access database for all of our clients. However, we have a ton of old information that needs to be scanned and stored in the database, and I'm sure we're going to easily go over the two gigabyte limit.
A couple of different things to unpack here. First, if you're talking about different tables, you can put each table in its own ACCDB file, provided that each one of those tables is less than two gigabytes. So for me, for example, my customers are in one table because that's like a gigabyte and a half. Orders are in another table; that's again, just about two gigabytes. Order details can go in a different table. So you can have multiple different tables that are attached to your main frontend database.
If you're dealing with one single large table, like if you've got more than two gigabytes of orders, then you can start talking about archiving your data, taking the oldest records and not deleting them completely, but you might not need stuff that's 10 years old. You still want to save it, of course, but you don't need it in your main database. You're done running reports on it; you don't have to look stuff up that often. So you can archive that into an order backup table. I'll show you how to do that in this video.
Like contacts, for example, if you don't care about contacts that are 15 years old, just archive them in an old table. You'll still have them if you need to go back and look for them later, or you can have your form put a message on the bottom like I do there that says, you've got archived contacts, do you want to see them? So that's another thing you can do.
But the big thing I want to mention from your question here is you said you want to scan and store the information in the database. We do not store files or images inside our databases. That's a big no-no. Yes, I know Access has an OLE Object. It has an Attachment data type. You're not allowed to use them if you're learning from me. Stay away from them. Do not put files in your database. Images, pictures, documents, all that stuff. I show you how to properly handle images in my images video.
You're going to basically scan your documents, save them as images, put them in a folder, in a specific folder that your database has access to, either under the database folder or on the server somewhere else in a different share. Then you're going to save that file name in the database only, not the file itself. Unless you've got lots and lots and lots of images, then you should be fine.
As far as other document types go, you can work with those as well. Let's talk more about that in this video. So split your database. You could put multiple tables in multiple files. If you've got one big table, you can break it up and archive those records. Don't store images and files in your database. Once you finally do outgrow Access, as I did a couple of years ago for my backend, you can always upgrade to SQL Server.
If you've got gigabytes and gigabytes of stuff and you want to store it in your database, SQL Server can handle big data needs on the backend, and it's more secure than Access is. You don't need it right away; I went 20 years without having to use SQL Server, so you'll be fine, but just keep in mind it's available for the future.
Next up, R2 says, I like your videos, but I've got a problem. The syntax of DMin functions is with semicolons. One thing that I have to admit is that I know absolutely nothing about the different regional settings for different countries around the world. I get questions like this all the time in email and in the forums. You're trying to use the lookup or DMin, and your functions, based on your regional settings for your country, might use semicolons instead of commas like I show.
That's something you just have to figure out. I don't know anything about it. I'll be honest. I haven't looked into it at all. One of the reasons I switched everything I do over to the ISO date format, which is year-month-day, is so that it's the same for everybody around the world. Before, when I used to do my date seminar and date lessons, here in the US, we're 10/23/72 for October 23rd, or month-day, whereas most of the world is day-month-year, which does make more sense. But this makes the most sense, especially for computers sorting it: year-month-day. So that's why I switched everything I do to this format.
The problem you're having, R2, is a regional settings problem. Without seeing exactly what you've got there, I really can't give you more help, but that's probably what it is. It's a regional thing. Not sure what you mean about the timestamp, though. That shouldn't change as long as you're giving it a valid datetime field.
That's about going to do it for Quick Queries today. I know it's a short one. I'm still getting over moving. I'm unpacking. I'm living out of boxes. I'm still trying to get my office settled.
This morning, I was going to do more. I finished those other ones last night, but I sat down at my desk, turned my computer on, and it just kept flashing white, flashing, flashing, flashing. I did a bunch of Google-ing stuff and nothing seemed to work. I ended up having to roll back the last Windows update. I don't like doing automatic Windows updates. I know Microsoft wants to force you to do them, but I like to say, okay, there's an update, I'll install it now. When the computer reboots, you know if there's a problem, that's what caused it. With the automatic updates, it just kicks in overnight and I come in and sit down and not only is my computer rebooted, but now it's not working too. So now I don't have any clue what happened.
I assumed it was a Windows update, which it was. I rolled back the update and you have to go into safe mode and a whole bunch of stuff. If you want to see the instructions on how to do that, let me know and I'll make a video about it. But it was a pain. It took me a couple of hours this morning to do this, and of course, then it threw my whole day off.
More to come with today. Friday the 17th, Happy St. Patrick Stewart's Day, and I have a lot planned for next week. We'll kick it off on Monday. Maybe some stuff over the weekend if I get some more of my moving finished. There's still stuff at the old house. It's a pain.
Live long and prosper. I'll see you next week.Quiz Q1. What should you do first before deleting or modifying objects in a database you inherited?
A. Start deleting tables you think you do not need
B. Backup your database
C. Archive every object
D. Compact and repair the database
Q2. According to Richard, what is a common mistake people make that leads to having too many tables in Access databases?
A. Creating too many relationships
B. Putting the same type of data in multiple tables instead of consolidating them
C. Not using enough queries
D. Backing up the database too often
Q3. What is a "helper table" as described in the video?
A. A table used for storing temporary records only
B. A consolidated table for multiple small look-up lists like gender, states, and cities
C. A table to help manage relationships between queries
D. A table that stores user credentials
Q4. Why might you want to replace simple saved queries with SQL statements in your VBA code?
A. To make the database run faster
B. To reduce the number of saved queries and improve organization
C. So users can access queries directly
D. To increase database size
Q5. When using conditional formatting for a Yes/No field in Access, what are the values Access uses for Yes and No?
A. 1 for Yes, 0 for No
B. 0 for Yes, 1 for No
C. -1 for Yes, 0 for No
D. "True" for Yes, "False" for No
Q6. How does Richard recommend calculating the difference between two records in Access (such as comparing salaries)?
A. Using a DLookup function to get each value, then subtracting one from the other
B. Using the SUM function in a query
C. Manually subtracting the values in Excel
D. Copying the records to Word and calculating there
Q7. What is the main reason Richard advises against storing images or documents directly inside an Access database?
A. It is too expensive
B. Access databases cannot handle images at all
C. It can bloat the database and hit the 2GB limit quickly
D. Images will be deleted after closing Access
Q8. What solution does Richard suggest for getting around the 2GB file size limit per Access database file?
A. Only store data in Excel, not Access
B. Split large tables into separate ACCDB files and link them
C. Increase memory on your computer
D. Use attachment fields for large data
Q9. How should images and documents be handled in an Access database according to Richard?
A. Store the file directly in an OLE Object field
B. Save the file path in the database and store the image in a folder
C. Convert them to text data and store inside a memo field
D. Only link to internet images
Q10. What is an appropriate step if your Access database grows beyond what Access can handle?
A. Stop storing any new data
B. Upgrade the backend to SQL Server
C. Use more macros instead of VBA
D. Move all data to PowerPoint
Q11. What does a "regional settings" problem affect in Access, as discussed in the video?
A. Font sizes in forms
B. Which separator (semicolon or comma) is used in expressions and functions
C. Number of records allowed per table
D. The language of the VBA editor
Q12. What date format does Richard recommend for international consistency?
A. Month-Day-Year (MM-DD-YYYY)
B. Day-Month-Year (DD-MM-YYYY)
C. Year-Month-Day (YYYY-MM-DD)
D. Year-Day-Month (YYYY-DD-MM)
Q13. What was the cause of Richard's computer flashing white repeatedly?
A. Too many Access databases open
B. A bad Windows update
C. A virus from an email attachment
D. Outdated Access version
Q14. What does Richard say you should do before experimenting with removing or reorganizing tables and queries?
A. Compact and repair the database
B. Make a backup of your database
C. Reinstall Access
D. Run every query to check for errors
Answers: 1-B; 2-B; 3-B; 4-B; 5-C; 6-A; 7-C; 8-B; 9-B; 10-B; 11-B; 12-C; 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 from Access Learning Zone, I am answering several of the questions I regularly receive through YouTube comments, forum posts, emails, and other channels. Quick Queries is meant for those questions that do not require a full video, but still deserve a thoughtful answer, so I collect them and address them all together here. This is episode number eight in the series, and if you are interested in previous installments, you can find links to the earlier ones below.
One of the questions today comes from someone who recently inherited a rather disorganized Access database containing nearly 100 tables and over 200 queries. They are wondering if such numbers are normal, and what they should do about it. I want to point out that large numbers of tables and queries are not necessarily a problem, as long as everything is set up correctly. If each table and query serves a defined purpose, is well normalized, and the relationships are appropriate, then this is perfectly acceptable. However, it is important to review the structure to ensure that there is no redundancy or misplaced information.
Often, when I see databases with an excessive number of tables, the problem is duplicate information spread over multiple tables when it could instead be consolidated into a single table. For example, storing customer addresses in multiple locations rather than one. It is also very common to have lots of small lookup tables – say for genders, states, or cities – which could generally be merged into one helper table to cut down on clutter. I have a video on building and using helper tables if you need guidance with that.
When it comes to queries, I try to keep the number of saved queries to a minimum, especially those that are only used within VBA code. In those cases, I will often write the SQL statements directly into my code, particularly for simple selects or basic action queries. This approach helps to keep your database organized by eliminating unnecessary queries. Needless to say, before making any deletions or major changes, back up your database. This is vital.
Another question today relates to conditional formatting on a combo box in a form. The scenario involves changing the display color based on whether a paid option is yes or no – green for yes and red for no. However, the user reports that moving between records does not change the color as expected. With combo boxes, it is essential to know if you are working with plain text or actual Boolean values. In Access, yes and no are represented as negative one and zero. So, your conditional formatting needs to look for zero and negative one rather than the words themselves. Misunderstanding the distinction between text values and true/false numeric representations is a common source of confusion.
A third question concerns copying nested tables, or in other words, duplicating a project along with all its related child records. Unfortunately, there is no simple feature to do this automatically in Access. Accomplishing this requires programming using recordsets to loop through the main record and its children and copy them accordingly. I address this at length in my Developer Level 24 class, where the example is copying an order along with its line items – a process that involves multiple recordsets and some VBA code.
Next, there is a question about how to calculate the difference between two records, such as comparing salaries between two employees. First, you need a method to identify which records you want to compare. The approach I suggest is to create a form with two combo boxes for picking employees. Then, use DLookup to display each salary, and a calculation can easily provide the difference. The DLookup function is quite handy for pulling a specific value from a table, and there are other domain aggregate functions like DSum or DAvg for performing calculations over multiple records.
Another viewer referred to an April Fools video I posted in which I jokingly claimed you could run Access on almost any device, including a Mac, phone, or even a retro gaming console. Most people understood this as a joke, but a few did not appreciate it. Still, it brings up the genuine question of running Access databases on platforms other than Windows. One robust solution is to use a service like Access Database Cloud, which lets you connect to your Access database through a virtual PC, enabling you to use it from various devices. There are also more advanced solutions, like moving your backend to SQL Server and building a web front end, both of which offer true cross-platform access.
The next topic is about overcoming the two-gigabyte file size limitation in Access. Each individual ACCDB file cannot exceed two gigabytes. If your database contains several large tables, it is possible to split them so that each table has its own ACCDB file, provided each is within the size limit. For example, customers could be in one file, orders in another, and so on, all linked back to your frontend. If you have a single table that is approaching the limit, consider archiving older records into separate tables that are infrequently accessed, such as storing old contacts or past years' orders in backup tables.
A critical point here is about storing scanned documents or images. Although Access offers attachments and OLE object types, you should avoid placing actual documents and images inside your database. Instead, save these files separately in folders and only keep the file name or path in the database. This helps maintain performance and prevents your database from ballooning in size. I go through the correct way to deal with images and other document types in another one of my videos.
As your data volumes grow even further, you might find yourself outgrowing Access altogether. When this happens, migrating the data backend to SQL Server is a good solution, as it handles much larger volumes of data securely and reliably. However, for many users, especially early on, Access will suffice for years before you need to make this kind of transition.
Another question concerns the syntax of functions like DMin and why some users need to use semicolons instead of commas as arguments. Regional settings can significantly influence how Access expects its syntax. For example, in some countries, the default delimiter is a semicolon rather than a comma. This is not something I am deeply familiar with, but it is definitely a regional configuration and not unique to DMin – it can affect all kinds of Access functions and queries. Similarly, adopting ISO date formats (year-month-day) is a good practice because it removes ambiguities and provides consistency across systems and locations.
That wraps up this Quick Queries episode. This installment is a shorter one – I am still in the process of moving and working out of boxes, which has hampered my productivity. Today alone, I spent hours recovering my main computer after an automatic Windows update caused it to malfunction. If any of you are interested in a video on troubleshooting similar Windows update problems, let me know and I will consider creating one.
Thank you for joining me. As always, a complete video tutorial covering all of the topics discussed here, step by step, is available on my website at the link below.
Live long and prosper, my friends.Topic List Assessing table and query normalization in large Access databases
Using helper tables to consolidate lookup data
Reducing query clutter by embedding SQL in VBA code
Safely deleting unused database objects and backing up
Troubleshooting Access combo box conditional formatting
Understanding true/false values in Access combo boxes
Copying nested related records with VBA recordsets
Calculating differences between two records using DLookup
Using domain aggregate functions in Access (DLookup, DSum)
Access alternatives for cross-platform database use
Access Database Cloud and remote access options
Splitting Access databases to bypass 2 GB file size limit
Archiving old records to manage large tables
Properly handling and storing images or files outside Access
Regional differences in VBA function syntax (comma vs semicolon)
Using ISO date format (yyyy-mm-dd) for consistency across locales |
