Unique Value in a Query (2000)

Topic

New Reply

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

Reply | Quote

Replies

Try using the keyword DISTINCT in your query

Reply | Quote

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

Leesha

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote