Fields from records to columns (1 Viewer)

dlugirapfr

Registered User.
Local time
Today, 06:46
Joined
Nov 22, 2012
Messages
54
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
Today, 00:46
Joined
Apr 9, 2015
Messages
3,573
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:46
Joined
May 21, 2018
Messages
3,192
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, 06:46
Joined
Nov 22, 2012
Messages
54
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, 06:46
Joined
Nov 22, 2012
Messages
54
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:46
Joined
May 21, 2018
Messages
3,192
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, 23:46
Joined
Feb 28, 2001
Messages
17,044
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, 06:46
Joined
Nov 22, 2012
Messages
54
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, 06:46
Joined
Nov 22, 2012
Messages
54
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, 06:46
Joined
Nov 22, 2012
Messages
54
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
 

dlugirapfr

Registered User.
Local time
Today, 06:46
Joined
Nov 22, 2012
Messages
54
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:46
Joined
May 21, 2018
Messages
3,192
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