Access DB Structure Problem

I have a VB6 front end reading from an Access 97 DB. The problem that I am running into may be multiple, then again it may not be. I am still learning how to use ADO, and this was my first task using it.
I was given an existing DB that had an Access front end, and I was told to convert it to a VB front end. The DB is one large table (approx 43 fields and growing). The first field is set to auto number, the next few fields are the typical contact information (Comp., Name, Address...). Then the last many (this keeps growing is why I say many) fields are all of the associated departments for each contact. I have wanted to break the DB up many times, but due to my lack of knowledge on how to do that I am uncertain as to where to begin. A contact can have many departments, and a department can have many contacts. I have even tried making the department fields actual records instead, but I am uncertain as how to associate them with each record.
The program actually works fine. I know before long it will start having problems if I do not fix it now. The only one that I have noticed so far is when there are multiple records of the same company with the same address, but a different contact person, when I view to see what departments are associated with that record it shows all of them for that company, and not just for that particular contact. If I change the DB structure will that fix this problem? Is it the way I am reading in the data?
Here is how load in all of the records initially:
Set connFile = New ADODB.Connection
Set rsDepartment = New ADODB.Recordset
connFile.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source= " & _
   App.Path & "\Christmas Card List.mdb; Mode = readwrite"
SQL1 = "SELECT * FROM [Departments]"
With rsDepartment
    .CursorLocation = adUseClient
    .CursorType = adOpenKeyset
    .LockType = adLockBatchOptimistic
    .Open SQL1, connFile, , , adCmdText
End With
'Populates the Listbox with the department names starting at the ninth column
For intCounter = 9 To rsDepartment.Fields.Count - 1
   lstNames.AddItem rsDepartment.Fields(intCounter).Name
Next intCounter
Like I said before this works great for all of the other records. Now here is how I read in to view what records are associated to the departments. Please note that all of the department fields are boolean.
Set rsStore = New ADODB.Recordset
rsStore.Open "SELECT * FROM Departments WHERE " & _
     "[Street Address] = True", connFile, , , adCmdText
Do Until rsStore.Fields("Street Address") = frmEnter.txtAddress.Text
For intCounter = 10 To rsStore.Fields.Count - 1
    If rsStore.Fields(intCounter).Value = True Then
        lstDepartments.AddItem rsStore.Fields(intCounter).Name
   End If
Next intCounter
In the above, the Street Address field is used because it is not a blank field, and I use it as a form of checking. I know that if the DB were broken up then I could match it other ways, since it is not, this is all I can do for now.
Since I work by myself, and have no one to bounce ideas off of except in places like this,  I need is a couple of things. First, how would I break a DB up like this one, and second, as far along as I am, how much recoding will it take to fix this? This is a pretty big program, and I know that eventually it will break if it is not fixed now.
Thanks in advance,
Who is Participating?

Improve company productivity with a Business Account.Sign Up

mdouganConnect With a Mentor Commented:

First off, there is no need to have records in the department_contacts table for contacts that are not in a department.

If you want to list contacts who are NOT in a department, your sql would be:

Select C.*
From Contacts C
Where not Exists
(Select D.Contact_ID
 From Department_Contacts D
 Where D.Contact_ID = C.Contact_ID

The result given the data above:
1             Bob Smith      555-1212 123 Main St.

If you want to list all departments a contact is NOT in:

Select D.*
From Departments D
Where not Exists
(Select DC.Department_ID
 From Department_Contacts DC
 Where DC.Department_ID = D.Department_ID

The result given the data above:
39            Finance        8887

If you want to select all departments and indicate with a "Y" or "N" if the contact is in that department:

Select D.*, "Y" as InDepartment
From Departments D
Where Exists
(Select DC.Department_ID
 From Department_Contacts DC
 Where DC.Department_ID = D.Department_ID


Select D.*, "N" as InDepartment
From Departments D
Where not Exists
(Select DC.Department_ID
 From Department_Contacts DC
 Where DC.Department_ID = D.Department_ID


So, you can see that you can display the Falses without having to store them in the database.  Only if you were going to be running queries like this over and over again would it be worthwhile to consider wasting the extra storage space in your database.

As far as populating the database, it's not too hard, except for the autonumber fields.  What you would do is first create your new tables as empty tables, and then in Access you'd create queries that are "append" queries.  You'd use your original table as the Source table, and then bring down to the query grid just the columns that correspond to the Contact (and for a second query the Department info) and Then under the Query menu, select the Append option.  Then, choose your new table name.  If your column names match in the old and new tables, then Access will automatically indicate which field in the new table will be populated by one of the fields in the old table that you brough down to the query grid.  But, you should double-check that the source and destination columns are correct.  Just make sure to leave out the column for the AutoNumber column

Once you run the query, your contacts table will be filled and all will have new keys.  Do the same for your departments table.

The only tricky part comes when you want to populate the department_contacts table.  If, say, the contact_name is unique and the dept_name is unique, you could have a query like this:

(this can be another append query)

If the name fields are not unique, then there is a way to use the primary key from the old table, but it's a little more work.......
You are correct in thinking that this is a bad structure and it's only going to get worse, and exponentially harder to work with.

You simply need to apply basic "relational" modeling to the structure.

You've already done this verbally by identifying the various "entities" and how they "relate" to each other.  So, given what you've said so far:

1             Bob Smith      555-1212 123 Main St.
2             Sally Jones    555-1212 345 Elm St.

66            Payroll        1256
39            Finance        8887

66            1            
39            2
66            2

Now, if the contacts are working for a specific company, then you probably need a company table, and then if contacts can only work for one company, you can put the COMPANY_ID column in the Contacts table.

To write the SQL for the code above you'd have queries like:

SELECT Departments.*
FROM Departments, Department_Contacts
WHERE Departments.DEPT_ID = Department_Contacts.DEPT_ID
AND Department_Contacts.CONTACT_ID = 2
order by DEPT_NAME

This would be to get all of the departments for a particular Contact.  Now, since you want to use the primary key (CONTACT_ID) as the field that you use to "join" the tables together, then it's helpfull when providing the user with a list of contact names, that you also store the CONTACT_ID.  The trick I use for this is to store the CONTACT_ID which is probably an autonumber field with a datatype of Long Integer in the combo or listbox's ItemData property.  Here is sample code for filling a combo:

While Not RS.EOF
   cboContacts.AddItem RS("CONTACT_NAME").Value
   cboContacts.ItemData(cboContacts.NewIndex) = RS("CONTACT_ID").Value

Then, in the combo's Click event, you can format the SQL using:

Dim sSQL as String

sSQL = "SELECT Departments.*"
sSQL = sSQL & " FROM Departments, Department_Contacts"
sSQL = sSQL & " WHERE Departments.DEPT_ID = Department_Contacts.DEPT_ID"
sSQL = sSQL & " AND Department_Contacts.CONTACT_ID = " & cboContacts.ItemData(cboContacts.ListIndex)
sSQL = sSQL & " order by DEPT_NAME"

Richie_SimonettiIT OperationsCommented:
Good explanation mdougan!
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Excellent explanation

RogerH1Author Commented:
Your description is great. In fact I started playing with it after I posted this message, and came up with a very similar layout as yours. The only difference is, the Department_Contacts also has a boolean field in it. I did this because I need to know what department is True or False for each contact. In your SQL statement I would make the WHERE statement at the end read "Department_Contacts.Boolean = True instead.

Now comes the big question:

How do I take an existing DB and make it into multiple tables? The problem is what is now fields will have to become records, and the boolean options below each of those fields will need to correlate to each of those records. I hope this all makes sense. The point is, will I have to basically recreate 680 records?  I hope that there is an easier way around this.

Oh, and if in your old table, you had 5 rows that had the same contact -- because that contact was in 5 departments, then in the Query, you are going to have to click on the "SUM" button, which will add a "Group by" clause to the query.  Just group by every contact field (for the contact query).  This will ensure that you only get unique contact records inserted into the table.
RogerH1Author Commented:
This definitely confirms what I was thinking. Plus it will help me with some of the SQL statements when dealing with multiple tables, which I have not dealt with before.

Thanks so much for your help,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.