Can Access do this?

[Jan]

We have sales opportunities in which we are “teamed†with other companies for
the same project. The goal is to know how many projects we “win†but also
which “team†we tend to “win†with the most or some analysis of that sort.
Another consideration is that we may have several projects with the same
company. Can an Access database handle all of this?

 

[Allen Browne]

If one 'quote' involves many companies, that's a classic one-to-many
relation:
Client table (one record for each company you bid with)
- ClientID AutoNumber primary key
- ClientName Text

Quote table (one record for each opportunity you submit on.)
- QuoteID AutoNumber primary key
- QuoteDate when you submitted this.
- Outcome some way of identifying whether it won.

QuoteClient table, with fields:
- QuoteID which quote this record is for
- ClientID which company was involved in the quote.

You could then create a query with both tables.
Depress the Totals button (on the toolbar in query design.)
Group by ClientID, and Count QuoteID.
Set criteria where the outcome is what you want, and where it's in the date
range you want to know about.

 

[tedmi]

Just for the record, for the OP's benefit: This is a classic MANY-TO-MANY
relationship.

 

[Allen Browne]

Thanks, TedMi. That's correct.