in need of HELP!

[BigSeba4]

Hi, i am currently using access for a project. my project is basically being designed for use of a staff rota, but i am really struggling to understand and come to terms with access and only have 4 weeks left to do this project. i need to design a actual system using access, so any tips or website etc or a designed system i could use to help me design mine would be of so much help!

Thanks

Seb

 

[Corticus]

Welcom BigSeba4!

Why don't you post what you're trying to do. As long as its not super-involved, and you're willing to work at it, we're here to help. IF you've got zero experience with Access, trying to develop your first application is going to be a big, educational experience, and definately not one for which you want a time limit. But you never know until you try!

As far as recommending a website...you found a great one.

 

[BigSeba4]

i am have created 3 tables, "Staff information" - "Hours Worked" and "department" they work in etc

but i need to do stuff like add all the hours together and get a total, and be able to type in a surname and there personal information comes up and is displayed

and i dont understand how to do queries that is my main problem and so on!

if i could understand how to use queries then, id be able to get my system designed and done

Thanks again

Seb

 

[Corticus]

Ok, that's a start,

Before you get into queries, or forms, it is critical that your tables and relationships are in good order. It sounds like the queries and forms you need should be pretty simple, once we get your tables set up.

For tracking staff information, I imagine you'll need tables as follows:

TblStaff
StaffID - autonumber - primary key
DepartmentID - number
PayRateID - number
LastName - text
FirstName - text
MI - text
Address - text
Phone# - phone number mask
HireDate - date
any other information specific to a staff member

TblDepartment
DepartmentID - autonumber - primary key
DepartmentName - text
DepartmentHeadID - number
any other information specific to a department

TblHours
PaymentID - autonumber - primary key
StaffID - number - primary key
Date - date - primary key
HourTypeID - number - primary key
HoursWorked - number
I put all these primary keys in this table to prevent entering an employees hours twice in a day, but you could enter some regular hours, and some overtime

TblHourType
HourTypeID - autonumber - primary key
HourTypeName - text
HourTypeRate - number
this table would be for holiday pay, overtime, double-time etc.

PayRate
PayRateID - autonumber - primary key
Position - text
PayRate - number
use this table if you track hourly pay by position, if hourly pay is employee specific, you will need a field in tblstaff for their hourly pay

Let me know if this sounds right, and we'll move from there. Next is your relationships.

HTH,

 

[BigSeba4]

In Need of HELP!

i have already created the tables, and they are pretty similar to the ones you have stated, and therefore it does sound right

help with the next stage will be of use

thanks again

 

[Corticus]

Allright, now we need to set up your relationships,

First, open your db, and select tools|relationships, and add all your tables. Add the staff table twice.

Here's your relationships,

You want to drag the bold field 'StaffID' in 'tblstaff' to 'StaffID' on the table 'TblHours'. Check enforce referential integrity and cascading updates.

Drag 'DepartmentID' from 'TblDepartment' to 'DepartmentID' on 'TblStaff'.
Check enforce referential integrity and cascading updates.

Drag 'HourTypeID' on 'TblHourType' to 'HourTypeID' on 'TblHours'.
Check enforce referential integrity and cascading updates.

Drag 'PayRateID' from 'TblPayRate' to 'PayRateID' on 'TblStaff'.
Check enforce referential integrity and cascading updates.

Drag 'StaffID' from the second 'TblStaff' to 'DepartmentHeadID' in 'TblDepartment'.
Check enforce referential integrity and cascading updates.

I am assuming that pay rate is determined by position. If this is not the case, you don't need a payrate table, just a field in 'TblStaff' to put each employees base pay rate.

Your relationships should look something like this:

http://www.theillumni.com/posts/images/staffrel.gif

HTH,

 

[BigSeba4]

in Need of HELP!

no that


what now???

Thanks

Seb

 

[Corticus]

Now you need forms so your users can easily interact with this data.

I'd start with a staff form. Use the form wizard to make a form that contains all the necessary data from tblstaff. Now view the form in design view, and add a dropcombo using the form wizard. Tell the wizard you want the form to retrieve a record based on what you've selected. Have the combo contain data for staffID, and all the name information, but have it hide the staffID. Tell the wizard you want the first column (staffID) to be the field by which you retrieve the record.

Let me know how this goes...you will use a similar technique in other forms when retrieving data from a table based on the information entered into a combobox.

Next we'll a means to record hours worked.

HTH,

 

[BigSeba4]

i have done my queries they are working

but i cant get the hang of creating forms?

any suggestions?

Thx again

Seb

 

[Corticus]

I'm putting together some forms for you that should do most of what you've said. I'll post a link to them when I get back from lunch, so you can download what I've got, and check it out.

Next, on to your reports!

I think you're surprisingly close to completing what you need, once you see the forms I put together. I'll get them up at some point today.

HTH,