Solved

Need help setting up solid table structure in my access 2010 Database

Posted on 2014-04-28
28
435 Views
Last Modified: 2014-05-06
Can someone please review my access 2010 DB table structure and ensure it is properly broken out and is associated correctly. I dont have much experience with this and need some help to ensure it is done properly before moving forward with further development.

Thanks in advance!
Database1.mdb
0
Comment
Question by:IT_Field_Technician
  • 16
  • 11
28 Comments
 

Author Comment

by:IT_Field_Technician
Comment Utility
initially i need 2 forms

form 1: client locations

Should have a dropdown box with the client name and the main office address fields with the ability to add several or more remote address locations per client record.


Form 2: Client Access

Should have 2 dropdown menus 1 for Client name and one for connection type:

All (all connection types)
RDP
ESXI
Router
0
 
LVL 6

Assisted Solution

by:ButlerTechnology
ButlerTechnology earned 50 total points
Comment Utility
Here's a few comments:
zip code is not a number -- Vermonters are aware of this fact.
Spaces are evil -- don't used them even if the system allows you to.
Client Name should be broken down in First and Last Name.

Your ESXI table needs a little work.  You have what is called repeating columns which are difficult to maintain.  You can easily reduce this down to just ID, CLientID....

This will allow you to have any number of ESXI servers for a client.

Tom
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
Thanks for the comments Tom!
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 450 total points
Comment Utility
What Tom means regarding your ESXI table is that you should not have "numeric" columns, like "ESXI Host Server 1 blah" and "EXSI Host Server 2 blah". You should instead have columns named:

InternalIP
ExternalIP
Username
Password

You also need a way to relate those records to the correct Client, so you'll need a ClientID column. That ClientID column will store the value from tblClientINfo.ID to the appropriate Client, for each ESXI record. So if I have this in tblClientInfo:

1  ABC Plumbing etc etc
2 XYZ Cleaning etc etc

Open in new window


And I then add this to my ESXI table:

1  198.162.1.1 177.77.23.23 Bob BobPass
1  196.162.1.3 177.77.23.24 Sam SamPass

Open in new window


Both of those records in the ESXI table are "related" to ABC Plumbing ...
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
zip code is not a number -- Vermonters are aware of this fact.

Done: Changed field type to text


Spaces are evil -- don't used them even if the system allows you to.

Done: replaced all field name spaces with underscores _


Client Name should be broken down in First and Last Name.

Done: Client name really represent company name and not the clients first and last name so i left this unchanged


Scott,

I have narrowed the field names down to the following in my ESXI table:

ESXI_Host_Server_Internal_IP_Address
ESXI_Host_Server_External_IP_Address
ESXI_Host_Server_Username
ESXI_Host_Server_Password

and i have added the ClientID column to my ESXI table but i have a few questions about the mechanics here when adding this:

q1
1) Do i keep the original "ID" Field or replace it with "ClientID"?
2) Do i set the clientID to auto-number?
3) where do i go and what is the step by step process of creating the association between the ESXI table and the tblClientInfo Table?
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
woops forgot to include the updated DB.
Database1.mdb
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
Scott, are you still there?
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 450 total points
Comment Utility
1) Do i keep the original "ID" Field or replace it with "ClientID"?

You'll need both - the EXSI.ID field is the Primary Key of that table, and ESXI.ClientID is the Foreign Key field that associates each record with it's "parent" in your ClientInfo table.

2) Do i set the clientID to auto-number?

No. ESXI.ClientID should be a standard Number field, not an AutoNumber field.

3) where do i go and what is the step by step process of creating the association between the ESXI table and the tblClientInfo Table?

Access will manage this for you if you set the correct Master/Child link fields for your Subform Controls. The "Master" field should be the Primary Key field of the Parent table (in this case, the ClientInfo table). The "Child" field should be the field in the child table that holds the value of Parent.Primary_Key_Field (in this case, the ESCI.ClientID field).

When you enter a new record in the ESXI table for a specific client, for example, Access will store the value from the Parent record in the Child record.
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
Understood Scott I have made the requested changes on the attached DB.

I guess we need to address the fact that i will need to have multiple addresses per client.

So i guess that means i need to break out all the address related fields from the clientinfo table and put it in its own table right?
Database1.mdb
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 450 total points
Comment Utility
Do you mean physical addresses? If so, then yes, you'd need to break that out, and you'd also need to relate the "connection" tables to those addresses instead of directly to the client.

So essentially a user would select a Client, then an Address, and the interface would then show all Connection info for the Client + Address
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
Yes the physical address - the client has other office locations where they will have completely different connection type data.

I am interested to make it so the user would choose a client name from a dropdown then the location from a dropdown and the connection type from a dropdown and the appropriate connection type that was selected would display on the form.

can you help me reach this goal?
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
OK Scott,

I have broken out the address info into its own table and called this table:

tblclientadddress

I have changed the name of the tblclientinfo.ID field to Client_Info_ID and have added this field to both the:

tblclientaddress
tblESXI

tables as a "number" data type field.

The requested changes are in the attached DB.

Whats next?

thanks!
Database1.mdb
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
did you check out the requested changes Scott? Whats next please?
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 450 total points
Comment Utility
You must "relate" the records in tblClientAddress to their respective records in tblClientInfo.

For example, if the record for 155 Awber Rd is associated with the client named "ABC Plumbing", then the record in tblClientAddress should have a value of 1 in tblClientAddress.Client_Info_ID.

These types of relationships are generally shown with a Mainform/Subform setup, so you should first create those forms.

First, create a form based on tblClientAddress. Be sure to include all the fields on the form. Save and close this form.

Next, create a form based on tblClientInfo. Add all the fields from tblClientInfo. Now drag/drop the form created above onto the form. Access will add a Subform control, and set the SourceObject to the name of the form you created above. The Subform control will have Master and Child link properties. Set them as such:

Master Field: Client_Info_ID
Child Field: Client_Info_ID

From this point, Access will automatically populate the correct value in tblClientAddress.Client_Info_ID for any new Address records you add.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:IT_Field_Technician
Comment Utility
Understood Scott and attached are the requested changes.

Concerning setting the master child fields I was unable to find where i could do that exactly  but after testing the form access has already done this for me automatically apparently when i dragged the subform onto the main form. pretty cool!

By the way i tried looking it up on youtube and on access 2010 help but didnt find it. So how exactly do i check and set the parent/child fields for a form???????

Other than that what is next?

Thanks!
Database1.mdb
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 450 total points
Comment Utility
The Master/Child links are properties of the subform control. Open the main form in design view and select the subform Control, then review the Properties of that control - you should see the Master/Child links.
0
 
LVL 84
Comment Utility
From here you would add subforms for the various connection types. You'd create forms based on the connection type tables and do roughly the same as you did for the Addresses - drop those forms on your main form, and review the Master/Child links for each to verify they're set correctly.
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
hmmmm i still dont see where the child links are what your referring too.

I have access 2010 and this is this what i am suppose to click on?

sub form properties
I dont see any child parent link details here under data tab:

data tab
am i in the wrong place to see these properties?
0
 
LVL 84
Comment Utility
It's not a property of the Form you're using as a Subform. It's a property of the Subform CONTROL that the parent form is using to show the subform.

A "Subform Control" is like any other control in Access (a Textbox, Combobox, etc). You place one of those on a form, and then you set the SourceObject of that control to the Form you want to use as a Subform, and set the Master and Child Field links.

So open the parent form, and select the Subform CONTROL. Open the properties of that control, and you'll see the Master and Child Field Names.Subform
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
ahhhh i got it - thank you very much for pointing that out as i would have never found that left to my own devices - LOL


So getting back to your directions:

From here you would add subforms for the various connection types. You'd create forms based on the connection type tables and do roughly the same as you did for the Addresses - drop those forms on your main form, and review the Master/Child links for each to verify they're set correctly.

1) Does this mean i need to add the "Client_Info_ID" Number type field to all of the connection type tables and then add them to the existing "frmclientinfo" form as a 2nd,3rd and 4th subform?
0
 
LVL 84
Comment Utility
A "connection" would be associated with a Client Address, not a specific client. So your connection would store the value of the tblClientAddress ID field, not the tblClientInfo ID field.
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
Understood, I have removed the tblClientInfo.ID number filed from the ESXI table and have put in its place the tblClientAddress.Client_Address_ID number type field.

I also added this same tblClientAddress.Client_Address_ID number type field to the GatewayRouter and RDP tables.

I am still confused on what to do next? Do I drag the tables onto the existing subform?
Database1.mdb
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
OK I believe I did what you requested and added the other 3 connection tables as subforms of the subform. I also checked to be sure that each link parent and link child fields had the Client_Addres_ID.

Whats next?
Database1.mdb
0
 
LVL 84
Comment Utility
You've essentially got it right, but you need to relate those Connection types to their associated address, the same as you related the Address to it's associated Client. You've got the field in the table, and now you need to modify that value to reflect the Client_Address_ID value of the associated record.
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
I'm unsure what you mean - i have already added the field to all connection type tables like you said and I have made sure that the Client_Address_ID is setup as the link master and link child fields for all connection type subforms.

d

What comes next?
0
 
LVL 84
Comment Utility
The value in tblESCXI.Client_Address_ID is not set, so none of the records in that table would be shown in your form. You must manually enter a value in that field at this point, since Access won't show you those records in the form.

You will NOT have to do this in the future. When you add new ESXI (or RDP, or any other connection records) Access will automatically fill in the correct value.
Master Child Relationship 2
0
 

Author Comment

by:IT_Field_Technician
Comment Utility
Understood and I have now added Id values so the data will be displayed in the form and i also added some new records as well and it seems to be working fine.

Whats next?

Does this conclude the DB structure answer?
Database1.mdb
0
 
LVL 84
Comment Utility
Yes, I'd say your question is answered.

From here you should identify what additional features you need, and work on adding those. If your basic data capture is working correctly - that is, you can add new Clients, Addresses and Connections, and you can edit existing ones - then you're pretty well done with this phase.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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

7 Experts available now in Live!

Get 1:1 Help Now