Results 1 to 7 of 7

Thread: make table

  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am making a new table by copying fields from the old table as follows
    DoCmd.RunSQL " SELECT ProductsPanolin.CodePanolin,ProductsPanolin.GradeP anolin,ProductsPanolin.ltr INTO TblPricesPanolin FROM ProductsPanolin"

    But how could i use this sql making this new table, to add three new empty fields with currency format called rig ,rig 2 and grossprice?

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='freelance' post='799206' date='21-Oct-2009 17:53']I am making a new table by copying fields from the old table as follows
    DoCmd.RunSQL " SELECT ProductsPanolin.CodePanolin,ProductsPanolin.GradeP anolin,ProductsPanolin.ltr INTO TblPricesPanolin FROM ProductsPanolin"

    But how could i use this sql making this new table, to add three new empty fields with currency format called rig ,rig 2 and grossprice?[/quote]

    Not sure you could do it in 1 query

    You could add Number fields just by modifying query

    DoCmd.RunSQL " SELECT ProductsPanolin.CodePanolin,ProductsPanolin.GradeP anolin,ProductsPanolin.ltr,0 AS rig ,0 AS [rig 2], 0 AS grossprice INTO TblPricesPanolin FROM ProductsPanolin"

    But to make them into currency fields you would need to run more queries

    DoCmd.RunSQL "ALTER TABLE TblPricesPanolin ALTER COLUMN rig Currency "
    DoCmd.RunSQL "ALTER TABLE TblPricesPanolin ALTER COLUMN [rig 2] Currency "
    DoCmd.RunSQL "ALTER TABLE TblPricesPanolin ALTER COLUMN grossprice Currency "

    I don't think it is possible to alter multiple columns but I could be wrong.
    Andrew

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Andrew is correct, you can't do it all in one SQL statement.
    Alternatively, you could add the fields after creating the table:

    DoCmd.RunSQL "SELECT ProductsPanolin.CodePanolin, ProductsPanolin.GradePanolin, ProductsPanolin.ltr INTO TblPricesPanolin FROM ProductsPanolin"
    DoCmd.RunSQL "ALTER TABLE TblPricesPanolin ADD COLUMN rig MONEY"
    DoCmd.RunSQL "ALTER TABLE TblPricesPanolin ADD COLUMN [rig 2] MONEY"
    DoCmd.RunSQL "ALTER TABLE TblPricesPanolin ADD COLUMN grossprice MONEY"

  4. #4
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='799218' date='21-Oct-2009 18:42']Andrew is correct, you can't do it all in one SQL statement.
    Alternatively, you could add the fields after creating the table:

    DoCmd.RunSQL "SELECT ProductsPanolin.CodePanolin, ProductsPanolin.GradePanolin, ProductsPanolin.ltr INTO TblPricesPanolin FROM ProductsPanolin"
    DoCmd.RunSQL "ALTER TABLE TblPricesPanolin ADD COLUMN rig MONEY"
    DoCmd.RunSQL "ALTER TABLE TblPricesPanolin ADD COLUMN [rig 2] MONEY"
    DoCmd.RunSQL "ALTER TABLE TblPricesPanolin ADD COLUMN grossprice MONEY"[/quote]



    Excellent ! creates everything i need! Thank you !!!

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='freelance' post='799228' date='21-Oct-2009 20:20']Excellent ! creates everything i need! Thank you !!![/quote]

    The Main difference between My Solution and Hans Solution is that
    Mine will set 0 to be the default value in existing data in the table.
    Hans Version will have values in New Fields as NULL unless you add default value to Column Definition.

    Also you can do Hans version in 2 queries

    DoCmd.RunSQL "SELECT ProductsPanolin.CodePanolin, ProductsPanolin.GradePanolin, ProductsPanolin.ltr INTO TblPricesPanolin FROM ProductsPanolin"
    DoCmd.RunSQL "ALTER TABLE TblPricesPanolin ADD COLUMN rig MONEY, [rig 2] MONEY, grossprice MONEY "
    Andrew

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Andrew, you could leave the new fields blank in your version as follows:

    DoCmd.RunSQL "SELECT ProductsPanolin.CodePanolin, ProductsPanolin.GradePanolin, ProductsPanolin.ltr, Null AS rig, Null AS [rig 2], Null AS grossprice INTO TblPricesPanolin FROM ProductsPanolin"

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='HansV' post='799250' date='21-Oct-2009 21:24']Andrew, you could leave the new fields blank in your version as follows:

    DoCmd.RunSQL "SELECT ProductsPanolin.CodePanolin, ProductsPanolin.GradePanolin, ProductsPanolin.ltr, Null AS rig, Null AS [rig 2], Null AS grossprice INTO TblPricesPanolin FROM ProductsPanolin"[/quote]

    Yes I know Hans, I wanted to set the initial values to the data transferred to the new table as Zero.
    That is really the only difference.
    Andrew

Posting Permissions

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