MS Access Database

[murdoduncan]

Good day everyone and are there any Access geniuses out there who can help Jose and myself with some links, dates and logic for our HR database that we are stuck with? Cheers, Murdo
PS My ady is <EMAIL SNIPPE>

[timothybard]

I'd love to help. Can you tell me the details of what you need? I suggest you start a new thread for each issue.

[murdoduncan]

Here is our problem and if anyone can help, please let me know!!! We are trying to link up the two, one from our database and covers leave perids taken and the other, under Excel format, is an allowance we pay uniquely for period on location. The dates changes all the time, the allownac is estimated in advance then adjusted the following month etc. How can we only use Access to do all of this and not have a million things to update? Any advice will be gratefully received. Murdo:'(

[timothybard]

Thank you for providing the attached file. However, I do have some questions about the document:

1) In the first image, what do the "days" columns refer to? Are they based on the Departure and Arrival dates?

2) What is the purpose of 'Estimate 11-2008'? Will an 'Actual 11-2008' section be needed?

3) How is the ADJ column calculated?

4) What are the meanings of the columns in the second image?

5) Which information is currently tracked in Access and which information is currently tracked in Excel?

I may be able to assist you further if I know the answers to those questions; however, my first thought as far as tracking this information in an Access database is below:

1) I would create a table called 'Leave' with the following fields: ID, person (FK), estimated departure date, estimated arrival date, actual departure date, estimated departure date, location (FK), ADJ

Since there is the location foreign key in the table, you can create a query from the table that groups by location, then, if there are actual values filled in, then the leave days are summed; otherwise, the estimated leave days are summed.

Please let me know if this helps; again, if you could explain the existing structure of your data and the meaning of the values in the attachment you provided, I can give you more help.

[murdoduncan]

Hiya and many thanks again for your reply. Let me try and explain the two attachments in a word document and then the series of events, which will give you the full picture. "spead" to you later and many thanks for taking an interest in our small company!!! Cheers, Murdo
PS We are open to any suggestions, even to revamp both tables and look forward to having some new ideas from outside the box!

[timothybard]

Thank you for the additional information. From the information you provided I would create a database with the following structure:

Person: ID(PK), Name, Address, etc
Rotation: ID(PK), Person(FK), Period State Date, Period End Date
Leave: ID(PK), Rotation(FK), Departure Date, Arrival Date
Allowance: ID(PK), Person(FK), Month, Monthly Allowance, Days Allowance Covers

Using that structure, you would first record the person in the database. You would then record the rotations that have been contracted. For each rotation, the person will acquire leave days; the leave days would be recorded in the leave table. Lastly, each month, the allowance can be recorded and later updated if needed.

To find out the number of days a person is on site and will receive an allowance, you could join the person table to the allowance table. You then go through each record and loop through the Leave table for the current person. Next, you can loop through each day on the allowance month and see if the person is on leave for that day. If they are not, then you increment the "days owed allowance" by one. Calculating the days the allowance covers will be VBA code that updates the "Days Allowance Covers" field.

[murdoduncan]

Hiya Timothy and we'll certainly give it a go. I also need to start another thread about a different table problem, again regarding dates, to see if anyone can help us thre too! Cheers and have a nice day! Murdo