Search code examples
datems-access

Get months names between two dates Ms-Access

I have this query

SELECT TblSales.ProductCode, TblSales.ProductName, TblSales.QtySold, Right([Zdate],7) AS [Mn/Yr]
FROM TblSales
WHERE (((TblSales.zDate) Between [Forms]![FrmSales]![From] And [Forms]![FrmSales]![FinalTo]))
GROUP BY TblSales.ProductCode, TblSales.ProductName, TblSales.QtySold, Right([Zdate],7);

I need this result to be like these columns (ProductCode-ProductName-Sum Of QtySold in First month from the given date - Second month - Third month - and so on)

Example : If the two dates were #1-1-2018# To #31-3-2018# -These dates can be changed due to [Forms]![FrmSales]![From] And [Forms]![FrmSales]![FinalTo]-

Columns: ProductCode -ProductName -Jan-2018 -Feb-2018- March-2018 Rows: A1-Computer-2000-2500-3000

Previous Qty is the SumOfQtySold in every month between the two dates,Thanks in advance.

Edit #1 : I couldn’t make a crosstab query and this message popup Crosstab Error

Solution

  • You can use a crosstab query to transpose row data into columns. Something like this:

    PARAMETERS StartDate DateTime, EndDate DateTime;
TRANSFORM NZ(Sum(tblSales.QtySold), 0) AS SumOfQtySold
SELECT tblSales.ProductCode, tblSales.ProductName
FROM tblSales
WHERE (((tblSales.zDate) Between [StartDate] And [EndDate]))
GROUP BY tblSales.ProductCode, tblSales.ProductName
PIVOT Format([tblSales].zDate,"mmm-yyyy");