Skip to content

Query: Qry Report Financial Summary

Type: Select
Has Parameters: Yes
Has Joins: No
Has Subquery: No

Tables Referenced

  • SubQry Company List Criteria
  • Delivery Locations
  • Qry Count Submission Financial Complete Entries by Company
  • Entrepreneur Company
  • Qry Get Last Pre-Ignition Job Entry by Company
  • Entrepreneur
  • Entrepreneur Company Programme Years
  • Qry Get Last Submission ID by Company
  • MonthLkUp
  • Qry Get Last Submission Job Entry by Company
  • CompanyStatus
  • Qry Get Last Ignition Date by Company
  • Products
  • Qry Count Pre-Ignition Financial Entries by Company
  • Qry Sum Pre-Ignition Financials by Company
  • Qry Sum Submission Financials by Company
  • Submissions

Columns Selected

  • [SubQry Company List Criteria].[Distribution Model ID]
  • [Entrepreneur Company].[Company Status ID]
  • [Entrepreneur Company].[RC Bookkeeper Assigned]
  • IIf(IsNull([Submission Year]),Null,IIf(IsNull([Month Number]),Null,DateAdd(\
  • [Entrepreneur Company].[Entrepreneur Company ID]
  • [Entrepreneur Company].[Company Name]
  • [Delivery Locations].[Delivery Location]
  • Status
  • Product
  • [Entrepreneur Company Programme Years].[Programme Year]
  • [Entrepreneur Company Programme Years].[Contract Start Date]
  • [Entrepreneur Company Programme Years].[Ignition Date]
  • Round(IIf(IsNull([Total PI Turnover]),0,IIf(IsNull([Number of PI Entries]),0,[To
  • Round(IIf(IsNull([PI Gross Profit]),0,IIf(IsNull([Number of PI Entries]),0,[PI G
  • Round(IIf(IsNull([PI Net Profit]),0,IIf(IsNull([Number of PI Entries]),0,[PI Net
  • Round(IIf(IsNull([Total PI Salaries]),0,IIf(IsNull([Number of PI Entries]),0,[To
  • Round(IIf(IsNull([Total PI Net Asset Value]),0,IIf(IsNull([Number of PI Entries]
  • IIf(IsNull([Last PI Directors]),0,[Last PI Directors])
  • IIf(IsNull([Last PI Permanent Staff]),0,[Last PI Permanent Staff])
  • IIf(IsNull([Last PI Contract Staff]),0,[Last PI Contract Staff])
  • [Qry Sum Submission Financials by Company].[Total Sub Turnover]
  • [Qry Sum Submission Financials by Company].[Sub Gross Profit]
  • [Qry Sum Submission Financials by Company].[Sub Net Profit]
  • [Qry Sum Submission Financials by Company].[Total Sub Salaries]
  • [Qry Sum Submission Financials by Company].[Total Sub Net Asset Value]
  • Round(IIf(IsNull([Total Sub Turnover]),0,IIf(IsNull([Number of Sub Entries]),0,[
  • Round(IIf(IsNull([Sub Gross Profit]),0,IIf(IsNull([Number of Sub Entries]),0,[Su
  • Round(IIf(IsNull([Sub Net Profit]),0,IIf(IsNull([Number of Sub Entries]),0,[Sub
  • Round(IIf(IsNull([Total Sub Salaries]),0,IIf(IsNull([Number of Sub Entries]),0,[
  • Round(IIf(IsNull([Total Sub Net Asset Value]),0,IIf(IsNull([Number of Sub Entrie
  • IIf(IsNull([Last Sub Directors]),0,[Last Sub Directors])
  • IIf(IsNull([Last Sub Permanent Staff]),0,[Last Sub Permanent Staff])
  • IIf(IsNull([Last Sub Contract Staff]),0,[Last Sub Contract Staff])
  • IIf([Avg PI Turnover]<0,(([Avg Sub Turnover]-[Avg PI Turnover])/[Avg PI Turnover
  • IIf([Avg PI Gross Profit]<0,(([Avg Sub Gross Profit]-[Avg PI Gross Profit])/[Avg
  • IIf([Avg PI Net Profit]<0,(([Avg Sub Net Profit]-[Avg PI Net Profit])/[Avg PI Ne
  • IIf([Avg PI Salaries]<0,(([Avg Sub Salaries]-[Avg PI Salaries])/[Avg PI Salaries
  • IIf([Avg PI Net Asset Value]<0,(([Avg Sub Net Asset Value]-[Avg PI Net Asset Val

Where Clause

((([Entrepreneur Company].[Company Status ID])=1 Or ([Entrepreneur Company].[Com

Order By

[Entrepreneur Company].[Company Name]

SQL

SELECT [SubQry Company List Criteria].[Distribution Model ID], [Entrepreneur Company].[Company Status ID], [Entrepreneur Company].[RC Bookkeeper Assigned], IIf(IsNull([Submission Year]),Null,IIf(IsNull([Month Number]),Null,DateAdd(\, [Entrepreneur Company].[Entrepreneur Company ID], [Entrepreneur Company].[Company Name], [Delivery Locations].[Delivery Location], CompanyStatus.Status, Products.Product, [Entrepreneur Company Programme Years].[Programme Year], [Entrepreneur Company Programme Years].[Contract Start Date], [Entrepreneur Company Programme Years].[Ignition Date], Round(IIf(IsNull([Total PI Turnover]),0,IIf(IsNull([Number of PI Entries]),0,[To, Round(IIf(IsNull([PI Gross Profit]),0,IIf(IsNull([Number of PI Entries]),0,[PI G, Round(IIf(IsNull([PI Net Profit]),0,IIf(IsNull([Number of PI Entries]),0,[PI Net, Round(IIf(IsNull([Total PI Salaries]),0,IIf(IsNull([Number of PI Entries]),0,[To, Round(IIf(IsNull([Total PI Net Asset Value]),0,IIf(IsNull([Number of PI Entries], IIf(IsNull([Last PI Directors]),0,[Last PI Directors]), IIf(IsNull([Last PI Permanent Staff]),0,[Last PI Permanent Staff]), IIf(IsNull([Last PI Contract Staff]),0,[Last PI Contract Staff]), [Qry Sum Submission Financials by Company].[Total Sub Turnover], [Qry Sum Submission Financials by Company].[Sub Gross Profit], [Qry Sum Submission Financials by Company].[Sub Net Profit], [Qry Sum Submission Financials by Company].[Total Sub Salaries], [Qry Sum Submission Financials by Company].[Total Sub Net Asset Value], Round(IIf(IsNull([Total Sub Turnover]),0,IIf(IsNull([Number of Sub Entries]),0,[, Round(IIf(IsNull([Sub Gross Profit]),0,IIf(IsNull([Number of Sub Entries]),0,[Su, Round(IIf(IsNull([Sub Net Profit]),0,IIf(IsNull([Number of Sub Entries]),0,[Sub , Round(IIf(IsNull([Total Sub Salaries]),0,IIf(IsNull([Number of Sub Entries]),0,[, Round(IIf(IsNull([Total Sub Net Asset Value]),0,IIf(IsNull([Number of Sub Entrie, IIf(IsNull([Last Sub Directors]),0,[Last Sub Directors]), IIf(IsNull([Last Sub Permanent Staff]),0,[Last Sub Permanent Staff]), IIf(IsNull([Last Sub Contract Staff]),0,[Last Sub Contract Staff]), IIf([Avg PI Turnover]<0,(([Avg Sub Turnover]-[Avg PI Turnover])/[Avg PI Turnover, IIf([Avg PI Gross Profit]<0,(([Avg Sub Gross Profit]-[Avg PI Gross Profit])/[Avg, IIf([Avg PI Net Profit]<0,(([Avg Sub Net Profit]-[Avg PI Net Profit])/[Avg PI Ne, IIf([Avg PI Salaries]<0,(([Avg Sub Salaries]-[Avg PI Salaries])/[Avg PI Salaries, IIf([Avg PI Net Asset Value]<0,(([Avg Sub Net Asset Value]-[Avg PI Net Asset Val FROM [Entrepreneur Company], [Qry Get Last Submission ID by Company], [Submissions], [MonthLkUp], [SubQry Company List Criteria], [Delivery Locations], [Qry Get Last Ignition Date by Company], [Entrepreneur Company Programme Years], [Products], [CompanyStatus], [Qry Get Last Submission Job Entry by Company], [Qry Count Pre-Ignition Financial Entries by Company], [Qry Count Submission Financial Complete Entries by Company], [Qry Sum Pre-Ignition Financials by Company], [Qry Sum Submission Financials by Company], [Qry Get Last Pre-Ignition Job Entry by Company] WHERE ((([Entrepreneur Company].[Company Status ID])=1 Or ([Entrepreneur Company].[Com