Fields from records to columns (1 Viewer)

dlugirapfr

Registered User.
Local time
Today, 05:49
Joined
Nov 22, 2012
Messages
68
Hi ,
I have table which looks like this
nr;amount;date
1;123;2020-04-01
2;321;2020-05-01
1;333;2020-04-03
3;111;2020-01-01
etc.

Which option of query I should make to receive result like this
nr;Maxamount1;date1;amount2;date2
1;333;2020-04-03;123;2020-04-01;
2;321;2020-05-01;
3;111;2020-01-01;

It should be some kind of union query? I have never use like this. Please give me some hint.

Thank you!
 

Ranman256

Well-known member
Local time
Today, 00:49
Joined
Apr 9, 2015
Messages
4,339
access doesn't store data this way. no query will do it.
it doesnt make sense.

A crosstab query will come close, it you want to use the query wizard to try.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 00:49
Joined
May 21, 2018
Messages
8,463
Can you explain this? Is it always the case that you will have 1 or 2 records for an NR?
NR 1 has two records and you pull the max record with the other record. What if there was more than 2 records? Is one the max and the other the min.
The other records only have 1 record so this is not a descriptive example.
 

dlugirapfr

Registered User.
Local time
Today, 05:49
Joined
Nov 22, 2012
Messages
68
In table every nr can has 10 or 0 records. But in query I have to show always 5 columns. From highest amount and date to lowest amount and date. (amount1,date1,amount2,date2,... amount5,date5). If there will be less than 5 records I should show null in the record.
I know it is unlogical but I have template to fill in it like this.
 

dlugirapfr

Registered User.
Local time
Today, 05:49
Joined
Nov 22, 2012
Messages
68
It will look's like this but one more field in tbl1 data. Thank you for help.

table.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 00:49
Joined
May 21, 2018
Messages
8,463
See answer.
Basically you rank each entry using a ranking query. Then you make separate queries by rank and join them together. Just copy and past the remaining 5-10 change the criteria and the labels.
 

Attachments

  • RowsToCol.zip
    44.3 KB · Views: 82

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 23:49
Joined
Feb 28, 2001
Messages
27,001
First, a clarification, please. In your post #5 you show six cases where NR = 1 but only show the top 5 in your desired result. But your words don't tell us the same thing as the picture you showed us, because the "lowest amount" for NR = 1 SHOULD be 100, not 200. What is the correct rule?
 

dlugirapfr

Registered User.
Local time
Today, 05:49
Joined
Nov 22, 2012
Messages
68
See answer.
Basically you rank each entry using a ranking query. Then you make separate queries by rank and join them together. Just copy and past the remaining 5-10 change the criteria and the labels.
Than you very much ! Have a nice day ! :)
 

dlugirapfr

Registered User.
Local time
Today, 05:49
Joined
Nov 22, 2012
Messages
68
First, a clarification, please. In your post #5 you show six cases where NR = 1 but only show the top 5 in your desired result. But your words don't tell us the same thing as the picture you showed us, because the "lowest amount" for NR = 1 SHOULD be 100, not 200. What is the correct rule?
It was my misunderstanding. Just 5 highest offer. Solution from MajP works perfect.
 

dlugirapfr

Registered User.
Local time
Today, 05:49
Joined
Nov 22, 2012
Messages
68
See answer.
Basically you rank each entry using a ranking query. Then you make separate queries by rank and join them together. Just copy and past the remaining 5-10 change the criteria and the labels.
Thank you once again but I think this field Rank in qryRank. Doesn't work properly. I understand logic of base but these field should rank field from highest to lowest amount. So I made code: Rank: (Select Count(NR) + 1 from Data as B where A.NR = B.NR and B.Amount < A.Amount) but it doesn't rank properly.
table1.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:49
Joined
May 7, 2009
Messages
19,169
see query1 and module1
 

Attachments

  • tbl.zip
    28.8 KB · Views: 81

dlugirapfr

Registered User.
Local time
Today, 05:49
Joined
Nov 22, 2012
Messages
68
Thank you. Everything works now perfectly. Topic can be closed.
Have a nice day.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 00:49
Joined
May 21, 2018
Messages
8,463
Sorry I thought you wanted this in date order not amount order. I read it wrong. You almost had it right, but needed > not <.
Code:
Rank: (Select Count(NR) + 1 from Data as B where A.NR = B.NR and B.Amount> A.Amount)
qryColumns

NRDate1Amount1Date2Amount2Date3Amount3Date4Amount4Date5Amount5
1​
7/1/2020​
798​
8/1/2020​
678​
6/1/2020​
456​
4/3/2020​
333​
4/1/2020​
123​
2​
2/1/2020​
999​
3/1/2020​
667​
5/1/2020​
321​
3​
7/1/2020​
444​
5/1/2020​
111​
 

Users who are viewing this thread

Top Bottom