distinct records query

Posted on 2003-02-28
Medium Priority
Last Modified: 2010-05-01
i have an app that filters what records a shown depending on what button is clicked.

the problem i have is that one of the filters requires me to join the customers table to another table on the ID field and select distinct records so i only end up viewing each customer once.

the problem is that the distinct query says it cannot select distinct on memo fields.  i need to show this memo field.  is there any way around it

Question by:Marcusw
LVL 32

Accepted Solution

Brendt Hess earned 200 total points
ID: 8041850
Depends.  Which table is the memo in?  If it is in the Customers table, you can use this technique:

(1)  Create a Distinct query with all of the information needed *except* the memo field.  Be sure to include the Customer ID field (the unique identifier for a customer record).  Save this as a query (for our example, call it Test1, but you would name it something sensible).

(2)  SELECT against both the Customer table and the Test1 query, JOINing on the Customer ID field, e.g.:

SELECT Test1.*, Customer.Memo
FROM Test1
INNER JOIN Customer on Test1.ID = Customer.ID

Author Comment

ID: 8041974
thanks for the suggestions since i have posted the questions i have tried to run the query from another computer and it works perfectly

do you know why this might be

i thought it was impossible but i have checked all the results it brings up and they are perfect

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month12 days, 17 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question