help designing query to get results
in [Tables]
|
From: JoAnn on 2 Jan 2010 22:24 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
From: Allen Browne on 3 Jan 2010 04:03 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" <JoAnn(a)discussions.microsoft.com> wrote in message news:870BFEBD-3365-4452-8DC7-034E41486C4B(a)microsoft.com... > 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
From: JoAnn on 3 Jan 2010 13:03 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" <JoAnn(a)discussions.microsoft.com> wrote in message > news:870BFEBD-3365-4452-8DC7-034E41486C4B(a)microsoft.com... > > 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 > > . >
From: Ken Snell on 3 Jan 2010 13:35 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" <JoAnn(a)discussions.microsoft.com> wrote in message news:DAB222A7-C850-4C44-86F7-53DF2D57FF33(a)microsoft.com... > 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" <JoAnn(a)discussions.microsoft.com> wrote in message >> news:870BFEBD-3365-4452-8DC7-034E41486C4B(a)microsoft.com... >> > 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 >> >> . >>
From: Allen Browne on 3 Jan 2010 22:39 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" <kthsneisllis9(a)ncoomcastt.renaetl> wrote in message news:OjdBcOKjKHA.4872(a)TK2MSFTNGP05.phx.gbl... > 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" <JoAnn(a)discussions.microsoft.com> wrote in message > news:DAB222A7-C850-4C44-86F7-53DF2D57FF33(a)microsoft.com... >> 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.)
|
Next
|
Last
Pages: 1 2 Prev: Pull Data From Multiple Tables ???? Next: Data Validation Rule in Table Design |