Solved

Access DB Structure Problem

Posted on 2002-04-10
8
282 Views
Last Modified: 2010-05-02
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
 
rsStore.MoveFirst
 
Do Until rsStore.Fields("Street Address") = frmEnter.txtAddress.Text
    rsStore.MoveNext
Loop
 
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
    rsStore.Close
 
==============================================================
 
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,
Roger.
0
Comment
Question by:RogerH1
8 Comments
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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:

Contacts:
CONTACT_ID    CONTACT_NAME   PHONE    ADDRESS
1             Bob Smith      555-1212 123 Main St.
2             Sally Jones    555-1212 345 Elm St.


Departments:
DEPT_ID       DEPT_NAME      COST_CENTER
66            Payroll        1256
39            Finance        8887

Department_Contacts
DEPT_ID       CONTACT_ID
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
   RS.MoveNext
Wend

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"

0
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
Good explanation mdougan!
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Excellent explanation

mlmcc
0
 

Author Comment

by:RogerH1
Comment Utility
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.

Thanks,
Roger.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 18

Accepted Solution

by:
mdougan earned 100 total points
Comment Utility
Thanks!

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
 AND D.DEPARTMENT_ID = 39
)

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
 AND DC.CONTACT_ID = 1)

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
 AND DC.CONTACT_ID = 1)

UNION

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
 AND DC.CONTACT_ID = 1)

ORDER BY DEPT_NAME

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)
Insert Into DEPARTMENT_CONTACTS (DEPT_ID, CONTACT_ID)
SELECT D.DEPT_ID, C.CONTACT_ID
FROM DEPARTMENTS D, CONTACTS C, OLDTABLE OT
WHERE D.DEPT_NAME = OT.DEPT_NAME
AND C.CONTACT_NAME = OT.CONTACT_NAME

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.......
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
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.
0
 

Author Comment

by:RogerH1
Comment Utility
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,
Roger.
0
 
LVL 18

Expert Comment

by:mdougan
Comment Utility
Cheers!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now