Access tables 1

[Bob500]

Hi,

I am attempting to design a database and am working through the Microsoft Access tutorial. It is a little confusing and it would be great if someone could point me in the right direction.

I have a list of factories that have been sent some application forms, as these are received back all the information from them is entered such as dates for work to be done, names and addresses etc.

So would all this be on one table or would it be necessary to produce several tables ie. one for name + address and several tables with dates for a specific work.


Hope someone can help!
Thanks

 

[JeremyNYC]

Bob,

You'd have to give us more information for the help we give you to be of much use. You will definitely want more than one table, though.

The article that I recommend you read is Paul Litwin's "Fundamental's of Relational Database Design". After reading through that you should be able to at least take a stab at your table structrue. Once you do that, post back here with descriptions your data and each of your tables. From there we'll be able to give you more meaningful advice.

There's a copy of the article on my website.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[willir]

Bob500

Yep more information would be helpful.

When designing a database, spend some time on "normalization". I have seen so many "projects" spin wheels because of a poorly designed database.

Also, ask questions. What do I want to see when I am finished? What reports do I need? Etc. After all, the purpose of database is to turn data into information.

From your reports and other end product visioning, work backwards to what information you and how to store it.

Lastly, look at the relationships. For a solid database you need to know when to use a one-to-on, one-to-many and many-to-many. I find this criteria is important for the usefulness of the project.

For example, on a computer inventory database, the tables were originally designed with a one-to-many for users to devices. One guy can have a PC, a monitor and a UPS. I changed this to a many-to-many. The end result was when a curve was thrown my way, design accommodated the change, and added a lot more functionality.

Then play. I use index cards for this. Write down fictitious data into the tables (a card for each table), and then see if the schema, the way the information is stored will allow you to produce the "information" or end dcouments you need.

One more thing. Also plan for things such as archiving old data, obsolence, security, indexing, duplicate records, null records, etc,

Richard

 

[Bob500]

Hi, Thanks for the advice guys.

I have read the notes and investigated all the advice you have given me but I am still a little unsure how this particular database would be constructed.

I will try to explain this more fully:

I currently have an excel spreadsheet with over 40 columns, I believe that these columns can be split into 15 different tables. For example the first one would be applicant details in which is stored the name, address, and two unique application numbers, one of these unique numbers I would use as the primary key.

The rest of the 14 tables would be for unique forms that are systematicaly sent out and received so the data within the tables would mainly be dates that forms are sent out and received by. But there would be some notes and yes/no options within some of these tables as well.

So I make my tables (normalization) and for each table the foreign key would be the primary key I set in the applicant details table, all would be one to one relationships? As one site only submits one form (albeit with several pieces of data).

But, would it be easier just to leave them all in one table? or is that a big no no!

Thanks again

 

[JeremyNYC]

Does each applicant only ever use one copy of each form? If so, than one table would be fine.

Also, you say you'd have two unique numbers in the applicant table. Why? Why not just use one number, if it's unique?

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[Bob500]

The form is sent once and the subsequent information entered just once, so essentialy all I need is a big list of dates.

The two numbers are necessary as one has superceeded the other, but older documentation still refer to the old numbers so it is needed for reference.

So I will make one table with the 40 fields in it, and then just make a form so that the data is easy to enter.
But would it then be easy to run a query and check to see if some forms have not yet been received, or would I need to split the data into tables as discussed before?

Thanks again.

 

[JeremyNYC]

Bob,

It sounds like you're headed in the right direction. I would make sure that the form forces the entry of a date for each of the forms the applicant sends back in--if you're going to have data from the form, make sure the user enters the date that form was received. You can do this by disabling all of the fields that take data from a form, but not the fields that hold the date each form was received. When the user enters a date in a received box, enable the related fields for the data from that form. If the user delte's the date from that box, prompt, with a choice to bail, letting the user know that all the data in the related fields will be delted, if they continue.

That's how I'd go about it.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[willir]

Hi Bob

First, I have a lot of respect for Jeremy. He has a lot of bareknuckles, common sense experience.

Per your transaction date table, I believe I have a good understanding on what you are trying do, it makes sense to use one table for this task - it does fit the rules of normalization, and it seems similar to a database I had to redesign (because the initial design was mainly one large table). In my case, the database was tracking the migration of 900+ applications to Windows XP environment for a corporation. Here are some things I did...

- Status of activity was stored on the master table. Instead of a yes/no, I used a status field because it providing more options -- accepted / rejected / pending.

- On the master table, I also had an unbound field that flagged any alert based on the transaction table.

- The date transaction table had...
date, activity code, comments (memo or text field depending on whether you want to search on the field), and a hyper-link. The activity code described the purpose of the tranaction. For me -- start testing, testing complete, issues, customer acceptance, etc. The table also had a hyperlink to link to any external documentation.

- More on the tranaction table...
Depending on the need, I have also include a "sort order" of events. For example, a check list would have a sequence of tasks to be carried out in a definitive process. I also occasionally use an urgency or level field - "1 high" ... "5 low". These information fields allow me to grab or sort pertinent information from the transaction table without having to scroll through dozens of rows. With Access, you can also use these codes to trigger an event where the field is colour field is shown - "red" for alert.

- Support table -- techie tip...
Quite early in life, I got real tired of creating suppot tables for every combo or list box; I got tired of editing combo or list boxes to add a new value or edit an existing value. I have seen lots of code in this forum and others for adding to ... combo and list boxes. But before then I developed the following approach which I find works real well for me.
tblVari (Variable)
fldCTL - text (15)
fldName - text (8 to 25 depending on need)
fldDate - date to track when the (Optional)

fldID - autonumber (Optional - depends on whether you intend to assign the reference number or the actual fldName to the table. For example, using the autonumber would allow you to change "LetReceived" "Letter Received" for all transactions if you stored the fldID instead of the text. Advantages and disadvantages to either solution.

Index - this is key, regardless of using the autonumber or not, you have to set the fldCTL + fldName to unique, no nulls to prevent duplicate entries.

Why do I perfer this. I can use it for any field using a code. For example for your letters, for letter_code for fldCTL, and then for fldName = "received", "sent", "phone", etc. For the status of the applicant, fldCTL = "AppStatus" and fldName could be "Accepted", "Rejected", "Reference Check", etc.

- Another table.
Will you need another supporting table? For example, if you are tracking job applications, it might be useful to have a skills table. Since one person can have many skills and many people have this skill, you would probably need a "join" table to support the many-to-many relationship.

tblSkills
skill_id (autonumber)
skill_desc (text)

tblSkillSet
applicant_ID (foreign key to applcant master table)
skill_id (foreign key to Skills table)
skill_level_code (text!!! - "1 subject expert", "2 expert"
comments (memo or text)

The primary key for this would be applicant_id + skill_id

Then on the applcant master file, you can display the skills either in a list box or a subform.

There are many variations of the above -- and there may be better ways to do some of these things -- this is just my thoughts based on the infromation you have provided.

Richard

 

[willir]

Bob

My browser session locked up when I was trying to ammend the text. One other comment for the date transaction table would be to consider adding a text field or linked field to person sending / receiving the transaction. (Then you may need an employee or authorizer table)

And you may ask how can these codes be helpful -- depends on your needs. For example - using the sort or status fields, you can query all forms that required research, or all applications that have been completed, or applicantions handled by John Doe (who is on vacation).

Good luck.
Richard