Sub subforms in combination with junction tables

[TomDS]

Hello!


I have a database where:

1. Participants (companies; tblParticipant) can submit a file/application (tblFile)
2. One file can have multiple companies and one company can apply for multiple files
3. Companies are represented by one or more representatives (tblParticipantRepresentative)

This is a screenshot of the relevant relationship:



What I want now is to show a form with all files. For each file, the corresponding participants (companies) should be visible and for each participant the corresponding representatives should be visible or easily accessible.
Files, participants and representatives should be easily created/updated/deleted.

Now I'm having troubles:

1. I don't know what the best way is to represent this on a form
2. I don't know how I should deal with the junction tables

I've attached the database as well. I appreciate any help I can get.



dbTenders.zip

[June7]

You appear to be committing circular relationships. Review http://www.codeproject.com/Articles/...atabase-Design

However, I am not really understanding relationships

Can a representative be in more than one location?

Representatives are employees of the Participant companies? Or are they your employees?

What is a Tender?

[TomDS]

Thank you for your reply!


Let me provide you with the context with (nearly) the full story:

We've a list of tenders (let's say our company want to give a grant or outsource something to another company who's the best candidate for something). Those will be entered in the table tblTenders. For each tender there are some requirements that can vary (e.g. who applies need to have an ISO-quality label 'tblCertificate' or be active in a specific region 'tblLocation').
External companies 'tblParticipant' can apply for this tender by submitting a file 'tblFile'. They may work together with other companies for a file. Each participant has one or more representatives 'tblParticipantRepresentative'. One representative may represent multiple participants (companies).

To answer your questions:

A representative (company) may have offices in more than one location.
You're correct: representatives are employees of the participant companies.
Tenders are assignments/orders we have.
Indeed I have circular relationships. I didn't knew that was bad practice, I'll have to dive into that link you gave. However the thing is that each tender may have specific requirements. And each participant applying for a file may have some of those requirements. I'm not sure how else to express this.

Did I provide you with the necessary information?

[June7]

A participant company has many locations - I get that.

Each representative may represent more than one location for any number of companies? So the representatives sound like independent agents, not employees.

So purpose of db is to match tender and representative based on specifications? Location can be a specified requirement?

[TomDS]

A representative is actually a regular employee, however he can work for multiple companies (each with a different business number, however part of the same group. Random example: a company that makes goods may have an independent branch that provides IT-services and one that makes the goods. An employee may work for both of those companies as a service worker). You could see them as independent agents, though.

The purpose of the db is indeed to match tender and representatives based on specifications (specifications can only be the location and quality labels). Location can be a specific requirement (example: if the tender is to teach, we want the location to be close to the students).

[June7]

This would be a tool where the tender specifications would be input to records and a match for representative that meets the specifications identified. I know I've seen this sort of db discussed in threads. Did a web search. Bing: database sample expertise specifications. Not finding sample Access database. Basically a search engine - input keywords and find records that match.

What are locations - just cities?

I've never had to build anything like this but consider:

tblReqs
ReqID
ReqDesc

tblCities
CityID
CityName

tblTenders
TenderID
WorkingName
TenderNumber

tblParticipants
PartID
CompanyNumber

tblRepresentatives
RepID
Surname

tblPartCitiesJoin
ID
PartID
CityID

tblPartRepsJoin
ID
RepID
PartCitiesID

tblRepReqs
ID
RepID
ReqID

tblTenderCerts
CertID
TenderID
CityID
ReqID (assumes each cert will have only 1 requirement)
CertDesc

Now build queries that show related data and eventually work up to a query that matches Reps with Certs.

[TomDS]

Wow heavy. I'm not entirely sure that this is what I'm looking for. But, I'll have to check this out first thing in the morning.

Meanwhile to answer your question: location is in general a city. However to make things more complicated, it could be a speciality as well (even though we call it in-house a location). It could be 'experience teaching math' as well as 'Brussels'...

In your proposal for tblPartRepsJoin, shouldn't PartCitiesID just be PartID?

[June7]

You said Rep can represent many combinations of Parts and Cities therefore - No. I am avoiding compound keys or having to directly associate the CityID with RepID. The link is on the ID of PartCities.