Results 1 to 6 of 6
  1. #1
    JEEVAN34 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    10

    How to combine the new MYSQL code with the existing Access sql code.

    Hello friends, before saying anything I want you to know that my access and SQl knowledge is very poor..



    I have an access query with following sql code..

    SELECT ([PCs].[Title]) AS PC, IIf(Left([Technologies.Title],4)="comp","compounding","Fill/Pack") AS Tech, [Departments.Title] AS Department, [Lines.Title] AS [Line_Comp_Fill-Pack], Lines.Supplier, Lines.[Maximum line speed (pcs/min)], Lines.[Size (liter)], Lines.[Product containment], Lines.[Formats in place], Machines.Title AS Machines, Machines.[Speed (pcs/min)], Machines.Supplier, Machines.[Machine model], Machines.[Serial number], Machines.[Year of construction], Machines.[Power supply (V), (Hz)]
    FROM (((PCs INNER JOIN Technologies ON PCs.ID = Technologies.PC) INNER JOIN Departments ON Technologies.ID = Departments.Technology) INNER JOIN Lines ON Departments.ID = Lines.parentDepartment) INNER JOIN Machines ON Lines.ID = Machines.Line
    WHERE (((([PCs].[Title])) Not Like "zz*" And (([PCs].[Title])) Not Like "*Example*" And (([PCs].[Title])) Is Not Null) AND (([Departments.Title])<>"xxxx_Old"))
    ORDER BY ([PCs].[Title]), IIf(Left([Technologies.Title],4)="comp","compounding","Fill/Pack"), [Departments.Title], [Lines.Title];

    Now, i want to add a new column(Line) to this query with following MYSQL code...

    SELECT t1.*, case when t1. Line_Comp_Fill-Pack = t2. Line_Comp_Fill-Pack then 'M' else 'L' END as Line
    FROM (select rank = row_number() over (order by field asc), * from access-table) as t1,
    (select rank = row_number() over (order by field asc), * from access-table) as t2
    WHERE( t1.rank+1 = t2.rank )


    But i couldn't figure ou..how to bind both these codes..Please help me out!

    Thanks in advance...

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    If the query is run in Access, the CASE construct is not supported in Access SQL. Similarly, the Rank...over is not supported in Access SQL.

  3. #3
    JEEVAN34 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    10
    is there any other way to get that column in access query?

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I'm sure there is. But what are t1 and t2 relative to the tables in the first query?
    Do you have relationships set up in your database?
    Tell us WHAT your database is about and what the additional field is for.

  5. #5
    JEEVAN34 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    10


    Above is the screenshot of excel sheet which I exported from Access, created using the above sql code in query. Now, I am trying to add another column in access query, which displays “L” for each different first line name in “Line_Fill-Pack” column and remaining as “M”. See the below screenshot, to understand what I am trying to do...


    I did it in Excel using the formula “=IF(COUNTIF($E$3:E3,E3)=1,"L","M")”. But, I want to generate this column directly through access query.

  6. #6
    JEEVAN34 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    10
    anyone..any suggestions????

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

Similar Threads

  1. How To Add Dsum To Existing VBA code
    By aamer in forum Access
    Replies: 4
    Last Post: 06-27-2016, 01:56 PM
  2. Adding loop to existing code
    By smithdam in forum Modules
    Replies: 21
    Last Post: 03-30-2015, 06:54 AM
  3. Replies: 3
    Last Post: 11-02-2012, 04:19 PM
  4. Code to combine report filter and date range
    By rhubarb in forum Reports
    Replies: 17
    Last Post: 10-28-2011, 03:08 PM
  5. VBA Code To Work With MySQL
    By botts121 in forum Programming
    Replies: 0
    Last Post: 07-08-2009, 08:51 AM

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