query dynamic fields but not crosstab

[jtkjames]

Hi - I've posted this on another forum and after a week I haven't got any replies. Hopefully here will be able to help or at least confirm it's not possible

I have a simple query, polling on several crosstab queries with fixed column headers based on relative dates. Basically the infamous Duane Hookom advice for relative dates.

What I cannot do, however, is turn the relative headers (that I am querying) back into ones that make sense. I have attached the SQL code of the simple query as it is:

SELECT qry_sub_GetSoldLast3Months.[End Item], 
qry_sub_GetSoldLast3Months.[Mth-3], 
qry_sub_GetSoldLast3Months.[Mth-2], 
qry_sub_GetSoldLast3Months.[Mth-1], 
qry_sub_GetSoldLast3Months.[Mth0]

and how I would "like" it to be

SELECT qry_sub_GetSoldLast3Months.[End Item], 
qry_sub_GetSoldLast3Months.[Mth-3] AS [dateadd("mm-yy", -3, now())], 
qry_sub_GetSoldLast3Months.[Mth-2] AS [dateadd("mm-yy", -2, now())], 
qry_sub_GetSoldLast3Months.[Mth-1] AS [dateadd("mm-yy", -1, now())], 
qry_sub_GetSoldLast3Months.[Mth0] AS [dateadd("mm-yy", 0, now())]

except of course, this doesn't work, because the aliases are not dynamic i.e. won't return 05-10, 06-10 and 07-10 etc - they will return literally "dateadd(..."

I know there is lots of info on how to do this with a crosstab, transform statement - but my question to the experts out there is: is it possible with a select query?

Any advice appreciated - thanks!!

 

[vbaInet]

Yes it is possible but what are you using the query for? A form, a report?

 

[jtkjames]

actually it's just a query, viewed as a datasheet. however, viewed as a form datasheet is just as good.

the idea is that the select query looks at two crosstabs (qry_sub_GetSoldLast3Months and qry_sub_GetDueNext3Months), one for previous 3 months' sales, and one for the predicted sales for next 3 months based on our due inflow, with one row for each product code ("end item"). the sql for the select query is as follows

SELECT qry_sub_GetDueNext3Months.[End Item], 
qry_sub_GetSoldLast3Months.[Sold-3], 
qry_sub_GetSoldLast3Months.[Sold-2], 
qry_sub_GetSoldLast3Months.[Sold-1], qry_sub_GetSoldLast3Months.Sold0, 
qry_sub_CurrentFor3MS.Count AS [Total In Stock], qry_sub_GetDueNext3Months.Due0, 
qry_sub_GetDueNext3Months.Due1, 
qry_sub_GetDueNext3Months.Due2, 
qry_sub_GetDueNext3Months.Due3, 
qry_sub_GetSoldLast3Months.Total AS [Total Sold], 
([Count]+[Due Term Total]) AS [Total In Stock plus Defleet]
 
FROM (qry_sub_GetDueNext3Months 
LEFT JOIN qry_sub_GetSoldLast3Months 
ON qry_sub_GetDueNext3Months.[End Item]=qry_sub_GetSoldLast3Months.[End Item]) 
LEFT JOIN qry_sub_CurrentFor3MS 
ON qry_sub_GetSoldLast3Months.[End Item]=qry_sub_CurrentFor3MS.[END ITEM]
 
GROUP BY qry_sub_GetDueNext3Months.[End Item],
qry_sub_GetSoldLast3Months.[Sold-3], 
qry_sub_GetSoldLast3Months.[Sold-2], 
qry_sub_GetSoldLast3Months.[Sold-1], 
qry_sub_GetSoldLast3Months.Sold0, 
qry_sub_CurrentFor3MS.Count, 
qry_sub_GetDueNext3Months.Due0, 
qry_sub_GetDueNext3Months.Due1, 
qry_sub_GetDueNext3Months.Due2, 
qry_sub_GetDueNext3Months.Due3, 
qry_sub_GetSoldLast3Months.Total, 
([Count]+[Due Term Total]), 
qry_sub_GetDueNext3Months.[Due Term total];

 

[vbaInet]

It seems the column headers/field names are static? Is that correct?

 

[jtkjames]

presently they are static - what i want is to change them to display the current month minus three, current month minus two, etc, rather than the static "Sold-3", "Sold-2"

thanks for the help btw

 

[jtkjames]

thank you for your help vbainet, based on your suggestion to consider a form, i have managed to sort it myself. i created a form datasheet based on the query then just set up some vba code to change the field labels to the appropriate text, such as

Private Sub Form_Activate()
forms!form1!sold3label.caption = format(now()-90, "mm-yyyy")
End Sub

hope this may help someone else in the future! kudos to vbainet for his hints and tips!

 

[vbaInet]

Excellent! Glad you found a suitable solution.

There are ways to amend the query def in code but you're better off with a form as long as the number of fields from the query is constant.