Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2014
    Posts
    6

    Question Unanswered: changing sql qrt to access qry

    update table1
    set table1.range=case
    when table1.wt>=cast(substring(table2.range1,1,charinde x('-',table2.range1)-1) as int)
    and table1.wt<=cast(substring(table2.range1,charindex( '-',table2.range1)+1,99) as int)
    then table2.range1
    when table1.wt>=cast(substring(table2.range2,1,charinde x('-',table2.range2)-1) as int)
    and table1.wt<=cast(substring(table2.range2,charindex( '-',table2.range2)+1,99) as int)
    then table2.range2
    else table1.range
    end
    from table2
    where table2.id=table1.id

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    CASE WHEN becomes IIF(<Condition>, <Result when True>, <Result when False>) You can nest several IIf() functions if necessary:
    Code:
    IIf(<Condition1>, <Result when True>, IIf(<Condition2>, ...))
    CAST would probably not be necessary: the Jet Engine (Access SQL intepreter) is able to perform implicit conversions. Otherwise, there is a specific casting function for every type of data (CStr(), CLng(), CDate(), etc.).
    SUBSTRING becomes MID(<Text value>, <Start position>, <Length>)
    CHARINDEX becomes Instr([<Optional Start position>], <string1>, <string2>, [<Optional Compare type>])
    You can get detailed explanations on these functions in Access help.

    Post some sample data in csv format and with the table definition if you want me to actually convert the query expression.
    Have a nice day!

  3. #3
    Join Date
    Aug 2014
    Posts
    6

    Question How to change sql qry to Access qry need help

    Table 2
    "ID","range1","range2"
    "A01","10-30","30-40"
    "A02","30-40","40-55"

    Table 3
    "ID","Wt","Range"
    "A01",33,

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im guessing Sinndho wanted the data in csv format AND the table design so he could make certain his suggestion works. By failing to do that you are making it a lot harder for him, assuming he does continue to work on your problem. Of course it may akso suggest to him that you value your time so much, and his time so little that there is little reason to continue helping you.

    if a respondant asks for specific inforjation its usually for a good reason, not beacuse they want to make life tough for you
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2014
    Posts
    6

    Question how to change qry of Sql to access

    Quote Originally Posted by Sinndho View Post
    CASE WHEN becomes IIF(<Condition>, <Result when True>, <Result when False>) You can nest several IIf() functions if necessary:
    Code:
    IIf(<Condition1>, <Result when True>, IIf(<Condition2>, ...))
    CAST would probably not be necessary: the Jet Engine (Access SQL intepreter) is able to perform implicit conversions. Otherwise, there is a specific casting function for every type of data (CStr(), CLng(), CDate(), etc.).
    SUBSTRING becomes MID(<Text value>, <Start position>, <Length>)
    CHARINDEX becomes Instr([<Optional Start position>], <string1>, <string2>, [<Optional Compare type>])
    You can get detailed explanations on these functions in Access help.

    Post some sample data in csv format and with the table definition if you want me to actually convert the query expression.
    I modified the query as per as ur sugg. but still not updating


    IIf([table3].[wt]>=CStr(Mid([table2].[range1],1,InStr('-',[table2].[range1])-1)) And [table3].[wt]<=CStr(Mid([table2].[range1],InStr('-',[table2].[range1])+1,99)),[table2].[range1],IIf([table3].[wt]>=CStr(Mid([table2].[range2],1,InStr('-',[table2].[range2])-1)) And [table3].[wt]<=CStr(Mid([table2].[range2],InStr('-',[table2].[range2])+1,99)),[Table2].[range2]))


    here is the table structure

    Table 2
    "ID","range1","range2"
    "A01","10-30","30-40"
    "A02","30-40","40-55"

    Table 3
    "ID","Wt","Range"
    "A01",33,

  6. #6
    Join Date
    Aug 2014
    Posts
    6

    Question converting Sql qry to access qry

    Sorry for inconvenience

    Table 2 Design

    ID Text
    range1 Text
    range2 Text


    Table 3 Design
    ID Text
    Wt Number
    Range Text
    Attached Files Attached Files

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by pnasz View Post
    here is the table structure

    Table 2
    "ID","range1","range2"
    "A01","10-30","30-40"
    "A02","30-40","40-55"

    Table 3
    "ID","Wt","Range"
    "A01",33,
    What about Table1?

    As the Original tables are in a SQL Server database, the easiest way of retrieving their structure consists in opening SSMS (SQL Server Management Studio), select the table in the treeview on the left, right-click on that table and select Script table as --> CREATE To --> New Query Editor Window. You will end up with the SQL DDL expression required to recreate the table, something like:
    Code:
    USE [XXXX]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ARITHABORT ON
    GO
    CREATE TABLE [dbo].[Tbl_Users](
    	[SysCounter] [int] IDENTITY(0,1) NOT NULL,
    	[User_Name] [nvarchar](128) NOT NULL,
    	[User_Alias] [nvarchar](50) NULL,
    	[Department] [nvarchar](50) NULL,
    	[Comment] [nvarchar](255) NULL,
    	[Creator] [nvarchar](128) NOT NULL,
    	[Creation_Date] [smalldatetime] NOT NULL,
    	[Modificator] [nvarchar](128) NOT NULL,
    	[Modification_Date] [smalldatetime] NOT NULL,
    	[Inactive] [bit] NOT NULL,
    	[User_ShortName]  AS (substring([User_Name],charindex('\',[User_Name])+(1),(50))),
     CONSTRAINT [PK_Tbl_Users] PRIMARY KEY CLUSTERED ([SysCounter] ASC)
     WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    The part in red is the "useful" part of the table definition. From there you can select/copy/paste it in your post.

    For extraction table data, select the database in the treeview, right-click and select Tasks --> Export Data. This opens the SQL Server Iport and Export Wizard. Go through each step of it. To yield a csv file, choose Flat File Destination in the Choose Data Destination page. A bit further (2 pages next) select the table from which you want to extract the data, keep Run immediately checked on the next page and click the Finish button twice to complete the export process.
    Have a nice day!

  8. #8
    Join Date
    Aug 2014
    Posts
    6

    Question converting Sql qry to access qry- help needed

    Here i am posting the qry which is working fine in sql Server.

    I need to convert it into Access query.

    All the table structure is shown below with the query. This query is giving me the exact result as needed but i want it to run in access.

    Create table table1
    (
    id varchar(5) not null primary key,
    Wt varchar(10) null,
    Range varchar(10) null
    )
    GO

    Create table table2
    (
    id varchar(5) not null primary key,
    Range1 varchar(10) null,
    Range2 varchar(10) null
    )
    GO

    Insert into table1 (id, Wt)
    Values ('A01', 33),
    ('A02', 20),
    ('A03', 39),
    ('A04', 25)
    GO

    Insert Into table2
    Values ('A01', '10-30', '30-40')
    Values ('A02', '15-40', '50-60')

    go

    select * from table1
    select * from table2


    update table1
    set table1.range=case
    when table1.wt>=cast(substring(table2.range1,1,charinde x('-',table2.range1)-1) as int)
    and table1.wt<=cast(substring(table2.range1,charindex( '-',table2.range1)+1,99) as int)
    then table2.range1
    when table1.wt>=cast(substring(table2.range2,1,charinde x('-',table2.range2)-1) as int)
    and table1.wt<=cast(substring(table2.range2,charindex( '-',table2.range2)+1,99) as int)
    then table2.range2
    else table1.range
    end
    from table2
    where table2.id=table1.id
    and ((table1.wt>=cast(substring(table2.range1,1,charin dex('-',table2.range1)-1) as int)
    and table1.wt<=cast(substring(table2.range1,charindex( '-',table2.range1)+1,99) as int)
    )
    or (table1.wt>=cast(substring(table2.range2,1,charind ex('-',table2.range2)-1) as int)
    and table1.wt<=cast(substring(table2.range2,charindex( '-',table2.range2)+1,99) as int)
    ))

  9. #9
    Join Date
    Aug 2014
    Posts
    6

    Question Changing sql qry to access: help needed

    TAble structure with qry which is running fine in sql server,how it can be converted into access please help

    Create table table1
    (
    id varchar(5) not null primary key,
    Wt varchar(10) null,
    Range varchar(10) null
    )
    GO

    Create table table2
    (
    id varchar(5) not null primary key,
    Range1 varchar(10) null,
    Range2 varchar(10) null
    )
    GO

    Insert into table1 (id, Wt)
    Values ('A01', 33),
    ('A02', 20),
    ('A03', 39),
    ('A04', 25)
    GO

    Insert Into table2
    Values ('A01', '10-30', '30-40')
    Values ('A02', '15-40', '50-60')

    go

    select * from table1
    select * from table2


    update table1
    set table1.range=case
    when table1.wt>=cast(substring(table2.range1,1,charinde x('-',table2.range1)-1) as int)
    and table1.wt<=cast(substring(table2.range1,charindex( '-',table2.range1)+1,99) as int)
    then table2.range1
    when table1.wt>=cast(substring(table2.range2,1,charinde x('-',table2.range2)-1) as int)
    and table1.wt<=cast(substring(table2.range2,charindex( '-',table2.range2)+1,99) as int)
    then table2.range2
    else table1.range
    end
    from table2
    where table2.id=table1.id
    and ((table1.wt>=cast(substring(table2.range1,1,charin dex('-',table2.range1)-1) as int)
    and table1.wt<=cast(substring(table2.range1,charindex( '-',table2.range1)+1,99) as int)
    )
    or (table1.wt>=cast(substring(table2.range2,1,charind ex('-',table2.range2)-1) as int)
    and table1.wt<=cast(substring(table2.range2,charindex( '-',table2.range2)+1,99) as int)
    ))

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is far more complex that expected and cannot be solved by simply translating the SQL functions (CAST, CHARINDEX, etc.) into their equivalent in Access.

    Keep in mind that the Jet Engine (i.e. the database engine and SQL interpretor of Access) only accepts a subset of the whole SQL language. Expressions such as
    Code:
    UPDATE Table1 SET... FROM Table2
    cannot be processed in Access and the whole query need to be refactored (probably using subqueries).

    I'll try to find a moment to see what I can come with. Please be patient, I'm back at work after 15 days of vacation and the first days are always very busy.

    More about me as soon as possible.
    Have a nice day!

Posting Permissions

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