Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2019
    Posts
    2

    Question Define data type when creating a table based on a query

    Hi guys!



    I have the query below getting data from a MySQL instance using ODBC connection. This query is ok and brings the correct data. Then I have another query, create table type, that refers to this query to create a new table based on the query result. The tables is been created with some fields with incorrect data type. For example, the field ID_Cliente is created with data type Text, but it should be created as Number. I tried to use cast function to convert, but without success.

    Is there a way to tell Access the destination data type of each field when the query is creating the table?

    SELECT CAST(custp.no AS SIGNED INTEGER) ID_Cliente, LEFT(custp.zip,8) CEP,
    custp.nei1 Bairro,
    custp.city1 Cidade,
    LEFT(custp.ddd,2) DDD_Telefone,
    LEFT(custp.tel,8) Telefone,
    ctcel.ddd DDD_Celular,
    MAX(ctcel.celno) Celular,
    ctmore3.auxstring6 Email

    FROM sqldados.custp

    LEFT JOIN sqldados.ctmore3 ON (ctmore3.custno = custp.no)
    LEFT JOIN sqldados.ctcel ON (ctcel.custno = custp.no)



    GROUP BY custp.no

    ORDER BY custp.no

    Thanks a lot

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,900
    I have encountered these issues, and have come across a method that works much better for me.
    Instead of using "Create Table" queries, I create the shell of my table, exactly as I want it (with all the Data Types I need), and then use an Append Query to write the information to that table shell.
    If this is something I need to do more than once, I simply run a Delete Query first, to delete all the existing data (from my last run) in the table I am trying to write to.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    Join Date
    Oct 2019
    Posts
    2
    Hey Joe, great idea!


    In some queries, the append query is the perfect solution. But for others, I can't stack the data. Gonna try the delete query, looks like it will solve that problem. IŽll let you know.


    Thank you very much.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,900
    You are welcome.

    Yep, if you cannot stack them, just do the Delete Query before the Append Query.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-27-2014, 09:48 AM
  2. Pre-Define query based on input data
    By twildt in forum Access
    Replies: 1
    Last Post: 04-21-2014, 08:36 AM
  3. Replies: 1
    Last Post: 03-07-2013, 03:21 PM
  4. How to Define a Hyperlink Data Type in SQL Server
    By EddieN1 in forum SQL Server
    Replies: 3
    Last Post: 02-07-2013, 11:26 AM
  5. Error:User-Define type not defined
    By A S MANN in forum Programming
    Replies: 8
    Last Post: 11-05-2011, 04:31 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums