Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Just checking if the relationship is correct.

Status
Not open for further replies.

BennyWong

Technical User
May 19, 2002
86
US
Hello All,
I am using Access 2000 to create a mailing list database. I am wondering if the table fields and relationship is correct. Here are the fields for the
table tblclient on the one side.
Client_id
FirstName
LastName
MiddleInitial
Title
ContactType
Organization
Alt1Address
Alt2Address
City
State
Zip
County
Region
Country
Entity1
Entity2
Entity3
FaxNumber
WorkPhone
CellPhone
EmailAddress
PracticeGroup
Source
LastUpdated
Comments
AgencyType

Here is the linking table fields on the many side tblcontacttype.

Contact_id
Client_id
ContactType

I also have some "Lookup" tables that I use to populate
the tblclient fields during data entry. Here are the lookup table fields:

tblAgencyType
tblCounty
tblClientStatus
tblSource
tblCountry
tblContactType

I have already defined all the fields for all the tables.
I have also created the Data Entry Forms and inserted some sample data. Please critique if you see something wrong or if another approach is better. Thank you very much for your assistance in advance.


 
I'm not sure what this means:
Here is the linking table fields on the many side tblcontacttype.

Contact_id
Client_id
ContactType


Also, you have Entity1 through three in tblClient. It's a bad idea to have repeating fields. If you ever have a client with four entities (whatever that is supposed to represent) you'll have to redesign your database. And if you have to search you'll have to do it three times. Instead, have
tblEntity
=========
ClientID
Entity

OR

tblEntity
=========
EntityID
EntityDescription

tblClientEntity
===============
ClientID
EntityID

depending on whether an entity can be assigned to more than one client. If you need to limit to three entities you'll have to do that through code, but it will be a more robust way of going about things.

For more about table design, check out the Fundamentals article on my web site.

Jeremy =============
Jeremy Wallace
AlphaBet City Dataworks

Take a look at the Developers' section of the site for some helpful fundamentals.
 
Hello Jeremy,
Thanks for your response! I really appreciate your input since I'm new to Access database programming. I will make the adjustment for the Entity 1 thru Entity 3. Also thanks for the link to AlphaBetCityDataworks.com for the Fundamentals of table design.
I still have another question and this if regarding
data entry / data edit. I have already created the form for
data entry for the user to enter data. However, if the user needs to edit the existing data, am I correct to use the same form for data entry but using macro and open the form as data edit mode? It looks like it is working fine the user can find the data and edit it however, the problem I'm having is on the field ContactType which is on the many-side and it is a multi-select listbox set to simple. I am able to have the user select multiple items of the listbox but on the edit side I can't figure out how he can either deselect or add new items. How or where can I find out how to get this feature for the user for the edit side of the data entry? Thanks for your help and look forward for any of your suggestion or advice. Thanks for your time.
 
If you want to be able to assign many whatever's I would say you'd be better off using a subform. If you use a listbox you'll have to write some vba code to translate between the table and the form.

Jeremy

PS: Even though you're just starting out, I would try to avoid macros as much as possible. Using VBA takes a bit more effort to learn but is MUCH more powerful. It's sometimes helpful to do something in a macro and then convert it to code (there's a menu function somewhere to do this), as that way you'll see what the code looks like. But other than that I would do everything in code. =============
Jeremy Wallace
AlphaBet City Dataworks

Take a look at the Developers' section of the site for some helpful fundamentals.
 
Hello Jeremy,
Thanks for the quick response! The data entry form is currently using a subform for the field ContactType. The user is able to enter data in the subform during data entry.
The user can edit the data in the subform in the data edit mode. However, the Management wanted the Multi-select listbox feature to minimize the tyos errors and speeding up the productivity of the user. I have not implemented this feature as of yet but have the "lookup table" field list for it. The code for the field Contacttype that I have is:private Sub cmdSave_Click()
Dim db As Database
Dim rs As Recordset
Dim ctl As Control
Dim itm As Variant

Set db = CurrentDb
Set rs = db.OpenRecordset("tblContactType")
Set ctl = Me.contactid

For Each itm In ctl.ItemsSelected
rs.AddNew
rs!ContactType = ctl.ItemData(itm)
rs!Client_id = Me.Client_id
rs.Update
Next itm

DoCmd.DoMenuItem acFormBar, acRecordsMenu,_ acSaveRecord, , acMenuVer70
rs.close
End Sub

I don't know how to code it for the data entry edit side where the user edit the data by either deleting or adding
thru the multi-select listbox.
I am learning VBA slowly but surely through a book titled:
Access 2002 Programming by Example by Bob Villareal - a QUE BOOK. Thanks for your advice I'll be coding in VBA if I can in the future. Thanks for your response and advice.


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top