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,052
Thanks: 0
Thanked 668 Times in 653 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
ridders
Part time moderator
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,131
Thanks: 83
Thanked 1,508 Times in 1,408 Posts
ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice ridders is just really nice
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.

New example databases:

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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
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.

-----------------------------------------------------------------------------------
nil illegitimi carborundum est
ridders 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 07:17 AM.


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

Sponsored Links

How to advertise

Media Kit


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