Fields from records to columns (1 Viewer)

dlugirapfr

Registered User.
Local time
Today, 01:16
Joined
Nov 22, 2012
Messages
51
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

Registered User.
Local time
Yesterday, 19:16
Joined
Apr 9, 2015
Messages
3,547
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
Yesterday, 19:16
Joined
May 21, 2018
Messages
3,103
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, 01:16
Joined
Nov 22, 2012
Messages
51
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.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 19:16
Joined
May 21, 2018
Messages
3,103
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

The_Doc_Man

Happy Retired Curmudgeon
Local time
Yesterday, 18:16
Joined
Feb 28, 2001
Messages
16,760
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, 01:16
Joined
Nov 22, 2012
Messages
51
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, 01:16
Joined
Nov 22, 2012
Messages
51
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, 01:16
Joined
Nov 22, 2012
Messages
51
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
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 19:16
Joined
May 21, 2018
Messages
3,103
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 (Users: 0, Guests: 1)

Top Bottom