Complex query?

[Steven W]

I have a field within the Subcontractors table which
indicates a group of companies which have tendered for a
particular project. I call this field 'shortlist'. I have
another field which states teh winning contractor (which
of course is also in the shortlisted companies field). I'd
like to be able to produce a report showing each
shortlisted company and stating which particular projects
they have tendered for. Some shortlisted companies have
tendered for multiple projects. Also, I'd like to be able
to show on the report the winning contractor.
Grateful advice.

 

[MacDermott]

Let's stop right at that first statement.
You have a table of Subcontractors, where one might assume each record
contains data (e.g. contact info) on a particular subcontractor.
You should also have a Projects table, where each record contains data about
a particular project.
One of the fields in the Projects table should be the winning contractor.
This field is specific to the project, not to the subcontractor.

Now, for your shortlist, you need a 3rd table, which we can call ShortList.
This table will have at least two fields, a foreign key to Projects and a
foreign key to Subcontractors.
If the primary key for a project and the primary key for a subcontractor
appear in the same record of ShortList, that means that this subcontractor
has tendered for this project.
Of course you can include other information in this table if you like, e.g.
date of tender, terms, etc.

Once you get your information organized in this way, you'll find it much
easier to query and get the data you want.

HTH
- Turtle

 

[Steven W]

Would you mind if I send my database to you as I cannot
figure out how to do this one
thanks

 

[MacDermott]

In the newsgroup, you get free advice from a wide variety of contributors,
who are willing to work with you on answering your questions.
Like most contributors, I also work for a living; if you would like to
purchase my services, please contact me directly by substituting
"mindspring" for "NoSpam" in my address. We can discuss rates and terms.
If you prefer to continue receiving free help in the newsgroup, put some
effort into it yourself and ask us specific questions we can help with.

- Turtle