query to return a single entry for client name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a DB that contains client personal details, and billing details.
I have a query that produces a list of clients that have been to see me
within a given time period. My query gives me the list I want, except that
if a client has been to see me more than once in the selected time period,
they show as multiple entries (1 for each time they have been to see me) in
the query result. Is there any way to set it up so only one entry will be
produced per client?
 
Did I see somewhere:
SELECT DISTINCT[Client]
to return only one instance for each client. (A guess to play with until
someone who knows answers)
 
Try again:
SELECT DISTINCT[ClientNo] FROM[YourTable]ORDER BY[ClientNo]
So where does that go then??
 
You can do a group by query containing the duplicated data and join it to a
query returning any other fields you want to see.
 
chewy said:
Did I see somewhere:
SELECT DISTINCT[Client]
to return only one instance for each client. (A guess to play with until
someone who knows answers)

Graham w said:
Hi,
I have a DB that contains client personal details, and billing details.
I have a query that produces a list of clients that have been to see me
within a given time period. My query gives me the list I want, except that
if a client has been to see me more than once in the selected time period,
they show as multiple entries (1 for each time they have been to see me) in
the query result. Is there any way to set it up so only one entry will be
produced per client?
Click to expand...
Click to expand...

Hi Chewy,
Thanks for post. Sorry not to reply sooner. I am new to this and am not
sure what you mean. Do you mean that I add all of SELECT DISTINCT[Client] to
the Criteria row of the client column in the query?
 
David F Cox said:
You can do a group by query containing the duplicated data and join it to a
query returning any other fields you want to see.





Hi David,
Click to expand...
Thanks for your Post. Sorry to take so long to get back to you. I am not
sure what you mean. Is a group by query a different sort of Query? Is the
query that I join it to a copy of the same group query, but with the fields
that I want e.g. clients address?
 
Sorry, Graham, my second post sent immediately after first, when I went back
to MS help to see what I was talking about(!) - not in reply to yours. I
haven't a clue about this, Just came across Distinct query in me travels,
studying for Access exams and thought you could try this until someone can
tell you what to do. (Just thought you might have lots of time on your hands
for a bit of discovery learning!) Copied the following from Help. Dunno how
to use it ...

(GRAHAM CHECK THIS OUT!:)

DISTINCT Omits records that contain duplicate data in the selected fields.
To be included in the results of the query, the values for each field listed
in the SELECT statement must be unique. For example, several employees listed
in an Employees table may have the same last name. If two records contain
Smith in the LastName field, the following SQL statement returns only one
record that contains Smith:
SELECT DISTINCT
LastName
FROM Employees;

If you omit DISTINCT, this query returns both Smith records.

If the SELECT clause contains more than one field, the combination of values
from all fields must be unique for a given record to be included in the
results.

The output of a query that uses DISTINCT is not updatable and does not
reflect subsequent changes made by other users.

(THE FOLLOWING IS DETAIL ABOUT SYNTAX - EASIER TO READ IN HELP. GOOD LUCK.
HOPE SOMEONE KNOWLEDGEABLE REPLIES SOON:)

SELECT Statement
Instructs the Microsoft Jet database engine to return information from the
database as a set of records.

Syntax
SELECT [predicate] { * | table.* | [table.]field1 [AS alias1] [,
[table.]field2 [AS alias2] [, ...]]}
FROM tableexpression [, ...] [IN externaldatabase]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]

The SELECT statement has these parts:

Part Description
predicate One of the following predicates: ALL, DISTINCT, DISTINCTROW, or
TOP. You use the predicate to restrict the number of records returned. If
none is specified, the default is ALL.
* Specifies that all fields from the specified table or tables are selected.
table The name of the table containing the fields from which records are
selected.
field1, field2 The names of the fields containing the data you want to
retrieve. If you include more than one field, they are retrieved in the order
listed.
alias1, alias2 The names to use as column headers instead of the original
column names in table.
tableexpression The name of the table or tables containing the data you want
to retrieve.
externaldatabase The name of the database containing the tables in
tableexpression if they are not in the current database.
 
Back
Top