concatenate distinct data

[Guest]

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?

 

[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.

 

[Guest]

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]

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.

 

[Guest]

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.

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?

 

[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

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.

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?

 

[Guest]

Thanks so much for your help.

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

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.

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?