Ms access duplicated records

Question

I`m having an issue with my MS Access query and I hope you can help me. I want product "ABC" to have code "6029" if the parent is anything else than "GYF", "RGY" & "DRF".

The mapping looks as follows:

| Output_code | Account | Product  | Parent |
+-------------+---------+----------+--------+
|        6029 | income  | ABC      |        |
|        7029 | income  | ABC      | GYF    |
|        7029 | income  | ABC      | RGY    |
|        7029 | income  | ABC      | DRF    |
+-------------+---------+----------+--------+

End result would be:

+-------------+---------+----------+--------+
|        6029 | income  | ABC      | DTF    |
|        6029 | income  | ABC      | DHS    |
|        7029 | income  | ABC      | GYF    |
|        7029 | income  | ABC      | RGY    |
|        7029 | income  | ABC      | DRF    |
+-------------+---------+----------+-------

How it works right now:

+-------------+---------+----------+--------+
|        6029 | income  | ABC      | DTF    |
|        6029 | income  | ABC      | DHS    |
|        7029 | income  | ABC      | GYF    |
|        7029 | income  | ABC      | RGY    |
|        7029 | income  | ABC      | DRF    |
|        6029 | income  | ABC      | GYF    |
|        6029 | income  | ABC      | RGY    |
|        6029 | income  | ABC      | DRF    |
+-------------+---------+----------+--------+

  Select A.*, B.Output_Code, "Product" as Comment from Source as A
  inner join Mapping as B on (B.Account=A.Account and B.Product = A.Product) 
  where (B.Parent = "" or B.Parent <> A.Parent);  
  union all  
  Select A.*, B.Output_Code, "Product+Parent" as Comment from Source as A 
  inner join Mapping as B on (B.Account=A.Account  and B.Product = A.Product 
  and A.Parent = B.Parent) where B.Parent <> "";

First part (Product) is not working as expected and combinations like ABC+RGY appear twice with both "6029" and "7029".

I tried using select within select (for this I introduced rowid) and it worked but right now is takes 30 min for my query to process (instead of 5 min).

Select A.*, B.Output_Code, "Product" as Comment from Source as A inner join 
Mapping as B on (B.Account=A.Account and B.Product = A.Product)
where (B.Parent = "" or B.Parent <> A.Parent) and A.rowid not in (Select 
A.rowid from Source as A inner join Mapping as B on (B.Account=A.Account and 
B.Product = A.Product and A.Parent = B.Parent) where B.Parent <> "");

Could you kindly share your ideas on what is the proper way of building the first query so it doesn`t affect the performance so heavily?

I went through the list of questions but couldn`t find anything similar.

Answer 1

I'll make a couple of assumptions about your base tables.
Your Mapping table looks like this:

| Output_code | Product | Parent |
|-------------|---------|--------|
| 7029        | ABC     | DRF    |
| 7029        | ABC     | GYF    |
| 7029        | ABC     | RGY    |
| 8593        | DEF     | XYZ    |

Your Source table looks like this:

| Product | Parent |
|---------|--------|
| ABC     | DTF    |
| ABC     | DHA    |
| ABC     | GYF    |
| ABC     | RGY    |
| ABC     | DRF    |
| DEF     | XYZ    |
| DEF     | ZXL    |  

This SQL will return all your Source values with the correct Output_code attached. A Null value is returned where there is no Output_code:

    SELECT  Mapping.Output_code
            , Source.Product
            , Source.Parent
    FROM    Source LEFT JOIN Mapping ON Source.Product = Mapping.Product AND    
                                        Source.Parent = Mapping.Parent



    | Output_code | Product | Parent |
    |-------------|---------|--------|
    |             | ABC     | DTF    |
    |             | ABC     | DHA    |
    | 7029        | ABC     | GYF    |
    | 7029        | ABC     | RGY    |
    | 7029        | ABC     | DRF    |
    | 8593        | DEF     | XYZ    |
    |             | DEF     | ZXL    |  

If the only product is ABC and you only want to to replace all NULL values with 6029 you could update the Mapping.Output_code line to NZ(Mapping.Output_code, 6029) to do this.

Nz Function help

SELECT  NZ(Mapping.Output_code,6029)
        , Source.Product
        , Source.Parent
FROM    Source LEFT JOIN Mapping ON Source.Product = Mapping.Product AND    
                                    Source.Parent = Mapping.Parent  

| Expr1000 | Product | Parent |
|----------|---------|--------|
| 6029     | ABC     | DTF    |
| 6029     | ABC     | DHA    |
| 7029     | ABC     | GYF    |
| 7029     | ABC     | RGY    |
| 7029     | ABC     | DRF    |
| 8593     | DEF     | XYZ    |
| 6029     | DEF     | ZXL    |

But I expect you have more than one product and want product DEF to show a different value if there's no parent available.
To do this add a third table called NoMap that lists the code for any product that doesn't already have one:

| Product | Output_code |
|---------|-------------|
| ABC     | 6029        |
| DEF     | 7593        |  

Primary Key: Product

You can then use this new table to replace any Null values found in your table:

    SELECT  NZ(Mapping.Output_code, NoMap.Output_code)
            , Source.Product
            , Source.Parent
    FROM    (
             Source LEFT JOIN Mapping ON Source.Product = Mapping.Product AND    
                                         Source.Parent = Mapping.Parent)
                    LEFT JOIN NoMap ON Source.Product = NoMap.Product  

| Expr1000 | Product | Parent |
|----------|---------|--------|
| 6029     | ABC     | DTF    |
| 6029     | ABC     | DHA    |
| 7029     | ABC     | GYF    |
| 7029     | ABC     | RGY    |
| 7029     | ABC     | DRF    |
| 8593     | DEF     | XYZ    |
| 7593     | DEF     | ZXL    |

Answer 2

You can use this.

Select A.*, NZ(B1.Output_Code, B2.Output_Code) as "Output_Code" from 
(Source as A
left join Mapping as B1 on (B1.Account=A.Account and B1.Product = A.Product and B1.Parent = A.Parent ) )
left join Mapping as B2 on (B2.Account=A.Account and B2.Product = A.Product and (B2.Parent Is Null or B2.Parent ="") ) 

Answer 3

"Go for the simple solution, not the perfect one"

SELECT 
  IIF(Parent IN ('GYF','RGY','DRF'), 7029, 6029) as Output_code
FROM
  Product

PS; i don't really do much Access SQL- i might not have used the correct string delimiters etc - you might need to jiggle this query around a bit

Edit: If you're looking to extend the mapping, you should consider a left join instead:

Select s.*, IIF(m.Output_Code IS NULL, 6029, m.output_Code) 
FROM 
  source s
  LEFT OUTER JOIN
  mapping m
  on (m.Account=s.Account and m.Product = s.Product and m.parent = s.parent)

Remove the 6029 row from the mapping table (redundant)

LEFT JOIN connects rows that match, and keeps rows from the left side (source) that don't have a match in the right (map) but puts nulls in the values for the right side. By comparison, an inner join throws away rows that don't have a match. It's the "this row from source has no match in map" quality that we want to leverage

This means the 'GYF','RGY','DRF' rows WILL have a match in the map table, they will not be null, the IIF will be false for those rows, the code from mapping will be used

Other rows will NOT have a match, the m.Output_code for those rows will be NULL, the IIF will be TRUE, the value 6029 will be used