Using the DISTINCT keyword may give you the results you want, although it will
not be the most efficient way of doing this.
SELECT DISTINCT EmployeeTraining.EmployeeID
, EmployeeTraining.LicenceID
, Concatenate("SELECT [Course Name] FROM [CourseTraining] INNER JOIN
[EmployeeTraining] ON [CourseTraining].CourseTrainingID =
[EmployeeTraining].LicenceID WHERE [EmployeeTraining].EmployeeID= " &
[EmployeeTraining].[EmployeeID] & "",",") AS coursename
FROM EmployeeTraining
WHERE (((EmployeeTraining.EmployeeID)=49) AND ((EmployeeTraining.LicenceID)
In (1,2,3,4,5,6,7)));
I don't know your data structure. There may be a more efficient way to do
this and there may not be. If the above works for you, then use it. If it is
too slow, post back and tell us some thing about your tables
EmployeeTraining - what kind of data does this hold
CourseTraining - what kind of data does this hold
This query with a nested subquery in the FROM clause should give you the
results you want AS LONG AS an employee has only one LicenceID.
SELECT ET.*
,Concatenate("SELECT [Course Name] FROM [CourseTraining] INNER JOIN
[EmployeeTraining] ON [CourseTraining].CourseTrainingID =
[EmployeeTraining].LicenceID WHERE [EmployeeTraining].EmployeeID= " &
[EmployeeID] & "",",") AS coursename
FROM (SELECT DISTINCT EmployeeID, LicenceID FROM EmployeeTraining WHERE
LicenseID in (1,2,3,4,5,6,7)) as ET
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
DontKnow wrote:
Hi John,
Here is the SQL view of my query:
SELECT EmployeeTraining.EmployeeID, EmployeeTraining.LicenceID,
Concatenate("SELECT [Course Name] FROM [CourseTraining] INNER JOIN
[EmployeeTraining] ON [CourseTraining].CourseTrainingID =
[EmployeeTraining].LicenceID WHERE [EmployeeTraining].EmployeeID= " &
[EmployeeTraining].[EmployeeID] & "",",") AS coursename
FROM EmployeeTraining
WHERE (((EmployeeTraining.EmployeeID)=49) AND ((EmployeeTraining.LicenceID)
In (1,2,3,4,5,6,7)));
I am actually getting the write values as a result of the query above but I
am also getting 3 records returned where I only need 1??
Any assistance greatly appreciated!!
I have been pulling my hair out on this one!!
Quite frustrating!
Cheers
:
I think you want to use something along the lines of
SELECT EmployeeTraining.EmployeeID
, EmployeeTraining.LicenceID,
concatenate("select [Course Name] from CourseTraining") AS coursename
FROM EmployeeTraining
WHERE EmployeeTraining.EmployeeID=
[forms]![Report Selector Form]![text12])
AND EmployeeTraining.LicenceID in (1,2,3,4,5,6,7)
PERHAPS the expression you are looking for would be.
Concatenate(
"SELECT [Course Name]
FROM [CourseTraining] INNER JOIN [Link Table]
ON [CourseTraining].CourseTrainingID = [Link Table].CourseTrainingID
WHERE [Link Table].EmployeeID=""" &
EmployeeTraining.EmployeeID & """",",")
That is all one line. I have just formatted it for readability.
You did say you can get the data you want horizontally and have a query for
that. May I suggest you post that query? That may give me a better idea of
your structure.
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
DontKnow wrote:
Hi John the SQKL of the query is:
SELECT EmployeeTraining.EmployeeID, EmployeeTraining.LicenceID,
concatenate("select [Course Name] from CourseTraining") AS coursename
FROM EmployeeTraining
WHERE (((EmployeeTraining.EmployeeID)=[forms]![Report Selector
Form]![text12]) AND ((EmployeeTraining.LicenceID)=1 Or
(EmployeeTraining.LicenceID)=2 Or (EmployeeTraining.LicenceID)=3 Or
(EmployeeTraining.LicenceID)=4 Or (EmployeeTraining.LicenceID)=5 Or
(EmployeeTraining.LicenceID)=6 Or (EmployeeTraining.LicenceID)=7));
The expression I am trying to use is:
coursename: concatenate("select [Course Name] from CourseTraining")
I have 2 tables, one is called CourseTraining, attributes: CourseTrainingID,
Course Name, Notes
2nd Table is a link Table, with attributes: EmployeeID, LicenceID,
Datecompleted, ...
My difficulty is that I am trying to get the [Course Name] from the
CourseTraining Table when I have an EmployeeID and LicenceID from the LInk
Table EmployeeTraing and i don't know how to do this via the QBE to provide a
horizontal output vice a vertical one from the usual QBE query??
I hope this helps??
Thnaks agian John!!
:
Perhaps, but I can't tell what it is.
Post the SQL of the query you want to use.
Post the Concatenate expression you are trying to use.
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
DontKnow wrote:
Yes you are right!!
Its OK with just numbers as parameters but if you use say
forms![myform]![mytextbox] it does create an error which is the one that I
was getting!!
Is there any work around for this??
Cheers,
thamnks for your help John!!
:
You can use a query in place of the table - IF the query you are using does
NOT have any parameters. If it does have parametersa, I think it will cause
an error.
Experiment and see what you learn.
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
DontKnow wrote:
Hi John,
In your example you have a query based on a table, what changes would be
required if the query was based on another query??
Os should I just poor the results of the query into a table??
Is there a better way I don't know??
Cheers in advance!!
:
Quoting Duane Hookom
I use a generic Concatenate() function. The code is listed below with both ADO
and DAO. There are comments regarding which lines to comment or uncomment
based on which library you prefer. Access 97 is mostly DAO while the default
for 2000 and newer is ADO.
See:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
Be sure to copy the function into your database and be sure to modify as
appropriate to make it work with your database. See Duane's comments in the
code for the modifications that MAY be needed.
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
DontKnow wrote:
Hi Guys,
I wanted to display the output from a single field generated from a query in
a textbox. For instance the single field is called "Course" and the output
is typically:
va
vt
be
as
tg etc
I wanted the output to look like this:
va vt be as tg etc
Is this possible
many thnaks in advance!!
Cheers
