Counting Contracts

[Guest_imported]

I have two tables Pharmacy( Name(PK), Address, PhoneNO) and Contract(PName(PK), PCoName(PK), StartDate, EndDate). I need to know the SQL query to display the details of the Pharmaceutical company with the largest number of contracts. Please help ASAP

 

[kathryn]

Create a query with both table. Create a join between the two fields holding PharmacyName. All all the fields about the Pharmaceutical company and the PCoName field. Click on the Totals button on the tool bar (or choose to View -> Totals from the menu) For all the detail fields make sure that the TOTAL line in the query is GroupBy, but for the PCoName make sure that it is Count. Run the query; it should show all your companies and the counts of their contracts. To get details on only the first one, go back to design view, sort Descending on the counted field and on the toolbar where you see a dropdown box with the word ALL, type a 1. This will give you the top 1 record.

Because I am a bear on database design, I have to point out thatyou really should create an Autonumber ID field for each pharmacy, instead of using the name. The contract table would then contain the ID, not the name. That way, when Pharmacy A buys Pharmacy B you will just have to change the name of the Pharmacy B in one place, not in every row in the contract table that has Pharmacy B. [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[DougP]

I agree with Kathryn.
Plus I like to have like fields in different tables have the exact same field name.
Like PharmID, that way when you create your queries it will automtically draw a line between matching fields.

So above you would have.
Pharmacy(PharmID, Name(PK), Address, PhoneNO) and Contract(PharmID, PCoName(PK), StartDate, EndDate).
One reason database's use numbers as a Key is they take up less space (not a problem today with 40 gig drives) but they search faster too. When you run your query it will find things much faster when it is looking for 2872 rather than &quot;Eckerd Drugs Corp.&quot;. Plus you can make the ID field and Autonumber and in the Pharmacy table and let Access take case of assigning unique ID's
[sig]<p>DougP, MCP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.[/sig]

 

[kathryn]

Doug,

Please tell me that you meant to type

Pharmacy(PharmID(PK), Name, Address, PhoneNO) and Contract(PharmID(PK), PCoName, StartDate, EndDate), with the ID as the PKs.


Kathryn

[sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[MichaelRed]

Sorry, I 'thought' i posted to this but ...

Why not a crosstab query using the count of Install/cancel for the value?

If - for some reason - this in inappropiate, just do the count of each and place these in a &quot;record set&quot; [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]