Solved Query Fields not Visible When Creating Form (1 Viewer)

[RonieB]

I have a query that I am trying to create a subform and place on a main form. When I create the subform it works fine but when I place it on the main form it will not show and gives the error that the form is not bound. When I try to create the subform from a query, the fields of the query are not available to select. Could you please help me figure out what I am doing incorrectly?

my query:
PARAMETERS [Forms]![frmWorksheetWT]![cboWSProjSel] IEEESingle;
SELECT Sum(Nz([NP])) AS NPR, Sum(qryMH_PSCT.PL) AS PLR, Sum(Nz([SW])+Nz([SF])) AS STEEL, Sum(Nz([PF])+Nz([PW])) AS PIPE, Sum(Nz([OM])+Nz([IM])) AS MACH, Sum(Nz([EL])) AS ELEC, Sum(Nz([RG])) AS RGG, Sum(Nz([CP])) AS CARP, Sum(Nz([DK])) AS DOCK, Sum(Nz([NP])+Nz([PL])+Nz([SF])+Nz([SW])+Nz([PF])+Nz([PW])+Nz([OM])+Nz([IM])+Nz([EL])+Nz([RG])+Nz([CP])+Nz([FW])+Nz([DK])) AS TOTAL
FROM qryMH_PSCT
GROUP BY qryMH_PSCT.WEstProj
HAVING (((qryMH_PSCT.WEstProj)=[Forms]![frmWorksheetWT]![cboWSProjSel]));

 

[arnelgp]

your Query is non-editable because what you have is a Total (aggregate) query.

 

[RonieB]

I changed it from a totals query, same thing.
my query:
PARAMETERS [Forms]![frmWorksheetWT]![cboWSProjSel] IEEESingle;
SELECT Sum(Nz([NP])) AS NPR, Sum(Nz([PL])) AS PLR, Sum(Nz([SW])+Nz([SF])) AS STEEL, Sum(Nz([PF])+Nz([PW])) AS PIPE, Sum(Nz([OM])+Nz([IM])) AS MACH, Sum(Nz([EL])) AS ELEC, Sum(Nz([RG])) AS RGG, Sum(Nz([CP])) AS CARP, Sum(Nz([DK])) AS DOCK, Sum(Nz([NP])+Nz([PL])+Nz([SF])+Nz([SW])+Nz([PF])+Nz([PW])+Nz([OM])+Nz([IM])+Nz([EL])+Nz([RG])+Nz([CP])+Nz([FW])+Nz([DK])) AS TOTAL
FROM qryMH_PSCT
WHERE (((qryMH_PSCT.WEstProj)=[Forms]![frmWorksheetWT]![cboWSProjSel]));

 

[RonieB]

I get this error

 

[plog]

First, that's still a totals query--you have SUM() functions in the SELECT.

2nd and more importantly, You've either not normalized your table properly or you did and then totally undid it in qryMN_PSCT. You should not have field named after data. NP, SW SF, etc. are all poor field names and should not be the names of fields in a table. If qryMN_PSCT is a pivot query and all those values are from a Type field, then you shouldn't add them together after you've pivoted the data. A pivot table should be the last step in a query process, not the first or intermediary.

I suggest you tell us what it is you are ultimately trying to do. Can you give us a copy of your database--one that includes the ultimate table, qryMN_PSCT and the forms?

 

[RonieB]

I am trying to get the craft hour totals on the Pricing sheet data tab of the worksheet in the attached database.

 

[plog]

Cross tabs are not your friend. They should be used sparingly and never to build other things upon.

Run qryMH_PSCT by itself and put 9898 into the prompt. You get 4 fields. Now go into design view of qMHbyCraft. It's based on just qryMH_PSCT and uses like 10 different fields. Its looking for fields that don't exist in its source, it has no idea what you are talking about and blows itself up. Then you try and build a form on top of that even.

I would ditch qryMH_PSCT and base your Craft form on a simple Totals query that lists your categorys vertially (with multiple rows) instead of horizontally (with multiple columns). It will show just the values that match your project and take like 20 minutes to set up.

Or, you could spend an hour or two and manually make a cross tab query that ensures all your values are represented. Right now you are transforming the sCraft field and if a value doesn't exist for one of those, it doesn't become a field. To ensure every value exists you make a query like so:

SELECT IIf(sCraft='DK', STEHR, 0) AS DK, IIf(sCraft='OM', STEHR, 0) AS OM, ...etc, etc.
FROM tbl_Worksheet INNER JOIN tbl_SOW ON tbl_Worksheet.ProjIdxID = tbl_SOW.ProjIdxID
etc, etc..

That way every field you expect will exist and you can keep your horizontal layout. Again though, I recommend a simple totals query and reconfigure your form to display vertically instead of horizontally.

 

[RonieB]

Thank you very much for the feed back and will do the suggested changes.

 

[RonieB]

If you don't mind my asking, where would I put the code if I were to spend the hour to build it?

 

[plog]

In a query.

 

[RonieB]

it tells me that I do not have SELECT IIf(sCraft='DK', STEHR, 0) AS DK, IIf(sCraft='OM', STEHR, 0) AS OM as part of an aggregate function. Do you mind explaining how I make it part of an aggregate function please?

 

[RonieB]

I was curious and testing it. This is my query code:
PARAMETERS [Forms]![frmWorksheetWT]![cboWSProjSel] IEEESingle;
TRANSFORM Sum(tbl_SOW.STEHR) AS SumOfSTEHR
SELECT IIf(sCraft='DK', STEHR, 0) AS DK, IIf(sCraft='OM', STEHR, 0) AS OM
FROM tbl_Worksheet INNER JOIN tbl_SOW ON tbl_Worksheet.ProjIdxID = tbl_SOW.ProjIdxID
WHERE (((tbl_Worksheet.WEstProj)=[Forms]![frmWorksheetWT]![cboWSProjSel]) AND ((tbl_Worksheet.WItemType)="A"))
GROUP BY "A-" & [WItemInd], tbl_Worksheet.ProjIdxID, tbl_Worksheet.WEstProj
PIVOT tbl_SOW.SCraft;

 

[plog]

You completely missed the point with your query code. You will not be using a cross-tab query, which means TRANSFORM and PIVOT should not be in your SQL. You are doing it manually, by adding a calculated field for every value you want to turn into a column.

That delt with your latest post, here's to your prior one--don't make it an aggregate query yet. You're biting off more than you can chew when you do. Get what a simple individual query working correctly first--its going to be complicated and very likely you mess up a field. Once you have it correct at the individual level you can then turn it into an aggregate function and add SUM to all those calculated fields.

 

[RonieB]

PARAMETERS [Forms]![frmWorksheetWT]![cboWSProjSel] IEEESingle;
SELECT "A-" & [WItemInd] AS [Work Item], tbl_Worksheet.ProjIdxID, tbl_Worksheet.WEstProj, tbl_SOW.SCraft, Sum(tbl_SOW.STEHR) AS SumOfSTEHR
FROM tbl_Worksheet INNER JOIN tbl_SOW ON tbl_Worksheet.ProjIdxID = tbl_SOW.ProjIdxID
WHERE (((tbl_Worksheet.WEstProj)=[Forms]![frmWorksheetWT]![cboWSProjSel]) AND ((tbl_Worksheet.WItemType)="A"))
GROUP BY "A-" & [WItemInd], tbl_Worksheet.ProjIdxID, tbl_Worksheet.WEstProj, tbl_SOW.SCraft
HAVING (((tbl_SOW.SCraft)=IIf([sCraft]='dk',[STEHR],0)));

 

[RonieB]

ok, I have the select query working and it will be in rows not columns. I apologize I was just trying to see if I could get it to work and spend the hour later. I am not SQL efficient and always look for ways to learn it. Simple queries I am good at lol.