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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
All Courses

From novice to tech pro — start learning today.