If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update Query for Fiscal Date range
I have one table that contains Today's Date. I have a second table that
contains fiscal month dates. For example July = 6/28/2008 - 8/01/2008. I need to populate a third table with the fiscal month beginning date and end date based on today's date. For example today's date = 7/17/2008; I need to populate the 3rd table with beginning date field = 6/28/2008 and ending date field = 8/01/2008. What logic do I use to write this? |
#2
|
|||
|
|||
Update Query for Fiscal Date range
SELECT FiscalStart, FiscalEnd FROM FiscalTable WHERE FiscalStart = Date() and FiscalEnd = Date() Expanding on that to do for multiple dates SELECT FiscalStart, FiscalEnd FROM FiscalTable, DatesTable WHERE FiscalStart = DatesTable.SomeDate AND FiscalEnd = DatesTable.SomeDate OR SELECT FiscalStart, FiscalEnd FROM FiscalTable INNER JOIN DatesTable ON FiscalTable.FiscalStart = DatesTable.SomeDate AND FiscalTable.FiscalEnd = DatesTable.SomeDate '================================================= === John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County '================================================= === workweek wrote: I have one table that contains Today's Date. I have a second table that contains fiscal month dates. For example July = 6/28/2008 - 8/01/2008. I need to populate a third table with the fiscal month beginning date and end date based on today's date. For example today's date = 7/17/2008; I need to populate the 3rd table with beginning date field = 6/28/2008 and ending date field = 8/01/2008. What logic do I use to write this? |
#3
|
|||
|
|||
Update Query for Fiscal Date range
On Thu, 17 Jul 2008 07:46:02 -0700, workweek
wrote: I have one table that contains Today's Date. I have a second table that contains fiscal month dates. For example July = 6/28/2008 - 8/01/2008. I need to populate a third table with the fiscal month beginning date and end date based on today's date. For example today's date = 7/17/2008; I need to populate the 3rd table with beginning date field = 6/28/2008 and ending date field = 8/01/2008. What logic do I use to write this? What's the actual structure of your fiscal dates table? It would be simplest if it had four fields: FYear, FMonth, StartDate and EndDate, with values 2008, 7 (or "July" if you prefer), #6/28/2008# and #8/1/2008#. Also, why do you have a *TABLE* for today's date? You can read it directly from your compter calendar using the Date() function. You can run an Append query from my suggested date range table to insert a record into the third table. Just use a criterion on StartDate of = Date(), and on EndDate of = Date(). Change the query to an Append query and append a record. -- John W. Vinson/MVP |
Thread Tools | |
Display Modes | |
|
|