Prev: Cumulative Percent
Next: Combine field entries in new field
From: Nancy Tang on 10 May 2010 21:51
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
From: Allen Browne on 10 May 2010 22:47
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" <NancyTang(a)discussions.microsoft.com> wrote in message
news:8E13E392-ACFE-4A83-8F1B-BC5C0D583C68(a)microsoft.com...
> 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

From: PieterLinden via AccessMonster.com on 10 May 2010 23:03
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/Forums.aspx/access-queries/201005/1

 | 
Pages: 1
Prev: Cumulative Percent
Next: Combine field entries in new field