A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

start date & end date - show all date in between



 
 
Thread Tools Display Modes
  #1  
Old May 11th, 2010, 02:51 AM posted to microsoft.public.access.queries
Nancy Tang
external usenet poster
 
Posts: 2
Default 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  
Old May 11th, 2010, 03:47 AM posted to microsoft.public.access.queries
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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  
Old May 11th, 2010, 04:03 AM posted to microsoft.public.access.queries
PieterLinden via AccessMonster.com
external usenet poster
 
Posts: 307
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:13 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.