Create a query where the fields are REVERSED based on field criteria

access 2010
I need a query(S) to do 2 things:

01..REVERSE(FLIP) 2 FIELDS
02..DELETE DUPLICATES



3 fields
MATERIAL_NO
INDEX_LEVEL1
INDEX_LEVEL2


MATERIAL_NO      INDEX_LEVEL1                  INDEX_LEVEL2
3EB46            Hand                  Towels
3EB46            Paper                  Towels
3EB46            Towels,                  Hand
3EB46            Towels,                  Paper
3EB46            Towels,                  Roll



IN THE EXAMPLE ABOVE:
ANY COMMA IN INDEX_LEVEL1 FIELD I NEED THE FIELDS TO REVERSE(FLIP)..REMOVE THE COMMA
1st Result
MATERIAL_NO      INDEX_LEVEL1                 INDEX_LEVEL2
3EB46            Hand                                  Towels
3EB46            Paper                                  Towels
3EB46            Hand                                        Towels
3EB46            Paper                                        Towels
3EB46            Roll                                            Towels


2nd Result:  NOW REMOVE DUPLICATES
MATERIAL_NO      INDEX_LEVEL1                     INDEX_LEVEL2
3EB46            Hand                                   Towels
3EB46            Paper                                       Towels
3EB46            Roll                                          Towels
LVL 3
FordraidersAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
First create this query, qdyMaterials, to clean up:

SELECT
    tblMaterials.Material_No,
    IIf(Right([Index_Level1],1)=",",[Index_Level2],[Index_Level1]) AS IndexLevel1,
    IIf(Right([Index_Level1],1)=",",Replace([Index_Level1],",",""),[Index_Level2]) AS IndexLevel2
FROM
    tblMaterials;

Then this query to remove duplicates and obtain original field names:

SELECT DISTINCT
    qdyMaterials.Material_No,
    qdyMaterials.IndexLevel1 AS Index_Level1,
    qdyMaterials.IndexLevel2 AS Index_Level2
FROM
    qdyMaterials;

/gustav
0
 
Dale FyeConnect With a Mentor Commented:
You don't indicate whether you actually want to store the results somewhere.  This should work to give you a recordset that meets your requirements as stated above

SELECT DISTINCT
Material_NO
, IIF(Right(yourTable.[Index_Level1], 1) <> ",",
        yourTable.[Index_Level1],
        yourTable.[Index_Level2]) as [Index_Level1]
, IIF(Right(yourTable.[Index_Level1], 1) <> ",",
        yourTable.[Index_Level2],
        LEFT(yourTable.[Index_Level1], LEN(yourTable.[Index_Level1]) -1) as [Index_Level2]
FROM yourTable
0
 
Dale FyeCommented:
gustav,

like your use of 'Replace' instead of my use of 'Left'
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
FordraidersAuthor Commented:
fyed:  getting circuler reference:
 AS IndexLevel1 alias field in   query definition SELECT list
0
 
FordraidersAuthor Commented:
gustav,
getting an operator error?

error in missing operator:
I changed the tablename.
operator error
0
 
DultonCommented:
circular reference comes from naming the outgoing (result) field the same thing as the original field name. many RDMS support this, but access won't let it fly.

The below will use a nested query in order to keep your result set field names the same as your originals.

SELECT DISTINCT t1.Material_NO
, t1.[IndexLevel1] AS [Index_Level1]
, t1.[IndexLevel2] AS [Index_Level2]
FROM
(SELECT 
Material_NO
, IIF(Right(mt.[Index_Level1], 1) <> ",", 
        mt.[Index_Level1], 
        mt.[Index_Level2]) as [IndexLevel1]
, IIF(Right(mt.[Index_Level1], 1) <> ",", 
        mt.[IndexLevel2], 
        LEFT(mt.[Index_Level1], LEN(mt.[Index_Level1]) -1) as [Index_Level2]
FROM yourTable AS mt) AS t1

Open in new window

0
 
Dale FyeCommented:
You should not get the circular reference error if you preface each of the field names in the IIF( ) clauses with the tablename.  If you try:

iif(Right([Index_Level1], 1) = ",", [Index_Level2], [Index_Level1]) as [Index_Level1]

Then you should get that error, but if you use:

iif(Right(YourTable.[Index_Level1], 1) = ",",
     yourTable.[Index_Level2],
     yourTable.[Index_Level1]) as [Index_Level1]

it should work.
0
 
Gustav BrockCIOCommented:
> error in missing operator:
> I changed the tablename.

You did more than that.
Try again, this time only changing the table name.

/gustav
0
 
FordraidersAuthor Commented:
Yes, Thanks  syntax error.
0
 
FordraidersAuthor Commented:
dulton, Sorry...I graded before viewing again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.