Dates In Tables

[helphelphelp]

Hi all

I am creating a database and I need to know figures at the end of each week on staff performance.

How will I do this as I have to report to my bosses at the end of each week what targets have been hit.

The trouble I am having is generating this date at the start of each week, the figures have to stay on the system so I would require a new column of information at the beggining of each week. Can this be done as I am really struggling or am I asking to much from access, I am a begginer with a small amount of knowledge and I am using 2003 access

Thanks for any help and I hope you underdstand what I am trying to do

 

[ColinEssex]

To demonstrate targets - you need the 'target figure' and the 'actual figure' then just compare the two as a plus, a minus, or an 'on target'

I'm not sure what you mean by 'generating this date at the start of each week' - do you mean sunday to saturday as a week? why can't you pop in a date range for the analysis of targets?

Col

 

[helphelphelp]

Dates for week commencing

Thanks for your reply, I have columns in the DB to work out the maths side of it like you said targets actuals and shortfalls etc, the trouble is I have to report to my boss each employee, at present I am using our best friend excel and inputing the start date of each week in a new column (5 day calender required), I will try and explain a little better what I am trying to do,

I have got to report to my bosses, the activities of 6 members of staff, the database is for recruitment of external people, I have already made a table with subdata for each employee and their activities. I need to know the date the job vacancy was put on the system, then a date for when the postion has gone for interview and a date for the start of the new employee.

Bascically its like a recruitment db I am trying to make.

The report my boss wants to know is how many vacancies were put on the system each week, how many went for interview and how many started.

This is why I need a system that will tell me on friday at end of play the complete activity for that week, so when all staff come back in on monday a new column has been generated for all information to start again, and obviously there are going to be shortfalls, where more vacancies went on the system than we found positions for

Hoep this is coming a little clearer now, and can I add that I cant read or speak access so please be gentle.

Sorry forgot to mention that the report my gaffer wants to see has to show the week commencing dates for the whole of say June so he can report to his gaffer the movement over the month.

 

[ColinEssex]

Ok. First you need to make sure you have set up the database tables correctly. The relationship link on the tables (below) will be on IdNo and a one-to-many relationship. One employee can have many vacancies to deal with. Also, link the StatusCodes in the two tables. Add the Status table in the query to give your the status description. StatusDate is the date something happened - put on system, interview or start employee.

I think you need 3 tables to start with. One being the 'staff' and the 2nd being the 'positions', the 3rd being 'status'. You don't need to save any calculated data because you can calculate that at any time.

Note each step of the vacancy is on a new line in the positions table

To calculate between the dates you need to use DateDiff() function in a query.

Staff (Demographic Data)

IdNo - Autonumber
Surname
Forename
etc

Positions

PosIdNo - Autonumber
IdNo - staff Id dealing with case
VacancyName
StatusCode
StatusDate

Status

StatusCode (S = System, I = Interview and St = start date)
StatusDesc

Col

 

[helphelphelp]

Thanks

Thanks for your help, no doubt I will have trouble, I will give it a go I do struggle with relationships sometimes i think the message I normally get is their is no index to relate to!!!!

Is it wise to make a small dummy db to start and then once grasped make the main, what worries me is that I have to convert the info I have already got on the excel ss. And thats when I start getting problems.

Could you also tell me when I create the tables should i always put a primary key into all tables.

I think the best thing I could do is mail the db across to you for a look and feedback then so you have a working project to see where I am going wrong.

 

[helphelphelp]

Thanks

Thanks for your help, no doubt I will have trouble, I will give it a go I do struggle with relationships sometimes i think the message I normally get is their is no index to relate to!!!!

Is it wise to make a small dummy db to start and then once grasped make the main, what worries me is that I have to convert the info I have already got on the excel ss. And thats when I start getting problems.

Could you also tell me when I create the tables should i always put a primary key into all tables.

I think the best thing I could do is mail the db across to you for a look and feedback then so you have a working project to see where I am going wrong.