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

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
can you provide sample inputs from the tables and the expected output.?
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

Pawan KumarDatabase 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


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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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

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.

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.


bmcollisAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.