insert a crosstab in to temp table query

  • Thread starter Thread starter inungh
  • Start date Start date
I

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,
 
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
 
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
Click to expand...
[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
Click to expand...
I tried to run above query to insert from a crosstab query to my temp
table.
The crosstab and insert into both are working.
Click to expand...
It seems that MS Access does not like PARAMETER after FROM reserve
word.
It works if I use a select query.
Click to expand...
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?
Click to expand...
Your information is great appreciated,- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

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,
 
Back
Top