Crosstab Query with Dynamic Rows and Columns for use in Report

[kjschmitz]

Bear with me as I am unsure if I am doing this in the correct manner.

I have a collection of 5 tables pertinent to this question.

Projects:
Project_ID (PK - autonumber)
Project_Name (text)
[additional fields not pertinent]

Eligible_Bidders
Project_ID (FK)*
Contractor_ID (FK)*

Contractors
Contractor_ID (PK - autonumber)
Contractor_Firm (text)
[additional fields not pertinent]

Unit_Pricing
Project_ID (FK)
Unit_Price_ID (PK - autonumber)
Unit_Price_Desc (text)
Unit_Price_Number (number)

Project_Alternates
Project_ID (FK)
Proj_Alt_ID (PK - autonumber)
Alt_Desc (text)
Alt_Number (number)

I would like to create a matrix for use in bid tab creation by our project managers. Each project will have a collection of eligible bidders (firms) that I would like to be represented in the rows of my crosstab query.

The columns would be both the project Alts (can have 0 - n) and Unit Prices (again 0 - n) as well as a base bid amount (easy part). I am struggling with creating the queries in a manner that will translate into a report (for printing purposes).

I have the following code for creating a ctb_Proj_Alts query:

PARAMETERS [Forms]![frm_Projects]![Project_ID] Long;
TRANSFORM Sum(Unit_Pricing.Unit_Price_Number) AS SumOfUnit_Price_Number
SELECT Contractors.Contractor_Firm
FROM ((Projects INNER JOIN (Contractors INNER JOIN Eligible_Bidders ON Contractors.Contractor_ID = Eligible_Bidders.Contractor_ID) ON Projects.Project_ID = Eligible_Bidders.Project_ID) INNER JOIN Project_Alternates ON Projects.Project_ID = Project_Alternates.Project_ID) INNER JOIN Unit_Pricing ON Projects.Project_ID = Unit_Pricing.Project_ID
WHERE (((Projects.Project_ID)=[Forms]![test_Projects]![Project_ID]))
GROUP BY Contractors.Contractor_Firm
PIVOT [Alt_Number] & ": " & [Alt_Desc];

and similarly for a ctb_Unit_Price query:

PARAMETERS [Forms]![frm_Projects]![Project_ID] Long;
TRANSFORM Sum(Unit_Pricing.Unit_Price_Number) AS SumOfUnit_Price_Number
SELECT Contractors.Contractor_Firm
FROM ((Projects INNER JOIN (Contractors INNER JOIN Eligible_Bidders ON Contractors.Contractor_ID = Eligible_Bidders.Contractor_ID) ON Projects.Project_ID = Eligible_Bidders.Project_ID) INNER JOIN Project_Alternates ON Projects.Project_ID = Project_Alternates.Project_ID) INNER JOIN Unit_Pricing ON Projects.Project_ID = Unit_Pricing.Project_ID
WHERE (((Projects.Project_ID)=[Forms]![frm_Projects]![Project_ID]))
GROUP BY Contractors.Contractor_Firm
PIVOT [Unit_Price_Number] & ": " & [Unit_Price_Desc];

I would ideally like to combine these into a single crosstab, but if that is unreasonable I can keep them seperate. Both queries run appropriately from the query design pane.

I do not need values to collect within the matrix (right now the SumOF### are just placeholders so that I can get the "Excel table" feel to the report.)

The problem I seem to have is when I try and combine them or create a report with the queries as subreports. I am able to create the subreports using the report qizard based on the crosstab queries, but any change to the variable [Forms]![test_Projects]![Project_ID] seems to create probelms.

Also, when I use those created reports as subreports, the error I recieve is the following:

You can't use a pass-through query or a non-fixed column crosstab
query as a record source for a subform or subreport.

Maybe I am missing something, this has been the basis of most of my efforts:

http://allenbrowne.com/ser-67.html

Any guideance would be greatly appreciated.

 

[dhookom]

Allen's link has links to my site. Did you attempt to use my demo on Roger's Access Library? Do you need a more recent version?

I also have an FAQ in the query's forum on multiple value crosstabs.

Duane
Hook'D on Access
MS Access MVP

 

[kjschmitz]

Thank you Duane,

I have convinced management that collecting data outside of the system on a printed "report" is not ideal, and have begun working on a form to allow entry of this data instead. I appreciate the example file and FAQ though, and will continue to reference those in future efforts.