Query Help Specifying Records Instead of Fields (1 Viewer)

[Denyial]

I've got this example data-set (see attached) where each number corresponds to the primary key in another table, each number being a company name (not really relevant).

What I want to be able to do is:

1. Produce a query table result with the Total Project Value (sum of all values) each company (305-9) is involved in. For example, in Project A and C, 308 are not involved, so the 308's Total Project value would be B+D+E+F Valuation.

2. Produce a query table result with the Average Project value. Similar to above, but taking the total and dividing it by the number of projects they were involved in. For example, 308 had a total of 259 over 4 projects, so an average of 64.75

3. Produce a query table result where it takes the most common role for each company ID. For example, Company 308 was Civil 3 times and Structural 1 time, giving 308 with Civil as their most common role.

I'm sorry this is so complicated, but I'm really not getting my head around this query stuff and am stressing out hugely.... thank you for any help you can lend me!!!

Just let me know if you need more info.

 

[Minty]

Your data structure doesn't make much sense - surely a contract is either Structural Or Civil not both ?
If it is both then I would guess that the values are split differently between them?
If it is you need to redesign your data as you will run into all sorts of issues.

I would suggest you need a table for the project and its data, then a secondary table with the individual bid elements per contractor and per work type. This will make adding things up and averaging them much easier as you will only have a record where they are involved.

Generally if you have a field that could be determined to be data in its type (e.g. a work type Civil, Structural, Commercial, Residential, Infrastructure, Housing etc etc) you should be storing it as a type of data not adding new fields for each type.

 

[plog]

To achieve what you want, you need to structure your table properly. You need to add an autonumber primary key to your Example Data table, take all the company fields out of that table and put it in a new table. Let's call that new table CompanyRoles. Then your tables would look like this:

Example Data
ExampleDataID, ProjectName, Stage, Valuation
1, A, Tender, 5
2, B, Contract, 10

CompanyRoles
ID_ExampleData, Role, ID_Company
1, Structural, 307
1, Civil, 306
1, Bidder, 305
2, Contractor, 306
3, Structural, 307
...


Get your data into that structure and what you want becomes simple. There might be other issues with the Example Data table, but you didn't provide a real name for it, so I don't know its purpose.

Also, I advise you remove spaces from table and field names. 'Example Data' and 'Project Name' shouldn't contain spaces, it just makes writing queries difficult.

 

[Denyial]

The companies (corresponding to the numbers) can perform multiple roles (Civil or Structural Engineer Consultants, or contractors in their own right etc) for a variety of projects (A,B etc).
Each record corresponds to a different project, with the various companies fulfilling the various roles depending on the project.

If you don't mind me asking, how would you structure the secondary table? I felt as if I might need to reformat my data but I didn't know how to.

 

[Denyial]

Lol Plog answered it for me, OK I'll see if I can work with that, thank you!!!