Results 1 to 7 of 7

Thread: SQL in Access

  1. #1
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53

    Question Unanswered: SQL in Access

    Using the code below to select records can I then add something in SQL that will allow me to copy the items from [Total Fax Long Distance] to the [Fax 800, 518, 888, 877, 866] field????

    SELECT CurrentMonth.[Account Number], [Total Fax Long Distance Local].[Fax Long Distance Billed Local], CurrentMonth.[Fax 800, 518, 888, 877, 866]
    FROM [Total Fax Long Distance Local] INNER JOIN CurrentMonth ON [Total Fax Long Distance Local].account = CurrentMonth.[Account Number];


    Not sure if I am thinking right or not and if it's possible or not or how to do it.


    Thanks,

    CAT

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: SQL in Access

    Originally posted by carley465
    Using the code below to select records can I then add something in SQL that will allow me to copy the items from [Total Fax Long Distance] to the [Fax 800, 518, 888, 877, 866] field????

    SELECT CurrentMonth.[Account Number], [Total Fax Long Distance Local].[Fax Long Distance Billed Local], CurrentMonth.[Fax 800, 518, 888, 877, 866]
    FROM [Total Fax Long Distance Local] INNER JOIN CurrentMonth ON [Total Fax Long Distance Local].account = CurrentMonth.[Account Number];


    Not sure if I am thinking right or not and if it's possible or not or how to do it.


    Thanks,

    CAT
    Yes ... It an Append/Insert query. Like: INSERT INTO TableNameHere (Field1, Field2, Field3, ...) SELECT ...

  3. #3
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53

    Re: SQL in Access

    The append does not work as All it does when I run an append query is put it in the table at the bottom of the table.

    Account numbers are what join my two tables and I need to copy the column from one table to the other table, however it is copying from a one to many situation.

    I can't seem to figure out how to do it in Access so I was thinking may I can do it in SQL by selecting the two tables and using copy. But I do not believe SQL supports copy. Am I on the right path???

    C


    Originally posted by M Owen
    Yes ... It an Append/Insert query. Like: INSERT INTO TableNameHere (Field1, Field2, Field3, ...) SELECT ...

  4. #4
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53

    Question

    Ok I tried this and got an error message. What am I missing?

    INSERT INTO CurrentMonth (Total Fax Long Distance Local);
    SELECT CurrentMonth.[Account Number], [Total Fax Long Distance Local].[Fax Long Distance Billed Local], CurrentMonth.[Fax 800, 518, 888, 877, 866]
    FROM [Total Fax Long Distance Local] INNER JOIN CurrentMonth ON [Total Fax Long Distance Local].account = CurrentMonth.[Account Number]

    C

  5. #5
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by carley465
    Ok I tried this and got an error message. What am I missing?

    INSERT INTO CurrentMonth (Total Fax Long Distance Local);
    SELECT CurrentMonth.[Account Number], [Total Fax Long Distance Local].[Fax Long Distance Billed Local], CurrentMonth.[Fax 800, 518, 888, 877, 866]
    FROM [Total Fax Long Distance Local] INNER JOIN CurrentMonth ON [Total Fax Long Distance Local].account = CurrentMonth.[Account Number]

    C

    I notice this is almost the exact question you asked in the regular SQL forum

    You must insert all the columns in your select statement into the table. You have listed Total Fax Long Distance Local as the only column in the current_month table. You are then trying to insert 4 columns into 1 column. Remember you need to explicitly name all of the columns you will be inserting into, and the select list must contain the same number of columns, with matching datatypes.


    You also need to take out the semicolon in the insert line - this whole statement is the query - dont seperate it.

    Check out the other forum where I answered this and gave you code.
    Last edited by ss659; 02-11-04 at 15:59.

  6. #6
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53
    So then this is what the code should look like??

    INSERT INTO CurrentMonth (Fax 800, 518, 888, 877, 866)
    SELECT (Fax 800, 518, 888, 877, 866)
    FROM [Total Fax Long Distance Local] where [Total Fax Long Distance Local].account = CurrentMonth.[Account Number];

    but I still get an error message. It tells me I have a syntax error in the Insert line.

  7. #7
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by carley465
    So then this is what the code should look like??

    INSERT INTO CurrentMonth (Fax 800, 518, 888, 877, 866)
    SELECT (Fax 800, 518, 888, 877, 866)
    FROM [Total Fax Long Distance Local] where [Total Fax Long Distance Local].account = CurrentMonth.[Account Number];

    but I still get an error message. It tells me I have a syntax error in the Insert line.
    Ok I will try to both clear this up and give you some invaluable advice all in the same post

    You should look into using underscores when you have multi-word tables/columns. In Access you can use the [table name] syntax, but that makes it difficult to read, and its difficult for someone else to understand off the bat.

    For instance, you have table Fax 800, 518, 888, 877, 866 . Its a very bad idea to use commas in a table/column name, as this is the problem you are running into with this query. You should also shorten up the table/column names to make them more manageable.

    When you give the INSERT INTO ( ) syntax, Access sees each comma as a separate field. You did not include the brackets [ ] around the column name, so Access thinks you are trying to insert data into the CurrentMonth table, but columns Fax 800, 518, 888, 877 and 866. You see that it thinks you have many columns when in fact you have only one. If you had underscores, you would avoid this pitfall.

    I am giving this advice from a database perspective and not necessarily from an Access point of view. I realize that Access writes the SQL for you, but the SQL it writes is very cluttered and overdone. I am from the Oracle world, but my advice holds true in your case. I like to use aliases to select columns, so you should look into learning this b/c it will save you time in the end!


    So personally I would recommend shortening your column/table names, and using underscores to make them more readable. So the table could become Fax_800_etc or something similar.

    To answer your original question, the reason your statement is errorring out is because of the commas in the insert statement. The best way to visualize copying columns is to formulate a select statement pulling back exactly the data you want. You then insert this data into another table.

    Also, if Current_month is a table, and Total_Fax_long_distance_total is also a table, you need a select statement more like this. This is assuming you have changed columns or tables to underscores.

    Code:
    insert into (Fax_800_518_888_877_866)
    SELECT Fax.Fax_800_518_888_877_866
    FROM Total_Fax_Long_Distance_Local fax, currentMonth cur 
    where fax.account = Curr.Account_Number;
    If this answer does not suffice, or you are still confused, maybe you will have to email me your database so I can actually see the data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •