Looking for some guidance on simple SQL Query

[kwhite100]

Hello all and thank you for viewing my post.

I currently have two tables. One is a Projects table, and one is a Discipline table. I have created a many-to-many relationship and am currently using a query to insert values in my junction table. I am trying to move forward with my design. Essentially I want to add columns to my junction table to show CurrentReportMonth and ForecastMonth. I want to add 12 months to my junction table that starts at the CurrentReportMonth.

This code works and inserts all of my needed records:

INSERT INTO ProjStruct ( DisciplineName, ProjectNum )
SELECT Disciplines.DisciplineName, Projects.ProjectNumber
FROM Disciplines, Projects;

I can view the many to many relationships between the tables used. Now, I want to add more records. Essentially each project will have Number of DisciplineNames*12 months of records. Essentially it will look like this:

TX001 Project Manager Dec-15 Dec-15
TX001 Project Manager Dec-15 Jan-16

I wanted to add code to the query to use the DateAdd function and just repeat the code to add 12 months.

Could someone please point me in the right direction that I need to resolve this? How can I add to the code above to insert the previous fields plus the additional 12 months that I am trying to add? My thought is that I can create another Month table and just add that to the select query, but maybe I can bypass adding another table and just add the months in the code.

Thank you in advance!

Kind Regards,

K

 

[kwhite100]

I decided to create a CurrForecastMonth table that will have 12 records that is always the range of the current forecast window. I have a query that updates the records in this table based on the current report date. I will append records from this table with my previous above. If
someone else has any input that help, I would love to hear. Just trying to improve my application.