Table Structure

[ccordner]

Hi.

I have a table, "tblStaff" which lists every employee in our area.



I have a second table, "tblLocations" which lists the cost centre for each location, address etc.

At the moment, tblLocations has a field called "Responsible Manager", which is a lookup from tblStaff. This all works fine for inputting data.

HOWEVER

This appears to cause problem as every employee has a location as their base (which is a lookup in tblLocations).

What I want to be able to do is record when somebody worked their day off in a third table "tblRestDaysWorked". This shows who worked it (tblStaff:ID), what date it was and why.

The problem comes when I want to build a query based on that table. I can use the employee ID to find out the details but when I add in the "tblLocations" to find out their cost centre (to group the report by cost centre) it causes a problem, presumably because of the "Responsible Manager" field.

Any ideas?

[jzwp11]

Do you want to see the responsible manager in the query results?

If not, remove the second join between the staff table (employeeID) and the location table (responsible manager) in the design grid of the query (not the main relationship window)

If you do want to include the responsible manager in the query, create a preliminary query that joins the location and employee tables via the responsible manager field. Save that query.

Create a second query that joins the above query to the staff table (via the locationID field)

I made several assumptions above since I had to take an educated guess at your table structure. I am assuming that it looks something like this

tblStaff
-pkEmployeeID primary key autonumber
-txtFName
-txtLName
-fkLocationID foreign key to tblLocation

tblLocation
-pkLocationID primary key, autonumber
-txtLocationName
-fkEmployeeID (responsible manager) foreign key to tblLocations

Technically speaking, the manager is an employee and a location has many people associated with it. With that in mind, the better structure would be as follows:


tblStaff (all employees)
-pkEmployeeID primary key autonumber
-txtFName
-txtLName

tblLocation
-pkLocationID primary key, autonumber
-txtLocationName

tblLocationStaff
-pkLocStaffID primary key, autonumber
-fkEmployeeID foreign key to tblStaff
-fkLocationID foreign key to tblLocations


You can then add a field to tblLocationStaff to flag a particular person as the responsible manager or even have a field that refers to job title for each person at the location

[dblife]

The thing that strike me from looking at your extremely well explained problem is..
"tblLocations has a field called "Responsible Manager", which is a lookup from tblStaff"
You have a look up within a table.
Lookups within tables are to be avoided at all costs.
They cause untold misery.
Tables are for data.
Forms are for combos or lookups.
Can you remove the lookup from the table (it won't damage anything) and see if you still have the problem?
Is this possible?

[jzwp11]

Good catch dblife, I missed that about a lookup in the table. This site has details of why lookups at the table level should be avoided.

[ccordner]

Gentlemen, thanks for your assistance.

I had never thought of using the "query within a query" approach. To clarify, most of my experience is with Excel (VBA mainly), but I realised that a lot of what I was doing would be better in Access (although I'm still annoyed there's no easy way to handle times of greater than 24 hours!)

I'm not very experienced with Access (we did it at school years ago, but I've not done enough with it since to retain any of that knowledge) so forgive me if some of my reasoning is a little skewed.

1) I take your point about the table structure, but it is slightly more complex than that. A manager may be based at one location, but be responsible for several others (maybe not including the one he is based at). Also, I have a nasty feeling that significant changes in the table structure may have implications in the code I've written elsewhere.

2) The lookups were put in, if I'm honest, because I'm inputting a lot of the data directly into the tables to set the thing up and it was much easier that way! If I take the lookup out, it won't lose the info stored in the fields will it? Another reason is that I have no idea as yet where I'm even going to start with the "Front End", but I will save that battle for another day!

3) How do I flag this as "Solved"?

Thanks
Chris

[dblife]

Iused to do it all the time when i started - for the same reasons you state here.
It confused me a lot at the time.
You should be able to remove the lookup within the table field and retain the foreign key value.
Any queries you have based the field on will probably be ok but forms may need tweaking.

[jzwp11]

...although I'm still annoyed there's no easy way to handle times of greater than 24 hours!

Date and times (date/time data type fields) are stored as a number but are just displayed according computer date settings. The portion to the left of the decimal part that corresponds to the date, is actually the number of days since 12/31/1899 (if my memory is correct). The portion of the number to the right of the decimal point is the fraction of a day. So if the date and time were: 12/1/2011 1:00pm, Access stores it as the number 40878.5416666667. 40878 days since 12/31/1899 and 0.5416666667 parts of a day: 0.5416666667*24=13.00 hours

In other words, if you want to store the number of hours, do not use a date/time field, just use a number field.

[ccordner]

I posted a separate thread on this one and it was suggested that the best way to store a time over 24 hours was to record "parts of an hour", so that 25h15m would be recorded as 25.25.

This seemed quite sensible, so I experimented with it, but gave up.

I am using Excel to write a lot of stuff to the tables and I got an error message about "having to truncate a decimal".

I figured this was probably because it was recurring or irrational and started investigating if I'd used the wrong data type when designing the table.

However, since all data types seem to be of finite length, I'm not sure how you would handle that kind of number.

It's just quite frustrating because Excel handles this quite neatly with the "[h]:mm" format - although I appreciate this may well be a bodge in Excel!

[jzwp11]

I have actually stored a time duration as a single or double precision number expressed as seconds and then used an expression (with some nested built-in functions) to show it in the HH:MM:SS format. The expression is kind of messy but you only need to do it once typically.

So 25 hours and 15 minutes would be stored as 90900 seconds. Of course, depending on what you are after you could just store the value as the total number of minutes (1515 minutes).

[ccordner]

Yes, I think that might be the way forward.

Thanks
Chris

[RayMilhon]

Personally I would rather store the start date/time and the end date/time then I can calculate any values I need. For example:
Start date time 11/30/2011 11:59:59 PM
End Date time 12/01/2011 12:01:00 AM

Using datediff("n",[starttime],[endtime]) Gives me the number of minutes between the 2 date/time fields which in this case would be 2.

You could create a VBA Function to display the # of minutes in any manner your choose.

Something like

Public Function showtime(Byref lngminutes as Long) as string

Dim hrs as long
dim minutes as long

hrs = lngminuts\60
minutes = lngminutes mod 60

Showtime = Str(hrs) & " Hours: " & Str(minutes) " Minutes"

End function

I would add some error handling and checks but you get the gist

[ccordner]

The only problem with that is that it is not one continuous time.

What happens is I have a cell on the roster that calculates the total number of hours worked in a week.

So there are many start and finish times, further complicated by the fact that some shifts may start before midnight and finish after midnight.

Thanks
Chris

[RayMilhon]

Midnight is irrelevant to the date diff function as it calculates the elapsed time between the 2 date/time values.

Your data structure did not show how your were tracking the time worked so I presumed there was a record with the start and end times for each person for each shift worked. Some HR systems have 4 time fields starttime, lunchstart, lunchend and endtime. As long as you are saving the start and end date/times you can use the datediff function to calculate the elapsed time.

Sounds like your storing the minutes worked rather than the start and end times. Why? That may be the way to do it in a spreadsheet but not in a relational database. At least that's my opinion.

[ccordner]

Thanks for your help with this one, I really do appreciate it and it's highly likely this is all my misunderstanding.

But

When the shift times are put onto the roster, they just put a time in. Therefore, my understanding is that Excel treats that as, for example, 8:00am on the 1st January 1900 or something.

How do I supply the date component?

In short, there are nine columns: name, Sun - Sat, Weekly Hours and three rows: On, Off, time worked.

If the weekly hours are greater than a set amount (in a cell on the sheet), I need to store the name, hours worked and ideally the amount by which they exceed that in a table.

I am writing this in Excel VBA, so what's the best way to transfer it across using Datediff?

Thanks
Chris

[RayMilhon]

I'm a bit confused is this an Access DB or an Excel Spreadsheet we're discussing here. Me giving you advice on Access when you're working in Excel No wonder we're both confused.

Can you supply a copy of the Excel spreadsheet with Row Headers and column Headers if they exist but no or BS Data so I can see exactly what you're talking about?