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 |
+-------------+---------+----------+--------+
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).
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?
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 |
+-------------+---------+----------+--------+
Code:
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).
Code:
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) [B]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 <> "")[/B];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?
