Query that returns one line of data

[Günter Brandstätter]

Hi all,

I have a serious query-problem. Have two tables which are joined by a
relation "Place"
Table 1
Key Place
1 2
2 3
2 1

Table 2
Place Description
1 Place1
2 Place2
3 Place3

I would need a query, that returns a line for one key in table1 e.g. Key=2
should return the line "Place3, Place1"
Any help appreciated
Günter

 

[mate]

Query should look as follows:

Place should be the primary key in the TABLE1. Link it to
the Place field in TABLE2. Add the Key field from TABLE1
to the query and in criteria type [enter key#]. Then add
the Place field and Description field from Table2. Type
the desired key # when prompted. hope this helps. mate

 

[Günter Brandstätter]

Thanks for your answer, but this kind of query will return two lines with
the places and descriptions when I select "2" as Key.
What I need is a query that combines these two lines to one string. The
result should be "Place3, Place1" in my case.
The primary key of the first table is alredy set to both fields and the two
tables are already linked by a 1 to n relation.

hope, anyone else has a solution for my problem
Günter

-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com


"mate" <[email protected]> a écrit dans le message de
Query should look as follows:

Place should be the primary key in the TABLE1. Link it to
the Place field in TABLE2. Add the Key field from TABLE1
to the query and in criteria type [enter key#]. Then add
the Place field and Description field from Table2. Type
the desired key # when prompted. hope this helps. mate

 

[John Vinson]

I would need a query, that returns a line for one key in table1 e.g. Key=2
should return the line "Place3, Place1"

You'll need some VBA code to do this. There's sample code at

http://www.mvps.org/access/modules/mdl0004.htm

 

[Günter Brandstätter]

Hi John,
I already thought that it would take some code to do this, but how do I
manage my problem now?
This "Query" is located in the backend of a database, and I use a WORD
Mailmerge function to get the data out. Is it possible to tell WORD to use a
piece of code instead of a query to get the mailmerg-data??
Thank you in advance
Günter

-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com

 

[John Vinson]

This "Query" is located in the backend of a database, and I use a WORD
Mailmerge function to get the data out. Is it possible to tell WORD to use a
piece of code instead of a query to get the mailmerg-data??

You can use a call to a VBA function as a calculated field in a Query
- but if the backend is in SQL/Server, you can't! You may need to use
a View if that's the case.

 

[Günter Brandstätter]

could you please tell me, how to use a calculated field in a query. What is
the SQL-instruction for this?

thanks
Günter

--


-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com

 

[John Vinson]

could you please tell me, how to use a calculated field in a query. What is
the SQL-instruction for this?

Let's say you have a custom function MyFunc() with one argument, a
number.

You can put

FunctionValue: MyFunc([numberfield])

in a vacant Field cell; or, equivalently,

SELECT MyFunc([numberfield]) AS FunctionValue FROM yourtable;

 

[Günter Brandstätter]

Thank you so much, I think that will do the job.
Günter

-------------------------------------------------------------------------
FIGHT BACK AGAINST SPAM!
Download Spam Inspector, the Award Winning Anti-Spam Filter
http://mail.giantcompany.com

could you please tell me, how to use a calculated field in a query. What is
the SQL-instruction for this?

Let's say you have a custom function MyFunc() with one argument, a
number.

You can put

FunctionValue: MyFunc([numberfield])

in a vacant Field cell; or, equivalently,

SELECT MyFunc([numberfield]) AS FunctionValue FROM yourtable;