Query to Transform Data (1 Viewer)

Arvive

Registered User
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
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
Joined
Oct 29, 2018
Messages
7,705
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
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

error reading drive A:
Joined
May 7, 2009
Messages
8,629
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

Arvive

Registered User
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
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
Joined
Oct 29, 2018
Messages
7,705
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

error reading drive A:
Joined
May 7, 2009
Messages
8,629

Arvive

Registered User
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
Joined
Oct 29, 2018
Messages
7,705
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 (Users: 0, Guests: 1)

Top Bottom