Mark each month between two dates

[jbuckner72]

I am trying to write a query to have a result that indicates the months a project was active. I know how to get my start month and end month as numbers. I am struggling to figure out all the months in between.



Table: Projects

Desc	Live_Date	Duration_Months
Project A	8/15/2020	4
Project B	6/12/2020	2

From the example above, here is the output of the query I want

Project

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Project A

X

X

X

X

Project B

X

X

[ranman256]

if your data table is designed correctly as:

Project, month,Marked

update table set marked = X where month between 5/1/19 and 8/1/19

then run the crosstab query to show your data above.

[June7]

If you are not able to redesign Projects table, options:

1. create a temp table and use VBA to populate with records, use that table as source for CROSSTAB

2. review https://www.accessforums.net/showthread.php?t=18459