Crosstab Type Form?? (1 Viewer)

mounty76

Registered User.
Local time
Yesterday, 18:51
Joined
Sep 14, 2017
Messages
341
Hi Arnelgp,

Good idea, however the dates on the qry that calculates the monthly expenses are not a normal dates in that it only shows the month and year from a range of dates, here is my SQL that works out the monthly total expenses:

SELECT DISTINCTROW Format$([tblExpenses].[Date],'mmm yyyy') AS [Date By Month], Sum(tblExpenses.[Home Currency (USD)]) AS [Sum Of Home Currency (USD)]
FROM tblExpenses
GROUP BY Format$([tblExpenses].[Date],'mmm yyyy'), Year([tblExpenses].[Date])*12+DatePart('m',[tblExpenses].[Date])-1;

If I can get the above code to return the same results but include a 'normal date' say the first date of each of the months it returns then I can use this date for the Left join query as suggested above?
 

mounty76

Registered User.
Local time
Yesterday, 18:51
Joined
Sep 14, 2017
Messages
341
Hi Arnelgp,

Good idea but the date field in qry2 is a Group of dates from a total query that returns a grouped value of dates in a month...IE

1/1/21 45000
2/1/21 10000
2/2/21 5000
3/3/21 1000

Returns
Jan 21 55000
Feb 21 5000
Mar 21 1000

Therefore the left join query does not work as it's not a true date in qry2.....any ideas? I was thinking if I can do an expression to make a date formatted date that uses the month and year from the returned results? Not had much success yet though1
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 09:51
Joined
May 7, 2009
Messages
19,169
post sample db and i will show you.
from Qry2 create another query that you can Group by Format(dateField, "mmm yy").
join this new query to Qry1.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 21:51
Joined
Feb 19, 2002
Messages
42,981
What is wrong with using the first of every month so you can use an actual date data type? If you want to use a string then it has to be yyyy/mm or two separate fields, one for year and the other for month. Otherwise, you won't be able to sort correctly unless you want all the january's to sort together instead of all the months for a year.

I gave you a working example of how to do this. Perhaps I should have charged for it.
 

Users who are viewing this thread

Top Bottom