Concatenate function

[Guest]

Sorry about the last post being empty, i experienced a slight problem.
Anyways, i have used Duane hookom's concatenate function before, but this
time i am having some problems. I copied the code for the function from his
website and have used one of his queries as the basis for mine. The problem
is that the results are not concatenated together, that is i get one record
per line.

Engine_ID Model
1 1
1 2
1 4
2 3
2 2

But when i want is this:
Engine_ID Model
1 1,2,4
2 2,3

This is the query that i used:
SELECT ModelsXREFEngines.Engine_ID, Concatenate("SELECT Model FROM Models
WHERE Model_ID =" & [Model_ID]) AS [Model Names]
FROM ModelsXREFEngines
ORDER BY ModelsXREFEngines.Engine_ID;

Like i said, i have used the concatenation function before, but now for some
reason it is not working with this query. Any help anyone can provide would
be most appreciated. Thank you

 

[Duane Hookom]

It would help to know your table/fields with data types of all significant
fields.
I really doubt you want to select from Models in the Concatenate function.
The table/query inside the concatenate function should have Engine_ID and
Model fields.

 

[John Spencer]

Well, it is doing what you tell it to do.

You need to change the query string to something like the following. SInce
I can't decide what your table stucture is, I'm limited to giving you
explicit advice.

Concatenate("SELECT Model FROM Models
WHERE [SomeFieldInModels] =" & [Engine_ID]) AS [Model Names]

It may be that you need.
SELECT DISTINCT Engine_ID
, Concatenate("SELECT Models
FROM ModelsXRefEngines as M
WHERE M.Engine_Id =" & ModelsXrefEngines.Engine_ID) as [Model Names]
FROM ModelsXRefEngines

 

[Marshall Barton]

Sorry about the last post being empty, i experienced a slight problem.
Anyways, i have used Duane hookom's concatenate function before, but this
time i am having some problems. I copied the code for the function from his
website and have used one of his queries as the basis for mine. The problem
is that the results are not concatenated together, that is i get one record
per line.

Engine_ID Model
1 1
1 2
1 4
2 3
2 2

But when i want is this:
Engine_ID Model
1 1,2,4
2 2,3

This is the query that i used:
SELECT ModelsXREFEngines.Engine_ID, Concatenate("SELECT Model FROM Models
WHERE Model_ID =" & [Model_ID]) AS [Model Names]
FROM ModelsXREFEngines
ORDER BY ModelsXREFEngines.Engine_ID;

Like i said, i have used the concatenation function before, but now for some
reason it is not working with this query. Any help anyone can provide would
be most appreciated.

I have no idea where the table Models came from, but based
on your example it's irreleveant. Try this:

SELECT Engine_ID,
Concatenate("SELECT Model
FROM ModelsXREFEngines
WHERE Engine_ID =" & Engine_ID
) AS [Model Names]
FROM ModelsXREFEngines
GROUP BY Engine_ID
ORDER BY Engine_ID

 

[Guest]

Ok, i created a new query to do the concatenation and it is based off of
another query: qryAIModelsXREFEngines. I have the fields of the query listed
below as well as the new concatenation query i created. This time i have
results where there are multiple instances of the same model match with each
engine.

Engine_ID Model
1 100, 100, 100, 100
2 101, 101
3 100, 100, 100, 100

qryAIModelsXREFEngines
Model_ID number
Model text
Engine_ID number
EngineName text
Production number
Notes memo
Engine_Source number

SELECT qryAIModelsXREFEngines.Engine_ID,
qryAIModelsXREFEngines.EngineName,Concatenate("SELECT Model FROM
qryAIModelsXREFEngines WHERE qryAIModelsXREFEngines.Model_ID = " &
[Model_ID]) AS Models
FROM qryAIModelsXREFEngines;

Like i said i have used this before with a different query and got the
correct results but i can't seem to get it to work this time around. When i
try to include the Engine_ID into the concatenate field, my results look like
my results from my first post. Again thank you for all of your help.

It would help to know your table/fields with data types of all significant
fields.
I really doubt you want to select from Models in the Concatenate function.
The table/query inside the concatenate function should have Engine_ID and
Model fields.

--
Duane Hookom
MS Access MVP

Sorry about the last post being empty, i experienced a slight problem.
Anyways, i have used Duane hookom's concatenate function before, but this
time i am having some problems. I copied the code for the function from
his
website and have used one of his queries as the basis for mine. The
problem
is that the results are not concatenated together, that is i get one
record
per line.

Engine_ID Model
1 1
1 2
1 4
2 3
2 2

But when i want is this:
Engine_ID Model
1 1,2,4
2 2,3

This is the query that i used:
SELECT ModelsXREFEngines.Engine_ID, Concatenate("SELECT Model FROM Models
WHERE Model_ID =" & [Model_ID]) AS [Model Names]
FROM ModelsXREFEngines
ORDER BY ModelsXREFEngines.Engine_ID;

Like i said, i have used the concatenation function before, but now for
some
reason it is not working with this query. Any help anyone can provide
would
be most appreciated. Thank you

 

[Guest]

THank you for your help, but when running your code i get an error: Run-time
error '-2147217904 (80040e10)': No value given for one or more required
parameters. This happened to both the sql statement that Marshall gave me as
well as the sql provided by John

Sorry about the last post being empty, i experienced a slight problem.
Anyways, i have used Duane hookom's concatenate function before, but this
time i am having some problems. I copied the code for the function from his
website and have used one of his queries as the basis for mine. The problem
is that the results are not concatenated together, that is i get one record
per line.

Engine_ID Model
1 1
1 2
1 4
2 3
2 2

But when i want is this:
Engine_ID Model
1 1,2,4
2 2,3

This is the query that i used:
SELECT ModelsXREFEngines.Engine_ID, Concatenate("SELECT Model FROM Models
WHERE Model_ID =" & [Model_ID]) AS [Model Names]
FROM ModelsXREFEngines
ORDER BY ModelsXREFEngines.Engine_ID;

Like i said, i have used the concatenation function before, but now for some
reason it is not working with this query. Any help anyone can provide would
be most appreciated.

I have no idea where the table Models came from, but based
on your example it's irreleveant. Try this:

SELECT Engine_ID,
Concatenate("SELECT Model
FROM ModelsXREFEngines
WHERE Engine_ID =" & Engine_ID
) AS [Model Names]
FROM ModelsXREFEngines
GROUP BY Engine_ID
ORDER BY Engine_ID

 

[Guest]

Ok, using your guys' suggestions i found out what my problem was: I should
have used the table that describes the engines as the basis for the main
query and for the concatenation part i should have used the
ModelsXREFEngines. Thank you very much for all of your help.

Sorry about the last post being empty, i experienced a slight problem.
Anyways, i have used Duane hookom's concatenate function before, but this
time i am having some problems. I copied the code for the function from his
website and have used one of his queries as the basis for mine. The problem
is that the results are not concatenated together, that is i get one record
per line.

Engine_ID Model
1 1
1 2
1 4
2 3
2 2

But when i want is this:
Engine_ID Model
1 1,2,4
2 2,3

This is the query that i used:
SELECT ModelsXREFEngines.Engine_ID, Concatenate("SELECT Model FROM Models
WHERE Model_ID =" & [Model_ID]) AS [Model Names]
FROM ModelsXREFEngines
ORDER BY ModelsXREFEngines.Engine_ID;

Like i said, i have used the concatenation function before, but now for some
reason it is not working with this query. Any help anyone can provide would
be most appreciated.

I have no idea where the table Models came from, but based
on your example it's irreleveant. Try this:

SELECT Engine_ID,
Concatenate("SELECT Model
FROM ModelsXREFEngines
WHERE Engine_ID =" & Engine_ID
) AS [Model Names]
FROM ModelsXREFEngines
GROUP BY Engine_ID
ORDER BY Engine_ID