Results 1 to 2 of 2
  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
    a. [Line_Fill-Pack]
    , a.YourTableID
    , Nz(Count(b. [Line_Fill-Pack])) AS Number
    , IIf([Number]=1,"L","M") AS LINE

    FROM YourQuery AS a LEFT JOIN YourQuery AS b ON a. [Line_Fill-Pack]= b. [Line_Fill-Pack]

    WHERE (((a. YourTableID)>=[b].[ YourTableID]))

    GROUP BY a. [Line_Fill-Pack], a. YourTableID;





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

    Thanks in advance...

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need to clarify - are you saying you want to convert a existing MySQL query to an Access one? or is the query a passthrough query to MySQL? And what is 'yourquery'? Or is the MySQL code doing what you want and you want to know how to do it in Access? Probably better to explain in simple English what you want to achieve with some examples of the existing data and the result required.

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

Similar Threads

  1. Replies: 5
    Last Post: 08-29-2016, 04:33 AM
  2. How To Add Dsum To Existing VBA code
    By aamer in forum Access
    Replies: 4
    Last Post: 06-27-2016, 01:56 PM
  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