Newbie: Beginning steps?

[Lifesnadir]

Hopefully you won't mind my beginner's questions...

As I'm reading forums/tutorials/articles on the web for Access, I'm trying to understand terms first. So if I mis-use a term or use it as in HTML instead of Access, please point out my mistakes.

Eventually I want to create a (possibly complex?) database.
Being brand new to Access, I am concentrating on learning proper design (and the use of correct terms - LOL).

I want to create a database (or is that plural?) for a web site. As I look at what I want to do, I think I have multiple intentions for how I want the data used / presented.

The main intentionS would allow
-- users to search using several main categories (so each category could be viewed separately) -- such as one for companies, one for employees of those companies, etc;
-- users to pull up ONE long record on ONE individual, and this record would contain ALL parts of the main categories as it pertains to that ONE person (but each record would be long and I'm not sure if that is acceptable?);
-- a separate way that USERS/searchers could contribute missing data in the record.


Let's say I want to create a database of ALL workers who ever worked at ANY G-M Dealership, and I want to include specific details about the worker... as well as include specific details about each dealership... and have the MAIN search be by the worker's name and have it pull up ALL the info as one record (and also have some pieces be separate databases too).

So I might have these
Worker last name
first name
middle name
date of birth
date of death
worker street address
city
state
county
country
Dealership Name
street address
city
state
county
country
Years worked
plus a lot more

Is it possible to create such a beast? Would I need separate tables? each as a separate database? then link those through queries?


Am I supposed to put ALL these things on one long table, then make the pieces available to separate lists through query options?

Guess I'll stop here; enough questions for one post -LOL

I read that there is a Wizard to help split info up, but it must not have loaded with the program because it is missing on my computer. Would this Wizard help me understand my beginning steps?

Please point me to any good beginning tutorials.

 

[belovedcej]

I don't know anything about the web side of things - but db stuff I can help with.

Is it possible to create such a beast?  Would I need separate tables? each as a separate database? then link those through queries?

Yes this is possible, and it's not that bad, really. Absolutely you need separate tables (within a single database). Check out information on the web on "normalization" to build this. A basic tip is - if there could be more than one piece of info in a given category, it needs its own table.

For example: Addresses. Say you have a list of people (one table.). They could have a work and home address. So you need a second table that has a field in it which references the person ID (a unique identifier - could be an autonumber or could be assigned.)

Another important tip - if a piece of information is applicable to SOME records, but not to others, again make a separate table. For example: Again we are tracking people in one table. Some are members of a professional organization - some are not. You want to know the NAMES of those organizations. So you create a table called Organizations where each organization has its own unique key. Then you create another table that has a field for personID and organizationID. A record is only created when a person is a member of some organization.

Then, yes, you draw it together through queries.

Am I supposed to put ALL these things on one long table, then make the pieces available to separate lists through query options?

run from this idea hard and fast!! A flat file (which is what you would be creating) is a BEAR to query from. I speak from the miserable experience of having been handed a single table SQL database and being expected to generate multiple reports. Take advantage of relational database building and use multiple tables.

These are the most important issues now - forget about the web - forget about how this will "look." Think about what kind of information you need tracked and split it up into unique pieces of info.

Check out this link - see if it helps:

http://databases.about.com/od/specificproducts/a/normalization.htm

 

[Lifesnadir]

Hello belovedcej,

Since my post and your reply, I've been tweaking the design more. Parts of this db are easy; other parts still confuse me.

For today, just one question (promise! LOL). I need to be able to enter years of 18xx and 19xx as part of dates in this historical db. I don't care if it is short or long dates. Is there a way to change something (the input mask??) to allow years in the 1800s? such as 1897... I need the db to allow BOTH 1800s and 1900s (one record might be 1897, another record might be 1902).

Thanks again for your first reply, which has helped me a lot with the design! Hope you can help me with this date-years question too.

Lifes

 

[Golom]

On your "years" issue, your first step should be to go to "Control Panel / Regional and Language Options" and set both the Short and Long date formats to display 4-digit years. Access has no problem storing 4-digit years but you need to avoid any instance of entering 2-digit years.

Access will interpret any 2-digit year in the range 30-99 to be in the twentieth century and any year in the range 00-29 to be in the 21st century. To get values outside that range you must use 4-digit year specifications. You can use a masked edit box to require 4-digit years or you can look into calendar controls that allow the user to select a year, month and day, relieving you of the problem of having to validate the entered values.

As to the DB design issues

I see at least 3 separate tables in your example
[tt]
Employees - Identifies the employee
Dealerships - Has dealer info (name, address, etc.)
WorkedAt - A table that matches an employee to a dealership.
For example, Joe Blow may have worked at
several different dealerships during his
career.
[/tt]
The whole idea is that you want to record a piece of information only once and not over and over as would happen if you stored all the information about a dealership in the record for each eemployee that ever worked there.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]