Crosstab query fields results into a make table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to get the results of my crosstable into a make table?


TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
FROM qTop3_Dates
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];


I use the crosstab query and another query to create a make table.

The problem is that the crosstab date fields changes.

Please assist.

Thanks
 
Try this by adding -- INTO Your_New_Table_Name ---
TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
FROM qTop3_Dates INTO Your_New_Table_Name
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];
 
I tried it.
However, I received Syntax Error in Transform Statement.

KARL DEWEY said:
Try this by adding -- INTO Your_New_Table_Name ---
TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
FROM qTop3_Dates INTO Your_New_Table_Name
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];

--
KARL DEWEY
Build a little - Test a little


Gwen said:
Is it possible to get the results of my crosstable into a make table?


TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
FROM qTop3_Dates
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];


I use the crosstab query and another query to create a make table.

The problem is that the crosstab date fields changes.

Please assist.

Thanks
Click to expand...
Click to expand...
 
My error ---
TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
INTO Your_New_Table_Name
FROM qTop3_Dates INTO Your_New_Table_Name
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];

The INTO statement follows the last SELECT item.
--
KARL DEWEY
Build a little - Test a little


Gwen said:
I tried it.
However, I received Syntax Error in Transform Statement.

KARL DEWEY said:
Try this by adding -- INTO Your_New_Table_Name ---
TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
FROM qTop3_Dates INTO Your_New_Table_Name
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];

--
KARL DEWEY
Build a little - Test a little


Gwen said:
Is it possible to get the results of my crosstable into a make table?


TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
FROM qTop3_Dates
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];


I use the crosstab query and another query to create a make table.

The problem is that the crosstab date fields changes.

Please assist.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Yet another error message.

KARL DEWEY said:
My error ---
TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
INTO Your_New_Table_Name
FROM qTop3_Dates INTO Your_New_Table_Name
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];

The INTO statement follows the last SELECT item.
--
KARL DEWEY
Build a little - Test a little


Gwen said:
I tried it.
However, I received Syntax Error in Transform Statement.

KARL DEWEY said:
Try this by adding -- INTO Your_New_Table_Name ---
TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
FROM qTop3_Dates INTO Your_New_Table_Name
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];

--
KARL DEWEY
Build a little - Test a little


:

Is it possible to get the results of my crosstable into a make table?


TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
FROM qTop3_Dates
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];


I use the crosstab query and another query to create a make table.

The problem is that the crosstab date fields changes.

Please assist.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
What was the error? It works for me.

If you are pasting my post make sure to remove any added returns the posting
may have inserted.
--
KARL DEWEY
Build a little - Test a little


Gwen said:
Yet another error message.

KARL DEWEY said:
My error ---
TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
INTO Your_New_Table_Name
FROM qTop3_Dates INTO Your_New_Table_Name
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];

The INTO statement follows the last SELECT item.
--
KARL DEWEY
Build a little - Test a little


Gwen said:
I tried it.
However, I received Syntax Error in Transform Statement.

:

Try this by adding -- INTO Your_New_Table_Name ---
TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
FROM qTop3_Dates INTO Your_New_Table_Name
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];

--
KARL DEWEY
Build a little - Test a little


:

Is it possible to get the results of my crosstable into a make table?


TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
FROM qTop3_Dates
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];


I use the crosstab query and another query to create a make table.

The problem is that the crosstab date fields changes.

Please assist.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Didn't copy and paste.
I entered "into mytablename" into my sql code and received:
Reserved error (-3002); there is no message for this error.

Also, you still have "into" after the from clause


KARL DEWEY said:
What was the error? It works for me.

If you are pasting my post make sure to remove any added returns the posting
may have inserted.
--
KARL DEWEY
Build a little - Test a little


Gwen said:
Yet another error message.

KARL DEWEY said:
My error ---
TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
INTO Your_New_Table_Name
FROM qTop3_Dates INTO Your_New_Table_Name
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];

The INTO statement follows the last SELECT item.
--
KARL DEWEY
Build a little - Test a little


:

I tried it.
However, I received Syntax Error in Transform Statement.

:

Try this by adding -- INTO Your_New_Table_Name ---
TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
FROM qTop3_Dates INTO Your_New_Table_Name
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];

--
KARL DEWEY
Build a little - Test a little


:

Is it possible to get the results of my crosstable into a make table?


TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
FROM qTop3_Dates
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];


I use the crosstab query and another query to create a make table.

The problem is that the crosstab date fields changes.

Please assist.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Ok, backup to where the crosstab is working.
Open the query in design view. Click on the icon that looks like a table
with a down arrowhead on the right - when the cursor passes over it a popup
displays the mesage "Query Type". Select 'Make-table Query' and enter the
table name at the prompt.
--
KARL DEWEY
Build a little - Test a little


Gwen said:
Didn't copy and paste.
I entered "into mytablename" into my sql code and received:
Reserved error (-3002); there is no message for this error.

Also, you still have "into" after the from clause


KARL DEWEY said:
What was the error? It works for me.

If you are pasting my post make sure to remove any added returns the posting
may have inserted.
--
KARL DEWEY
Build a little - Test a little


Gwen said:
Yet another error message.

:

My error ---
TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
INTO Your_New_Table_Name
FROM qTop3_Dates INTO Your_New_Table_Name
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];

The INTO statement follows the last SELECT item.
--
KARL DEWEY
Build a little - Test a little


:

I tried it.
However, I received Syntax Error in Transform Statement.

:

Try this by adding -- INTO Your_New_Table_Name ---
TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
FROM qTop3_Dates INTO Your_New_Table_Name
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];

--
KARL DEWEY
Build a little - Test a little


:

Is it possible to get the results of my crosstable into a make table?


TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
FROM qTop3_Dates
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];


I use the crosstab query and another query to create a make table.

The problem is that the crosstab date fields changes.

Please assist.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Thank you
I got it.
I think the below is what you were trying to get me to do.

SELECT * INTO newTableName FROM crosstabQueryName;

KARL DEWEY said:
Ok, backup to where the crosstab is working.
Open the query in design view. Click on the icon that looks like a table
with a down arrowhead on the right - when the cursor passes over it a popup
displays the mesage "Query Type". Select 'Make-table Query' and enter the
table name at the prompt.
--
KARL DEWEY
Build a little - Test a little


Gwen said:
Didn't copy and paste.
I entered "into mytablename" into my sql code and received:
Reserved error (-3002); there is no message for this error.

Also, you still have "into" after the from clause


KARL DEWEY said:
What was the error? It works for me.

If you are pasting my post make sure to remove any added returns the posting
may have inserted.
--
KARL DEWEY
Build a little - Test a little


:

Yet another error message.

:

My error ---
TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
INTO Your_New_Table_Name
FROM qTop3_Dates INTO Your_New_Table_Name
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];

The INTO statement follows the last SELECT item.
--
KARL DEWEY
Build a little - Test a little


:

I tried it.
However, I received Syntax Error in Transform Statement.

:

Try this by adding -- INTO Your_New_Table_Name ---
TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
FROM qTop3_Dates INTO Your_New_Table_Name
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];

--
KARL DEWEY
Build a little - Test a little


:

Is it possible to get the results of my crosstable into a make table?


TRANSFORM Count(qTop3_Dates.[SERIAL#]) AS [CountOfSERIAL#]
SELECT qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
FROM qTop3_Dates
GROUP BY qTop3_Dates.Hospital, qTop3_Dates.[Service Type], qTop3_Dates.CLINIC
ORDER BY qTop3_Dates.[RUN DATE] DESC
PIVOT qTop3_Dates.[RUN DATE];


I use the crosstab query and another query to create a make table.

The problem is that the crosstab date fields changes.

Please assist.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
I should ask "Why?". A crosstab has column headings, and column headings do
not fit well with relational table design.

When I last did this task I exported to Excel, and then imported the Excel
sheet. Excel not required.
 
Back
Top