query help

  • Thread starter Thread starter newbie
  • Start date Start date
N

newbie

I have a table that I created in Access and I also have created a report to
display the information I want from the query, but the report is showing me
more than what I want. I have grouping specified on my report to groups
records based on the same date and city. However, I only want to report on
those records where the date and city match but only on more than 3
occurances and no greater than 12 occurances. Do you recommend I change my
query or change something in my report to accomplish this? Any help would be
appreciated.
 
create a new query

Put the date field, city field, and record ID field (in this order)

Select the Aggregate Icon (is the big greek E)...all three fields will
default to 'GroupBy'....change the RecordID field to 'Count'

then in the criteria of the RecordID field put: >3 AND <13

this result then should be those date/city combos that meet this criteria...
save the query with a name.

Now open the existing query for your report in design view. Add the
NewQuery just like you would add another table.

Create a relationship from NewQuery's Date field to the Tables Date field
and define the relationship to be All records of NewQuery and only those of
Tables that match. The relationship line should be an arrow pointing from
NewQuery to Table.

Repeat for City field.

So now you have 2 relationship lines between the two.

try it and see if it is what you want....
 
Back
Top