Access-SQL-Break out a date Range?

[mintz87]

i have a table that has a beg_date & end_date (3/1/1985 & 3/1/1990)
how do i, in access, break out this date range into another table by month. since this date range is a 60 month range it would insert 60 lines of data into the new table. (3/1/1985, 4/1/1985, 5/1/1985, etc). there will be mulitple dates to break out with various ranges.

thanks
mintz

[PianoMan64]

i have a table that has a beg_date & end_date (3/1/1985 & 3/1/1990)
how do i, in access, break out this date range into another table by month. since this date range is a 60 month range it would insert 60 lines of data into the new table. (3/1/1985, 4/1/1985, 5/1/1985, etc). there will be mulitple dates to break out with various ranges.

thanks
mintz

Are you trying to back the information up into another table that is going to be sum of each month's data in each record?

If that is the case, the easiest way to do this would simply be is to create a select query that selects all the data in the date range that you seek ex. Between #3/1/1985# and #3/1/1990#

After you have that, change the Date field so that it only displays month and year only. by creating a Date Expression TheMonth:=Format({TheNameOfTheDateField},"MM/YYYY")

So it will display the month and year of each item in the select query.

Then you simply use the group by option which is the ε (Sum) symbal that is in the query designer.

Then you simply select which items you want to sum, and which ones you want to count, or what ever you want there.

Once you have all that design and displaying the way that you want it to work, you simply change the query from a select query to a Make Table query and you're all done.

Hope that helps,

Joe P.

[mintz87]

Are you trying to back the information up into another table that is going to be sum of each month's data in each record?

If that is the case, the easiest way to do this would simply be is to create a select query that selects all the data in the date range that you seek ex. Between #3/1/1985# and #3/1/1990#

After you have that, change the Date field so that it only displays month and year only. by creating a Date Expression TheMonth:=Format({TheNameOfTheDateField},"MM/YYYY")

So it will display the month and year of each item in the select query.

Then you simply use the group by option which is the ε (Sum) symbal that is in the query designer.

Then you simply select which items you want to sum, and which ones you want to count, or what ever you want there.

Once you have all that design and displaying the way that you want it to work, you simply change the query from a select query to a Make Table query and you're all done.

Hope that helps,

Joe P.

All i am trying to do is to break out that date range per line and input that into another table. I just need the code to do that in Access.
thanks
m3ntZ

[PianoMan64]

All i am trying to do is to break out that date range per line and input that into another table. I just need the code to do that in Access.
thanks
m3ntZ

But as I explained in my answer before, all you need to do is create a Append Query with Group BY option to do what it is that you want to do. If you follow my instructions that I laid out for you, that is the simplest way to accomplish what it is that you need to do.

Since I don't have data examples, and table structure, it's kind of hard to give you the exact code that you seek.

Hope that helps,

Joe P.

[mintz87]

But as I explained in my answer before, all you need to do is create a Append Query with Group BY option to do what it is that you want to do. If you follow my instructions that I laid out for you, that is the simplest way to accomplish what it is that you need to do.

Since I don't have data examples, and table structure, it's kind of hard to give you the exact code that you seek.

Hope that helps,

Joe P.

There is nothing to select as far as dates. my table is the following: Lease_Tb - ID (Primary_key), Beg_date, End_date, Store_Num, Rent
there will be several 100's of lines in this table haveing different date ranges (the date range is from Beg_date thru End_Date. i have to have code generic enough to break this Date Range out on a per month to show the $$$ (Rent) that we are paying on a monthly basis. The final view will look like:
Date - Rent - Store #
2/1985 - $3500.00 - 1234
2/1985 - $3700.00 - 4567
3/1985 - $3500.00 - 1234
3/1985 - $3700.00 - 4567
Etc.

Thanks
m3ntZ

[PianoMan64]

There is nothing to select as far as dates. my table is the following: Lease_Tb - ID (Primary_key), Beg_date, End_date, Store_Num, Rent
there will be several 100's of lines in this table haveing different date ranges (the date range is from Beg_date thru End_Date. i have to have code generic enough to break this Date Range out on a per month to show the $$$ (Rent) that we are paying on a monthly basis. The final view will look like:
Date - Rent - Store #
2/1985 - $3500.00 - 1234
2/1985 - $3700.00 - 4567
3/1985 - $3500.00 - 1234
3/1985 - $3700.00 - 4567
Etc.

Thanks
m3ntZ

Ok, now that I know what it is that you want, can I have an example of what the data look like that you're wanting to sum together by month?

Thanks,

Joe P.

[mintz87]

Ok, now that I know what it is that you want, can I have an example of what the data look like that you're wanting to sum together by month?

Thanks,

Joe P.

I'm not sure where "sum" is coming from, but i'm not trying to sum anything.
i am taking the beg_date column, the end_date column and breaking the range out per month into a new table.

i know that i am going to use the DateDiff to calculate the number of months and put that into a loop to add the months to the beg_date to get the date range broke out. i'm just not sure how to put that all together and get the syntax correct for access.

thanks
m3ntZ

[PianoMan64]

I'm not sure where "sum" is coming from, but i'm not trying to sum anything.
i am taking the beg_date column, the end_date column and breaking the range out per month into a new table.

i know that i am going to use the DateDiff to calculate the number of months and put that into a loop to add the months to the beg_date to get the date range broke out. i'm just not sure how to put that all together and get the syntax correct for access.

thanks
m3ntZ

So if I understand your request, you're wanting a table created that will create a table for each month since the month and year of the Beg_date to current to figure out how much rent has been collected and/or Bill for that location?

If so let me know,

Joe P.

[mintz87]

So if I understand your request, you're wanting a table created that will create a table for each month since the month and year of the Beg_date to current to figure out how much rent has been collected and/or Bill for that location?

If so let me know,

Joe P.

No collections, no rent, just take the rent piece out of the equation. I just need code that will create a table that will break out the months by using the beg_date through end_date. That’s all I need.

m3ntZ