New Database Help

[saip15]

Hi guys,

So I don't know if I am over my head yet but I'm sure trying and learning. I would greatly appreciate any help.

So here is the situation:
I have been given a couple tasks but i think I need to focus on one portion of it and get it running like we want. I have attached an example of the form that we use to fill out information. We want the information from the form to be able to be searched which brings up the changes, types of changes, engineers, etc. in a query. These are internal forms that our employees need to fill out for different jobs. So pretty much a data entry form for our department.

This is what I have so far:
I have a table with a list of about 40 employees; ID, Name, Email, Phone, Office. Trying to keep it simple at first. I have one form which is the one I have attached. I have drop down boxes for each 6 of the employees with the specific role they have in the project. The top box uses the employees table for the list. The other drop boxes I just entered the names since there were only a few in each one. Now this is where I am stuck. What is my next step? I'm guessing I will have mostly all text boxes now that will store the information that gets entered. I just need help setting one up and I can do the rest but i have no idea how to do it properly. Do I make the blank tables? How do i make a FrontEnd and BackEnd? How do i get the form useful for the employees to enter data? These are just a few questions I wanted to know. There not really spelled out in tutorials to well.

Sorry for being such a noob! Thanks so much for any help! Let me know if you need any more information.

 

[jzwp22]

The absolute most critical step in setting up a database is creating a normalized table structure (tables with fields and the relationships between the tables). At this point, you should not even be thinking about forms. There are many references to normalization; here is just one link to get you started. There are probably hundreds of others.

Before you can even think about creating a normalized database structure, you have to know everything about the process you are trying to model. I usually map out the process either in my head or on paper.

So can you tell us exactly what you are doing in detail? Then we can try to help you with mapping out your process and then creating a normalized structure from there.

 

[saip15]

Thanks jzwp22!

Alright, Taking a step back. I think I should focus only on the Drawing Change Form that I attached on the first post. Drawings get reviewed and this form is used to track those changes, who is suggesting the change, and whether or not it should be made. Before, these forms were filled out in Word and printed out and attached to the actual drawing. The problem is when there are 500 hard-copy drawings it is hard keep track of all the changes. I want to make this form available for the employees to use and in turn construct a database from the data that is compiled. The main purpose for the database is to quickly and effectively search for specific changes and drawings instead of searching through a pile of drawings. So a Data Entry Database.

Here's the information that is on the form:

Table 1:
Drawing Number:
Drawing Title:
Current Revision:
Project:
Task ID/Description:

Table 2:
Recommending Engineer:
Logistics:
Engineer1:
Engineer2:
System Engineer:
Lead Engineer:
Initial / Date:

Table 3:
-Sheet Number - Description of Change - Remarks/Initial & Date
-Concur/Non-Concur - Not sure how I should work those with entering data.

So each one of these fields would need an input.

So all of the information on a single sheet will be related.
Task ID/Description>Project>Current Revision>Drawing Title>Drawing Number
All people reviewing are related to the drawing.

So it likes the Drawing Number should be the most important and focused on since everything on a form relates back to which drawing is being reviewed.

Is that a good start or do you guys need some more information? I'm going through the normalization process now. Thanks again!

 

[jzwp22]

You said this:

I think I should focus only on the Drawing Change Form

Don't focus on the form, you need to focus on your process:

You create drawings and drawings can change and those changes are captured as revisions. In database lingo that says that a drawing has many revisions which describes a one-to-many relationship. Each revision has to be reviewed/approved/rejected by several people.


tblDrawings
-pkDrawingID primary key, autonumber
-DrawingNumber

tblDrawingRevisions
-pkDrawRevID primary key, autonumber
-fkDrawingID foreign key to tblDrawings
-RevLevel
-fkEmpID foreign key to tblEmployees (the person responsible for the revision)

Each revision requires multiple reviews/approvals so: one (revision) to many (reviews/approval) relationship

tblDrawRevReviews
-pkDrawRevReviewID primary key, autonumber
-fkDrawRevID foreign key to tblDrawingRevisions
-fkEmpID foreign key to tblEmployees
-txtAction review/approve/reject etc? (you can put the possible actions themselves in a table and use a foreign key here--it saves on typing, prevents typos and makes your data more consistent)
-dteAction date of action

Hopefully this will get you started in understanding how to structure the database & definitely read up on normalization.

 

[saip15]

I have a couple forms that I have to do this and i just meant I need to focus on one database for this form. That's a great start, thanks for your help. I'm sure i will bugging you again.

What should I do about 6 different people that review the document? For instance there only 2 Hardware Engineers, is there anyway that only those two names would be selectable?

I create the primary to foreign key relationships by clicking the relationship button?

RevLevel?

So after setting up this structure the next step i set relationships correct?

 

[GaryPanic]

"What should I do about 6 different people that review the document? For instance there only 2 Hardware Engineers, is there anyway that only those two names would be selectable?"

Put all the personal in one table - and giventhem a code accorrding to the job they do


John Smith Harware Code 1
Peter Allen Hardware Code 1
Jonny Depp Software Code2
James Dean Software Code 2

Peter Rabbit Tech code 3

the code is so that you can group people together (use numbers for this so code :- 1)
you cna now use a qry to filter according to the code in the relivant drop down box

 

[saip15]

I keep getting the Relationship error when trying to connect everything I'm reading on the error now. Here is my Database if you don't mind. Thanks so much for your help.

 

[jzwp22]

For everyone that has to review/approve the revision, you will add a new record in tblDrawRevReviews. I was getting the same VBA code error, I don't know why, so I just imported the tables into a new database and established the relationships. The fkxxxID fields must be long number fields to match the same data type as the autonumber pkxxxID field. Also, you used the word "name" as a field name; "name" is a reserved word in Access and should not be used. I made the corrections in the attached database. Just as a general tip, it is best not to use spaces or special characters in your table and fieldnames.

 

[saip15]

So in the HW Team table where did the subdatasheet + come from? Is it because the relationships are set? So if I wanted a first and last name I should have two different fields?

So what is the next step? So I can get started on some tutorials and play with it.

What was the RevLevel?

Thanks so much jzwp22! I can't thank you enough.

 

[jzwp22]

The subdatasheet are a result of establishing the joins between the tables. I generally use separate fields to hold the first and last names, so people prefer one field, so it is really up to you. If you chose to use separate fields, it is easy to combine them for display purposes using an expression in forms, queries and reports.

RevLevel is just a field to capture the revision level of the drawing; feel free to change it to meet your needs.

The next step is to continue to map out your process and translate it into the database structure. Once you are done with that, you will need to create forms that your users will need to interact with the database to accomplish data entry, editing, viewing, searching etc. The users should never see your tables, they should interact strictly through forms.

 

[saip15]

Ok, great! I have one more question. Would you suggest using the autmated processes for designing the forms? I tried designing one from design view and I couldn't figure out how to get the data entered from the form into the table. I'm guessing now that the structure and relationships are set this will be easier to accomplish. I guess my question is how do I set up the form for data entry?

So when I get my forms set up and ready to use. How will the end user save an individual form and also compile data into the tables? Will the individual forms be able to be saved? Just wanted to get a heads up on what is next? Do you know of any in-depth data entry tutorials?

Thanks

 

[jzwp22]

The database designer creates the forms. The users enter/view/edit data via the form, but do not alter the form. You can set a form's properties such that it can be used only for data entry (can only add new records) or for data entry/viewing/editing.

If you want to generate a printable document that looks like the one you posted early on with data that has been entered in the database, that is actually a report.

Check out this site for some tutorials

 

[saip15]

Thanks jzwp22 for the great start! I can still have form look like one from my first post right? But if I need to print than I can just save the form as a report correct?

How do you get the data entered in text box to show up in a certain field on a table?

 

[jzwp22]

You can try to get the Access form to look like the one you posted earlier, but I cannot say for sure if you will be able to. You'll just have to give it a try.

I haven't been really successful in saving a form as a report and having it work correctly, but it is definitely worth a shot. If it doesn't work, you just have to recreate your form as best as possible using the report tools in Access. As an alternative, if your form (the actual one you use) is available in Microsoft Word, you can use it as a template and have Access dump the data into the template. This requires some Visual Basic for Application (VBA) coding.

 

[saip15]

That was my first choice of just using the word document but my VBA coding skills aren't that great. I can't just code from a blank page. I tried searching on linking Word to Access but didn't find anything to useful. And it seemed like a lot of extra work. So that's when I choose to stick with Access only. I feel that a Database they want would need to be created by a professional but I'm just trying to get a couple forms to work.

On my previous form I just use the word document as an image and placed combo and text boxes over the image. That's the same thing as using a form wizard right? Not as easy but same concept.

**** Last Question: How should I work the text boxes with the actual Description of the Change? I will need at least 5 or 7 text boxes where the changes can be entered. Which table should I store these in? Can you store multiple text box entries into one field on a table?

 

[jzwp22]

On my previous form I just just the word document as an image and placed combo and text boxes over the image. That's the same thing as using a form wizard right? Same concept.

No, I don't think so; the form wizard binds the form it is creating to an underlying table. In your original database, the form you had was not bound to any underlying table, so no data would ever have been entered into Access.

 

[saip15]

Sorry, I updated my last post with a question.

 

[jzwp22]

**** Last Question: How should I work the text boxes with the actual Description of the Change? I will need at least 5 or 7 text boxes where the changes can be entered. Which table should I store these in? Can you store multiple text box entries into one field on a table?

If you want to capture every change as a separate item for each revision, you are once again describing a one-to-many relationship which requires a change in your table structure.

 

[saip15]

So I can't just fields to my Drawing Revision Table? I need to go back re-design my structure?

 

[jzwp22]

If the items going in the textboxes are all essentially the same type of data then they should be records in a related table not fields