concatenate distinct data

F

fsuds

Hello, I was wondering if there is a way to select distinct when you
concatenate. Basically I have a query that pulls EVENTID from tblEvent and
then concatenates NAME and FILENUMBER from the same table. I would like to
be able to SELECT DISTINCT FILENUMBER in the concatenating statement (if that
makes sense), without having to set up a separate query. Can this be done?
If so, how?
 
D

Duane Hookom

Are you using a concatenate function that combines values from multiple
records? If so, how about sharing your table structures and possibly your
attempted SQL.
 
F

fsuds

Sure thing. I'm trying to throw the data into a combo, so here is the SQL of
the row source:

SELECT [RENTAL].[EventID], ([EVENT].[Name]+', '+[EVENT].[FILENUMBER]) AS
[Event Name and Filenumber] FROM EVENT LEFT JOIN RENTAL ON
[EVENT].[EVENTID]=[RENTAL].[EventID];

EVENTID is in table Rental and table EVENT. I know they should be tblRental
and tblEvent but I'm working with an existing DB creating a front end.
 
D

Duane Hookom

Try:
SELECT DISTINCT [RENTAL].[EventID], [EVENT].[Name] & ', ' &
[EVENT].[FILENUMBER] AS [Event Name and Filenumber]
FROM EVENT LEFT JOIN RENTAL ON
[EVENT].[EVENTID]=[RENTAL].[EventID];

OR
SELECT [RENTAL].[EventID], [EVENT].[Name] & ', ' & [EVENT].[FILENUMBER] AS
[Event Name and Filenumber]
FROM EVENT LEFT JOIN RENTAL ON
[EVENT].[EVENTID]=[RENTAL].[EventID]
GROUP BY [RENTAL].[EventID], [EVENT].[Name] & ', ' & [EVENT].[FILENUMBER];


--
Duane Hookom
MS Access MVP


fsuds said:
Sure thing. I'm trying to throw the data into a combo, so here is the SQL
of
the row source:

SELECT [RENTAL].[EventID], ([EVENT].[Name]+', '+[EVENT].[FILENUMBER]) AS
[Event Name and Filenumber] FROM EVENT LEFT JOIN RENTAL ON
[EVENT].[EVENTID]=[RENTAL].[EventID];

EVENTID is in table Rental and table EVENT. I know they should be
tblRental
and tblEvent but I'm working with an existing DB creating a front end.

Duane Hookom said:
Are you using a concatenate function that combines values from multiple
records? If so, how about sharing your table structures and possibly your
attempted SQL.
 
F

fsuds

That seems to have done the trick. I used the select distinct statement and
I think we're working. One question for you. Does distinct apply to all of
the columns being selected or just for eventID. Sorry, I'm a bit of a newbie.

Duane Hookom said:
Try:
SELECT DISTINCT [RENTAL].[EventID], [EVENT].[Name] & ', ' &
[EVENT].[FILENUMBER] AS [Event Name and Filenumber]
FROM EVENT LEFT JOIN RENTAL ON
[EVENT].[EVENTID]=[RENTAL].[EventID];

OR
SELECT [RENTAL].[EventID], [EVENT].[Name] & ', ' & [EVENT].[FILENUMBER] AS
[Event Name and Filenumber]
FROM EVENT LEFT JOIN RENTAL ON
[EVENT].[EVENTID]=[RENTAL].[EventID]
GROUP BY [RENTAL].[EventID], [EVENT].[Name] & ', ' & [EVENT].[FILENUMBER];


--
Duane Hookom
MS Access MVP


fsuds said:
Sure thing. I'm trying to throw the data into a combo, so here is the SQL
of
the row source:

SELECT [RENTAL].[EventID], ([EVENT].[Name]+', '+[EVENT].[FILENUMBER]) AS
[Event Name and Filenumber] FROM EVENT LEFT JOIN RENTAL ON
[EVENT].[EVENTID]=[RENTAL].[EventID];

EVENTID is in table Rental and table EVENT. I know they should be
tblRental
and tblEvent but I'm working with an existing DB creating a front end.

Duane Hookom said:
Are you using a concatenate function that combines values from multiple
records? If so, how about sharing your table structures and possibly your
attempted SQL.


--
Duane Hookom
MS Access MVP

Hello, I was wondering if there is a way to select distinct when you
concatenate. Basically I have a query that pulls EVENTID from tblEvent
and
then concatenates NAME and FILENUMBER from the same table. I would
like
to
be able to SELECT DISTINCT FILENUMBER in the concatenating statement
(if
that
makes sense), without having to set up a separate query. Can this be
done?
If so, how?
 
D

Duane Hookom

The best way to find the answer is to try it. If you can't find the answer
by experimenting then read on....

The distinct applies to all columns returned in the SELECT statement.


--
Duane Hookom
MS Access MVP

fsuds said:
That seems to have done the trick. I used the select distinct statement
and
I think we're working. One question for you. Does distinct apply to all
of
the columns being selected or just for eventID. Sorry, I'm a bit of a
newbie.

Duane Hookom said:
Try:
SELECT DISTINCT [RENTAL].[EventID], [EVENT].[Name] & ', ' &
[EVENT].[FILENUMBER] AS [Event Name and Filenumber]
FROM EVENT LEFT JOIN RENTAL ON
[EVENT].[EVENTID]=[RENTAL].[EventID];

OR
SELECT [RENTAL].[EventID], [EVENT].[Name] & ', ' & [EVENT].[FILENUMBER]
AS
[Event Name and Filenumber]
FROM EVENT LEFT JOIN RENTAL ON
[EVENT].[EVENTID]=[RENTAL].[EventID]
GROUP BY [RENTAL].[EventID], [EVENT].[Name] & ', ' &
[EVENT].[FILENUMBER];


--
Duane Hookom
MS Access MVP


fsuds said:
Sure thing. I'm trying to throw the data into a combo, so here is the
SQL
of
the row source:

SELECT [RENTAL].[EventID], ([EVENT].[Name]+', '+[EVENT].[FILENUMBER])
AS
[Event Name and Filenumber] FROM EVENT LEFT JOIN RENTAL ON
[EVENT].[EVENTID]=[RENTAL].[EventID];

EVENTID is in table Rental and table EVENT. I know they should be
tblRental
and tblEvent but I'm working with an existing DB creating a front end.

:

Are you using a concatenate function that combines values from
multiple
records? If so, how about sharing your table structures and possibly
your
attempted SQL.


--
Duane Hookom
MS Access MVP

Hello, I was wondering if there is a way to select distinct when you
concatenate. Basically I have a query that pulls EVENTID from
tblEvent
and
then concatenates NAME and FILENUMBER from the same table. I would
like
to
be able to SELECT DISTINCT FILENUMBER in the concatenating statement
(if
that
makes sense), without having to set up a separate query. Can this
be
done?
If so, how?
 
F

fsuds

Thanks so much for your help.

Duane Hookom said:
The best way to find the answer is to try it. If you can't find the answer
by experimenting then read on....

The distinct applies to all columns returned in the SELECT statement.


--
Duane Hookom
MS Access MVP

fsuds said:
That seems to have done the trick. I used the select distinct statement
and
I think we're working. One question for you. Does distinct apply to all
of
the columns being selected or just for eventID. Sorry, I'm a bit of a
newbie.

Duane Hookom said:
Try:
SELECT DISTINCT [RENTAL].[EventID], [EVENT].[Name] & ', ' &
[EVENT].[FILENUMBER] AS [Event Name and Filenumber]
FROM EVENT LEFT JOIN RENTAL ON
[EVENT].[EVENTID]=[RENTAL].[EventID];

OR
SELECT [RENTAL].[EventID], [EVENT].[Name] & ', ' & [EVENT].[FILENUMBER]
AS
[Event Name and Filenumber]
FROM EVENT LEFT JOIN RENTAL ON
[EVENT].[EVENTID]=[RENTAL].[EventID]
GROUP BY [RENTAL].[EventID], [EVENT].[Name] & ', ' &
[EVENT].[FILENUMBER];


--
Duane Hookom
MS Access MVP


Sure thing. I'm trying to throw the data into a combo, so here is the
SQL
of
the row source:

SELECT [RENTAL].[EventID], ([EVENT].[Name]+', '+[EVENT].[FILENUMBER])
AS
[Event Name and Filenumber] FROM EVENT LEFT JOIN RENTAL ON
[EVENT].[EVENTID]=[RENTAL].[EventID];

EVENTID is in table Rental and table EVENT. I know they should be
tblRental
and tblEvent but I'm working with an existing DB creating a front end.

:

Are you using a concatenate function that combines values from
multiple
records? If so, how about sharing your table structures and possibly
your
attempted SQL.


--
Duane Hookom
MS Access MVP

Hello, I was wondering if there is a way to select distinct when you
concatenate. Basically I have a query that pulls EVENTID from
tblEvent
and
then concatenates NAME and FILENUMBER from the same table. I would
like
to
be able to SELECT DISTINCT FILENUMBER in the concatenating statement
(if
that
makes sense), without having to set up a separate query. Can this
be
done?
If so, how?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top