finding specific months in a range

miconian

Well-known Member
Joined
Aug 18, 2004
Messages
768
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!
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

Well-known Member
Joined
Aug 18, 2004
Messages
768
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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.
Code:
SELECT DISTINCT tblOrders.OrderLine, AllTime.AT_Month
FROM tblOrders, AllTime
WHERE (((AllTime.AT_Date) Between [tblorders].[startdate] And [tblorders].[enddate]));

Denis
 

miconian

Well-known Member
Joined
Aug 18, 2004
Messages
768
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:

Code:
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.
Code:
SELECT DISTINCT tblOrders.OrderLine, AllTime.AT_Month
FROM tblOrders, AllTime
WHERE (((AllTime.AT_Date) Between [tblorders].[startdate] And [tblorders].[enddate]));

Denis
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,331
Members
414,446
Latest member
CRAVIN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top