MS Access - Muti-column lookup and replace

Hello, and thank you for your help.

I am moving data from one system to another.  As part of the move I want to change some of the data in a couple of fields - "Issue" & "Sub-Issue".

My output will be a query combining data from a couple of different tables, that I will export to CSV for import into the new system.

I do not like the categories used in the old system for Issue & Sub Issue and wish to change them prior to import.

I have a table that, I am in the process of building, that will consist of - Old Issue | Old Sub-Issue | New Issue | New Sub-Issue

I do not care if the proposed solution does either;
1) Changes the values in the original table  OR
2) Does the mapping/changes when the final query is run prior to export.

So how can I have the 'system' lookup and find a match for the two 'Old' fields and them change them to the 'New' values

I hope this all makes sense.

More Info - Table that has the 'Issue' and 'Sub-Issue' fields  - 'Tickets'
                   - Table that will have the Old New Mappings - 'Issues'

Thank you

Brian
bmcollisAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Please try this-

select T.[Ticket #] , T.[Company Name] , I.[New Issue] , I.[New Sub Issue] , T.[Short description] , D.[Detailed Description] 
,T.[Date Opened]
from TicketsTable AS T 
INNER JOIN TicketDetailsTable AS D ON T.[Ticket #] = D.[Ticket #]
INNER JOIN IssuesTable AS I ON I.[Old Issue] = T.Issue AND I.[Old Sub Issue] = T.[Sub Issue]

Open in new window


OUTPUT

Ticket #    Company Name           New Issue        New Sub Issue    Short description         Detailed Description                              Date Opened
----------- ---------------------- ---------------- ------------------------------------------ ------------------------------------------------- -----------------------
1           Barber Barber          POS              No Network Icon  POS is not working        The POS terminal is not showing the network Icon  2017-11-10 00:00:00.000
2           Garage Door Experts    Workstation      Other            PC Blue Screens           The PC Blue screens after 1/2 hour                2017-10-23 00:00:00.000

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
can you provide sample inputs from the tables and the expected output.?
0
 
bmcollisAuthor Commented:
Tickets Table
Ticket # | Company Name          | Issue | Sub Issue       |   Short description       | Date Opened |
1                Barber Barber                 Pos        Down               POS is not working        10 Nov 2017
2                Garage Door Experts     PC          Blue Screen     PC Blue Screens             23 Oct 2017

Issues Table
Old Issue |  Old Sub Issue |  New Issue   | New Sub Issue
POS               Down                  POS                 No Network Icon
PC                  Blue Screen       Workstation  Other

Ticket Details Table
Ticket #  |  Detailed Description |
1                  The POS terminal is not showing the network Icon
2                  The PC Blue screens after 1/2 hour

Output Query
Ticket # | Company Name          | New Issue |   New Sub-Issue    |   Short description       | Detailed Description |                                               Date Opened |
1                Barber Barber                 POS                 No Network Icon     POS is not working        The POS terminal is not showing the network Icon
10 Nov 2017
2                Garage Door Experts     Workstation   Other                         PC Blue Screens             The PC Blue screens after 1/2 hour
23 Oct 2017


As originally indicated, don care if the solution makes the change in the Output Query or alters the original Ticket table

Brian
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
bmcollisAuthor Commented:
Thanks for your quick response - Will try and get back to you this afternoon (my time - MST)

Brian
0
 
bmcollisAuthor Commented:
Hi Pawan,
Thanks for the answer - worked perfectly, so you get the Best Answer

However it turns out there are 163 records where, "Sub Issue" field in the Ticket table is Blank/Null (it was imported form a Excel)
It appears that when I run the query and it finds this black field, it does not see a match & therefore does match up the correct "new" entries.

Any suggestions on why a black field would stop the match?

Note: its 163 records out of 1500, so I am happy to manually update the 163 records, but if you have a suggestion that would be great.


Brian
0
 
bmcollisAuthor Commented:
Hi Pawan

I assumed it was that the blank field was a null or something.
So I went through and added a space and deleted it, hoping that it would 'initialize' the filed in both the Ticket and Issue tables.
Did not work.
So I added a"." to all the blank fields and that worked so all is good.

I thank you for your input, it save me a lot of time.

Regards

Brian
0
 
bmcollisAuthor Commented:
Thanks
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.