Crosstab query fields results into a make table?

G

Gwen

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
 
K

KARL DEWEY

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];
 
G

Gwen

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
 
K

KARL DEWEY

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
 
G

Gwen

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
 
K

KARL DEWEY

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
 
G

Gwen

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
 
K

KARL DEWEY

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
 
G

Gwen

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
 
D

David Cox

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top