Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-16-2018, 10:45 AM   #1
kafka89
Newly Registered User
 
Join Date: Sep 2018
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
kafka89 is on a distinguished road
Duplicated records

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 |
+-------------+---------+----------+--------+

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) 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?

kafka89 is offline   Reply With Quote
Old 09-16-2018, 10:57 PM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,159
Thanks: 0
Thanked 690 Times in 675 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Duplicated records

your 3rd table has no duplicates.
it has the correct product.
Ranman256 is offline   Reply With Quote
Old 09-16-2018, 11:48 PM   #3
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,398
Thanks: 92
Thanked 1,826 Times in 1,697 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Duplicated records

I'm unclear how 4 records in the first list become 5 in the second.

Using a UNION query will give you additional unwanted results as in the final list

If you want to change the Output code in the original table, use a UPDATE query instead.
OR if you just want these to be displayed differently without changing the table, use a query with the REPLACE function to replace 7029 with 6029 for your specific criteria

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Records in subform duplicated itself shafara7 Modules & VBA 5 06-21-2017 09:09 PM
[SOLVED] Duplicated Records Ihussein Queries 1 05-19-2016 12:14 AM
How to find duplicated records? itlearner Queries 2 10-06-2006 05:12 AM
Duplicated records in Query rynorem Queries 1 03-13-2006 04:46 PM
Duplicated records Steven811 Queries 3 05-03-2004 11:14 AM




All times are GMT -8. The time now is 05:21 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World