Complicated SQL Query help! (1 Viewer)

[Denyial]

I currently have two tables.
One with a company list with related information on each company.
The other, with projects and related information on each project.

Table 1's field headers are:

Company Name | Percentage

Table 2's are:

Project Name | Stage | Contractor | Bidder

where under Contractor and Bidder are Company names.

The aims and order of my code needs to:

1. Take Table 1, and take the company name in that record
2. Count the number of times that company name is listed under 'Contractor' in Table 2
3. For all records Stage = Contract in Table 2, count the number of times the company name is listed underneath Bidder
4. Use the two counts to find a percentage
5. Auto-fill that percentage into 'Percentage' on Table 1.

I know it's a bit complicated but I was wondering if anyone was able to give me some help/tips/pointers! Particularly with Points 3 and 4...

Thanks!

 

[plog]

5. Auto-fill that percentage into 'Percentage' on Table 1.

First, that's not how databases work. You don't store calculated data. Instead, you calculate it. This value you want shouldn't be stored in a table, but generated by a query. Then when you need to reference it, you reference the query.

Second, " Use the two counts to find a percentage". Is ambigous. Deriving percentages means division, which means a numerator and a denominator, but which is which?

Thirdly, You really shouldn't use the [Company Name] as a link between the two tables. You should assign an autonumber primary key in Table 1 and use that value in Table 2. That way you avoid misspellings (Dave's Towing Company, Daves Towing Co., David's Tow, etc.) and if a company ever changes names you won't have to update that value everywhere, you simply update it in Table 1 and since Table2 is linked by a number it automatically finds the right name.

4th, can you post sample data? Include 2 sets--A. starting sample data from your tables (include table and field names) and enough data to cover all cases. And B.--what you expect as a results based on the data in A.

 

[Denyial]

OK let me set it up, how best to send the example Tables?

 

[Denyial]

Table 1:

---------------------------------------------------------------------------------
| Company ID | Company Name | Percentage |
---------------------------------------------------------------------------------
| 305 | Carillion Lafarge Jv | |
---------------------------------------------------------------------------------
| 306 | Carillion Plc | |
---------------------------------------------------------------------------------
| 307 | Carillion Plc | |
---------------------------------------------------------------------------------
| 308 | Carillion Plc Head Office | |
---------------------------------------------------------------------------------
| 309 | Carless & Adams Partnership | |
---------------------------------------------------------------------------------

Table 2:

---------------------------------------------------------------------------------
| Project Name | Stage | Contractor | Bidder |
---------------------------------------------------------------------------------
| A | Tender | 0 | 305 |
---------------------------------------------------------------------------------
| B | Contract | 306 | 305 |
---------------------------------------------------------------------------------
| C | Tender | | 306 |
---------------------------------------------------------------------------------
| D | Contract | 309 | 306 |
---------------------------------------------------------------------------------
| E | Contract | 305 | 307 |
---------------------------------------------------------------------------------
| F | Contract | 305 | 306 |
---------------------------------------------------------------------------------

Ideal Table 3 (with correct percentages):

---------------------------------------------------------------------------------
| Company ID | Company Name | Percentage |
---------------------------------------------------------------------------------
| 305 | Carillion Lafarge Jv | 67 |
---------------------------------------------------------------------------------
| 306 | Carillion Plc | 33 |
---------------------------------------------------------------------------------
| 307 | Carillion Plc | 0 |
---------------------------------------------------------------------------------
| 308 | Carillion Plc Head Office | |
---------------------------------------------------------------------------------
| 309 | Carless & Adams Partnership | 100 |
---------------------------------------------------------------------------------

 

[Denyial]

That might not have been as helpful as I envisaged. Sorry.

 

[plog]

You missed a few instructions (Table name, how to calculate percentage), but the data you provided makes sense and I can see what you want. This isn't too hard, so I'm going to walk you through it. Its going to take 3 queries.

CompanyPercentages_sub1: Create a query based on table 2 and bring down the Stage and Contractor fields. Then, in a blank field space type in this:

ContractorTimes: Contractor

Then in the ribbon make this a totals query by clicking the Sigma (aka Summation sign). Underneath the Stage field change the 'Group By' to 'Where' and type in 'Contract'. Leave the 'Group By' under the Contractor field and change the 'Group By' under ContractorTime to 'Count'. Save this query with the name 'CompanyPercentages_sub1'.

CompanyPercentages_sub2: Copy the query you just made,'CompanyPercentages_sub1' and paste it with the name 'CompanyPercentages_sub2'. Open it in Design View, replace the Contractor field with Bidder and and change the ContractorTimes field to this:

BidderTimes: Bidder

Save the query.

CompanyPercentages: Create a new query using Table 1, CompanyPercentages_sub1 and CompanyPercentages_sub2. Join Table1 to CompanyPercentages_sub1 from Table1.CompanyID to CompanyPercentages_sub1.Contractor. Join Table1 to CompanyPercentages_sub2 from Table1.CompanyID to CompanyPercentages_sub2.Bidder. Then, right click on the link line from Table1 to CompanyPercentages_sub1, select Join Properties and select the option that shows all the results from Table1. Do the same on the link line from Table1 to CompanyPercentages_sub2.

Bring down the CompanyID field and then create a calculated field using this:

Percentages: ContractorTimes/(ContractorTimes + BidderTimes)

Run that query and you should get the results you want, along with a few errors for the fields without results. Do all that and I will walk you through fixing those errors.

 

[Denyial]

OK, first off thank you so much for being the first person ever on an advice forum to give a clear and non-patronising answer, seriously thank you!

Did everything, got a blank table as the result... https://imgur.com/F19oaIP

 

[Denyial]

Probably should have cropped that.. oh well.

 

[plog]

That TestProject table is screwing things up. The problem is you've linked it to TestCompany in a very restrictive way. Logically, it will only show results where the Contractor is the same as the Bidder in the TestProject table.

Contractor has to equal CompanyID
Bidder has to equal CompanyID
Therefore, Contractor has to equal Bidder.

As a first step, I'd completely remove TestProject from the query entirely. Run it and look at the results you get. Make sure they seem right--maybe manually check one or two.

Then we can talk about adding TestProject back in the mix. Explain what you are trying to accomplish with having it there.

 

[Denyial]

OK when I run it without TestProject, I get the list of Company IDs, all the Percentages are blank.

 

[plog]

Time to open up the individual sub queries one at a time to see if they are working.

If can, will you upload your database? I'll give it a look.

 

[Denyial]

I am aware it's probably too late for you, but you've been just so darned helpful. Can I still send it to you?

 

[Minty]

You can upload a copy to the forum. Just with sample data.

 

[Denyial]

Here's what I've done so far. Sorry for being a useless newbie, got set a job and am woefully bad at this.

 

[plog]

I screwed up on the _sub2 query. This should be that SQL:

SELECT TestProject.Contractor, Count(TestProject.Bidder) AS BidderTimes
FROM TestProject
WHERE (((TestProject.Stage) Like "*Contract"))
GROUP BY TestProject.Contractor;

Use that as your _sub2 query then rebuild the main query appropriately.