Solved

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

Posted on 2014-04-16
245 Views
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
0
Question by:fordraiders
• 4
• 3
• 2
• +1

LVL 49

Accepted Solution

Gustav Brock earned 400 total points
ID: 40003397
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

LVL 47

Assisted Solution

Dale Fye (Access MVP) earned 100 total points
ID: 40003399
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

LVL 47

Expert Comment

ID: 40003400
gustav,

0

LVL 3

Author Comment

ID: 40003613
fyed:  getting circuler reference:
AS IndexLevel1 alias field in   query definition SELECT list
0

LVL 3

Author Comment

ID: 40003629
gustav,
getting an operator error?

error in missing operator:
I changed the tablename.
0

LVL 6

Expert Comment

ID: 40003686
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
``````
0

LVL 47

Expert Comment

ID: 40003720
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

LVL 49

Expert Comment

ID: 40003730
> error in missing operator:
> I changed the tablename.

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

/gustav
0

LVL 3

Author Closing Comment

ID: 40003772
Yes, Thanks  syntax error.
0

LVL 3

Author Comment

ID: 40003776
dulton, Sorry...I graded before viewing again.
0

## Featured Post

### Suggested Solutions

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…