Question Relationships - Kickin' my hind end!

[MoHntr]

Hello everyone,

I have posted a question in "Theory and Design" forum... with a sample of the database I am trying to design. I hope it is ok that I am asking for help here too.

I am trying to design a database to track civil construction jobs along with employees and equipment on the jobs. I have had almost all of it working at one time or another. I soon became overwhelmed with the learning process. I bought the "Inside Out" book and it is very informative. I originally spent too much time trying to make everything attractive rather than fully functionable. So in my frustration I pretty much deleted everything and have started over. Here is what I need the most help with:

1: What is the correct way to show more than one employee and more than one piece of equipment on a job? I know it is a one to many relationship but still have trouble setting up the form to make it work.

2: Is it difficult to store the jobs with employees and equipment to look back on... but still be able to move the employees and equipment to a different job number?

Thanks for any help or advice,

MoHntr

 

[georgedwilkinson]

Please take a look at Pat Hartman's M:M demo: Many-to-many example

 

[MoHntr]

Thank you George for the link. I have a question about the above database. I see that Pat Hartman is using a linking or joining table. (I think) - So by using the joining table this creates the many to many relationship?

Thanks for your time.

MoHntr

 

[georgedwilkinson]

Yes. It is called an associative or junction table. That is how you implement a many to many relationship.

You'll put the junction table's data in your subform(s) as Pat did, with look ups into the other side of the M:M relationship.

 

[MoHntr]

Yes, I see. I have created a simple db based on Pats.... using my tblClients and tblJobs. I have been able to add several clients to a job. Now I just need to re-think some things and get this to work with my employees and equipment.

Thanks again George,

MoHntr

 

[georgedwilkinson]

Let us know if you have questions. Be specific!

 

[gemma-the-husky]

Thank you George for the link. I have a question about the above database. I see that Pat Hartman is using a linking or joining table. (I think) - So by using the joining table this creates the many to many relationship?
Thanks for your time.

MoHntr

I think its worth clarifying this - the pont is there IS NO many to many relationship. Access (any RDBS) cannot handle them. You have to break a many to many into two 1 to many relationships

So if you have

employees /equipemnt , and each employee can have several pieces of equipment, and each equipment can be used by several employees (ie many - many) then you have to do it weith an intermediate table

so

employee 1 - m employee-equipment-usage m - 1 equipment

hope that makes sense

 

[MoHntr]

Thank you George and Gemma, I am now studying the linking table. I am still a bit confused on which way my querys should run, but I am learning. At least I believe I'm headed in the right direction.

MoHntr