Need help with changing SQL!!!!

[rvmams]

Hi,

I have got two tables with the following fields:

"tblcompanies"
companyid (primary key)
companyname

"tblprojects"
projectID (primary key)
Projectname
company1
company2
company3
company4
company5
and many other fields

(please note that all company fields in this table are lookup fields and that these tables have a one to many relationship)

I am trying to make a form where a user first picks the company from a combo box (called pickcompany) which then limits the values for another combo box (called combo84)which shows all relevant projects. After selecting a project in combo84 the form shows all the rest of the fields from "tblprojects"

Row source for pickcompany is:

SELECT [Companies].[Company ID], [Companies].[Company Name] FROM Companies;

Row source for combo84 is:

SELECT [Companies].[Company ID], [Companies].[Company Name], [Projects].[Project ID], [Projects].[Project Name] FROM Companies INNER JOIN Projects ON [Companies].[Company ID]=[Projects].[Company ID] WHERE ((([Companies].[Company Name])=[Forms]![Projects]![PickCompany])) ORDER BY [Companies].[Company Name], [Projects].[Project Name];

This all works fine if one company has many projects but I am stuck when multiple companies have one project!

Which combo box' query should I change? And to what????

Thnx in advance

RvM

 

[KenReay]

Hi

I assume that Company1, Company2 etc in your projects table are the companies associated with that Project?

If yes, your table design is fatally flawed

You should have three tables

tblCompanies
CompanyId (PK)
CompanyName
.. etc

tblProjects
ProjectId (PK)
ProjectName
..etc

tblCompanyProjects
CompanyId (PK)
ProjectId (Pk)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK