What’s in a Table?

Stu Alderman explores the structure of your Access database and discusses the relationship between Access objects and System tables.

I’m currently developing an application that imports several files from mainframe sources at the State of Nevada. The application uses a number of tables in a local Access database to clean up and normalize the data before it’s transferred to a SQL Server database. I’d been using a very good commercial tool to document my Access database designs from FMS, but at the time I needed the tool, the version for Access 2000 wasn’t available.  The result looks like this … read on Continue reading

Posted in Design and Tables | Leave a comment

Renaming Tables – and SQL Linked Tables

Renaming a Bunch of Tables

I am working on a conversion database that requires linking to lots of tables in lots of databases with exactly the same tables. I then need to rename the linked tables to remove the Suffix of 1 (that appears on all linked tables that are the same) and give the new linked tables a new prefix . Here is the code to do that

Code to strip out the DBO prefix on SQL Server linked tables

and for people doing conversions to SQL Server

Posted in Design and Tables | Leave a comment

Querying a Customer Survey Table

Peter Vogel answers a question about querying a customer Survey table.        

 I have a table that lists our customers. Some customers have agreed to participate in a survey and others haven’t. I need to calculate the number of participants who have and the number of participants who haven’t and give both numbers as a percentage of the total number of customers, grouped by city. Whether or not a customer has agreed to participate is indicated by a Yes or No in the Survey field. Is there an easy way to do this? I’m using two queries right now, and I’d like a simpler solution. Continue reading

Posted in Design and Tables | Leave a comment

Managing State Transitions

This month, Rebecca Riordan looks at two simple techniques for expanding the standard Lookup tables. First, she shows you how Lookup tables can handle hierarchies, but then she moves on to show how to manage state transitions–the movement of an entity from one state (married, for example) to another (separated or divorced). As you’ll see, even complex transition rules can be modeled using a very simple variation of the standard Lookup table.

Lookup tables are common in modern database applications, and their implementation is well-understood. So common and well-understood, in fact, that Microsoft added intrinsic support for Lookup fields to Microsoft Access (many database professionals consider Microsoft’s implementation ill-advised, but that’s a different issue). Continue reading

Posted in Design and Tables | Leave a comment

Managing Lookup Tables

Chris presents the Lookup Table Manager — a one-stop utility that allows data to be added into and removed from any of the lookup tables that populate your Access databases.

I build a lot of systems that use combo boxes to select data. Typically, the data for the rowsource of the combo box comes from a lookup table like the one in Figure 1 (this is a Country table that I use quite a lot). Table 1 shows the structure of the Country table, which is typical of my lookup tables. Continue reading

Posted in Design and Tables | Leave a comment