relationship design diffs

[chaeljc]

hi guys and gals

im hoping someone can help me with a problem i am having designing a database.

the structure is something like this...

At the top are Projects.

Stages
Projects have (work related) Stages. Each project can have many stages. A master list of stages exists from which each project can assign itself its stages. Once the number of stages are set up they are fixed for the life of the project.

Records
Each project can have many many records.

THIS IS WERE MY PROBLEMS START TO ARISE...

(Work) Officers
For each record attached to a project, that record will have a certain number of work stages assigned to it (as detailed previously) that need to be actioned before work on that record can said to be complete.

For each stage of every record, I need to record the officer who worked that stage (name start finish date etc)

if you take a look at the attached example database you will see a form 'ProjectRecordsStagesOfficers Parent' if you open it you will see a sub form which (i think ) includes everything to link the work officer to the stage which is linked both simultaneously to the project stage and project record. only problem is try as i might the system wont allow me to assign the officers to the records' stages.

i have been trying for several days and im totally beat. Is it my relationships? do i need junction tables? should i be bringing them together with queries. I've tried multiple variations on all mentioned to no avail

I would be sooooo greatful if someone could take a look and find a solution.

kind regards

michael
(from not so sunny bangor northern ireland UK )

 

[jdraw]

When you cross post, please advise the reader so we're not wasting time with something that may already be resolved.

Cross posted at
http://www.accessforums.net/databas...ble-many-many-relationships-design-29727.html

see rationale for cross posting with message
http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters

 

[chaeljc]

hey jdraw

many thanks for your advice. cross post link read and understood.

the link to the original thread is....

ive tried posting the link to the thread as advised but as my post count is low it wont let me.

with that in mind you can find the original post at access forums . net under heading 'possible multi table many to many relationships design confusion'

you'll see from the other post that it was my first ever thread to that board and im pretty desparate to resolve problem and been working almost solely on it in work and at home from mid last week.

This is my second ever thread to any sort of IT board so please forgive my ignorance i'll be sure to follow correct procedure in future. if i get problem resolved ill be sure to update both boards.

grateful for what ever assistance.

regards

michael

 

[Severin]

If you upload your db we can have a look at it!~)

 

[chaeljc]

hi severin

That would be most appreciated ( im in work here stilllll struggling away trying to resolove the prob)

if you look back a couple of posts the database has already been uploaded 'Project Database.zip'

if you need any further clarification, please don't hesitate to contact me.

kind regards

michael

ps: in case my efforts lead you astray... as im struggling with the relationships i am not confident in what FK's should be in what tables etc.

ta

 

[Severin]

Sorry I was looking at all that cross posting stuff and lost track!~)

I will have a quick look..

 

[Severin]

If you open qry ProjectRecordsStagesOfficers and click on the grey area then you get the property sheet for the query. Change recordset type from Dynaset to Dynaset (Inconsistent Updates)

 

[chaeljc]

Re: relationship design diffs - SOLVED

hi all

just a brief note to let you know that this problem has been solved.... see cross thread note earlier.

thanks to all for your assistance.

regards

michael