UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Cross Tab Query Error, Access 2016    
 
   
LagoDavid
post Apr 15 2018, 10:38 AM
Post#1



Posts: 349
Joined: 12-October 03
From: Texas


I have a SELECT query and then a CROSSTAB query based upon the select query. One field of the select query, SampleGroupID, has a criteria that comes from a user input form. The Select query works just fine and uses the criteria from the form, Forms!frmReportSetup!txtSampleGroupID. The CrossTab query that is based on the Select query will not execute and instead produces the error "The Microsoft database engine does not recognize '[forms]![frmReportSetup]![txtSampleGroupID]' as a valid field name or expression".

The select query is qryAllSampleResultsOLD:
SQL
SELECT tbl101Sites.SiteName, tbl110SamplePoints.SamplePointName, [tblParametersBetzmanLIMS.BetzmanParameterName] & ", " & [tblParametersBetzmanLIMS.Units] AS ParameterName, Format([SampledDateTime],"Short Date") AS [Sample Date], IIf([Comparator]="=",[Result],[Comparator] & " " & [Result]) AS Results, [Result]/([NitrogenCount]*14) AS ResultAsN, tbl110SamplePoints.SampleSort, tblParametersBetzmanLIMS.ParameterSort, tblSampleGroups.SampleGroupID, [MoleWeight]/([NitrogenCount]*14) AS N_Factor
FROM ((tbl110SamplePoints INNER JOIN (tbl101Sites INNER JOIN (tblSampleGroups INNER JOIN tblSampleGroups_SamplePoints ON tblSampleGroups.SampleGroupID = tblSampleGroups_SamplePoints.SampleGroupID) ON tbl101Sites.SiteID = tblSampleGroups.SiteID) ON tbl110SamplePoints.SamplePointID = tblSampleGroups_SamplePoints.SamplePointID) INNER JOIN tblSamples ON tbl110SamplePoints.SamplePointID = tblSamples.SamplePointID) INNER JOIN ((lu_tblParameterGroups INNER JOIN (tblSampleResults INNER JOIN tblParametersBetzmanLIMS ON tblSampleResults.[ParameterBetzmanLIMSID] = tblParametersBetzmanLIMS.ParameterBetzmanLIMSID) ON lu_tblParameterGroups.ParameterGroupID = tblParametersBetzmanLIMS.ParameterGroupID) INNER JOIN lu_tblAmineN ON tblParametersBetzmanLIMS.ParameterBetzmanLIMSID = lu_tblAmineN.ParameterBetzmanLIMSID) ON tblSamples.SampleID = tblSampleResults.SampleID
WHERE (((tblSampleGroups.SampleGroupID)=[forms]![frmReportSetUp]![txtSampleGroupID]))
ORDER BY tbl101Sites.SiteName, Format([SampledDateTime],"Short Date"), tbl110SamplePoints.SampleSort, tblParametersBetzmanLIMS.ParameterSort;


The cross tab query is:
SQL
TRANSFORM First(qryAllSampleResultsOLD.Results) AS FirstOfResults
SELECT qryAllSampleResultsOLD.SiteName, qryAllSampleResultsOLD.ParameterSort, qryAllSampleResultsOLD.SampleSort, qryAllSampleResultsOLD.ParameterName, qryAllSampleResultsOLD.SamplePointName
FROM qryAllSampleResultsOLD GROUP BY qryAllSampleResultsOLD.SiteName, qryAllSampleResultsOLD.ParameterSort, qryAllSampleResultsOLD.SampleSort, qryAllSampleResultsOLD.ParameterName, qryAllSampleResultsOLD.SamplePointName
ORDER BY qryAllSampleResultsOLD.ParameterSort, qryAllSampleResultsOLD.SampleSort
PIVOT qryAllSampleResultsOLD.[Sample Date];
Go to the top of the page
 
theDBguy
post Apr 15 2018, 11:38 AM
Post#2


Access Wiki and Forums Moderator
Posts: 71,921
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Try adding the following line at the beginning of your Crosstab SQL:

PARAMETERS [Forms]![frmReportSetup].[txtSampleGroupID] Long;
TRANSFORM...

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
LagoDavid
post Apr 15 2018, 01:21 PM
Post#3



Posts: 349
Joined: 12-October 03
From: Texas


Well, not surprisingly since all your other advice has been excellent, this worked.

I don't understand why this was necessary for this particular cross tab. I have another "pair" (cross tab based on a select) for which this was not necessary. Any advice on understanding why sometimes and sometimes not?
Go to the top of the page
 
RJD
post Apr 15 2018, 02:27 PM
Post#4


UtterAccess VIP
Posts: 8,224
Joined: 25-October 10
From: Gulf South USA


Hi: I'll let theDBguy address the parameter issue, since you are working with him on that, but I wanted to point out something in your first query's ORDER BY clause ...

ORDER BY tbl101Sites.SiteName, Format([SampledDateTime],"Short Date"), tbl110SamplePoints.SampleSort, tblParametersBetzmanLIMS.ParameterSort;

When you use FORMAT, the result is text. So the "date" sort order will not be sequential by date but by the resulting text characters. If you really need to use FORMAT (not sure why you are using it), you should use ...

FORMAT([SampledDateTime],"yyyymmdd")

That will assure the correct date sort for a text value.

But ...

DateValue([SampledDateTime])

... will give you the date without the time, and it WILL be a date and sort-able.

This does not appear to affect the query's use in the Crosstab, but something about which you should be aware.

Just an observation ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
LagoDavid
post Apr 15 2018, 03:14 PM
Post#5



Posts: 349
Joined: 12-October 03
From: Texas


Thank you for that tip. You have been SO helpful! I will record that in my OneNote notes.
Go to the top of the page
 
RJD
post Apr 15 2018, 03:23 PM
Post#6


UtterAccess VIP
Posts: 8,224
Joined: 25-October 10
From: Gulf South USA


thumbup.gif

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
LagoDavid
post Apr 18 2018, 07:38 PM
Post#7



Posts: 349
Joined: 12-October 03
From: Texas


So DBGuy can you explain why using this PARAMETER statement was necessary?

this seems very inconsistent. I made the change you recommended and it worked. OK, good. It worked for a multiple instances, then VOILA it no longer worked. And I had to add to more parameters to the parameter statement, and not it works again. I now have a parameter for all three fields in the underlying query which have criteria, so I guess I should not have any more problems. But I don't understand the inconsistency. I have another query/CrossTab query pair, almost identical, and I have not had to add the PARAMETER line. Is that just lucky and for the sake of continuous prosperity should I go ahead and add the PARAMETERS statement to the other pair?
Go to the top of the page
 
theDBguy
post Apr 18 2018, 08:07 PM
Post#8


Access Wiki and Forums Moderator
Posts: 71,921
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Unfortunately, I don't have the answer for the "why." Yes, it would be safer to just add the PARAMETERS statement to any CROSSTAB query with parameters. My guess is the TRANSFORM statement needs to be aware of all the data types involved while processing the query.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
LagoDavid
post Apr 18 2018, 09:45 PM
Post#9



Posts: 349
Joined: 12-October 03
From: Texas


Well, your 2 cents is worth a lot more than mine so I will take both pennies worth of advice.
Go to the top of the page
 
LagoDavid
post Apr 18 2018, 09:48 PM
Post#10



Posts: 349
Joined: 12-October 03
From: Texas


Oh, and FYI. It is not the cross tab query that has parameters. It is the query that the cross tab is based on that has the criteria in three of the columns. And each of the criteria is in the format of Forms!frmMyForm!txtMyTextBox, and all are on the same form.
Go to the top of the page
 
theDBguy
post Apr 18 2018, 11:03 PM
Post#11


Access Wiki and Forums Moderator
Posts: 71,921
Joined: 19-June 07
From: SunnySandyEggo


Hi,

In case it wasn't clear from my previous post, you were getting the error only because you were using a Crosstab query. You can skip declaring parameters in a regular query and you might not get an error. However, if you don't declare parameters in a Crosstab query, you will almost always get an error.

Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
LagoDavid
post Apr 19 2018, 10:11 AM
Post#12



Posts: 349
Joined: 12-October 03
From: Texas


And just so I am clear on this

There are no parameters in my cross tab query. The parameters are actually located in the query that the cross tab is based upon. Perhaps this means I should be saying there are parameters in the cross tab?

If I have a cross tab query, and the cross tab is based on a query with criteria, I should include a PARAMETERS clause in the cross tab SQL and include each of the criteria that are in the "base" query.
Go to the top of the page
 
RJD
post Apr 19 2018, 10:25 AM
Post#13


UtterAccess VIP
Posts: 8,224
Joined: 25-October 10
From: Gulf South USA


It is a characteristic of crosstabs that if the feeder queries have parameters, generally these must be also defined in the crosstab, for definitional purposes. The criteria, however, need not be repeated in the crosstab.

I do not know the underlying details on this. Just experience ...

HTH
Joe

from phone

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
theDBguy
post Apr 19 2018, 10:30 AM
Post#14


Access Wiki and Forums Moderator
Posts: 71,921
Joined: 19-June 07
From: SunnySandyEggo


Hello,

If the crosstab query is based on a parameter query, then the crosstab query needs to be aware of its data type. You could try declaring the parameters in the underlying query to see if the crosstab would be fine with it or not.

Just to clarify, the crosstab query probably needs to know in advance about the data types of any parameters that need to be evaluated during the entire process - those parameters could be in the crosstab or in the underlying data source.

I think the confusing part is parameters don't need to be declared for a normal query to work but then fails once it becomes part of a crosstab query.

Hope it makes sense...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
LagoDavid
post Apr 20 2018, 10:13 PM
Post#15



Posts: 349
Joined: 12-October 03
From: Texas


thank you. I do understand the requirements now.

The confusing part is that apparently it is not always required, because I have the other parameter query / crosstab query which works just fine without defining the parameter data types. So I will just leave it as a wonderment and plan to define the parameter data types for each crosstab query if the underlying query has parameters.

Thank you again for the help.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd April 2018 - 05:48 AM