Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need to eliminate duplicates (Access2000)

    I am sure someone has asked this before; but I can not find a way to search before bothering everyone with a repeat question. I have a report based on a query-- the query combines tblFamily; tblPerson; tblAid---- I am able to gather the information for how many PERSONS received food during a given month ( using like "food" in the query) but I need to find out how many FAMILIES received food (actually will use this for several different inquiries) The information is entered into the database by person rather than family since we often need to know PERSONS who receive services. Try as I may, I can not get the query/report to count each family only once. Any help you can offer will be appreciated

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need to eliminate duplicates (Access2000)

    If you haven't done so already, group your report by family, i.e. enter the FamilyID or whatever you use to identify families in the Grouping and Sorting window, and set Group Header or Group Footer to Yes.

    Put a text box in the group header or footer, set its Visible property to No, its Control Source to =1, its Running Sum property to Over All, and its name to txtRunningFamilyCount. Since the text box is invisible, you can make its height (and that of the section it is in) very small, if you like.

    Finally, put another text box in the report footer, and set its Control Source to =[txtRunningFamilyCount]. This text box will display the number of families.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Need to eliminate duplicates (Access2000)

    Another option is to restructure you query such that the only information that is displayed from the query is info from tblFamily. You still include tblPerson in the join, and can use any fields in it for selection criteria, just don't check the SHOW box. Then in the query properties, set the "Distinct Values" property to Yes. This will return 1 record per family. You report can then do a simple Count(*) to find the # of families.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •