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...