Concatenating Fields Based On Repetition, Access 2010

[Baalu]

Hi Friends,

I have a difficulty in writing a query, need your help on this.
I don’t have any other base tables for this, I actually get this data from other departments in excel format. I am trying to automate my regular tasks using access. I import the data from excel to access as is and then writing queries to get the reports what I require.



My requirement is - if I have same “ItemID” for same “OrderID” I should show the “ItemID” as “Department\ItemID” else only “ItemID” should be shown with all other information same. How can I achieve this? I actually provided the sample data here, actual data differs but the process is same.

Result should be like





Please help me in getting the solution.

Thanks in Advance,
Baalu

[Baalu]

Hi All,

Finally I found the solution for this.

Here is the Query:

SELECT MyTable.[Sl.No], MyTable.Department, IIf(DCount("*","[MyTable]","[ItemID]='" & [ItemID] & "' And [OrderID] = '" & [OrderID] & "'")>1,[Department] & "\" & [ItemID],[ItemID]) AS ItemID_Rev, MyTable.OrderID
FROM MyTable
ORDER BY MyTable.[Sl.No];

Thank You All.

Have a Nice Day..