Table Structure

[ccordner]

I think perhaps a little bit of background is appropriate.

The original program was an Excel Spreadsheet containing the roster. This has, say, twenty-four tabs with twenty-four weeks worth of rosters in. I have (I think) attached a guideline layout).

This has to then be converted into another spreadsheet, in a slightly different format, to be submitted to our Paybills department as a Time Sheet.

As part of our rostering guidelines, we have to make sure we don't fall foul of certain criteria - minimum rest, maximum shift length, maximum hours per week, without authorisation.

I have written a VBA module which, for each member of staff, finds their pay number in the roster, then copies their shifts across to the time sheet for submission to paybills.

It then struck me that it would be much better if rather than using a list of pay numbers in Excel, I used a table in Access, which I could also use as the centre of a staff database.

I then started working on the current aspect, which is to pull out certain information (when the aforementioned criteria are breached) and write them to an Access table, so that the management could run reports and find out information about the number of breaches per month etc.



So, in a nutshell the information is all coming from Excel, but being written to Access.

There are good reasons for keeping the roster in Excel (makes it easier to navigate) but using VBA in Excel to manipulate Access does cause some headaches in itself.

Hope this makes sense.

Chris

[RayMilhon]

I'll look at this tonight and get back to you tomorrow

[ccordner]

Thanks for that. I'm finding myself on a steep learning curve, both with Access and fragments of SQL.

Apologies for any confusion.

[RayMilhon]

Looked at this last night. My recommendation is that you eliminate Excel and Do this in Access. For what you're trying to accomplish a relational DB is the better way to go in my humble opinion. However, I'm not sure that you will be able to keep the interface you're currently using. I understand it's what your users are used to however, using Access it could be simplified and your reporting of the data would be improved. For example, Presuming that each user has a unique Windows Login. You could set access to open a data entry form when the DB is opened and have the Dateworked, starttime, endtime, Notes As the fields on the form. behind the scenes the username would be populated by the logged in user Dateworked could be set to default to today and then the user would only have to enter 2 data elements. Start time and end time.

[ccordner]

Thanks

The main reasons I decided not to use Access for the whole thing are:

1) I presume that in order to do it in Excel, rather than having the thing laid out as a form, I would have to have tables of shifts, allocating each member of staff to a shift on a date etc. This could get tricky when a shift is being partially covered on overtime, as one member of staff would be allocated to one shift and partly allocated to another

2) I had a fear that doing it through Access would mean that every time you wanted to look at a different week or location, it would have to find the information in the database and then populate it. With Excel, all the information is there, simply by switching between tabs. Whether this would actually slow it down or not, I'm not sure.

3) My capabilities in excel far outstrip my aptitude in Access, as you may be able to tell from my posts - there will definitely be more! Although I am fairly comfortable with the concepts of tables and relationships and to some degree with queries, how the forms interact with them is in some ways a bit of a mystery to me. I am absolutely dreading making a start on the "front end!"

4) A spreadsheet doesn't need much work doing to it before it can be used by the end user. While far from ideal, as long as the row and column headings are in, they can be using it - I can tidy up the layout and the code behind it in and amongst. Not sure how well that can be achieved in Access.

5) Not really a reason from the outset, but certainly a consideration now, wis the fact that I have done quite a lot in Excel and that side is more or less ready to go - as there is a bit of time pressure on this one, I daren't start from scratch - maybe for a later version when I'm a bit more comfortable with Access.

Thanks
Chris

[RayMilhon]

In that case we are in a sense exactly opposite. I am much more comfortable in MS Access then Excel. I have worked in Relational Databases. Access, SQL Server, Oracle for most of 20 years. I consider myself a Beginner to Novice in Excel. I think you need advice from someone equally versed in both and unfortunately that isn't me. I will watch this thread with interest but I don't think I can be of much use to you at this point.

[ccordner]

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.

Hi.

Got round to the form stage now and wondered what's the best way to do a lookup on a form?

For example tblLeaveBooked has a field "Employee" which needs to contain the autonumber ID field from tblStaff.

Fields in tblStaff include Surname, Title, PreferredName and Paynumber.

I want the combobox to list them as "Surname, Title PreferredName (PayNumber).

What's the best way?

[jzwp11]

Lookups on forms are fine, just not in the tables. I typically use the combo box wizard and step through the process. You will be able to pick the table (or query) you want (tblStaff) and the fields you want displayed. Make sure to include the key field (autonumberID) and bind that to the corresponding field in the form's underlying recordsource (tblLeaveBooked)

I want the combobox to list them as "Surname, Title PreferredName (PayNumber).

If you want to bring the "surname, Title Preferred Name (PayNumber)" as one field in the combobox which I assume is what you want to do based on the above , then you will need to do that in a query and then pick the query as the combo box's row source.


The query would go something like this:

SELECT Surname & ", " & Title & " " & PreferredName & " (" & paynumber & ")" as StaffMember
FROM tblStaff