Microsoft Access Quick Queries #20: Avoiding Lookup Fields, Dlookup, and Database Maintenance Tips

 

Today's TechHelp tutorial from Access Learning Zone focuses on addressing various questions that don't require an entire video on their own. This is episode 20 of our Quick Queries series, where I compile your frequently asked questions into one comprehensive session. Let's get started.

A common inquiry I receive is about my aversion to the lookup wizard and lookup fields in tables. The main reason is that using lookup fields in a table isn't considered proper normalization or good database design. For instance, if you have a customer table and want to store titles, suffixes, or prefixes, such lists should reside in separate tables, not as lookup fields within the customer table. This approach ensures better database design and facilitates easier manipulation and programming of data later.

Some people worry that using lookup functions in queries, such as Dlookup, could corrupt the database. While it's unlikely to cause corruption, it will significantly slow down performance. Each Dlookup call in a query must process a lookup for each record, which can be inefficient. Instead, I recommend using joins to link tables, which is much faster and more efficient.

Database corruption in Access is typically due to issues like improper shutdowns, large file sizes approaching the two-gigabyte limit, and network problems. Some bugs in recent Access versions can also cause corruption, which underscores the importance of regular backups. Using lookup functions is fine for small-scale lookups, like those on forms, but avoid putting them in queries to maintain speed.

Regarding the use of temporary variables (temp vars), it is best practice to clear them at the end of their use within a function. While temp vars persist for the entire database session and modern computers have ample memory, tidying up by removing or clearing temp vars can prevent unnecessary memory usage, especially in extensive databases.

Another handy tip from our forum moderator, Kevin, is about reverting to the older Access theme. If you prefer the look and feel of the 2013-2022 theme, you can change to it by navigating to File -> Options -> Client Settings, and selecting the checkbox to use that theme.

Eve asked about automatically importing multiple CSV or text files when they are added to a folder. You can set this up by having your database run a loop to check the folder or by adding a button that triggers the import process. This can be similar to how you would import images into a database.

For those who struggle with moving objects in Access forms without using a mouse, Matt Hall shared a useful tip. You can use arrow keys for moving objects, control-arrow keys for finer movement, shift-arrow keys to resize, and control-shift-arrow keys for precise resizing. These shortcuts offer more precision than dragging with a mouse.

Another common issue involves date formatting in forms. Even if you have set the date format in the table correctly, it might not reflect on the form because the form's settings override the table's settings. Ensure you check the format settings in both the table and the form.

Larry brought up a question about command button names not matching those in my videos. Every new control added to a form receives a sequential number, which is arbitrary. To avoid confusion, give meaningful names to controls as soon as they are created, and be cautious when renaming them to avoid breaking code.

Many users sometimes lose the little box or rulers in their form design view. To restore them, go to the Arrange tab, select Size & Space, and click the Ruler icon.

Adam contributed code to convert a form into a report, which can be useful if you need such functionality.

Finally, Donald asked about verifying required fields before committing forms to the database. While setting fields as required ensures they must be filled, it's often better to allow blank fields rather than risk invalid data entry. For complex validation involving multiple fields, consider table-level validation rules. 

Kenneth wondered why not all note fields use rich text. While rich text can be useful for formatting, it can complicate data export processes. Use it only when necessary.

James suggested using filter properties to make A-Z jump buttons more efficient by filtering rather than jumping to specific records. This method could be particularly beneficial when dealing with large datasets.

That's it for today's Quick Queries. For more detailed tutorials and step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends.

For a complete video tutorial on this topic, please visit https://599cd.com/QQ20?key=Blogger

Student Grades in Microsoft Access: Proper Data Entry, Spreadsheet-Like View (Crosstab)

 

Today's TechHelp tutorial from Access Learning Zone addresses a frequently asked question: how to store students and their grades in a Microsoft Access database and present this data in a spreadsheet-like format using a cross-tab query. This query came from Candace in Beiberton, Oregon, who is transitioning from Excel to Access for managing student grades. Candace, and many others, have been accustomed to the Excel format with students as rows and assignments as columns.

The big difference between Excel and Access lies in data storage. In a relational database like Access, data isn't stored in a flat format. Instead, you use tables, each representing different entities. For student grade management, this means you need to set up multiple tables. I'll guide you through this process and show how to create a cross-tab query for displaying data in a familiar format.

This tutorial is at an expert level. Though advanced, you don't need programming knowledge. Knowing terms like many-to-many relationships, junction tables, subforms, and relational combo boxes will be helpful. I recommend watching my other free videos for a detailed explanation of these concepts.

In moving data from Excel to Access, you need three tables: one for students, one for assignments, and a junction table to link them. Here's how to set it up:

1. Create a Student table for storing student information such as student ID, name, and other details.

2. Create an Assignment table to store details about quizzes, tests, projects, etc.

3. Create a junction table, StudentAssignment, to link students and assignments and store their grades.

To illustrate, let's set up the database. We'll use the TechHelp free template, available for download on my website. We start from scratch but sometimes use pre-set templates for ease.

First, create the Student table:

- Go to Create, then Table Design, and set up the table with fields like student ID and student name. Save it as the Student table.

- Populate the table with sample data by copying from an existing Excel list.

Next, create the Assignment table:

- Similar to the Student table, use Create and Table Design. Add fields like assignment ID and assignment name. Save it as the Assignment table.

- You can transpose a horizontal list from Excel to vertical if needed and copy it into Access.

Now, create the StudentAssignment table:

- Again, use Table Design for creating fields like student assignment ID, student ID, assignment ID, and grade. Save this table.

Entering data in Access can be more complex than Excel, but sample data helps in building the database. You'll link students and assignments through the junction table, and grades will be stored there.

Next, we create a data entry form with a main form for student information and a subform listing assignments and grades. We will also make a reverse form where the assignment is the main form with a list of students.

For the main student form:

- Copy a blank single form from your template and bind it to the Student table. Set the form properties accordingly.

For the subform:

- This will be a continuous form bound to the StudentAssignment table. It lists each student's grades for assignments. Remove unnecessary fields and set up the combo boxes to display names instead of IDs.

Finally, to avoid displaying raw IDs, use combo boxes for user-friendly data entry. I'll show how to create these combo boxes and bind them to the appropriate fields.

This completes today's tutorial. For a detailed, step-by-step guide, visit my website. Until then, live long and prosper, my friends.

For a complete video tutorial on this topic, please visit https://599cd.com/StudentsGrades?key=Blogger

Customizing the Quick Access Toolbar for a Single Database in Microsoft Access

 

Today's TechHelp tutorial from Access Learning Zone is geared towards beginners. We'll be discussing how to customize the Quick Access Toolbar (sometimes referred to as the Quick Launch Toolbar) for a single database in Microsoft Access. This topic comes from a question by Heather from Levittown, Pennsylvania, one of my Palette members. Heather wants to know how to set up the Quick Access Toolbar for different databases, each requiring unique buttons.

There are two ways you can configure the toolbar: for Access in general on your system, affecting every database, or for each individual database. If you haven't seen my initial video on setting up the Quick Access Toolbar, I recommend watching it first. In that video, I cover what it is and how to create a macro that runs and opens a form. I'll cover that briefly again today.

Let's start in my TechHelp free template database, which you can download from my website. I've already customized the Quick Access Toolbar to include buttons for actions like save, undo, redo, design view, running a query, datasheet view, form view, print preview, and opening the Visual Basic Editor. One button even opens the code for the form you're currently on.

Next, there is a customized button for my specific database. When clicked, it opens the main menu using a macro named "open main menu." On the Quick Access Toolbar, you can create buttons that execute macros to open forms, which is one of the few ways I use macros. Another frequent use is the auto exec macro.

Let's assume you have a specific form, like a customer form. In most of my databases, I have a main menu form named "main menu F." I typically copy the "open main menu" macro into each database, which covers almost all of my databases. However, not every database will have a customer form, so let's create a macro to open it.

First, make a copy of an existing macro. Rename it "open customer F" and modify it to open the customer form. Change the form name in the macro to "customer F," save, and close it. Running this macro will open the customer form.

Now, add this macro to the Quick Access Toolbar. Go to more commands, and you'll notice options for the toolbar in all documents and then for specific databases. In the specific database section, add the new macro. You can modify the button with a custom icon and name, like "Open Customers." Once added, you'll have a button that opens the customer form exclusively in this database.

If you open another database, the customized button won't appear because it's specific to the initial database. This ensures a tailored Quick Access Toolbar for each database you work with.

If you enjoy learning with me, check out my four-hour Access Beginner 1 class. It's comprehensive but fun and goes by quickly. There's also a condensed 30-minute version for those short on time. That's all for today's TechHelp tutorial. I hope you found it helpful. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

For a complete video tutorial on this topic, please visit https://599cd.com/QATForOneDb?key=Blogger

Passing a Form as an Object Variable to a Global Subroutine in Microsoft Access VBA

 

Today, I will show you how to pass a form as a variable to a subroutine or function in Microsoft Access. This allows you to send the actual form as an object to your code and modify it based on the received form.

Clara from Pureland, Texas, a platinum member, sent in a question. She asked if it's possible to create a global function to set the form's background color to gray when a record is not active. This is smart because it avoids duplicating code across multiple forms.

The goal is to use a global subroutine to prevent repetitive code. If all you need is to check if 'is active' is true and set the background accordingly, then the same code can be utilized across different forms.

First, you need to understand how to pass a form as a variable. Although you can technically use a function for this, a subroutine is sufficient because it does not return a value. Functions typically do return values. Both subroutines and functions are called procedures. However, for simplicity, I often refer to them as functions.

This topic came up in the forums as well. Richard Van Wagner had a similar query, trying to use "me" in a public global function. However, "me" only works within forms and reports, not globally. Adam, Kevin, and Sammy provided helpful inputs, and we concluded that passing the form as a variable is the way to go.

This tutorial is for those with some VBA experience. If you're new to VBA, I recommend watching a beginner tutorial first. The key concept here is using the "on current" event to trigger actions when you navigate through records or when the form opens.

First, let's work on this in the customer form. Open the customer form in design view. You want the code to run when you move from record to record and when the form opens. Insert it in the "on current" event.

In the "on current" event, check if the 'is active' field is not active. If it's not, set the form's background color to gray. Forms themselves do not have background colors, but their sections do. Specifically, the detail section's background color can be set to gray using RGB values. For instance, RGB(100, 100, 100) gives a medium gray. If the field is active, set it to another color, like blue.

After testing and verifying that the code works for one form, the next step is to make it work globally. Cut out the existing code from the form module and paste it into a global module. Name the subroutine, for example, "public sub change background." Ensure you move the "end sub" to the correct place.

During compilation, the code won't recognize "is active" or "me" because they are specific to the form. You need to tell the subroutine which form you want to apply changes to by passing the form as a variable.

Instead of using screen.active form, you should pass the form as a variable. Define the subroutine to receive a form variable, making it an object of type form. Use object variable references for fields and sections within that form accordingly.

After modifying the subroutine to use the form variable, compile it to check for errors. Then, in the form's "on current" event, call the subroutine with the "me" keyword, which refers to the current form.

Finally, test the updated customer form to ensure the changes apply correctly. The same subroutine can now be used for other forms. For instance, you can modify it to work with an "is paid" field in the orders form, and adjust colors as needed for different forms.

To explore additional concepts, such as restoring the default design color of each form or different field names, stay tuned for the next part of this tutorial. Members can watch it immediately.

For a complete video tutorial with step-by-step instructions, visit my website using the link below.

For a complete video tutorial on this topic, please visit https://599cd.com/PassingaForm?key=Blogger

Generate Sum of Last Orders by Customer in Microsoft Access

 

Today's TechHelp tutorial from Access Learning Zone addresses the concept of calculating the total of the last orders placed by each customer. I'm Richard Rost, your instructor, and today we're focusing on a query that can help you make future sales predictions by using the most recent order information of regular customers. Instead of relying on last month's total sales, which might include sporadic walk-ins, we're isolating data for those who consistently purchase each month.

One of our platinum members, Grant from O'Lake, Kansas, posed an interesting question: "How do I compile a total using only the most recent order from each customer?" This approach can impressively assist in estimating upcoming sales. I can certainly relate to Grant's needs, as I also analyze my members' activities similarly to forecast my own sales and filter out random transactions.

Let's break down the process involved. It's slightly advanced but doesn't require VBA, although I will share some VBA methods in the members' extended cut, which can sometimes simplify the task.

Firstly, ensure you're familiar with order entry and invoicing systems. I recommend checking my previous videos on building these systems if you're not already. This tutorial also requires an understanding of aggregate queries, so reviewing my DSum-related content is beneficial, too, as it shows how to display totals using DSum efficiently.

We'll construct a query listing each customer's maximum order ID and corresponding order totals. We'll then use DSum to calculate a complete total for the main menu. The completed task can be seen on my website and YouTube channel.

In our scenario with the TechHelp free template, every customer has associated orders that detail their purchases. Our goal is to sum all the most recent orders. Observing the table structure, you'll see that totals aren't stored directly in tables but determined through extended price calculations in order detail queries.

I'll adjust a few orders as examples. After manipulating the data to create scenarios where the date is a point of conflict, we'll proceed to build essential queries. It's crucial to adapt this to your own filtering preferences, like distinguishing between regular customers or specific payment statuses.

Our initial task is to extract a list of customers alongside their maximum order date. This involves creating a query, setting it as an aggregate query, and employing the max function to filter dates effectively. Using a descriptive name for this query will aid in maintaining clarity.

Next, we dive deeper by joining this result with the order summary to identify the corresponding order totals. Sometimes, having multiple identical dates for different orders might complicate things; hence we refine our query further, focusing on maximum order IDs per customer. Secure these detailed results with a second query, which helps resolve discrepancies without overwhelming one single query.

Finally, let's incorporate DSum to aggregate our query results for a synthesized total, showcasing each customer's latest purchase total. This sum can be effortlessly displayed on your main menu. Decomposing tasks into incremental steps is key; resist the urge to overcomplicate individual queries.

An alternative approach involves using a record set loop, looping through customers to obtain and accumulate their maximum order totals. This will be further explained in the extended cut available to members.

To explore additional examples of queries, joins, and functions, consider my Access Expert lessons. The program spans multiple levels and offers members enriching content that bridges non-programming practices with advanced Access functionalities.

In summary, this method aids in understanding and implementing a practical query system for better sales forecasting. For a detailed video guide on all the topics mentioned, visit my website through the link below. Live long and prosper, my friends.

For a complete video tutorial on this topic, please visit https://599cd.com/SumofLastOrders?key=Blogger

Align Text Boxes with Margins in Microsoft Access Forms: Dates and Currency

 

Today's TechHelp tutorial from Access Learning Zone focuses on a fundamental concept for beginners: aligning text boxes for numbers, dates, and currency so they appear neat and tidy. It's straightforward to align the text boxes themselves, but there's often extra space inside that can disrupt the alignment. The goal is to ensure everything looks well-organized.

Brandon from Tustin, California, one of my platinum members, reached out with a query about this. He has a form with a column of vertical text boxes, some formatted as currency and others as dates. The issue is that currency values leave additional space to the right, allowing for negative number parentheses, whereas date values do not. This misalignment makes the column appear disorganized. Brandon wants to know how to add space to the right of date values to achieve a neatly aligned column.

This can be easily addressed by setting a margin inside the text boxes for date or number values that require alignment adjustment. For currency data types, the space is already provided for negative parentheses. By adding a margin, rather than padding (which is different), we can resolve this discrepancy.

In my TechHelp free template, available on my website, I have a customer form with three aligned number fields. Personally, I prefer left alignment, but I understand the preference for right alignment. Let's take a moment to adjust these values for left alignment by accessing the format options.

If you're unfamiliar with the process, I usually keep my ribbon collapsed when recording videos for a cleaner view. You can double-click to expand it and maintain it open. Let's select the text boxes and apply right alignment. I'll save, close, and reopen to verify they look correct.

Now, observe the text boxes closely. The currency fields have additional space for potential negative parentheses, while others do not. To add a margin, enter design view. Select the text boxes by drawing a box that touches them, without encircling them completely. Access the property sheet, located at the bottom of the right-click options, and open it if necessary.

With the text boxes selected, navigate to the Format tab and locate margins (distinct from padding). We'll start by adding a small right margin, around 0.1, to see how it looks. Save, close, and reopen the form to check the result. If it's excessive, return to design view and adjust the margin to slightly smaller values, such as 0.09 or 0.08. Consider 'Twips' - a measurement unit - that may cause numbers to appear slightly altered. Adjust accordingly until the alignment is visually satisfactory.

These margin adjustments apply equally to left, right, and top margins. If this interests you, I cover various form formatting techniques in my beginner series. You can start with Microsoft Access Beginner Level 1, a four-hour free course introducing the basics. Beyond that, I offer beginner, expert, advanced, and developer lessons on my website.

That's today's TechHelp tutorial. I hope you found it instructive. Live long and prosper, my friends. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website.

For a complete video tutorial on this topic, please visit https://599cd.com/TextBoxMargins?key=Blogger

Detecting Duplicate Product Purchases by a Customer in Your Microsoft Access Databases

 

Today's TechHelp tutorial from Access Learning Zone is focused on a scenario where you need to detect if a customer has made a duplicate purchase of the same product - a situation that can often arise with memberships. If you're managing memberships, it's crucial to ensure that your customers don't inadvertently sign up for the same membership more than once, leading to double billing. This topic arose from a question posed by Phoebe in Roswell, Georgia, who wanted a way to identify if a customer accidentally signed up for the same membership more than once. 

In my own experience, I've had similar issues with memberships like Silver, Gold, and Platinum. Sometimes, a customer may join on a certain day and forget, only to sign up again later. While my website isn't programmed to prevent this, my database flags any duplicate memberships, enabling me to address any potential billing errors promptly.

Today, I'll guide you through a method to set up a routine that checks for duplicate purchases using Access queries. While this is an expert-level task, you don't need to know VBA; instead, you'll need familiarity with aggregate queries. If you're new to these, I recommend watching my introductory videos on summary queries and Access query criteria available on my website and YouTube channel.

In previous tutorials, I've demonstrated how to prevent duplicate entries on an order by employing composite keys. However, today's method involves using an aggregate query, which offers flexibility for exceptions and generates a report showcasing duplicates.

We'll start by inputting some data into a sample database. This free TechHelp template, available on my website, includes customers, orders, and products. Let's illustrate with an example: suppose a customer orders the same product, like a 'Silver Membership', twice. Using text fields or product IDs, we set up scenarios to test for duplicates.

Next, we'll design a query that brings together customer, order, and order detail information. The goal is to list each customer along with their product purchases. By implementing sorting options, you can easily identify duplicate entries.

The key task is to group this data by customer and product, enabling us to count occurrences of each product for each customer. This approach will highlight those individuals who have purchased the same item more than once. Applying the appropriate criteria will refine the list to show only duplicates.

Once you've adjusted the query to count occurrences, you can eliminate single entries. The query results will then only display customers who bought multiple quantities of the same product. This way, you can manage exceptions while keeping an eye on potential double billing issues.

I encourage you to explore further Access lessons on my website, where I cover a range of topics from beginner to advanced levels, including queries, form design, and database management.

For those wishing to follow this tutorial step-by-step, a complete video with in-depth instructions is available on my website. Live long and prosper, my friends.

For a complete video tutorial on this topic, please visit https://599cd.com/SameProductTwice?key=Blogger