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?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...|||The designn looks OK. Id' change your Employ3ee table a little though:

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.|||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.|||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...|||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|||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|||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.