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
|
|||
|
|||
start date & end date - show all date in between
I have a date table & a leave table which leave table contains startdate &
end_date. How i do show all date in between startdate & end date . For example : Leave startdate = 1/5/2010, end date =3/5/2010. i want the data show all date - 1/5/2010 NANCY AL 2/5/2010 NANCY AL 3/5/2010 NANCY AL |
#2
|
|||
|
|||
start date & end date - show all date in between
You can use a Cartesian Product query to generate a record for every date in
the period. 1. Create a table with just one field of type Number, named (say) CountID, and mark it as primary key. Save the table as (say) tblCount. 2. Enter records from zero to the highest number of days someone will have off. If you need more than a couple of dozen records, you can use the code in this link to create the data: http://allenbrowne.com/ser-39.html 3. Create a query that uses both your leave table and tblCount. In the upper pane of query design, there should be no line joining these 2 tables. This gives you every possible combination of the two. 4. In the Field row enter this expression: LeaveDate: [startdate] + tblCount.CountID In the Criteria row under this enter: = [end date] 5. Output the staff name field as well. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Nancy Tang" wrote in message ... I have a date table & a leave table which leave table contains startdate & end_date. How i do show all date in between startdate & end date . For example : Leave startdate = 1/5/2010, end date =3/5/2010. i want the data show all date - 1/5/2010 NANCY AL 2/5/2010 NANCY AL 3/5/2010 NANCY AL |
#3
|
|||
|
|||
start date & end date - show all date in between
Nancy Tang wrote:
I have a date table & a leave table which leave table contains startdate & end_date. How i do show all date in between startdate & end date . For example : Leave startdate = 1/5/2010, end date =3/5/2010. i want the data show all date - 1/5/2010 NANCY AL 2/5/2010 NANCY AL 3/5/2010 NANCY AL SELECT Leave.PersonID, Leave.LeaveStart, Leave.LeaveEnd, TableOfDates.TheDate FROM TableOfDates, Leave WHERE (((TableOfDates.TheDate) Between [Leave].[LeaveStart] And [Leave]. [LeaveEnd])); You don't really need the Leave.LeaveStart and Leave.LeaveEnd fields in there. .. I just put them in to make sure my math was right... Note that there is NO join between the two tables... that's deliberate. Basically, I'm creating a deliberate Cartesian product and then filtering it with the Where clause. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/201005/1 |
Thread Tools | |
Display Modes | |
|
|