New to Access and need some help

[dups]

I am new to access and working on a project for my office i have started designing my tables and created a relationship and three forms but i asked someone to check if everything was ok with the relationship and he said it was not done properly so i am uploading the database and i would like someone to check it out for me and tell me what have i done wrong?
thanks in regards

 

[John Big Booty]

Any time you are going to reuse data you should store that in it's own table for example you should have a table for Department and then only store the DepartmentID in your Employee table, and the same for Title and any other data that is consistent. This eliminates the possibility of a department/title appearing in your employee table a number of times with various misspellings or abbreviations. You can then populate your form using a Combobox, reducing the number of key strokes your user requires to populate a form. It will also making searching a whole lot easier too.

 

[edwinastono]

Join Type

What u have to do now is set referential integrity and join type at ur relation.

 

[dups]

Thanks for reply
the thing is i work in the airline industry and my department we handle cargo , we import and export cargo ..
The thing is we have alot of different job functions and for each new function that an employee has to move on to he usually has to get a two weeks period of training for that particular function, now each employee has to learn basically every function at some point in time so we constantly has to do alot of different training especially for the new employees..
so JOHN BIG BOOTY , should i still redesign my tables the way u suggested ?
thanks in advance

 

[dups]

Also John it's really just one department which is the Cargo department , the only reason why i created a field name department is because we have another branch some where else in the country and i was thinking that maybe i could make them use the same database for their department..
I hope you don't mind me asking u questions John because as i said i am new and i think i am going to need alot of help ...

 

[Luddite Lad]

Thanks for reply
the thing is i work in the airline industry and my department we handle cargo , we import and export cargo ..
The thing is we have alot of different job functions and for each new function that an employee has to move on to he usually has to get a two weeks period of training for that particular function, now each employee has to learn basically every function at some point in time so we constantly has to do alot of different training especially for the new employees..
so JOHN BIG BOOTY , should i still redesign my tables the way u suggested ?
thanks in advance

If I have understood you correctly the title is and indication/record of the training an employee has undertaken. In that case I would introduce a new table that stores the employeeID and titleID, I would then display that on the Employee record form as a subform, this way you can show the various rolls that the employee has completed training for. You will also be able to add new roles as the employee completes the necessary training.

Also John it's really just one department which is the Cargo department , the only reason why i created a field name department is because we have another branch some where else in the country and i was thinking that maybe i could make them use the same database for their department..
I hope you don't mind me asking u questions John because as i said i am new and i think i am going to need alot of help ...

If the DB is to be centralised I would definitely follow JBB's suggestion. If each branch is to maintain it's own version of the DB and there is only the one department in each branch, then there is no need to store that data in your DB. However I would think carefully about how you envisage this DB operating as you move forward and what changes in the structure of your branch might take place in the future, so I would from personal experience tend to follow JBB's suggestion. This will mean that future changes within your branch structure will be easier to adapt into the DB in the future.

 

[dups]

Hey guys thanks for your help i am going to try and do something and then upload the new Db..

 

[dups]

I need some help again

i have made a few changes to my design but i maintained the the two tables that i had before the thing is this , i would like when i am on the employeedatabase form and i click on the update database button, i would like after updating the function and the both the dates that the data will be automatically saved in the filed nameof the "name" that's showing on the employeesdatabase form , how do i achieve this? or do i have to create a combobox or listbox on the functions form that has all the names of the employees..
i will update the changes i made so far and i really need some sugestions
thanks

 

[Luddite Lad]

Based on what you've said, I've made a few adjustments to your DB. I'm only guessing that I've headed in the right direction.

 

[dups]

hey Luddite Lad , i see u r really good , anyway thanks for trying but it's not exactly the direction i am heading to let me try and explain it a little more ..
we have about 42 persons in my department as i mentioned before i work in the airline industry and my department we import and export cargo ...
We have about twelve different job functions for example we have import cashiers , export cahiers , telephone agents,manifesting agent , palletizing agent and others , now each employee has a title in my department u can only be a Cargo Agent , a Supervisor or the Manager.. Now because the work can become monotonous each employee is rotated every three months or so to work the various functions, whenever an employee is going to be rotated to work a new function for the first time , he/she has to get training in the area for about two weeks,also because we handle cargo each staff has to undergo certain training once a year to handle Dangerous goods and stuff like that....i hope u r keeping up with me , Also each employee is assigned an ID number for example my number would be say "7847" , so that's basically y i am trying to create this database because it will keep track of each training that a particular employee has done and the date in which he started the training and for example if it's training to handle dangerous goods then it would show when that training should be renewed since it has to be done annually, so what i am trying to do is create a form where all the employees data like first name , last name, id number, title can be entered and also a form where each function that the employee has completed can be entered like the function in which they did the traing and the date of the training can be entered but i want to set it up in a way that when u r viewing a specific employee data u can click on a button to add the function that that employee is going to start training in and the date of the training and the date of the next training , i hope i am not comfusing you..
post back a reply and tell me if u understand what i am trying to do here please
thanks for your help so far

 

[Luddite Lad]

I think I see where you're headed. If you get your hammer out you should be able to give my form a bit of a bash to do pretty much what you want

In the mean time I'll have another look at what I've done and see if I can make it a little bit more what you're looking for.

 

[Luddite Lad]

I've made a couple of changes to the form, and I do realise that it is still some way from what you're after, but hopefully it will give you some ideas as to how to get to where you want.

 

[dups]

Thanks for trying Ludlite but it's not exactly what i want, i would like the database to be like my original design, but what i think i am going to do is , on my functions form i am going to have a combo box with all the employees name in the list and when the user select a name from the list and a function from the functions combo box and the date of training that all that data will be loaded in the "training table" do u see where i am going here? so help me with that part, and i would like that when all that data is added and the user wants to view all the functions for a particular employee then the employee name and each function they get training in will be available, so i want you to check the relationship i created to c if it would work.
But thanks for your help anyway

 

[dups]

ludlite check out my functions form and look at it , how do i not make the page updated until the usewr clicks on the ok button , i want them to select an employee from the employee combobox and a function and the date of training then after all that is selected then that data is added only when the ok button is clicked and cancel when the cancel button clicked..

 

[dups]

sorry i didn't add the file , here it is

 

[Luddite Lad]

Hi dups

Firstly can I suggest that you change the way that you have set up your ComboBoxes, in most (if not all) cases I would suggest that the ComboBox be populated from it's own table. For example your Title ComboBox on your main form; instead of having the titles already held in the ComboBox itself, the ComboBox should draw the data from it's own table and only the TitleID stored in your employee table. This has a number of effects, it means that should you have another form you can keep the data consistent, it will also allow new titles to be added as they are created, and importantly once the DB grows it will help reduce the size of the file as your will only be storing the ID number for each record rather that a string of text. Have a look at my form and see how I have set up the ComboBoxes. Also check out the On not In List event and also the On Double Click Event.

I would strongly suggest that you populate your Department field with a ComboBox if for no other reason than data consistency. In fact I would us a ComboBox anywhere that you require consistency in your data, ie. information that is common across a number of records (Title, Department, etc)

As it stands your Functions form is not bound to any table so the data is not being saved at all. Once you have resolved that (if I have understood your question correctly) you will achieve your aim of not saving data until the OK button is clicked by using an Undo Record action with a close form action on your cancel button.

You might also want to look at your Employee Table and set your Employee ID Number as Indexed YES(Duplicates not allowed). Also is your Employee ID Numeric or alpha numeric?

Hope that helps, and have fun

 

[dups]

hey ludlite you know i am just seeing the last post that yoiu made , anyway look at this next database for me, check out the form name Frmrelationship and the two tables Cneerelationship and shprrelationship and tell me why when i open the frmrelationship form the autonumber that is generated links back to the the first field in the table , and therefore when i try to create the relationship the primary field is empty , give me ur opinion on the design and the codes i used please and also can u tell me how to use the sql statement "delete"
check out my vba codes too
thanks

 

[Luddite Lad]

Hi Dups

The major problem with your form was that almost all of your controls where unbound. You also seemed to be storing the consignee name in the relationship table rather than the ConsigneeID. Please check how I have set up my comboboxes.

I've made some changes to your form and linked it to a new table. You will need to rewrite your code to take into account the new field names I have used.

If I was building this form I would store the commodities in their own table and select them from a combobox. If you do this you could make them cascading and thus prevent duplicate relationships, for consignee, shipper, route and commodity. I would probably consider setting up a Shipper commodity relationship, as most shipper will only deal in a limit range of commodities.

Hope that helps.