Thursday, June 30, 2011

Microsoft Releases Office 2010 SP1

Office 2010 Service Pack 1 has been released by Microsoft.  See MS Knowledgebase article: http://support.microsoft.com/kb/2460049.  The service pack can be downloaded from the article as well.

The main improvements to Access appear to be in the area of bug-fixes and stability.  There are only three improvements listed in the KB article, but in the downloadable fix list there’s a much longer list of fixes.

Everybody has different priorities, but to my mind, here are some of the more important problems that were fixed:

  • Access does not activate or return the user to the correct Ribbon tab for a previously opened database object when the user returns to that object.
  • Access Wizards are not loaded correctly when "Disable all controls without notification" is selected in Trust Center.
  • The program crashes when you apply a sort to a query that is based on a multi-value field.
  • "Reserved error -5500" occurs when you try to run a cross-tab query that would generate null values in the column names of the query.
  • "Object invalid or no longer set" error occurs when you try to use an ALTER TABLE query to change a field type or size.
  • You cannot relink tables in Access databases that have linked tables to other MDBs/ACCDBs that cannot be found
  • The file format that is displayed in the title bar for Access 2010 databases is "(Access 2007)."
  • Incorrect data is displayed when a user's query has a list that includes a combination of GroupBy and either OrderBy or Where
  • "Invalid precision for decimal data type" or results are truncated when the user runs a crosstab query.

There are also a lot of “crash fixes”, all of which are important.

Friday, June 24, 2011

New Sample: NormalizingRepeatingColumnsVBA.mdb

By Roger Carlson

This sample demonstrates how to normalize a table that has repeated columns with VBA.  It's purpose is to demonstrate the general principles of normalizing denormalized data from a spreadsheet with code rather than SQL statements.

Full Article Included

You can find the sample here:
http://www.rogersaccesslibrary.com/forum/normalizingrepeatingcolumnsvbamdb_topic567.html

Thursday, June 23, 2011

Normalizing Repeated Columns: VBA

In my earlier blog series (The Problem of Repeated Columns), I defined repeated columns and talked about the data integrity problems associated with them. I also showed several different examples of repeated columns and the how difficult it is to query repeated columns as compared to the normalized equivalent. If you haven't read these yet, it would be worthwhile to read first. Similarly, if you are not familiar with the concept of Normalization, you should read my blog series What is Normalization?

One of the comments suggested I should discuss how to convert a table with repeated columns into a normalized table structure. I thought that was a really good idea, so I'm going to spend the next few posts doing so. The difficulty in discussing this issue, however, is that the exact solution differs with each table, so there is no single solution. Fortunately, in the last series, I showed 5 different tables, each with a slightly different structure, so while I cannot show a single solution that will work for every table with repeated columns, hopefully, one of the following will work for most cases.

Others in this series:

In my previous examples, I used purely SQL solutions. This time, I thought I'd illustrate how to do this with VBA. There's no real advantage to using VBA over the SQL solutions. In fact, in large databases, it will almost always be slower. Nevertheless, there may be situations with complex data that VBA would be the best solution, and besides, it represents another tool in your Access toolkit.

In my post Aggregating Across Repeated Columns: Summing, I discussed an example of table with repeated columns that looked like this:

image
Figure 1: Student Scores table with repeated columns

Normalized to the First Normal Form (1NF) to remove the repeated columns, the table would look like this:
image
Figure 2: Patient Symptom table (1NF)

Figure 2, however does not represent the final form of normalization. Because the student names are repeated, they should be removed to a separate table. So normalizing to Third Normal Form (3NF) I would have something like this:
image
Figure 3: Normalized to Third Normal Form (3NF) - Tables and Relationships Views

(For a further explanation of both First and Third Normal Forms, see The Normal Forms , What is Normalization, and Entity-Relationship Diagramming).

Normalizing to First Normal Form (1NF)

I'll start with the 1NF because it's less complex. Overall, it's a matter of looping through the records in the denormalized table (i.e. StudentScores_RepeatedColumns) and writing each column value into a new record in the 1NF table (i.e. StudentScores_1NF).

Public Sub Normalize_RepeatedColumns_VBA_1NF(ParamArray FieldNames())

'   This routine writes data from a table with repeated columns into a
'   table normalized to the First Normal Form (1NF)
'   The field names which are send in via the parameter list of the call
'   are written into an array called FieldNames.

'   declare variables
Dim i As Integer
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset

'   Open database object to current database
Set db = CurrentDb

'   Open the denormalized table to read the values
Set rsSource = db.OpenRecordset("StudentScores_RepeatedColumns")

'   Open the normalized table write the values
Set rsTarget = db.OpenRecordset("StudentScores_1NF")

'   Loop through the denormalized Source table
Do While Not rsSource.EOF

    '   Loop through the fields, i.e. the values in the parameter array
    For i = LBound(FieldNames) To UBound(FieldNames)

        '   Add a New record to the target table, write the values,
        '   and save (update) the record
        rsTarget.AddNew

            '   write the student name
            rsTarget!StudentID = rsSource!Student
            '   write the field name
            rsTarget!TestNum = FieldNames(i)
            '   write the field value
            rsTarget!Score = rsSource(FieldNames(i))

        rsTarget.Update

     Next i
     rsSource.MoveNext

Loop
End Sub

You can call the subroutine like so:

Sub test1NF()
'   To run the subroutine, place the cursor in this sub and click "run"
'   The arguments are the field names of the columns you want to
'   normalize

  Call Normalize_RepeatedColumns_VBA_1N _
       ("Test1", "Test2", "Test3", "Test4")
   
End Sub

The end result looks like Figure 2 above.

Normalizing to Third Normal Form (3NF)

The process for normalizing repeated column data into 3NF is similar to the 1NF process. It does, however, require two loops, one to add records to the "one-side" table ("Student") , and an inner loop (For...Next) to write records to the "many-side" table ("StudentScores").

It is important to note that the order in which the data is moved is vital. Data must be written into the "one-side" table first, and then data can be moved into the "many-side" table. Overall, the process is to loop through the records in the denormalized table, write common values to the a record in the one-side table, store the primary key value from the new record, and then for each field in the parameter array, create a new record in the many-side table.

Public Sub Normalize_RepeatedColumns_VBA_3NF(ParamArray FieldNames())
'   This routine writes data from a table with repeated columns into two
'   normalized tables: Students and StudentScores
'   The field names which are send in via the parameter list of the call
'   are written into an array called FieldNames.

'   declare variables
Dim i As Integer
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTargetOneSide As DAO.Recordset
Dim rsTargetManySide As DAO.Recordset
Dim StudentIDtemp As Long

'   Open database object to current database
Set db = CurrentDb

'   Open the denormalized table to read the values
Set rsSource = db.OpenRecordset("StudentScores_RepeatedColumns")
'   Open the "one-side" table to write the values
Set rsTargetOneSide = db.OpenRecordset("Student")
'   Open the "Many-side" table to write the values
Set rsTargetManySide = db.OpenRecordset("StudentScores")

'   Loop through the denormalized Source table
Do While Not rsSource.EOF

    '   Add a New record to the "one-side" target table, save
    '   the primary key value (autonumber) for use later,
    '   and save (update) the record
    rsTargetOneSide.AddNew

        rsTargetOneSide!Student = rsSource!Student
        '   save the StudentID created by the autonumber field
        StudentIDtemp = CLng(rsSource("StudentID"))

    rsTargetOneSide.Update
   
    '   Loop through the fields in the Parameter Array
    For i = LBound(FieldNames) To UBound(FieldNames)
   
        '   Add a New record to the "many-side" target table,
        '   write the values, and save (update) the record
        rsTargetManySide.AddNew

            '   write the saved student id
            rsTargetManySide!StudentID = StudentIDtemp
            '   write the field name
            rsTargetManySide!TestNum = FieldNames(i)
            '   write the field value
            rsTargetManySide!Score = rsSource(FieldNames(i))

        rsTargetManySide.Update
       
    Next i
    rsSource.MoveNext

Loop
End Sub

You can call the subroutine like so:

Sub test3NF()
'   To run the subroutine, place the cursor in this sub and click "run"
'   The arguments are the field names of the columns you want to
'   normalize  

  Call Normalize_RepeatedColumns_VBA_3NF _
      ("Test1", "Test2", "Test3", "Test4")
   
End Sub

The end result looks like Figure 3 above.

You can find a sample which illustrates the above here:
http://www.rogersaccesslibrary.com/forum/normalizingrepeatingcolumnsvbamdb_topic567.html

Friday, June 17, 2011

New Sample: Form_SynchronizedSubforms

By A.D. Tejpal

This sample db demonstrates synchronized scrolling of two subforms (both in datasheet view).

Two modes are covered:
    (a) One way synchronization: Top subform always functions as the master while the other one serves as the slave.
    (b) Two way synchronization: Whichever subform happens to be the active one, functions as the master while the other one serves as the slave.

Note: For ready identification, the subform currently serving as the slave, has a darker back color as compared to the master.

For each of the above modes, three alternative styles of scroll synchronization are demonstrated:
    (a) Synchronize horizontal scroll only.
    (b) Synchronize vertical scroll only.
    (c) Synchronize both horizontal and vertical scroll.

Sample data depicts student's scores in phase 1 (top subform) and phase 2 (bottom subform). For each student, wherever the scores in these two subform happen to differ, the same get highlighted as follows:
    (a) Top subform: Light grey.
    (b) Bottom subform: If value is greater than that in other subform, it gets highlighted in light green. On the other hand, if value is less than that in other subform, it gets highlighted in light pink.

Note: The above highlights get suitably updated promptly on editing of data in either of the two subforms.

Version:  Access 2000 File Format

You can find the sample here:
http://www.rogersaccesslibrary.com/forum/Form-synchronizedsubforms_topic566.html

Wednesday, June 15, 2011

New Sample: Query_ComputeAcrossFields_CodeLess

By A.D. Tejpal

    This sample db demonstrates computations across fields via pure SQL. In the first step, normalization of data is accomplished, using Cartesian join between the source table and an ancillary single field table holding the field names. Thereafter, a totals query provides the desired results.

    Two styles are covered:
    (a) Compute across all fields.
    (b) Compute across top 3 fields (i.e. fields holding top 3 values).

Version:  Access 2000 File Format

You can find the sample here:
http://www.rogersaccesslibrary.com/forum/query-computeacrossfields-codeless_topic565.html

New Sample: NormalizingRepeatingColumns3.mdb

By Roger Carlson

This sample demonstrates how to normalize a table that has repeated Yes/No columns.  It's purpose is to demonstrate the general principles of normalizing denormalized data from a spreadsheet.

Full article included

You can find the sample here: http://www.rogersaccesslibrary.com/forum/normalizingrepeatingcolumns3mdb_topic564.html

Tuesday, June 14, 2011

Normalizing Repeated Columns: Yes/No Fields (Part2)

Last time, in Normalizing Repeated Columns: Yes/No Fields (Part1) I talked about Normalizing data in repeated columns from a spreadsheet, like this:

image

Into a structure like this:

image

image

Using a One-Time process that creates the tables and relationships in addition to copying the data.  This time, I’ll talk about appending data to existing tables.

 

Appending to Existing Tables

The process for normalizing repeated column data into Existing Tables is similar to the One-Time process. It does, however, require you to create the tables and relationships correctly ahead of time. Because I've just created them in the previous section, I'll simply delete the data and re-append it from the linked spreadsheet.

The two processes are very similar, but I'll be using Append queries rather than Make-Table queries.

Step 1: Remove the common Patient elements.

Since I've already got a table, I'll use an Append query rather than the Make-Table query I used earlier:

INSERT INTO Patient ( Patient )
SELECT Patient
FROM Patient_RC
GROUP BY Patient;

Or in the Query Builder:

image

The resulting table looks like this:

image

Since I already have an autonumber primary key defined on the table, I don't need to do anything further.

Step 2: Create Symptom_Temp table

This step is exactly the same as Step 2 above. I'll use a Make-Table query to create a temporary table that will be used for creating the relationships later.

SELECT Patient, IIf([cough],"Cough") AS Symptom
FROM Patient_RC WHERE [Cough] = True
UNION ALL
SELECT Patient, IIf([Sneeze],"Sneeze") AS Symptom
FROM Patient_RC WHERE [Sneeze] = True
UNION ALL
SELECT Patient, IIf([Fever],"Fever") AS Symptom
FROM Patient_RC WHERE [Fever] = True
UNION ALL
SELECT Patient, IIf([Body_Aches],"Body aches") AS Symptom
FROM Patient_RC WHERE [Body_Aches] = True
UNION ALL SELECT Patient, IIf([Nausea],"Nausea") AS Symptom
FROM Patient_RC WHERE [Nausea] = True
ORDER BY Patient, Symptom;

As I showed in the previous post, there are two ways to make this into an Append query: 1) a Stacked Query, and 2) All In One.

Stacked Query

A stacked query is simply a query which uses another query in the FROM clause instead of a table. So if I save the above query as "qryTemp", I can use that query in the FROM clause of an Append query:

INSERT INTO Symptom_temp ( Patient, Symptom )
SELECT Patient, Symptom
FROM qryTemp;

Or in the Query Builder:

image

All-in-one Query

It's also possible to it in a single query. To do that, I surround the SQL of qryTemp in parentheses and use it in the From clause of the Append query. Like this:

INSERT INTO Symptom_temp ( Patient, Symptom )
SELECT qryTemp.Patient, qryTemp.Symptom
FROM
(SELECT Patient, IIf([cough],"Cough") AS Symptom
FROM Patient_RC WHERE [Cough] = True
UNION ALL
SELECT Patient, IIf([Sneeze],"Sneeze") AS Symptom
FROM Patient_RC WHERE [Sneeze] = True
UNION ALL
SELECT Patient, IIf([Fever],"Fever") AS Symptom
FROM Patient_RC WHERE [Fever] = True
UNION ALL
SELECT Patient, IIf([Body_Aches],"Body aches") AS Symptom
FROM Patient_RC WHERE [Body_Aches] = True
UNION ALL
SELECT Patient, IIf([Nausea],"Nausea") AS Symptom
FROM Patient_RC WHERE [Nausea] = True
ORDER BY Patient, Symptom)
AS qryTemp;

The result will be a table that looks like this:

image

Step 3: Remove Duplicates from Symptom_Temp

To fulfill 3NF, I need to create a query that removes the duplicate Symptom values into the Symptom table. The GROUP BY clause works well for that. . As before, I'll use an Append write the data to my existing Software table:

INSERT INTO Symptoms ( Symptom )
SELECT Symptom
FROM Symptom_temp
GROUP BY Symptom;

Or in the Query Builder:

image

With the resulting table:

image

Again, this table already has an autonumber primary key, so I don't need to create one.

Step 4: Creating the Linking Table: Patient_Symptoms

So now, I've got my two "One-Side" tables: Patient and Symptoms. Now I just need to fill the linking table.

I can do that with an Append Query and a simple join of Patient, Symptoms and Symptom_Temp:

INSERT INTO Patient_Symptoms ( PatientID, SymptomID )
SELECT Patient.PatientID, Symptoms.SymptomID
FROM (Patient INNER JOIN Symptom_temp ON Patient.Patient = Symptom_temp.Patient)
INNER JOIN Symptoms ON Symptom_temp.Symptom = Symptoms.Symptom;

Or in the Query Builder:

image

The final result will look like this:

image

It is important to note that the order in which the data is moved is vital. Data must be written into the "one-side" tables (Patient and Symptoms) first, and then data can be moved into the "linking" tables (Patient_ Symptom).

This two-part post is available as a single download (with sample database) here:
http://www.rogersaccesslibrary.com/forum/topic564.html

Tuesday, June 7, 2011

Normalizing Repeated Columns: Yes/No Fields (Part1)

In my earlier series (The Problem of Repeated Columns), I defined repeated columns and talked about the data integrity problems associated with them. I also showed several different examples of repeated columns and the how difficult it is to query repeated columns as compared to the normalized equivalent. If you haven't read these yet, it would be worthwhile to read first. Similarly, if you are not familiar with the concept of Normalization, you should read my blog series What is Normalization?

One of the comments suggested I should discuss how to convert a table with repeated columns into a normalized table structure. I thought that was a really good idea, so I'm going to spend the next few posts doing so. The difficulty in discussing this issue, however, is that the exact solution differs with each table, so there is no single solution. Fortunately, in the last series, I showed 5 different tables, each with a slightly different structure, so while I cannot show a single solution that will work for every table with repeated columns, hopefully, one of the following will work for most cases.

Others in this series:

Patient Symptoms

In my post Querying Repeated Columns: Multiple IIFs, I discussed an example of a Patient table with repeated Yes/No columns that looked like this:

clip_image001
Figure 1: Patient table with repeated, Yes/No columns

Normalized to remove the repeated columns, the tables relationships would look like this:

clip_image002
Figure 2: Relationships for the Patient Symptom tables.

So how do I get the data from Figure 1 to Figure 2? Well, first of all, it depends on whether this is a one-time process where you are creating new normalized tables, or whether it is an on-going process where you are appending the data to existing, normalized tables.

In either case, however, not only do I have to split the records into separate tables, I also have to preserve the relationships between those records, so I have to do it is a specific order.

One-Time Process

Suppose I'm given a spreadsheet of data like this:

clip_image003

And I need to create a normalized database from it. I've already determined the tables I need (Figure 2 above), but if you're not certain what your table structure should be, you should read through my blog serie: What is Normalization, Entity-Relationship Diagramming, and The Normal Forms.

Link the spreadsheet into a database.

First of all, I need to get the spreadsheet into the database. I find it is preferable to link rather than import the file. Since I'm not going to be changing the data, there's no reason to import it. Once the file is linked it acts just like a table, and I can begin the process of normalizing it.

Step 1: Remove the common Patient elements.

Since I need to create a new table (called Patient) to hold these values, I'll use a Make-Table query:

SELECT Patient_RC.Patient INTO Patient
FROM Patient_RC
GROUP BY Patient_RC.Patient;

Or in the Query Builder:

clip_image004

The resulting table looks like this:

clip_image005

At this point, I need to add a Primary Key to the table. I prefer to use Surrogate Keys. (For a discussion of natural vs. surrogate keys, see my blog post: What is a Primary Key?)

Add surrogate key to PC table.

Adding a surrogate key to a table is as simple as opening it in Design View, adding an Autonumber Field, and making it the Primary Key.

clip_image006

Saving the table will automatically fill the autonumber field with values:

clip_image007

Step 2: Create Symptom_Temp table

As I showed in my post Querying Repeated Columns: Multiple IIFs it is necessary to combine multiple UNION queries with multiple IIF statements to create a list of Symptoms. Something like this:

SELECT Patient, IIf([cough],"Cough") AS Symptom
FROM Patient_RC WHERE [Cough] = True
UNION ALL
SELECT Patient, IIf([Sneeze],"Sneeze") AS Symptom
FROM Patient_RC WHERE [Sneeze] = True
UNION ALL
SELECT Patient, IIf([Fever],"Fever") AS Symptom
FROM Patient_RC WHERE [Fever] = True
UNION ALL
SELECT Patient, IIf([Body_Aches],"Body aches") AS Symptom
FROM Patient_RC WHERE [Body_Aches] = True
UNION ALL SELECT Patient, IIf([Nausea],"Nausea") AS Symptom
FROM Patient_RC WHERE [Nausea] = True
ORDER BY Patient, Symptom;

Note: You can't create or view this query in the Query Builder, however, you can create the first on (Cough) in the QB, then switch to the SQL View and copy and paste, modifying each for the specific column.

I still need to make this query into a Make-Table query. There are a couple of ways to do this. I can use a stacked query or all-in-one query.

Stacked Query

A stacked query is simply a query which uses another query in the FROM clause instead of a table. So if I save the above query as "qryTemp", I can use that query in the FROM clause of a Make-Table query:

SELECT Patient, Symptom INTO Symptom_temp
FROM qryTemp;

Or in the Query Builder:

clip_image008

All-in-one Query

It's also possible to it in a single query. To do that, I surround the SQL of qryTemp in parentheses and use it in the From clause of the Make-table. Like this:

SELECT Patient, Symptom INTO Symptom_temp
FROM (SELECT Patient, IIf([cough],"Cough") AS Symptom
FROM Patient_RC WHERE [Cough] = True
UNION ALL
SELECT Patient, IIf([Sneeze],"Sneeze") AS Symptom
FROM Patient_RC WHERE [Sneeze] = True
UNION ALL
SELECT Patient, IIf([Fever],"Fever") AS Symptom
FROM Patient_RC WHERE [Fever] = True
UNION ALL
SELECT Patient, IIf([Body_Aches],"Body aches") AS Symptom
FROM Patient_RC WHERE [Body_Aches] = True
UNION ALL
SELECT Patient, IIf([Nausea],"Nausea") AS Symptom
FROM Patient_RC WHERE [Nausea] = True
ORDER BY Patient, Symptom) AS Temp;

(Note: This cannot be done in the Query Builder)

The result (in either case) will be a table that looks like this:

clip_image009

Step 3: Remove Symptoms to Symptom Table

To fulfill 3NF, I need to create a query that removes the duplicate Symptom values into their own table, which I will call Symptom. The GROUP BY clause works well for that. As before, I'll use a Make-Table query to create a new table to hold the software values:

SELECT Symptom INTO Symptoms
FROM Symptom_temp
GROUP BY Symptom;

And as before, I'll add an Autonumber primary key, so the table looks like this:

clip_image010

Step 4: Creating the Linking Table: Patient_Symptoms

So now, I've got my two "One-Side" tables: Patient and Symptoms. Now I just need to create the linking table.

A "Linking table" is a mechanism by which Many-To-Many relationships are built in a relational database. (See: What is Normalizion Part V for more information.)

I can do that with a Make-Table Query and a join of Patient, Symptom_Temp, and Symptom:

SELECT CLng([Patient].[PatientID]) AS PatientID,
    CLng([Symptoms].[SymptomID]) AS SymptomID
INTO Patient_Symptoms
FROM (Patient INNER JOIN Symptom_temp
    ON Patient.Patient = Symptom_temp.Patient)
        INNER JOIN Symptoms
            ON Symptom_temp.Symptom = Symptoms.Symptom;

Or in the Query Builder:

clip_image011

Notice the expressions in the SQL Statement and the Query Builder: CLng([Patient].[PatientID]) and CLng([Symptoms].[SymptomID]). These are necessary because the Make Table query will attempt to create both PatientID and SymptomID as autonumber fields. Since a table cannot have two autonumber fields, this will give me an error.

Since they are both Foreign Keys, I don't want either to be autonumber. (A foreign key should NEVER be an autonumber.) So I use the CLng() function to convert them to the Long Integer datatype, which is the datatype which should ALWAYS be used to a foreign key to an autonumber primary key.

This will give me a table with two fields: PatientID and SymptomID. I'll also want to make these fields a compound primary key:

clip_image012

The final result looks like this:

clip_image013

The final step is to create the relationships between the three tables: Patient, Patient_Symptoms, and Symptoms.

Step 5: Create the Relationships

The easiest way to create relationships in Access is to use the Relationship Window. Add the four tables to the Relationships Window:

clip_image014

Click and drag PatientID from the Patient table to PC_ID in the Patient _Symptoms table. In the pop-up window, choose the Enforce Referential Integrity box:

clip_image015

And click Create. Do the same for SymptomID between Symptoms and Patient _Symptoms. The final result will look like this:

clip_image016

clip_image017

Next Time:

Next time, I’ll finish up by importing the spreadsheet to existing tables:
Normalizing Repeated Columns: Yes/No Fields (Part2)

Thursday, June 2, 2011

New Sample: NormalizingRepeatingColumns2.mdb

By Roger Carlson

This sample demonstrates how to normalize a PC_Inventory table that has two sets of repeated columns.  It's purpose is to demonstrate the general principles of normalizing denormalized data from a spreadsheet.

Document Included

You can find the sample here: http://www.rogersaccesslibrary.com/forum/normalizingrepeatingcolumns2-beginner_topic563.html