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 » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

help designing query to get results



 
 
Thread Tools Display Modes
  #1  
Old January 3rd, 2010, 04:24 AM posted to microsoft.public.access.tablesdbdesign
JoAnn
external usenet poster
 
Posts: 144
Default help designing query to get results

In my database of employees I have three date/time fields - Agency Start
date, Rehire date (a lot of our employees only work part of the year) and
Separation Date. If I wanted to show only the employees who have either
started, were rehired or separated in a certain time frame, how would I do
that? Example: Which employees started or were rehired or separted between
july 1 and july 30?
--
JoAnn
  #2  
Old January 3rd, 2010, 10:03 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default help designing query to get results

In query design view, enter the date range in the Criteria row under the
first date field.

Below the Criteria row, you'll see another one marked Or. Enter the same
date range under the 2nd field, in the Or row.

Under the Or row is another Or row. Enter the same range on this next Or
row, under your 3rd date field.

If you often rehire people, it might be a better design to remove these
dates from your table, and put them in a related table (so someone can be
hired multiple times.)

--
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.


"JoAnn" wrote in message
...
In my database of employees I have three date/time fields - Agency Start
date, Rehire date (a lot of our employees only work part of the year) and
Separation Date. If I wanted to show only the employees who have either
started, were rehired or separated in a certain time frame, how would I do
that? Example: Which employees started or were rehired or separted
between
july 1 and july 30?
--
JoAnn


  #3  
Old January 3rd, 2010, 07:03 PM posted to microsoft.public.access.tablesdbdesign
JoAnn
external usenet poster
 
Posts: 144
Default help designing query to get results

Thanks Allen, That worked well when I put in the actual dates into the
criteria, but when I put in a parameter -ex: between [enter start date] and
[enter end date] it did not work. Is there a way to use a parameter this
way?

(I will work on redesigning my table as you suggested as well)
--
JoAnn


"Allen Browne" wrote:

In query design view, enter the date range in the Criteria row under the
first date field.

Below the Criteria row, you'll see another one marked Or. Enter the same
date range under the 2nd field, in the Or row.

Under the Or row is another Or row. Enter the same range on this next Or
row, under your 3rd date field.

If you often rehire people, it might be a better design to remove these
dates from your table, and put them in a related table (so someone can be
hired multiple times.)

--
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.


"JoAnn" wrote in message
...
In my database of employees I have three date/time fields - Agency Start
date, Rehire date (a lot of our employees only work part of the year) and
Separation Date. If I wanted to show only the employees who have either
started, were rehired or separated in a certain time frame, how would I do
that? Example: Which employees started or were rehired or separted
between
july 1 and july 30?
--
JoAnn


.

  #4  
Old January 3rd, 2010, 07:35 PM posted to microsoft.public.access.tablesdbdesign
Ken Snell
external usenet poster
 
Posts: 177
Default help designing query to get results

You will need to explicitly declare the parameters as a date/time datatype.
While in design view, click on Query on menu bar (assuming you're using
ACCESS 2003 or earlier), then select Parameters from menu list. In the popup
window, type the parameters (one on each row) exactly as you'll have them in
the query, and select Date/Time as the datatype for each.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"JoAnn" wrote in message
news
Thanks Allen, That worked well when I put in the actual dates into the
criteria, but when I put in a parameter -ex: between [enter start date]
and
[enter end date] it did not work. Is there a way to use a parameter this
way?

(I will work on redesigning my table as you suggested as well)
--
JoAnn


"Allen Browne" wrote:

In query design view, enter the date range in the Criteria row under the
first date field.

Below the Criteria row, you'll see another one marked Or. Enter the same
date range under the 2nd field, in the Or row.

Under the Or row is another Or row. Enter the same range on this next Or
row, under your 3rd date field.

If you often rehire people, it might be a better design to remove these
dates from your table, and put them in a related table (so someone can be
hired multiple times.)

--
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.


"JoAnn" wrote in message
...
In my database of employees I have three date/time fields - Agency
Start
date, Rehire date (a lot of our employees only work part of the year)
and
Separation Date. If I wanted to show only the employees who have either
started, were rehired or separated in a certain time frame, how would I
do
that? Example: Which employees started or were rehired or separted
between
july 1 and july 30?
--
JoAnn


.



  #5  
Old January 4th, 2010, 04:39 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default help designing query to get results

Yes, Ken's given you the right approach JoAnn.

Use actually the same expression in the criteria rows under each of your 3
date fields:
Between [enter start date] And [enter end date]

Then declare the 2 parameters in the dialog:
[enter start date] Date/Time
[enter end date] Date/Time

It will only ask you once for the start date and once for the end date.

--
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.


"Ken Snell" wrote in message
...
You will need to explicitly declare the parameters as a date/time
datatype. While in design view, click on Query on menu bar (assuming
you're using ACCESS 2003 or earlier), then select Parameters from menu
list. In the popup window, type the parameters (one on each row) exactly
as you'll have them in the query, and select Date/Time as the datatype for
each.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"JoAnn" wrote in message
news
Thanks Allen, That worked well when I put in the actual dates into the
criteria, but when I put in a parameter -ex: between [enter start date]
and
[enter end date] it did not work. Is there a way to use a parameter
this
way?

(I will work on redesigning my table as you suggested as well)
--
JoAnn


"Allen Browne" wrote:

In query design view, enter the date range in the Criteria row under the
first date field.

Below the Criteria row, you'll see another one marked Or. Enter the same
date range under the 2nd field, in the Or row.

Under the Or row is another Or row. Enter the same range on this next
Or row, under your 3rd date field.

If you often rehire people, it might be a better design to remove these
dates from your table, and put them in a related table (so someone can
be hired multiple times.)


  #6  
Old January 6th, 2010, 02:53 AM posted to microsoft.public.access.tablesdbdesign
JoAnn
external usenet poster
 
Posts: 144
Default help designing query to get results

Great - thanks guys and happy new year!
--
JoAnn


"Allen Browne" wrote:

Yes, Ken's given you the right approach JoAnn.

Use actually the same expression in the criteria rows under each of your 3
date fields:
Between [enter start date] And [enter end date]

Then declare the 2 parameters in the dialog:
[enter start date] Date/Time
[enter end date] Date/Time

It will only ask you once for the start date and once for the end date.

--
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.


"Ken Snell" wrote in message
...
You will need to explicitly declare the parameters as a date/time
datatype. While in design view, click on Query on menu bar (assuming
you're using ACCESS 2003 or earlier), then select Parameters from menu
list. In the popup window, type the parameters (one on each row) exactly
as you'll have them in the query, and select Date/Time as the datatype for
each.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


"JoAnn" wrote in message
news
Thanks Allen, That worked well when I put in the actual dates into the
criteria, but when I put in a parameter -ex: between [enter start date]
and
[enter end date] it did not work. Is there a way to use a parameter
this
way?

(I will work on redesigning my table as you suggested as well)
--
JoAnn


"Allen Browne" wrote:

In query design view, enter the date range in the Criteria row under the
first date field.

Below the Criteria row, you'll see another one marked Or. Enter the same
date range under the 2nd field, in the Or row.

Under the Or row is another Or row. Enter the same range on this next
Or row, under your 3rd date field.

If you often rehire people, it might be a better design to remove these
dates from your table, and put them in a related table (so someone can
be hired multiple times.)


.

 




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 07:29 PM.


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