[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


MS Access - Muti-column lookup and replace

Posted on 2017-12-07
High Priority
Last Modified: 2017-12-07
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

Question by:bmcollis
  • 5
  • 2
LVL 37

Expert Comment

by:Pawan Kumar
can you provide sample inputs from the tables and the expected output.?

Author Comment

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

LVL 37

Accepted Solution

Pawan Kumar earned 3000 total points
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

Industry Leaders: 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!


Author Comment

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


Author Comment

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.


Author Comment

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.



Author Closing Comment


Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Join & Write a Comment

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

873 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