Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Unique Value in a Query (2000)

    Hi All,

    I'm wondering if there is a way to limit a field in a query to only show a value once. I've found how to do it in the parameters by setting the unique value or record but don't see how to specify which field. For example, in this situation I only want an invoice number to appear once. Is this even possible?

    Thanks,

    Leesha

  2. #2
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unique Value in a Query (2000)

    Try using the keyword DISTINCT in your query

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

    Re: Unique Value in a Query (2000)

    If you mean that you want to suppress repeating values in a single field, it would be very complicated to do that in a query. It is very easy in a report: simply set the Hide Duplicates property of the text box bound to the field to Yes.

  4. #4
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unique Value in a Query (2000)

    I tried using this but get an error message that states "data type mismatch in critereia expression".

    Leesha

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unique Value in a Query (2000)

    This worked in hiding the duplicate but doesn't quite solve what I need it to in that it left a space in the report where the duplicate record would be in not invisible and the totals from the hidden invoice are still icluded in the formula at the end of the report. BTW, the forumula isn't calculated in the query but on the report itself in a footer.

    Leesha

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unique Value in a Query (2000)

    Perhaps if you explain in detail what you need to accomplish instead of asking how to do something specific, you might get better answers. Your question asked about unique values in a query, but it sounds like you're talking about a report. Explain please.
    Charlotte

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unique Value in a Query (2000)

    Charlotte,

    The query is what populates the report which is why I asked the question regarding whether it was possible to limit the query to only show unique values. I'm just starting to build the report and didn't want to bother the members of the lounge with that process. But, before I spend hours trying to do something that may not even be possible I figured I'd check in with the lounge to see if I'm wasting my time or not or if there was a better approach.

    Thank you,
    Leesha

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unique Value in a Query (2000)

    Sorry, Leesha, but with only bits and pieces about a query and report you're just starting to build, there isn't any way to give you a very helpful answer. Tell us what you actually want to see on the report, not what you're trying to hide. Tell us what fields are used in the query and what tables. Don't rely on people having read other threads about other queries or other reports. Each question needs to be largely stand-alone, although it is acceptable to post a link to another thread to avoid repeating a lot of background information.
    Charlotte

  9. #9
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Unique Value in a Query (2000)

    I am writng are report that tells the user the amount of sales tax owed per quarter. The report needs to be broken down to show the total amount paid during the quarter. It also needs to separate the non-taxable amount paid and the taxable and give a grand total for the two combined. (not all accounts are charged tax based on their tax status). The report works fine with the exception of invoices that have partial allocations applied. In those instances the invoice number comes up on the report twice and thus get counted X the number of times they show up on the report. As I said, I've only just started working with this and am sure I can find a way to get the numbers I need. However it was in attempting to limit the invoice number to only being counted once that I got stuck and thus asked the question.

    Leesha

Posting Permissions

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