Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Oct 2002
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Make new field in query (XP-SP1)

    How to create a filed formatted as numeric double using select query.

    I can create a field that is text, but how to create numeric format in query or using SQL I guess. ALTER Table?

    TIA.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make new field in query (XP-SP1)

    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> You don't create fields in a select query. You can have calculated fields in a select query; just enter something of the form Alias:Expression in the query grid, for example:

    SaleAmount: [NumberSold]*[PricePerUnit]

    If you want to create a new field in an existing table, you can use ALTER TABLE: create a new query, but don't add any table. Switch to SQL view (View menu) and enter an SQL statement like the following:

    ALTER TABLE tblEmployees ADD COLUMN Salary DOUBLE;

    and execute it (Query/Run).

  3. #3
    Lounger
    Join Date
    Oct 2002
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make new field in query (XP-SP1)

    Thanks Hans,

    Running .......

    DoCmd.RunSQL "ALTER TABLE [tblFIType_PeerGroups_Redemptions] ADD Avg NUMERIC (20);"

    from module not quite wotking yet.

    You are correct.

    In a MAKE TABLE query I can create a text field by setting a column to FIELD:x.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make new field in query (XP-SP1)

    You can't specify a size for numeric fields, only for text and binary fields. Try

    DoCmd.RunSQL "ALTER TABLE [tblFIType_PeerGroups_Redemptions] ADD Avg DOUBLE;"

    Possible types for numeric fields are: BYTE (8-bit integer), SHORT (16-bit integer), LONG (32-bit integer), SINGLE (single precision floating point) and DOUBLE (double precision floating point)

    By the way, Avg is also a summary function (Average), so using it as a field name can cause confusion.

  5. #5
    Lounger
    Join Date
    Oct 2002
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make new field in query (XP-SP1)

    Thank you kind sir. I was close and you provided the cigar.

  6. #6
    Lounger
    Join Date
    Oct 2002
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Make new field in query (XP-SP1)

    Also, thaks for the cautionary note about the use of Avg. It was an ingherited set of DoCmd SQL I am working with.

    i.e.,

    DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [Avg]=0;"
    DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [MaxAvg]=0;"
    'DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [Avg]=([SumOfREDEMPTION_AMT]/[CountOfID]) WHERE ([FI Type] IN (15,23,32,33,34,37,61,72,91,8003844,8003845,800384 7,8003848,8003849,8003852,8003853,8003861,8003867, 8003870,8003877,312,323,368,8003850,8003851,800385 5,8003858,8003859,8003862) AND [CountOfID]>49);"
    DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [Avg]=([SumOfREDEMPTION_AMT]/[CountOfID]) WHERE [CountOfID]>49;"
    DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [MaxAvg]= [Avg]*1.2;"
    DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [MaxAvg]=125.00 WHERE [MaxAvg]=0;"
    DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [Avg]=125.00 WHERE [Avg]=0;"
    DoCmd.RunSQL "UPDATE [tblFIType_PeerGroups_Redemptions] SET [MaxAvg]=125.00 WHERE [Avg]<20.00;"

    Thanks again.

Posting Permissions

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