TRANSFORM First(Price)
SELECT DateOfPrice
FROM (SELECT P.Title,
R.Price,
R.DateOfPrice
FROM tbl_Product AS P
INNER JOIN tbl_Prices AS R ON P.ID = R.ProductID
WHERE R.DateOfPrice >= Date()-4)
GROUP BY DateOfPrice
PIVOT Title
But if the amount of Products is increasing, the amount of columns will increase also. In Access, the amount of columns is limited to 255. So instead, you might consider this option?
TRANSFORM First(Price)
SELECT Title
FROM (SELECT P.Title,
R.Price,
R.DateOfPrice
FROM tbl_Product AS P
INNER JOIN tbl_Prices AS R ON P.ID = R.ProductID
WHERE R.DateOfPrice >= Date()-4)
GROUP BY Title
PIVOT DateOfPrice
Member
102 Points
247 Posts
select top 5 groups of row (group by based on date)
Jun 07, 2015 08:18 AM|uid571490|LINK
Hi,
Consider we have a table for our Products like below:
admin can add another product to this table dynamically.
also we have another table like below that we save daily products prices in it: (Prices may change every day)
the problem:
I have to show last 5 days Prices like below:
I could group by rows by date using this query:
but I dont know how can I fetch and show related fileds of each date, row by row, in a gridview.
any Idea?
Thanks
All-Star
25756 Points
7014 Posts
Re: select top 5 groups of row (group by based on date)
Jun 07, 2015 12:08 PM|hans_v|LINK
But if the amount of Products is increasing, the amount of columns will increase also. In Access, the amount of columns is limited to 255. So instead, you might consider this option?