Why a Free Access Resource Site?

Does a Value Exist in a Table?

Does a Value Already Exist in the database?
Find out before trying to add it.

Before Adding a New Value, Check to See if It Already Exists

This is one of the more commonly asked questions: "Before I add a new value to a table I want to check whether it exists already. How can I do that?" I decided to show you how you might go about it. The sample database uses several validation methods, including the form's Error Event, to make sure values entered into to fields are valid and not duplicated before they are committed to the table.

Random Values, Variable Number Demo

If you've ever wondered how to avoid database bloat in Access databases, you need to check this one out.

Random Selection of "N" members in each Category

This little demo grew out of a question regarding a recordset from which the user needed to RANDOMLY select three records from each category. Normally that's possible with a subquery to select the Top 3 records to match. However, in this table, there was no primary key, and therefore, no way to order the subquery records so as to get the correct results. My solution was to generate a temp table with a randomly generated unique key for each record, from which the Top 3 subquery could select the right records. For the demo, I added support for selecting ANY number "N" of records per category.

However, in order to support the constant addition and deletion of records in a temp table without bloating the main accdb, I added support for creating an on-the-fly scratch accdb into which the temp table could be placed.

Create a DUAL table Demo

Here's a handy way to completely hide a table. And I do mean completely....

A Hidden "System-Like" table to support UNION queries in Searching Combo Boxes

Dual tables have characteristics similar to the ORACLE DUAL table:

a) They are hidden, so users are not tempted to use it for other purposes.
b) They consist of a single column and contain a single record.
c) They are not editable.

You can read about the reasons for creating this demo, which you can use to create your own DUAL tables, in my blog

Most of the code in this demo was created by my friend Banana Republic

Travel "Pack" Demo

One-to-Many data entry requires a main form/sub form approach.

One-to-Many in a main form/subform design.

This little demo is based on a travel scenario, where groups of people travel together, "in a pack". I did it for a poster at Utter Access. I am also including it here because it is a good illustration of the main form/sub form design required to manage one-to-many relationships in a transaction scenario. The transaction itself (the journey, or travel) is tracked in the one-side table, and the details of the transaction (the "pack members" traveling together) are tracked in the many-side table.

Singing Cowboys Demo

Many-to-Many relationships are often confusing for newbies.

Many-to-Many in a main form/subform design.

This little demo shows ONE way to enter records in all three tables in a many-to-many relationship. It was created in response to a thread at Utter Access. The content is recordings and artists. One artist can record many songs, one song can be recorded by many artists. The use of two main form/sub form pairs allows the user to enter either songs or artists or both and slip back and forth easily between them.

Multi-Field Filter Demo

Multi-Field Filter Demo.

A recent question at Utter Access was the motivation for this one. The poster needed a way to filter records on a form, but the standard methods he'd found in MS templates didn't cover the range he needed. After a while, it became a challenge for me to make it work for a variety of situations and the result is Multi-Field Filter Demo

I also included a second form to model the method I use to search for a record using a selecting combo box. This one if very common and there isn't anything too unique in my approach. I only included it because it IS so common and a good example seems worthwhile. Enjoy.

Tenant Billing Demo

Simple Tenant Billing Demo

Here's a demo I created for a question at Utter Access. The question had to do with billing tenants for charges pertaining to their leased properties. This simple demo shows how to track charges and payments in two different workflows, and how to calculate balance due. Of course, in my typical fashion I couldn't leave well enough alone and ended up with a fairly complex Union query for one  report. However, the basic queries are there and should be useful as guides.

Oh, this one only has an Access 2007 version. I decided to see how much of it I could build with just embedded macros. It worked out really well, I think. Simple Tenant Billing Demo

Previous Weekday Demo

Previous Weekday Demo

I uploaded a new demo database, available in both Access 2003 (mdb) and Access 2007 (accdb) formats.  The demo itself is fairly simple; it takes two arguments--a start date and a weekday--and returns the date in the prior 7 days on which that weekday falls. To me, the larger significance of the demo is that:

1) it's my first demo using Access 2007, and

2) it has some simple language swapping in it to change captions on controls on the forms from one language to another (English and Español only so far).