Query that only returns "average" value?

[patchesohouli]

Hi – is there a way I can have a query only give me the average number from that field? In other words, I have a date field and I want the query to only return records with the average value in that field. Let’s say “2/4//2016” is the average or most common date, can a query only return records with that date value?
The date values will change weekly.
Thanks for your help!

 

[plog]

Let’s say “2/4//2016” is the average or most common date

Not to get too mathematical on you, but those are different concepts (mean and mode). However, whichever you want, the answer is yes it is possible.

You would either build a query or function to determine the mean/mode, then you could use that in another query as criteria.

If you could post sample data, including table and field names, I could help more specifically.

 

[patchesohouli]

Thanks for your help - in this case it must be mode I am looking to use.

I've attached a sample table. In this scenario I would only want records with a "1/4/2016" value in the date field.

Thanks!

 

[plog]

1. Create a query (sub1) to get the record counts of every date in your table.
2. Create a query (sub2) to get the date of the record(s) with the highest count in sub1. Hint: you will use the DMax function.
3. Create a query based on your table and INNER JOIN it to sub2 on the date field. This will limit your results to just those you want.

One note, if you have 2 dates (or more) with the same amount of records, they will both have records appear in your results.