im trying to set up an application but i have never used access before.
What i am trying to do is have a list of, Business name, business address, business contact name, engineer name, engineer contact number, and job description....i want to be able to enter all the information manually, but i also want the engineer names to appear in a drop down list and when the selected person is highlighted i want it to automatically input the contact number for that engineer. How do i go about this? have i bitten off more than i can chew for a first time user.....its really bugging me but i have tryed everything i can think of
The first step is to decide what data is needed, how it should be stored, part of the this process is called normalisation - effectivdly in each data store stor eonly information to that entity.
Paul Litwin, on Rudy937's site as a good article on normalisation which may be of interest
For now resist the temptation to dive into access and design your tables. Take a pencil & lots of paper
to give an example
you identify a requirement to add an engineer's name to a job, and include his / her / their / its phone number - so that means you have an entity / data store / tabel which stores inforamtion pertinent to an engineer. Normalisation comes in when you try to resolve what is truly relevant to that entity at that level -. The aim is not to duplicate information.
so say you engineer had the folliwing attributes
lump hammer size
you could argue that a person may have more than one telephone number or address, so the question is do yuou move the contact details to a sub table(s). only you can decide - you may elect to rule that an engineer can only have one phone number - fair enough its your table. but you should resist the tempatiuon to have, say work phone1, phone2, phone3, phone4 etc... if the same basic information type is being stored in the same table then its a clue that your data may not be optimised.
Other things you need to think about is how to display information - do you want your engineer to have a single name or surnames & forenames.
Your customers should be handled in a similar manner
A company has a single company name, but may have multiple addresses / locations - so that indictaes that you perhaps should consider having 2 tabels
a company also has employees (contacts) - do you have one contact per company, or mulptple contacts. if multiple contacts - is that one contact per location?
if the former then the link is off company name, if the latter then the link is off company address.
The key thing to think of is to identify specific data elements which are related and identified by a single element / identifier. if you need multiple elemetns to uniquely identify and storgae requirmenet then you may well have a problem which requires a another redesign..
Physical tabel design is an interative process hence why you need a lot of paper and pencil(s).