Solved

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

Posted on 2014-04-16
10
249 Views
Last Modified: 2014-04-16
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
Comment
Question by:fordraiders
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 50

Accepted Solution

by:
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

by:Dale Fye (Access MVP)
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

by:Dale Fye (Access MVP)
ID: 40003400
gustav,

like your use of 'Replace' instead of my use of 'Left'
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

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

Author Comment

by:fordraiders
ID: 40003629
gustav,
getting an operator error?

error in missing operator:
I changed the tablename.
operator error
0
 
LVL 6

Expert Comment

by:Dulton
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

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
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 50

Expert Comment

by:Gustav Brock
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

by:fordraiders
ID: 40003772
Yes, Thanks  syntax error.
0
 
LVL 3

Author Comment

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question