Assistance on DB Design required - please

[Optimus_1]

Hello all,

many thanks in advance for looking and helping me.



I am relatively new to access and have a DB design query, I have completed roughly in excel how i would like my database entry form/forms to look like in access, if anyone can please give me some direction on how best to complete this i would be very grateful.

Also i am unsure if you can have external files atached to forms (that are specific to a table entry?.

I hope i have explained my self in the attached spreadsheet.

thanks again
Nathan

[jzwp11]

I would recommend holding off on the forms until your table structure is worked out. Having the correct table structure is critical to any relational database, so it is best to plan it out carefully.

From your spreadsheet, it looks like you are tracking work schedules. Can you explain in words what you have to do with these work schedules?

From what I can tell, a work schedule has to be issued (by someone) and has to be completed. Each work schedule has many external files related to it. If I have interpreted that correctly, this is how I would set up the tables:

A table to hold the basic work schedule information:

tblWorkSchedule
-pkWorkSchID primary key, autonumber
-txtWordScheduleName

Since a work schedule has many actions (issue and completion) associated with it, you have a one-to-many relationship which requires a table such as this. Associated with an action you have a date and a person and something that describes the action being taken.

tblWorkScheduleTransactions
-pkWSTransID primary key, autonumber
-fkWorkSchID foreign key to tblWorkSchedule
-fkActionTypeID foreign key to tblActionTypes
-fkPeopleID foreign key to tblPeople (person doing the action)
-dteAction date of the action

A table to hold the various actions that might occur for a work schedule; this table would have 2 records at present (issue and complete). You can add more later as needed.

tblActionTypes
-pkActionTypeID primary key, autonumber
-txtActionTypeName

A table to hold the people involved in your organization

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

As to the external files, it is best in Access 2002 to not store the actual file as this will bloat the database size. Alternatively, you can store the path to the file. Again since you have more than 1 file associated with a work schedule you have a one (schedule) to many (files) relationship.

tblWorkScheduleFiles
-pkWSFilesID primary key, autonumber
-fkWorkSchID foreign key to tblWorkSchedule
-txtPathToFile

This is probably a lot to take in, so please do not hesitate to post back with questions.

[Optimus_1]

Hello,

thanks for your reply, I was assuming it would be more simple than that.

maybe i didnt explain well enough or maybe i simply need to do what you have suggested.

Basically the work schedules (and related external documents) are predefined before the year starts and then issued during the year and returned obviously during the year. I was of the impression that a single table could be set up with all the details in and then update the one table as and when, is this not the case?

I have attached a version of something like what i had imagined, there are errors on this, namely:

I would want a dropdown box for workschedule not just tab through them, also i dont know how you make the external links into a button so that you can just press it and the paperwork arrives from the destination.

Hope this makes more sense and thanks again for your time to have a look at this.

please dont hesitate to corrrect me in my naivity!

ta

[jzwp11]

Since you describe many events or transactions relative (issue and complete) to a work schedule as well as many external files relative to a work schedule, you are basically describing one-to-many relationships which require the table structure I have suggested. The main principle in setting up a relational databases is normalization. This link provides some details on normalization. Your fields External Link 1, External Link 2, External Link 3 are a prime example of a repeating group which is a violation of normalization rules.

Regarding having a button to go to a document, there is a Visual Basic for Application command that you can execute on the button click event to open a document or webpage etc. It is basically a hyperlink command. That will have to wait until after your table structure is finalized.

[Optimus_1]

Hi, thanks again.

I have created the tables (i think correctly) how do i go about creating the table forein keys to enforce the correct normalization of my database?.

thanks again for your help

[jzwp11]

Looking through your tables, I changed the foreign key fields from text to number (long) data types. When joining the primary key field (an autonumber field) to it's corresponding foreign key field in a related table, the two fields must be of the same datatype. The autonumber datatype is actually just an auto incrementing long number. So in other words, the autonumber primary key field must join to a long number foreign key field in the related table.

To create the relationships between a primary key and it's corresponding foreign key, you need to go to the relationship window. I have created the relationships in that window (DB attached). Please take a look at how the tables relate, and let us know if you have any questions?

[Optimus_1]

Hello again,

Thanks for the help, This is all new to me with the foreign keys etc, how do i set up my input data form to collect the data?. I am confused at the moment!.

I need to be able to select a work schedule and print of the relevant additional external paperwork and then mark as issue (with date) and then returned when complete!.

Work schedule can be predifined at start of the year and willnot change throughout the year, would it not be easier to complete a table of workshedules and assign there external links to this table?.

I am sure the way you have set this DB is making perfect sence to you but im a little baffled!.

Thanks

[jzwp11]

As a general guide, the table on the one side of a one-to-many relationship generally provides the records shown in the main form while the table or tables on the many side of the relationship are shown in subforms. So in your case, you would create a form based on the work schedule table (tblWorkSchedule). On that form, you will have two subforms. One of those subforms will be based on tblWorkScheduleFiles and the other on tblWorkScheduleTransactions.

You can use that main form/subforms set up to add your new work schedule information at the start of the year and then go back to them during the course of the year to make additions/updates in the subforms. Give the forms a shot and let me know if you have any questions.

[Optimus_1]

Many many thanks for your assistance and time helping me, really appreciate it.

I have created a form with subforms and i have a few queries:

Should i use the form i have created as simply a 'set up' information form?.
I can understand the 'external file' links if this is the case but am a little unsure as to the other fields in this form.

Do i need a seperate form to 'log' the information as to:
a) when the work is 'issued'
b) Completed
and
C) By whom

These fields cannot be added on the initial set up form as they are not know until during the year or should i add them to this form when they become known?.

Do i need more than one dteAction field?. ie issued / complete?

thanks again

[jzwp11]

The main form you created is fine. The subform for the transactions is where you would record the issued/completed dates. I have modified that form to include combo boxes so that you can choose the action (issue/completed) and the person doing the action and the action date. You will have at least 2 records in this subform by the end of the year in which the work schedule was completed, based on our previous discussions.

As to the external files subform, I modified that to a continuous form so that I could include a command button that when clicked will open the external file based on the path that is entered.

You would create your work schedule records using this form at the beginning of the year and then go back to those records to update the information in the subforms.

I've attached the revised DB.

[Optimus_1]

Hi again,

This form looks brilliant, i think its what i require, certainly to set up the data.

May i ask how you went about 'creating the continuos form' for the hyperlink boxes for future reference as im trying to learn the basics of Access, also i have looked at the other sub form and the item within the form are not the ones on the form to input into?. is this simply the 'count column' you have amended? - fkWorkSchID is present there as well but not visible on the form, why is that?.

Finally, when all the work schedules are set up (aprox 100) is it from this form that i request the external documents with the 'Go to File' button?.
and will 'tblWorkScheduleTransactions' be populated by this form or do i need to creat another form/report?

Thanks

[jzwp11]

When you create forms, you have several different options, single form view (1 record at a time, like your main form), datasheet view (like the work schedule transaction form--multiple records at a time) and the continuous form (like the external file form). With a datasheet view there is no place to add buttons. The single form and the continuous form allow you to add command buttons that will, in turn, allow you to provide more options for your users. When you use the form wizard you will see the different types available in addition to a couple of others that I have not mentioned. If you create a form in one view and want to switch, go into design view of the form then View (from the main tool bar)--> Properties-->Format tab-->Default View.

i have looked at the other sub form and the item within the form are not the ones on the form to input into?. is this simply the 'count column' you have amended? - fkWorkSchID is present there as well but not visible on the form, why is that?.

I assume you are referring to the combo box. From design view of the form (subform), click on the combo box and the View (from the main tool bar)-->Properties-->Data tab. Look for the Row Source; this is a query that provides the data for the combo box. You can see the list of fields following the word SELECT. Now switch to the Format tab. If you don't want your users to see a field (like the key field fkWorkSchID), all you have to do is to adjust the Column Widths property to zero. You might see something like this 0";1";2";0 This says that the first and fourth fields in the SELECT statement will have a width of zero, which will not be visible to the user when they click the dropdown on the combo box. The second and third fields with be 1" wide and 2" wide respectively and will be able to be viewed. The column/field with the first non-zero value is what will be viewable in the combo box when the dropdown arrow is not clicked. If you switch back to the Data tab, look for Bound Column, in most cases I have the Bound Column property set to 1. This means that the first field in the SELECT statement is the one that is stored in the underlying table when a choice is made in the combo box.

Finally, when all the work schedules are set up (aprox 100) is it from this form that i request the external documents with the 'Go to File' button?.

Yes, but you do have to put in the path so when the button is clicked it knows where to look.

..and will 'tblWorkScheduleTransactions' be populated by this form or do i need to creat another form/report?

When actions take place relative to the work schedule listed in the main form, you would record them in the transactions subform. There will be no need for another form.

[Optimus_1]

Thanks for that explaination, makes sense.

One final quick question hopefully!!.

On my Input form is there a way of being able to select the work schedule by either a drop down box or by starting to type the name rather than having to tab through to find the correct one?. Hope that makes sense.

Thanks again

[jzwp11]

Yes, there is a way; it takes a little VBA code, so I went ahead and added it to you form. It uses the after update event of an unbound combo box and a clone of the form's recordset; finds the workscheduleID and then moves the form's recordset to that ID. The example DB is attached.