finding specific months in a range

[miconian]

So I have a table with these fields:

[order line], [start date], [end date]

And I want to create a table that looks like this:

[order line] [first month]
[order line] [second month]
[order line] [third month]

So that, for example, if we start out with this:

orderline start end
1232432 1/23 3/15

We would end up with

orderline month
1232432 1
1232432 2
1232432 3


Thanks very much for looking!

 

[SydneyGeek]

Does the month in the desired layout represent the actual month (Jan, Feb, Mar) or just the row number (ie the thrid month in the series)?

Denis

 

[SydneyGeek]

Does the month in the desired layout represent the actual month (Jan, Feb, Mar) or just the row number (ie the thrid month in the series)?

Denis

 

[miconian]

The actual month. So, for a longer example:

orderline start end
1232432 1/23 3/15
2452321 3/5 6/21

becomes

orderline month
1232432 1
1232432 2
1232432 3
2452321 3
2452321 4
2452321 5
2452321 6

 

[SydneyGeek]

I tend to do this with an accessory table called AllTime.
AT_Date is a date series covering whatever range you need; say, years 2001 to 2025 inclusive
AT_Month is the month number for each date
AT_Year is the year number for each date
I usually include fields for quarter, FinYear, and others. Build in Excel, import into Access.

OK, fields and field names.
tblOrders has
OrderLine
StartDate
EndDate
... corresponding to the fields you mentioned in your first post.

A simple query now gives you what you want.

SELECT DISTINCT tblOrders.OrderLine, AllTime.AT_Month
FROM tblOrders, AllTime
WHERE (((AllTime.AT_Date) Between [tblorders].[startdate] And [tblorders].[enddate]));

Denis

 

[miconian]

I'm pretty sure that I'm only going to need to worry about the months for this project. So here's how I tried to implement your solution:

SELECT DISTINCT [Order Lines].[Order Line], AllTime.AT_Month
FROM [Order Lines], AllTime
WHERE (((AllTime.AT_Month) Between Month([Order Lines].[Start Date]) And Month([Order Lines].[End Date])));

Seems to work. Thanks!

I tend to do this with an accessory table called AllTime.
AT_Date is a date series covering whatever range you need; say, years 2001 to 2025 inclusive
AT_Month is the month number for each date
AT_Year is the year number for each date
I usually include fields for quarter, FinYear, and others. Build in Excel, import into Access.

OK, fields and field names.
tblOrders has
OrderLine
StartDate
EndDate
... corresponding to the fields you mentioned in your first post.

A simple query now gives you what you want.

SELECT DISTINCT tblOrders.OrderLine, AllTime.AT_Month
FROM tblOrders, AllTime
WHERE (((AllTime.AT_Date) Between [tblorders].[startdate] And [tblorders].[enddate]));

Denis