Grow your Microsoft Access skills with TechRepublic’s Fast Track: Microsoft Access CD-ROM. Learn to use and maximize all that Microsoft Access has to offer with 75 complete lessons covering Access 97, 2000, and 2002 (XP).

TechRepublic
member Navjun recently asked the following question in the TechRepublic Technical
Q&A
forum:

“I’m making a guest list for my wedding
and I have done the following. One table with all the names and address and how
many people are in each family and a field that connects to a second table with
just table numbers in it. So this way I could go to this certain family and
choose a table number for them to sit, so I’m able to choose the same table
number for more than one family. Now I would like to make a query that would
tell me how many people are at each table and warn me if I go over a certain
limit.”

Based on Navjun’s description, I’m surmising that his
database structure looks something like this:

Family_Table

Family_ID

AutoNumber

Family_LastName

Text

Street_Address

Text

City

Text

State

Text

ZipCode

Text

Phone_Number

Text

Number_in_Family

Number

Table_ID

Number

Seating_Table

Table_ID

AutoNumber

Table_Type

Text

Capacity

Number

Closer to normalization

While
this setup is serviceable for Navjun’s purposes, a few slight changes to the
structure and the addition of another table will make the database more in line
with the rules of normalization, as well as make it easier to use for wedding
invitations, catering, thank-you cards, personalized table settings, etc.

Additionally,
Navjun may have large amounts of data in his current database that he isn’t
interested in retyping. Therefore, my proposed restructuring can be
accommodated with little heartburn.

Let’s
take a look at Figure A and see what
changes we can make.

Figure A

Tables

The first change is to create a new table called member_table.
This table will be used to store the actual members of each family and will
have a many-to-one relationship with the family_table. Don’t forget to create
the family_id field that will act as a foreign key to the family_table and
allow us to relate the two tables.

Next, we make a minor change. We eliminate the number_in_family
field in the family_table. We do this because we can now calculate the number
of family members by counting the number of records in the member_table that
share a common family_id.

That’s
all the changes required to get more functionality out of Navjun’s original
data structure. One could argue that you could further break down both the
family and member tables because the structure above doesn’t allow multiple
address information per family, or that some of the information in the family
table could be kept at the member level, etc. But this application is not a
contacts database per se, and the structure above suits the needs of the
wedding and then some.

One
other caveat: The current structure assumes that families will be assigned as a
group, and members cannot be split among tables (at least not without first
designating them as a separate family).

Form some queries

Now
that we have the structure, we’ll take a look at the queries necessary to get
our desired result—a list of the table assignments with a warning indicator for
any table that is over capacity. In order to get our desired result, we’ll need
two queries to base our final query on. The first is a query that counts the
number of members for each family and also displays the table to which they’re
assigned. Figure B shows the results
of this query.

Figure B

Query results

The
SQL used to derive the results looks like this:

SELECT DISTINCTROW [Family_Table].[Family_ID], [Family_Table].[Family_Lastname], [Family_Table].[Table_ID], Count(*) AS [Count Of Member_Table]FROM Family_Table INNER JOIN Member_Table ON [Family_Table].[Family_ID]=[Member_Table].[Family_ID]GROUP BY [Family_Table].[Family_ID], [Family_Table].[Family_Lastname], [Family_Table].[Table_ID];

In the SQL code, we’re selecting distinct rows (one row for
each Family_ID) from the family table, and then displaying the contents of the Family_ID,
Family_Lastname, and Table_ID columns.

Additionally, we’re using the expression Count(*) AS [Count
Of Member_Table] to count the number of records that make up each family from
the member table that we’ve linked to by an inner join on Family_ID. Lastly, we
added a Group BY statement to group our records.

We now need a query that combines information from the
family query above and the tables_description table to give us the results set
shown in Figure C.

Figure C

Results set

Here’s the SQL that was used to derive the results set in
Figure C:

SELECT [Tables_Description].[Table_ID], [Tables_Description].[Table_Type], [Tables_Description].[Capacity], [Family_Summary_Query].[Family_ID], [Family_Summary_Query].[Family_Lastname], [Family_Summary_Query].[Count Of Member_Table]FROM Family_Summary_Query INNER JOIN (Tables_Description INNER JOIN Family_Table ON [Tables_Description].[Table_ID] =[Family_Table].[Table_ID]) ON [Family_Summary_Query].[Family_ID] =[Family_Table].[Family_ID];

Now that we have our subqueries, we can use them in our
final query to get our list of table assignments. Figure D shows the results set we’re trying to achieve.

Figure D

Final results set

Here’s the SQL used to get the final results set:

SELECT DISTINCT td.Table_ID, td.Table_Type, td.Capacity, fsq.Family_ID, fsq.Family_Lastname, fsq.[Count of Member_Table] AS Member_Count, td.memcnt, IIF(td.memcnt>td.capacity,’*’,”) AS OverCapacityFlagFROM Family_Summary_Query AS fsq, [select table_id,table_type,capacity,sum([count of Member_table]) as memcntFROM [Tables_Description Query]Group BY table_id, table_type, capacity]. AS tdWHERE fsq.table_id = td.table_id;

As you can see in Figure D, our query gives us the results
that Navjun has asked for: a list that shows each table (Table_ID); its total
capacity (Capacity); which families are assigned to it (Family_ID and Family_Lastname);
the total number of members from each family assigned to the table (Member_Count);
and the total number of people assigned to each table (memcnt).

We also have a flag that’s set to indicate which tables are
overbooked. So, by looking at the query results, we can quickly see which
tables we need to address and which families (because of size) might be causing
the problem.

Note that some tables are duplicated because we’re showing
the various families assigned to the tables. Had we left out that information,
we would have only one row per table, but we would have had to refer to a
second report to see which families were at each table.

In order to dissect the query above, I need to point out a
shortcut. Aliases were used for some of the query names. For example, td is used as a shortcut for table_description_query,
and fsq stands for the Family_Summary_Query.

As in the queries above, we’re selecting distinct rows. This
time, they’re coming from the td and fsq queries where the table IDs are identical
(WHERE fsq.table_id = td.table_id). We’re also using an immediate if statement to create our over-capacity
flag (IIF(td.memcnt>td.capacity,’*’,”) AS OverCapacityFlag). Finally, we’re
grouping by table_id, table_type, and capacity.

I hope you don’t find this to be a terribly long-winded explanation
of how to accomplish what Navjun was asking. But this article does provide a
solution that Navjun can implement quickly without reengineering the whole
process. It also shows you the power of data normalization in database design.