Query to Transform Data (1 Viewer)

Arvive

Registered User.
Local time
Today, 13:37
Joined
Aug 14, 2019
Messages
13
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 :banghead:

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

Like this

Thank you and Regards.
 
Last edited:

Arvive

Registered User.
Local time
Today, 13:37
Joined
Aug 14, 2019
Messages
13
Maybe not. But any advice to go around it please. Even if it involves creating additional table.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:37
Joined
Oct 29, 2018
Messages
21,357
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?
 
Last edited:

madcats

Registered User.
Local time
Today, 06:37
Joined
Jun 24, 2005
Messages
36
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:37
Joined
May 7, 2009
Messages
19,169
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.
 

Attachments

  • Design.zip
    59 KB · Views: 110

Arvive

Registered User.
Local time
Today, 13:37
Joined
Aug 14, 2019
Messages
13
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

Registered User.
Local time
Today, 13:37
Joined
Aug 14, 2019
Messages
13
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:37
Joined
Oct 29, 2018
Messages
21,357
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:37
Joined
May 7, 2009
Messages
19,169
saw this one https://www.experts-exchange.com/ar...ues-from-one-field-from-a-table-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).
 

Arvive

Registered User.
Local time
Today, 13:37
Joined
Aug 14, 2019
Messages
13
Thank all. @arnelgp @MrHans @theDBguy and all team members.
This link helped a lot. I'm able to present the data in the report. Much regards
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:37
Joined
Oct 29, 2018
Messages
21,357
Thank all. @arnelgp @MrHans @theDBguy and all team members.
This link helped a lot. I'm able to present the data in the report. Much regards

Hi. Congratulations! Glad to hear you got it sorted out. We were all happy to assist. Good luck with your project.
 

Users who are viewing this thread

Top Bottom