Results 1 to 8 of 8

Thread: Many To Many

  1. #1
    Join Date
    Jun 2004
    Posts
    46

    Unanswered: Many To Many

    hi im implementing a database in ms access to migrate it later to SQL, its a project tracking/ employee tracking and im having trouble with some of the tables.... the relationships are as follow

    Employee : M
    Employee_ID
    Name
    Phone
    Supevirsor_Name
    Supervisor_Email

    Project : M
    Project_ID
    Project_ Name
    Description
    Project_Added

    EmployeeProject
    Employee_ID
    Project_ID
    AssignedBy

    i made this third table called EmployeeProjects for the relationship, but when i go to collect the data everything is ballistic, when i go to capture employees in the employee table everything is fine, i go to the projects and everything is fine there is a "+" in the projects its lists every single employee that i captured in employees in the same project i go to the next record and the same deal, there is a possibility that this could be that many employees can be in a project and also working in another project, what is wrong with it? can anybody help me???

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'd say nothing...

    Why don't you post the DDL for the tables (CREATE TABLE myTable99(Col1 int, ect)

    Some sample Data (INSERT INTO myTable99(Collist) SELECT Data UNION ALL SELECT ect)

    The DML You've attempted (SELECT Col1, Col2 FROM myT INNER JOIN myt2, ect)

    And the results you'd expect...

    I'd say you'd get an answer in 15 minutes of that post....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The designn looks OK. Id' change your Employ3ee table a little though:
    Code:
    Employee_ID
    LastName
    FirstName
    MiddleName
    FullName (calculated...not sure if access has that)
    PhoneArea
    PhonePrefix
    PhoneSuffix
    Phone (calculated...same as FullName)
    EMail
    Supervisor_ID (NULL or same if it's a supervisor)
    The reason you may have the situation you describe is only because of the contents of EmployeeProject. Do this:

    select Project_ID, count(*) from EmployeeProject group by Project_ID
    union all
    select 0, count(*) from Employee

    This will get you started on finding out how many employees are assigned to each project and what's the total number of employees assigned vs. the total number of employees in the organization.
    Last edited by rdjabarov; 06-30-04 at 13:23.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't have any SQL procedures that can parse that last sentence of yours, so I'm not exactly sure what the problem is.

    But I have to ask why you are developing this in MS Access if you are planning to upsize it to SQL Server anyway. Have you considered creating it in SQL Server and using an Access Data Project (.adp file) front-end? You'd get all the benefits of Access forms, reports, and modules for the interface, and you wouldn't have to upsize it later. Plus SQL Server's security is much better and easier to implement than MS Access security.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2004
    Posts
    46
    the reason for me doing it in access first is my boss maily..... hes a manufacturing engeneer and he knows nothing about servers and all that... he asked me to do it first kinda like a prototype for collecting the data... i said prior to him that i could build it in SQL save us a hole lot of time.. but he wouldnt budge... (putz)... any way i explained that particular situation ( me using access as a front end) but he was like no no Y complicate it so much... just doit in access and then we will see what to leave or what not.... any ways thanx for your help...

  6. #6
    Join Date
    Jun 2004
    Posts
    46
    i actually did that... in fact i did get the results that i wanted... but also... i dropped the tables and created them again... exactly and got them as i wanted... your solution was indeed good just got to it now... if only i'd gotten to it sooner... thanx for your help u really did help me

  7. #7
    Join Date
    Jun 2004
    Posts
    46
    Exactly what i was doing.... but also i don't know what in the heck was wrong with the tables.... so i'd dropped'm and built them again got what i wanted... what u posted... was what i did and it worked... thanx

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your boss is a putz. Oh wait, you already said that. Well tell him I said so too.

    Asking him why he bothers hiring competent people if he isn't going to trust their expert judgement. Duh.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •