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

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

Thanks for the comments Tom!

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:


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?

woops forgot to include the updated DB.
Database1.mdb

Scott, are you still there?

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

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?

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

did you check out the requested changes Scott? Whats next please?

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

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.

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?


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


am i in the wrong place to see these properties?

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.

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?

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.

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

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

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.

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.



What comes next?

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.

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

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.