Upgrade your account to a Community Supporter account and remove most of the site ads.

Reply to thread

Message: <blockquote data-quote="maransreth" data-source="post: 3543907" data-attributes="member: 4390">The following are a few different website pages that help decide the planning of a database and what is required:<a href="http://www.databasedev.co.uk/general.html" target="_blank">http://www.databasedev.co.uk/general.html</a><a href="http://office.microsoft.com/en-us/access/HA012242471033.aspx" target="_blank">http://office.microsoft.com/en-us/access/HA012242471033.aspx</a><a href="http://support.microsoft.com/kb/209534" target="_blank">http://support.microsoft.com/kb/209534</a><a href="http://www.allenbrowne.com/casu-06.html" target="_blank">http://www.allenbrowne.com/casu-06.html</a> (while the example is with students and grades, it helps explain database design)<a href="http://www.geekgirls.com/databases_from_scratch_1.htm" target="_blank">http://www.geekgirls.com/databases_from_scratch_1.htm</a> (this is quite good, esp when you continue the different parts)I used to teach Access, as well as other MS Programs, and Access and databases in general are difficult to understand if you dont understand the underlying concept. I would recommend looking at the templates that are found in Access and see if one of the Customer or Contact templates that will do a lot of the work for you.Before even touching Access you need to sit down and think of what you want the database to do, what information you need to store, what you want to get out of the database, etc.Then you write out all the tables. The easiest way of doing this is grouping like information together. So start with the main table - Contacts: what fields would it contain? Then get the other main groups of information, be it Events, Billing Info, Industries, etc and decide what fields they need.You then go through the normalising and decide what other tables need to be made, so that you dont double up information, find that you are repeating information in multiple tables, etc. The support.micorost link above gives examples on these. Basically information should only be typed into one table.To access that row's information in another table you need to have a relationship. To have a relationship however you first need a primary key, ie. a field that uniquely identifies that record. Eg a customer code/ID. That key field is then also found in the related field.So using your example below some customers will have a licensing agreement. So first you need to have a table of what the different agreements are. Each agreement must also have a field that uniquely identifies it. Now comes the relationship part of things. To simplify it there are 3 main relationship types - 1 to 1, 1 to many, and many to many. To decide what relationship type you have between two tables you need to ask the following question: "How many time will one of these items be found in the corresponding table?" After that you ask the same question, BUT to the other table.So continuing with your example - How many agreements could a customer have? 1 or many? How many customers will there be for each agreement? 1 or many? (more than likely multiple customers for each agreement).Now if you said that each customer will have at the most 1 agreement, you have a 1 to many relationship between customers and agreements. To create the relationship the table that has the many side (in this case the agreements) has its primary key (Agreement ID for example) found as a field in the Customer table (but not as the primary key field, as each customer is a unique record in the customer table).So the customer table will have the following fields for example - Customer ID, Customer Name, Address, City, State, Zip, Agreement ID (which is related to the Agreement table), etc.By having only the Agreement code in the Customer table, you dont have to type out all the words for the agreement, only the code that it refers to.Now all of this is the underlying table structure and relationship. To make your life and other people who will use the database life's easier you make forms that do all the work for you. On a form you will have a possible drop down box (called a combobox in Access) that lists either the Agreement names, Agreement codes or both and when a person choses the Agreement for that customer, it will put in the Code for that record of the Customer table.Are you still with me? To be honest I would do some research into who knows Access within your company or within your local area and pick their brains to help you. BUT make sure it is not someone who has dabbled in Access, but actually knows what they are talking about. It might cost you some money to get their help, but it will save you a lot of grief in the long run and your Access database will work.Access databases are finicky and there are a lot of settings and properties and code that can be done to do different things in the database to make your life easier, but if you dont know about them, you do things the long way or spend forever wondering why it isnt working.</blockquote>

Verification