I wonder if anyone can help me? Please forgive the long explanation below, i've been working on this for a few days, and finally I think I know the question I need to ask!

I'm creating a database from an existing dataset. Unfortunately i'm not able to interrogate it directly, so I have to extract the data and load it up into Access to run my queries. I have been doing this using Excel and am just reaching the limit of manipulating the data.

The data is finance and project information for a set of projects, my business needs are to assess individuals' performance by accumulating info from several projects, and track finance by project or person or location. The structure I am thinking of is as follows:

tblProjects - this info will be unique to the project
Project Number - this is a unique identifier
External partner (this is the original partner and doesn't change)
External reference

tblProjectinfo - this links the project personnel to the project
ID (autonumber field which i will insert on uploading the data)
Project Number (link to tblProjects)
Person name
Personnel number (this can't find the name in a "people" table, as people sometimes have more than one personnel no, so it has to be specific to that project)
Role in the project
Person Location ID (again, needs to be specific to project)

Then I have a load of financial info for each project that has to be in 3 separate tables. These are organised by the same records as tblProjectinfo, so each person working on a project will have a record in each of 3 tables of financial data, specific to that project/person/location/role profile. This will look like:

TblFinance1
Project No
Person name
Personnel number
Role in the project
Location ID
Expenditure2010Q1
Expentidure2010Q2
...and so on

Initially i thought each personnelNo/projectID combination could function as a composite key. However as I investigated the data, the same person can be listed more than once in the same project, either in >1 role, or >1 location, or both.

(So, to clarify, if person A is listed twice on one project in different locations or roles, the existing system should spit out 2 sets of financial records, as if 2 separate people.)

My question is: when i download the finance info from the system, i would like to link it to tblProjectInfo so I can query the finances by person. The only way I can think of doing this is to make a big composite key of projectID/PersonnelNo/Location/Role and relate this to the equivalent key in each of the the finance tables. Is this a terrible idea? Is there a better way of doing it?

I really appreciate any advice you can give. I hope i've given enough info, but if not please ask and I will try to clarify.

The composit key is good I think, if that is your identifier, it is your identifier. You can however instead of using a 4 part primary key put a unique index on the 4, then use a ID (Auto number) as your database key, so as not to overload your FKs

Where I have a problem is with the
Expenditure2010Q1
Expentidure2010Q2
...and so on
part, this is repeating data this should not be stored in columns, rather in rows in a related table.

Because primary keys are always indexed it is more efficient to use a single numeric key. Indices of text fields use much more space and take longer to update. This becomes more of an issue as the number of records in the table increase.

Thanks both, very useful comments.

Re Expenditure2010Q1 etc, I believe it isn't repeated as it's specific to that individual's part of the project. It's itemised this way so that a person's contributions to the overall expenditure can be totalled across many projects. The finance tables are lots of small packets that can be summed in several different ways. So i think it makes sense to hold each time period's expenditure as a separate field, with a value for each person/project record?

Re the 4-part key issue, on testing I've discovered a few records where all of these 4 are identical (sigh), so it's back to the drawing board on that one. I may go back to excel!

Many thanks for your time though!

Thanks both, very useful comments.

Re Expenditure2010Q1 etc, I believe it isn't repeated as it's specific to that individual's part of the project.
Yes, but.... 2010Q1, Q2, Q3, Q4
2011 ....
2012 ...
2013 ...
2020 ...

How is that not repeating?
Even if you only do Q1, Q2, Q3, Q4
Then add in the year in a seperate field, what will happen if the project lasts only 2 quarters? You have 2 empty columns...
What happens if a project lasts 1,5 years? your short 2 columns.
Solving this in a related table, IMHO, is the only way to go.


Re the 4-part key issue, on testing I've discovered a few records where all of these 4 are identical (sigh), so it's back to the drawing board on that one. I may go back to excel!

Many thanks for your time though!

This doesnt sound like something you would want to do in excel (again IMHO)

By the way, this on creating a multi-field index (http://www.btabdevelopment.com/ts/mfi) might be good to look at.

Thanks for your help everyone. I've referred some concerns to the central team who look after the source data. The existing data store isn't really a database - there are no rules about what can be entered, so it's no surprise the data is full of anomalies. In the meantime I'll work with your very helpful suggestions and try to make something that works locally!

Mailman you are right of course, I need to ditch the comfort blanket and use the right tool for the job!

thanks again,
Lizzie