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

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Cross Tab Query, Office 2003    
 
   
Ajazz
post Apr 11 2012, 08:52 AM
Post #1

UtterAccess Addict
Posts: 172
Hello Everyone !

CODE
TRANSFORM First([01DBase].Enquiries) AS FOE

SELECT [01DBase].Region, [01DBase].User

FROM 01DBase

GROUP BY [01DBase].Region, [01DBase].User, [01DBase].MnDate

PIVOT [01DBase].MnDate In (SELECT Test.Dts FROM Test);


Error:- Data type mismatch in criteria expression

'MnDate' & 'Dts' are date fields !


THANK YOU !
Go to the top of the page
 
+
RAZMaddaz
post Apr 11 2012, 09:01 AM
Post #2

UtterAccess VIP
Posts: 6,170
From: Bethesda, MD USA
In the Crosstab, view the Parameters and enter Tests.Dts in the Parameters column and choose the Data Type of the Field in the Data Type column. If that doesn't work do the same thing with [01DBase].MnDate too.

RAZMaddaz
Go to the top of the page
 
+
Ajazz
post Apr 11 2012, 09:08 AM
Post #3

UtterAccess Addict
Posts: 172
is it like this :

CODE
PARAMETERS [test.dts] DateTime, [01dbase.mndate] DateTime;
TRANSFORM First([01DBase].Enquiries) AS FOE
SELECT [01DBase].Region, [01DBase].User
FROM 01DBase
GROUP BY [01DBase].Region, [01DBase].User, [01DBase].MnDate
PIVOT [01DBase].MnDate In (SELECT Test.Dts FROM Test);
Go to the top of the page
 
+
RAZMaddaz
post Apr 11 2012, 09:18 AM
Post #4

UtterAccess VIP
Posts: 6,170
From: Bethesda, MD USA
It looks like you entered the Field in the Parameter area like I suggested. I have never tried using In with a Crosstab Query, so I don't know if this will work. However, this is something that is needed to be done when you want to use the data from a Form in a Crosstab Query.
Go to the top of the page
 
+
Ajazz
post Apr 11 2012, 09:44 AM
Post #5

UtterAccess Addict
Posts: 172
CODE
TRANSFORM First([01DBase].Enquiries) AS FOE
SELECT [01DBase].Region, [01DBase].User
FROM 01DBase
GROUP BY [01DBase].Region, [01DBase].User
PIVOT [01DBase].MnDate In ( 01/01/2012, 01/02/2012, 01/03/2012 );


This query works if MnDate is Numeric or Text, i have tried it. But it is not working with dates !

Please try something !
Go to the top of the page
 
+
arnelgp
post Apr 11 2012, 09:44 AM
Post #6

UtterAccess Ruler
Posts: 1,090
Hello Ajazz,

I think it is not possible to do it directly like that.
Doable but by VBA.

How is your work?
Go to the top of the page
 
+
Ajazz
post Apr 11 2012, 09:45 AM
Post #7

UtterAccess Addict
Posts: 172
I m gud arnel. how bout u!


I knew u wud surely suggest VB !
Go to the top of the page
 
+
RAZMaddaz
post Apr 11 2012, 09:53 AM
Post #8

UtterAccess VIP
Posts: 6,170
From: Bethesda, MD USA
FYI, I was able to just enter the In function in the Field without the Parameters suggestion I made earlier. Here is an example:

TRANSFORM Sum(Alpha.Qty) AS SumOfQty
SELECT Alpha.Equipment, Sum(Alpha.Qty) AS [Total Of Qty]
FROM Alpha
WHERE (((Alpha.Equipment) In (SELECT Alpha2.Equipment FROM Alpha2;)))
GROUP BY Alpha.Equipment
PIVOT Format([TheMonth],"yyyy-mm");
Go to the top of the page
 
+
Gustav
post Apr 11 2012, 10:00 AM
Post #9

UtterAccess VIP
Posts: 1,822
.. In (SELECT Test.Dts FROM Test)

Not possible. The values for In to look up must be fixed when parsing the SQL.

/gustav
Go to the top of the page
 
+
Ajazz
post Apr 11 2012, 10:01 AM
Post #10

UtterAccess Addict
Posts: 172
Thanks RazMaddaz!

This is not what i need. Here u r filtering the records accordingly !

But my case is different.

I am preparing a report using the results of crosstab. Hence the needs the fields or columns to be static, but here it's dynamic.

So in order to make it static, we can the following:

CODE
PIVOT <column heading> IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20);


if the column heading field is numeric or text, there is no problem. But date is not working ???
Go to the top of the page
 
+
Ajazz
post Apr 11 2012, 10:04 AM
Post #11

UtterAccess Addict
Posts: 172
thnk u gustav.

yes ! the table 'test' has fixed number of values i.e. i am mentioning 12 months.


or

CODE
TRANSFORM First([01DBase].Enquiries) AS FOE
SELECT [01DBase].Region, [01DBase].User
FROM 01DBase
GROUP BY [01DBase].Region, [01DBase].User
PIVOT [01DBase].MnDate In ( 01/01/2012, 01/02/2012, 01/03/2012 );


may u check the syntax in the PIVOT statement
Go to the top of the page
 
+
Ajazz
post Apr 12 2012, 01:26 AM
Post #12

UtterAccess Addict
Posts: 172
Does someone know why this query is creating a additional blank record, though there are no blank records in either tables.

CODE
TRANSFORM First([01DBase].Enquiries)
SELECT [01DBase].Region, [01DBase].User
FROM TblDates LEFT JOIN 01DBase ON TblDates.Dts = [01DBase].MnDate
GROUP BY [01DBase].Region, [01DBase].User
PIVOT TblDates.Dts;



THNK YOU !
Go to the top of the page
 
+
Ajazz
post Apr 12 2012, 01:49 AM
Post #13

UtterAccess Addict
Posts: 172
http://www.fmsinc.com/microsoftaccess/quer...port/index.html
Go to the top of the page
 
+
« Next Oldest · Access Queries · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 21st May 2013 - 08:23 AM