Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-13-2019, 05:06 PM   #1
Arvive
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 6
Thanked 0 Times in 0 Posts
Arvive is on a distinguished road
Query to Transform Data

Dear all kindly assist me in doing this. I have data in a table which look like below
DATE DESIGNCODE TRAINEE
01/08/2019 D0D23 1001
01/08/2019 D0D24 1001
02/08/2019 D0123 1002
02/08/2019 100RT 1002

I need a query to present it like below in a report. I have tried crosstab queries. Maybe my approach was wrong

DATE TRAINEE DESIGNCODE1 DESIGNCODE2
01/08/2019 1001 D0D23 D0D24
02/08/2019 1002 D0123 100RT

Like this

Thank you and Regards.


Last edited by Arvive; 08-14-2019 at 05:26 AM. Reason: error in presentation
Arvive is offline   Reply With Quote
Old 08-13-2019, 05:14 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,938
Thanks: 13
Thanked 4,051 Times in 3,987 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Query to Transform Data

Would this work?

http://allenbrowne.com/func-concat.html
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
Arvive (08-14-2019)
Old 08-13-2019, 05:21 PM   #3
Arvive
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 6
Thanked 0 Times in 0 Posts
Arvive is on a distinguished road
Re: Query to Transform Data

Maybe not. But any advice to go around it please. Even if it involves creating additional table.

Arvive is offline   Reply With Quote
Old 08-13-2019, 05:41 PM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,549
Thanks: 38
Thanked 867 Times in 850 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Query to Transform Data

Hi. This looks like it would work with a crosstab query but you'll need to add a calculated column to designate as column headers. Also, if it's going to be a crosstab query, then each column designator must have a different name. In other words, you can't have two columns both named DESIGNCODE1. Is that okay?
__________________
Just my 2 cents...

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.

Last edited by theDBguy; 08-14-2019 at 08:37 AM.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Arvive (08-14-2019)
Old 08-13-2019, 06:13 PM   #5
madcats
Newly Registered User
 
Join Date: Jun 2005
Posts: 36
Thanks: 9
Thanked 1 Time in 1 Post
madcats is on a distinguished road
Re: Query to Transform Data

If I understand your question: I do not know how to use crosstab queries so this is probably not the most efficient way. I would build a table with Distinct Date&Trainee combined, along with the fields: Date, Trainee, DesignCode1, DesignCode2 ,DesignCode3, etc.

Then I would do an update query using if statements to test for the next blank DesignCode field.

I am reading the other response, I am not sure if you are wanting multiple columns named DesignCode1, if so this way may not help you.
madcats is offline   Reply With Quote
The Following User Says Thank You to madcats For This Useful Post:
Arvive (08-14-2019)
Old 08-13-2019, 08:36 PM   #6
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,533
Thanks: 57
Thanked 2,419 Times in 2,319 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Query to Transform Data

you can do the ConcatRelated as suggested.
on the report use Split() function to get each DesignCode.
see the Query, qryDesign.
see the DetailFormat Event of Report (report Design).
you can view the report and print in Print Preview.
it will not correctly display on Report View.
Attached Files
File Type: zip Design.zip (59.0 KB, 3 views)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-13-2019, 09:39 PM   #7
MrHans
Newly Registered User
 
Join Date: Jul 2015
Location: NL - Amsterdam
Posts: 132
Thanks: 49
Thanked 17 Times in 15 Posts
MrHans is on a distinguished road
Re: Query to Transform Data

Recently I stumbled upon an improved version of ConcatRelated written by Gustav Brock, see this link: https://www.experts-exchange.com/art...-or-query.html

Looks quite nice actually.

MrHans is offline   Reply With Quote
The Following 3 Users Say Thank You to MrHans For This Useful Post:
Gasman (08-13-2019), theDBguy (08-14-2019), Uncle Gizmo (08-13-2019)
Old 08-14-2019, 12:36 AM   #8
Arvive
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 6
Thanked 0 Times in 0 Posts
Arvive is on a distinguished road
Re: Query to Transform Data

Thanks all for the support. Sorry I made a mistake I rather want the columns like below;
DATE TRAINEE DESIGNCODE1 DESIGNCODE2
01/08/2019 1001 D0D23 D0D24
02/08/2019 1002 D0123 100RT
Arvive is offline   Reply With Quote
Old 08-14-2019, 12:45 AM   #9
Arvive
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 6
Thanked 0 Times in 0 Posts
Arvive is on a distinguished road
Re: Query to Transform Data

Quote:
Originally Posted by madcats View Post
If I understand your question: I do not know how to use crosstab queries so this is probably not the most efficient way. I would build a table with Distinct Date&Trainee combined, along with the fields: Date, Trainee, DesignCode1, DesignCode2 ,DesignCode3, etc.

Then I would do an update query using if statements to test for the next blank DesignCode field.

I am reading the other response, I am not sure if you are wanting multiple columns named DesignCode1, if so this way may not help you.
Thanks alot. I erred at that area DESIGNCODE1, DESIGNCODE2, DESIGNCODE3, etc is how I want to present the data
Arvive is offline   Reply With Quote
Old 08-14-2019, 08:41 AM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,549
Thanks: 38
Thanked 867 Times in 850 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Query to Transform Data

Quote:
Originally Posted by Arvive View Post
Thanks all for the support. Sorry I made a mistake I rather want the columns like below;
DATE TRAINEE DESIGNCODE1 DESIGNCODE2
01/08/2019 1001 D0D23 D0D24
02/08/2019 1002 D0123 100RT
Hi. I'm not sure if it solves your problem, but have you tried Arnel's demo? Otherwise, as I was saying earlier, to use a crosstab query, you'll need to add another field to your table (it could be a calculated column in a query). For example:
DATE, TRAINEE, DESIGNCODE, DESIGNCODEPOSITION
__________________
Just my 2 cents...

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.
theDBguy is offline   Reply With Quote
Old 08-15-2019, 08:40 AM   #11
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,533
Thanks: 57
Thanked 2,419 Times in 2,319 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Query to Transform Data

saw this one https://www.experts-exchange.com/art...-or-query.html.
and yet much faster, on the second run (first run, saves all results to collection object).
be warned though that you need a form to host your query.
and on the form, you need to "Reset" the DJoin when you make any changes on the field you are DJoining.
otherwise, it will return same values again and again (since it searches the collection for the value already in it).

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Reply

Tags
pivot query , query <> problem , report

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Queries to transform this data PRTP Queries 10 06-14-2019 02:20 PM
Transform my Excel data ECEK Queries 7 06-13-2019 11:06 AM
Data format Transform mr moe General 2 07-26-2018 11:19 AM
Problem with Transform Data sandy70 Modules & VBA 3 06-08-2015 06:38 AM
Question data transform sandy70 General 7 04-12-2012 10:21 AM




All times are GMT -8. The time now is 05:26 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 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World