Results 1 to 3 of 3
  1. #1
    DKruse1969 is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    2

    Question Create table out of union query

    I would like to say first off that I have found many solutions to this, but they do not produce the outcome I need, so any help would be greatly appreciated.



    Here is the scenario.

    I get lead lists with phone numbers on them in field7. When I format the leads to address labels for the call center, I need to place those numbers into another "wash" table so that when the next set of leads come in, I wash the old numbers out to prevent recalling. So what I have been doing is creating a union query with each table (field7) and washing the new import to that query.

    When I try to create a table from that query, it deletes all the SQL code and leaves me with "SELECT INTO wash1;"

    then when I put my code into the SQL design:

    "SELECT [field7]

    FROM GR07019

    UNION SELECT [field7]

    FROM GR080109

    UNION SELECT [field7]

    FROM GR090109

    UNION SELECT [Field7]

    INTO Wash;


    (The "GR070109" is the table I created from the lead import)

    and run it, I get an error "Syntax error in FROM clause."


    What I am really trying to accomplish here is to get a table with all the phone numbers in Field1 so that I can use it as the wash with out having to keep all the tables (old lead files) as a reference.

    I am using Access 2007 and have a Vista machine.

    Any help would be very appreciated!

    Regards,

    DK
    Last edited by DKruse1969; 08-27-2009 at 01:42 PM. Reason: fix code

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Why are you creating so many tables? cant you simply add them to one table and use the import date or batch number as a filter. By that I mean when you import your data you add the date the import was performed by creating a new field and setting the default value to Date(). At the same time you also give the import a batch number, so if you ran two imports on the same day you could diferentiate between the two imports.

    David

  3. #3
    DKruse1969 is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2009
    Posts
    2
    Thanks for the reply David, but one thing I forgot to mention is that I am very new to Access. I have taken over a job from a guy who up and left with no training. I am looking at this and might as well be reading the Chinese Dictionary.

    However, my initial thought was to import the numbers into the original wash table, but do not know how to do that. When I try to create a new table with existing tables, the result is a new table with many different columns and the data is all screwed up. (meaning that with a table that should have 1 column with a few thousand records, I get a table with multiple columns of phone numbers and a few hundred thousand records.)

    What I am ultimatly looking for is a table with all the phone numbers from each lead file (field7) in one column (field1) as my wash table.

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

Similar Threads

  1. Replies: 1
    Last Post: 05-13-2010, 10:50 AM
  2. Create Table If Not Exists
    By jp2access in forum Queries
    Replies: 7
    Last Post: 07-14-2009, 12:49 PM
  3. Using VBA create a new table from an existing table
    By KramerJ in forum Programming
    Replies: 0
    Last Post: 03-25-2009, 04:07 PM
  4. Replies: 0
    Last Post: 12-28-2008, 01:56 PM
  5. Auto-Create a Table
    By Mxcsquared in forum Forms
    Replies: 3
    Last Post: 01-28-2006, 11:36 PM

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