Query Filter

[zzggreen]

I have 10 rows that list dates. I need to be able to filter the dates, so that i can have all information for one particular date. Ie i am counting people on a number of different occasions. Often people will be away, and hence wont be counted. But for any paricular date i need all information, including previous dates that they were listed.

 

[Corticus]

Hi zzggreen,

Make a query with the table of interest, include the date field. For all records on 01/01/03, set criteria to #01/01/2003#. For all records on this date and before, set criteria to <#01/01/2003#.

Please post back with more info if I'm on the wrong track...

HTH,

 

[Dugantrain]

Or are you trying to say: "If this person had activity on this date, then I want to pull that record as well as all previous dates for that person, but ignore all records for a person if there is no activity on that date"? That's a bit more complex if that's what you're looking for.

 

[zzggreen]

Yea sorta along the lines. If i perform the count on a weekly basis, it is possible that someone will have 10 counts against there name and others will have 1 or 2. I need to know everyone that has been counted on day x. if they have been counted on other days thats fine, but i need to know all those that have been counted on a particular day.

 

[zzggreen]

Yes Dugantrain, thats it exactly.

 

[Corticus]

I think you got it Dugan and I missed the point,

You could just set the criteria for the 'person' field to something like [Enter Person], or a control that might house this value. Then set the date criteria as I stated before (<#date#)

HTH,

 

[zzggreen]

Ok. The only problem is i am talking up to 500 people and need an overview of the whole lot.

 

[Corticus]

Ouch,

Okay, this sounds rough, but the best thing I can think of is
set the criteria for [name] to
Name1 or Name2 or Name3 ... or Name500.

You could also use a form to house all the names you might search for, and replace Name1 with the control names, ie Txt1 or Txt2 ... or Txt500.

Because of the date criteria, you won't pull records for an employee preceeding the date specified.

HTH,

 

[zzggreen]

Ouch. Anyone else got any ideas????

 

[Dugantrain]

OK, I think I have a simple solution:
-Build a query (probably a parameter query so that you can reuse it) which just captures the Name and the Date (Enter something like [Enter A Date] in the date criteria so that it will prompt you for a date).
-Build a second query using the 1st query and the original table. Tie only the Name fields together. Now, select Names from the Query and Date from the Table (not from the Query!) and any other fields that you want to see from the Table. In the criteria of the Date field, write:

<=Query1.date

(or whatever you called the first query). This will return all records before or equal to a certain date, but only if they have activity for that specified date. Let us know if this worked out.