A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Query question



 
 
Thread Tools Display Modes
  #1  
Old June 8th, 2008, 07:45 PM posted to microsoft.public.access.queries
Ticotion
external usenet poster
 
Posts: 56
Default Query question

Hi

I have a cross tab query that gets week no. input from a form. The cross tab
should show week number, department and a OEE value. I use the following
select statement and I've defined the form input in the parameter menu.

PARAMETERS [Forms]![Frm_fac]![combo5] Text ( 255 ),
[Forms]![Frm_fac]![combo11] Text ( 255 );
TRANSFORM Sum(qry_generelOEE.OEE) AS SumOfOEE
SELECT qry_generelOEE.weeknb
FROM qry_generelOEE
WHERE (((qry_generelOEE.weeknb) Between [Forms]![Frm_fac]![combo5] And
[Forms]![Frm_fac]![combo11]))
GROUP BY qry_generelOEE.weeknb
PIVOT qry_generelOEE.dptnb;

The problem is that I get an error massage saying the statement is to
complex (error statement 3010). Is there anyóne who get help me with a
solution how to simplyfie the query?

Thanks
Ticotion

  #2  
Old June 9th, 2008, 03:57 AM posted to microsoft.public.access.queries
Allen Browne
external usenet poster
 
Posts: 11,706
Default Query question

"Too complex" is Access-speak for "can't figure it out." There can be many
reasons for that (e.g. too many ANDs in WHERE), but a common one is that the
data types are not what is expected.

If you open your table, in design view, is weeknb actually a TEXT field? If
so, you will get some very strange results, e.g. weeks 11 through 19 will
sort before week 2 (since text fields sort character-by-character, and the
first character - the 1 - is less than 2.)

If weeknb is a Number field, you need to set up the *bound* column of combo5
and combo11 so that it is a numeric value (regardless of what the combo's
display value actually is.) Once the Value of the combos is the correct
number, set their Format property to General Number (so Access knows they
are numbers), and change the parameters in the query to Integer rather than
Text. It should now be able to apply the numeric numbers as parameters for
the numeric field weeknb.

If that still doesn't work, the problem is probably deeper down (in the
lower level query.)

More info about forcing Access to understand the correct data type:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ticotion" wrote in message
...

I have a cross tab query that gets week no. input from a form. The cross
tab
should show week number, department and a OEE value. I use the following
select statement and I've defined the form input in the parameter menu.

PARAMETERS [Forms]![Frm_fac]![combo5] Text ( 255 ),
[Forms]![Frm_fac]![combo11] Text ( 255 );
TRANSFORM Sum(qry_generelOEE.OEE) AS SumOfOEE
SELECT qry_generelOEE.weeknb
FROM qry_generelOEE
WHERE (((qry_generelOEE.weeknb) Between [Forms]![Frm_fac]![combo5] And
[Forms]![Frm_fac]![combo11]))
GROUP BY qry_generelOEE.weeknb
PIVOT qry_generelOEE.dptnb;

The problem is that I get an error massage saying the statement is to
complex (error statement 3010). Is there anyóne who get help me with a
solution how to simplyfie the query?

Thanks
Ticotion


  #3  
Old June 10th, 2008, 04:08 AM posted to microsoft.public.access.queries
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Query question

"Allen Browne" wrote in
:

"Too complex" is Access-speak for "can't figure it out." There can
be many reasons for that (e.g. too many ANDs in WHERE), but a
common one is that the data types are not what is expected.


Another common one is that the existing query compilation is out of
date and for some reason is not being updated. A compact can fix
this, or pasting the SQL into a new query if you don't want to
compact the whole db (which will invalidate all the "good"
compilation of queries).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
  #4  
Old July 7th, 2008, 02:04 PM posted to microsoft.public.access.queries
Ticotion
external usenet poster
 
Posts: 56
Default Query question

Hi

I worked it out. It was simply related to paramter date typ which wass set
as text and not as date

thanks for your help

Ticotion

"Ticotion" wrote:

Hi

I have a cross tab query that gets week no. input from a form. The cross tab
should show week number, department and a OEE value. I use the following
select statement and I've defined the form input in the parameter menu.

PARAMETERS [Forms]![Frm_fac]![combo5] Text ( 255 ),
[Forms]![Frm_fac]![combo11] Text ( 255 );
TRANSFORM Sum(qry_generelOEE.OEE) AS SumOfOEE
SELECT qry_generelOEE.weeknb
FROM qry_generelOEE
WHERE (((qry_generelOEE.weeknb) Between [Forms]![Frm_fac]![combo5] And
[Forms]![Frm_fac]![combo11]))
GROUP BY qry_generelOEE.weeknb
PIVOT qry_generelOEE.dptnb;

The problem is that I get an error massage saying the statement is to
complex (error statement 3010). Is there anyóne who get help me with a
solution how to simplyfie the query?

Thanks
Ticotion

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:23 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.