The purpose to some of your columns in the last screen shot are not clear. What is the purpose of the following columns?
SP, R1T/R4, R2
And does each row in the last screen shot represent a different record? If not, please describe what you did to produce that layout.
I need to be able to understand the basic set up of your tables/fields/relationships before I can suggest ways to get the results that you want.
SP is sales person, and R1T/R4 and R2 are the roles that consultants do on a project. Which is one of the things we need to track.
Each row is a new record and the fields are as follows:
Month – Contract Number – Project – Sales Person – Year – Day 1 – Day 2 – Consultant 1 – Consultant 2 – Notes
Month is the sort order and I need to keep it sorted by this field
Contract Number is the link between all the tables I use.
And hopefully the rest are all self-explanatory. I know it’s not the easiest to do from the information I give but I can’t use actual screen shots as the data I have is sensitive and I can’t post it on forums.
Basically what I’m trying to achieve is a summary of the days that each consultants does summarised on YTD and on a month on month basis. Image2 shows the YTD and Image3 shows the month on month.
And what does that one single record represent? Am I correct that you have two separate fields for identifying the consultant? Why two fields and not just one?
And what is the purpose of having both a day1 and a day2 field?
Each record represents a project, but only the days the project took place and the consultants working on it, it's more of a tracker of the consultants number of days worked for us this is the information we are after. There are two fields for the consultant as there is two areas of expertise, likewise there are two fields for days as there are occasionally two days.
Hope this helps.
Each record represents a project, but only the days the project took place and the consultants working on it,
It seems that you have one record where you really need two or three. What happens if you end up needing 3 days instead of 2?
I'd use one table for projects where I have one record for each project and a related table for logging a single day worked on a single project by a single consultant in a single area of expertise. This table of days worked can then be linked to a table of consultants if needed.
That would make pulling together your summary information into a "cross tab" format such as you show much simpler.
If I do it as you suggested would it mean that users would have to fill in the information on multiple pages or would they be able to input it on just one page? I'm trying to keep it down to a minimum and on just one page. I don't mind using multiple records and tables etc if I can keep it in one front end.
Could I do this as you suggest?
This could still be data entry on one layout. That one of the purposes of using portals--to be able to work with multiple child records (days worked) from the context of a single parent record, the project. And I may have been a bit extreme in my last recommendation. You could log multiple days worked in one record, but I'd still limit each record where you log that data to a single consultant working on a single project.