Considering going from Excel to Access. Need some help with the overall design

[June7]

The database does not 'know' - until you tell it. This is data management. You would not only have to create the new record for weeks 3-4 but also adjust the data for the original record. If you later cancel week 4 then adjust the EndDate and FTE for that record.

[jaredmason]

So I have decided to keep it quite straight-forward so far. These are my tables:

TBL_PROJECT
ProjectID*
ProjectName
Provider
IsActive
Status
Confidence

TBL_ALLOCATION
AllocationID*
ProjectID
ResourceID
FTE
WeekStartDate

TBL_RESOURCE
ResouceID*
ResouceType
ResouceLocation

At the moment I have opted out on end dates. Each record will record one work week and the date recorded is forced to be a Monday. It will be less efficient, I'm sure, but it is a bit more manageable for me to get my head around and I won't have to worry about splitting and merging allocations.

My next question is how can I easily manipulate my data from within a form. Ideally I would like to be able to open a form with a smallish table on it with WeekStartDate headings along the columns and ResourceName headings along the rows, with cells recording the various FTE's. There would be one record for every non-zero cell in the table. And adding an FTE to the table would create a record with the appropriate Project, Resource and WeekStartDate.

This is my ideal - not necessarily what I will end up doing. Can anyone think of what kind of a form I might be able to create along these lines? I definitely can't be entering data on a per allocation basis - it would be too time consuming and is just not a logical way to work with project data.

[June7]

How could configuration of date column headings be practical? You want 52 columns? The form could not be bound to the table you have described. An unbound form would require elaborate VBA code to save records.

[jaredmason]

I wouldn't expect to need more columns and rows than I could view on a single page. 12-20 columns would be nice. Definitely not 52.

How many records could you reasonably expect to be bound to a form? Or what is a possible solution that incorporates a similar way of viewing the data? I need to be able to see the numbers for several weeks and several resources in quite a close proximity. Otherwise I will end up doing everything twice - once to view all my FTE's in excel and then again when I pump the numbers into Access. In which case I can't see myself saving much time using a database.

[June7]

What other way would you enter data than as a record for each allocation?

A form can be bound to a table or query. Number of records is dictated by the size limit of Access which is 2gb.

I already described alternative to bound form. It would be an unbound form with VBA code to display and save data. Can be very complicated.

Your first post said you wanted to remove Excel from this process and use Access for data management. Why would you view FTE's in Excel?

[jaredmason]

What other way would you enter data than as a records for each allocation?

Sorry - what I meant was that I need to be able to enter records in sets. If I was to enter records one-by-one it would not only be time consuming, it would be very hard to visualize what was happening in the project. Hence I said that if I was not able to create an entry form that was in a table format I would end up needing to use Excel as well to analyze my figures before entering them.

The key is, any time I enter a record I need to also be looking at the other records for that project in the surrounding weeks to be able to visualize the flow of the project.

[June7]

How would Excel facilitate this 'set' entry? Still need to enter each distribution portion into a row that represents a week. That is what would take place in an Access form. Set the form to Continuous or Datasheet view and can see all the week records for a project.

[jaredmason]

It sounds like its not going to be possible without VBA coding, but at some point I need to be able to see the data in this format:

         JAN-2 JAN-9 JAN-16 JAN-23
R&D_____  1.0   1.0   1.0
DEV_____              2.0    2.0
TESTING_                     1.0

Instead of this:

R&D_____ JAN-2  1.0
R&D_____ JAN-9  1.0
R&D_____ JAN-16 1.0
DEV_____ JAN-16 2.0
DEV_____ JAN-23 2.0
TESTING_ JAN-23 1.0

In terms of seeing where a project is heading there is a big difference - I need to be able to see the spread of resources as I am adding them. I have not yet found a way to create this in Access, because it is not a just a filtered, sorted list of records; it is a different way of viewing records.

[June7]

The first output sample is a crosstab (pivot) query. Crosstabs can be very dynamic as far as column headings, especially when dates are involved, so it can be tricky to stabilize as RecordSource for perpetual form/report. A Pivot View form might be viable option.