How to Convert Result of an Select SQL Query into a New Table in Ms Access

how to convert result of an select sql query into a new table in ms access

You can use sub queries

SELECT a,b,c INTO NewTable 
FROM (SELECT a,b,c
FROM TheTable
WHERE a Is Null)

Making a new table from an Access Query result

One way to solve your issue is to create a new query which says the following:

SELECT *
INTO [Insert Table Name Here]
FROM [Insert Query Name Here]

For example using one of your queries from the screenshot:

SELECT *
INTO [Query 34 & 35]
FROM [Query 34 & 35]

Hope this helps!

using MS SQL I need to select into a table while casting a whole load of strings to ints! can it be done?

I'm unsure whether you're talking about doing the data transformation in Access or SQL Server. Either way, since you're redesigning the schema, now is the time to consider whether you really want resultsItemData table to include 200+ fields, from nq1 through nq220 (or ultimately nq240). And any future question additions would require changing the table structure again.

The rule of thumb is "columns are expensive; rows are cheap". That applies whether the table is in Access or SQL Server.

Consider one row per id/question combination.

id q_number answer
 1      nq1      3
 1      nq2      1

I don't understand why your current approach crashes at 400 rows. I wouldn't even worry about that, though, until you are sure you have the optimal table design.

Edit: Since you're stuck with the approach you described, I wonder if it might work with an "append" query instead of a "make table" query. Create resultsItemData table structure and append to it with a query which transforms the qx values to numeric.

INSERT INTO resultsItemData (id, nq1, nq2, ... nq220)
SELECT id, CInt(q1), CInt(q2), ... CInt(q220) FROM results;

Use SELECT inside an UPDATE query

Well, it looks like Access can't do aggregates in UPDATE queries. But it can do aggregates in SELECT queries. So create a query with a definition like:

SELECT func_id, min(tax_code) as MinOfTax_Code
FROM Functions
INNER JOIN Tax 
ON (Functions.Func_Year = Tax.Tax_Year) 
AND (Functions.Func_Pure <= Tax.Tax_ToPrice) 
GROUP BY Func_Id

And save it as YourQuery. Now we have to work around another Access restriction. UPDATE queries can't operate on queries, but they can operate on multiple tables. So let's turn the query into a table with a Make Table query:

SELECT YourQuery.* 
INTO MinOfTax_Code
FROM YourQuery

This stores the content of the view in a table called MinOfTax_Code. Now you can do an UPDATE query:

UPDATE MinOfTax_Code 
INNER JOIN Functions ON MinOfTax_Code.func_id = Functions.Func_ID 
SET Functions.Func_TaxRef = [MinOfTax_Code].[MinOfTax_Code]

Doing SQL in Access is a bit of a stretch, I'd look into Sql Server Express Edition for your project!

Converting SQL Query to Access Query - SELECT within SELECT

Nested queries are allowed in Access as well as in SQL server, but at least in SQL server it requires that you set a dummy alias for your nested query, and all columns in your nested query need a name. This might have caused the error.

I would suggest following query:

SELECT q1.CasesAssigned, q2.CasesClosed
FROM 
(SELECT COUNT(*) AS CasesAssigned
FROM CaseDetail 
WHERE CaseAssignedDate Between '1/1/2008' AND '1/1/2009') as q1,
(SELECT COUNT(*) AS CasesClosed
FROM CaseDetail 
WHERE CaseClosedDate BETWEEN '1/1/2008' AND '1/1/2009') as q2

MS Access: Syntax error in CREATE TABLE statement

That doesn't look like valid Access syntax. If you are trying to create a new table based on the results of a SELECT, try this intead:

SELECT EXPORT_POSTING_ID, IMPORT_POSTING_ID, DIS, MILE_SAVED, IMPORT_AVAILABLE
INTO EX_P_TEMP
FROM POTENTIAL_PAIRS;

CREATE new TABLE from a query on another TABLE using ADO

To CREATE a new table from a query on existing tables, you can use SELECT INTO(this creates a new table) or INSERT INTO SELECT(this inserts into an existing table) statements.

Check this MSDN page, it has nice examples that you need.

How to move UNION query results to a new table?

In SQL Server you have to use

SELECT <COLUMNS_LIST>
  INTO <NEW_TABLE_NAME>
  FROM <TABLES, WHERE ETC>

More information @ http://msdn.microsoft.com/en-us/library/ms188029.aspx

Try this:

SELECT *
  INTO  #temp_UNION 
FROM
(
        SELECT     *
    FROM         [#temp1]
    UNION
    SELECT     *
    FROM         [#temp2]
    UNION
    SELECT     *
    FROM         [#temp3]
    UNION
    SELECT     *
    FROM         [#temp4]
    UNION
    SELECT     *
    FROM         [#temp5]
) a

SQL Server SELECT into existing table

SELECT ... INTO ... only works if the table specified in the INTO clause does not exist - otherwise, you have to use:

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

---

Related Topics