insert a crosstab in to temp table query

[inungh]

INSERT INTO tbltmp (Field1, Filed2, Field3) SELECT DISTINCT
[4/10/2009],[4/17/2009],[4/24/2009] FROM

[PARAMETERS [forms]![frmMain]![dtpStartDate] DateTime, [forms]!
[frmMain]![dtpEndDate] DateTime, [MyID] Text ( 255 );
TRANSFORM Sum([Mytable2].[aht] AS AHTD
SELECT MyTable1.MyID, MyTable2.Login_ID, MyTable2.Split, Sum
([MyTable2].[aht]) AS [Total of AHTD] FROM MyTable2 INNER JOIN
MyTable1 ON MyTable2.Login_ID = MyTable1.LOGIN WHERE ((([MyTable1].
[MyID])=[MyEID]) AND ((MyTable2.Activity_date) Between [MyStart] And
[MyEnd] ))
GROUP BY MyTable1.MyID, MyTable2.Login_ID, MyTable2.Split
PIVOT CDate(Int([Activity_Date])) - Weekday(CDate(Int
([Activity_Date]))) + 6]
AS MyTbale


I tried to run above query to insert from a crosstab query to my temp
table.
The crosstab and insert into both are working.

It seems that MS Access does not like PARAMETER after FROM reserve
word.
It works if I use a select query.

Basically, I tried to use Crosstab query SQL as a table.
Are there any solution for this query or I am on the wrong track?

Your information is great appreciated,

 

[John Spencer MVP]

I would try the following. If it fails, you are probably going to need a
saved query. From what you have posted, I don't see why you have the two date
time parameters in the query, since you are not using them - unless MyStart
and MyEnd are supposed to be [forms]![frmMain]![dtpStartDate] and
[forms]![frmMain]![dtpEndDate].

PARAMETERS [forms]![frmMain]![dtpStartDate] DateTime
, [forms]![frmMain]![dtpEndDate] DateTime
, [MyID] Text ( 255 );
INSERT INTO tbltmp (Field1, Filed2, Field3)

SELECT DISTINCT
[4/10/2009],[4/17/2009],[4/24/2009]

FROM
[TRANSFORM Sum([Mytable2].[aht] AS AHTD
SELECT MyTable1.MyID
, MyTable2.Login_ID
, MyTable2.Split
, Sum([MyTable2].[aht]) AS [Total of AHTD]

FROM MyTable2 INNER JOIN
MyTable1 ON MyTable2.Login_ID = MyTable1.LOGIN

WHERE [MyTable1].[MyID]=[MyEID]
AND MyTable2.Activity_date Between [MyStart] And [MyEnd]

GROUP BY MyTable1.MyID
, MyTable2.Login_ID
, MyTable2.Split
PIVOT CDate(Int([Activity_Date])) - Weekday(CDate(Int([Activity_Date]))) + 6].
as MySourceTable


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[inungh]

I would try the following.  If it fails, you are probably going to needa
saved query.  From what you have posted, I don't see why you have the two date
time parameters in the query, since you are not using them - unless MyStart
and MyEnd are supposed to be [forms]![frmMain]![dtpStartDate] and
[forms]![frmMain]![dtpEndDate].

PARAMETERS [forms]![frmMain]![dtpStartDate] DateTime
,  [forms]![frmMain]![dtpEndDate]  DateTime
, [MyID] Text ( 255 );
INSERT INTO tbltmp  (Field1, Filed2, Field3)

SELECT DISTINCT
[4/10/2009],[4/17/2009],[4/24/2009]

FROM
[TRANSFORM Sum([Mytable2].[aht] AS AHTD
SELECT MyTable1.MyID
, MyTable2.Login_ID
, MyTable2.Split
, Sum([MyTable2].[aht]) AS [Total of AHTD]

FROM MyTable2 INNER JOIN
MyTable1 ON MyTable2.Login_ID = MyTable1.LOGIN

WHERE [MyTable1].[MyID]=[MyEID]
AND MyTable2.Activity_date  Between [MyStart] And [MyEnd]

GROUP BY MyTable1.MyID
, MyTable2.Login_ID
, MyTable2.Split
PIVOT CDate(Int([Activity_Date])) - Weekday(CDate(Int([Activity_Date]))) + 6].
  as MySourceTable

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

INSERT INTO tbltmp  (Field1, Filed2, Field3) SELECT DISTINCT
[4/10/2009],[4/17/2009],[4/24/2009]  FROM

[PARAMETERS [forms]![frmMain]![dtpStartDate] DateTime,  [forms]!
[frmMain]![dtpEndDate]  DateTime, [MyID] Text ( 255 );
TRANSFORM Sum([Mytable2].[aht] AS AHTD
SELECT MyTable1.MyID, MyTable2.Login_ID, MyTable2.Split,  Sum
([MyTable2].[aht]) AS [Total of AHTD]  FROM MyTable2 INNER JOIN
MyTable1 ON MyTable2.Login_ID = MyTable1.LOGIN  WHERE ((([MyTable1]..
[MyID])=[MyEID]) AND ((MyTable2.Activity_date)  Between [MyStart] And
[MyEnd] ))
GROUP BY MyTable1.MyID, MyTable2.Login_ID, MyTable2.Split
PIVOT CDate(Int([Activity_Date])) - Weekday(CDate(Int
([Activity_Date]))) + 6]
AS MyTbale

I tried to run above query to insert from a crosstab query to my temp
table.
The crosstab and insert into both are working.

It seems that MS Access does not like PARAMETER after FROM reserve
word.
It works if I use a select query.

Basically, I tried to use Crosstab query SQL as a table.
Are there any solution for this query or I am on the wrong track?

Your information is great appreciated,- Hide quoted text -

- Show quoted text -

Thanks for helping,
Yes, they are two same parameters.
I got "Invalid Bracketing name of Transform " error message,
It fails, it seems that I must have a save corsstab query to insert
into a temp table.

Thanks millions,