I have a couple of (OK, several) suggestions ...
Where do I place the function in the following query. I want to concatenate
the Serial Number field from the Web Orders table, the linking field is
RECORDID of date type Double.
I notice that your SQL never mentions any [Web Orders].[Serial Number]
field, leading me to think that you are actually referring to
[Upgrade Record].[Serial Number]. If that be true, you may not need to
link the Tables in this Query, as fConcatChild() will return the values
you need.
You could place the function call immediately after the "SELECT",
followed by a comma, or (following a comma) after any other field name
up to the "FROM". For example, suppose your Tables contain these data
(I left out some fields for simplicity):
[Web Orders] Table:
RECORDID FIRSTNAME LASTNAME COUNTRY
-1779165370 Buster Brown US
-1672414385 John Doe US
[Upgrade Record] Table:
Serial Number Web confirmation Num
------------- --------------------
185 -1779165370
325 -1779165370
884 -1779165370
2855 -1779165370
18423 -1779165370
31415 -1672414385
Then your [Q_SerialNums] Query SQL (simplified to omit some of the
fields) might look like this:
SELECT [Web Orders].RECORDID, [Web Orders].LASTNAME,
[Web Orders].COUNTRY,
fConcatChild("Upgrade Record","Web confirmation Num",
"Serial Number","Long",[Web Orders]![RECORDID])
AS SerialNumbers
FROM [Web Orders]
WHERE ((([Web Orders].COUNTRY)="US"))
ORDER BY [Web Orders].LASTNAME, [Web Orders].ADDRESS2;
Then your [Q_SerialNums] Query Datasheet view would look like this:
RECORDID LASTNAME COUNTRY SerialNumbers
----------- -------- ------- ----------------------
-1779165370 Brown US 185;2855;18423;884;325
-1672414385 Doe US 31415
Notice that the numbers in [SerialNumbers] are not sorted because
fConcatChild() contains no "ORDER BY" clause. Also note that you will
have to include a reference in your Module to a Microsoft DAO object
library to compile the code for the function.
SELECT [Web Orders].RECORDID, [Web Orders].ORGANIZATION, [Web Orders].Title,
[Web Orders].FIRSTNAME, [Web Orders].LASTNAME, [Web Orders].SUFFIX, [Web
Orders].ADDRESS1, [Web Orders].ADDRESS2, [Web Orders].CITY, [Web
Orders].STATE, [Web Orders].ZIPCODE, [Web Orders].COUNTRY, [Upgrade
Record].[Serial Number], [Upgrade Record].[Extended Warranty Period], [Web
Orders].[SALES REP]
FROM [Upgrade Record] INNER JOIN [Web Orders] ON [Upgrade Record].[Web
confirmation Num] = [Web Orders].RECORDID
WHERE ((([Web Orders].COUNTRY)="US"))
ORDER BY [Web Orders].ADDRESS2;
I have a couple of naming suggestions, to make it easier to maintain
your database. You might want to consider renaming your primary key
[Web Orders].RECORDID
to be [Web Orders].[Web OrdersID]
and to rename the linking (foreign) key similarly, changing
from [Upgrade Record].[Web confirmation Num]
to [Upgrade Record].[Upgrade RecordID]. If you do that, it will be more
obvious that it's a key value, and in which Table it's the primary key.
Note that the function fConcatChild() returns a string that will look
like this, assuming you have serial numbers like 0185, 2855, etc.:
"0185;2855;18423;884;0325"
and, if I read the copyright notice correctly, you're not allowed to
replace that ";" with some other character in the code (you'll have to
modify the string after the function returns it). That concatenated
value will be the value of one field, that you can include in your Mail
Merge document with one field name.
===
Here's another thought, which you may find useful (though I'm not sure
exactly what you're doing in your Mail Merge document); this does not
utilize the fConcatChild() function:
You could define a Query, [Q_NamesAndSerialNumbers], as follows (I
omitted some more fields, but you can easily put them back in if you
need them):
SELECT [Web Orders].RECORDID, [Web Orders].LASTNAME,
[Upgrade Record].[Serial Number],
Count([Upgrade Record_1].[Serial Number])
AS [CountOfSerial Number1]
FROM ([Upgrade Record]
INNER JOIN [Web Orders]
ON [Upgrade Record].[Web confirmation Num]
= [Web Orders].RECORDID)
INNER JOIN [Upgrade Record]
AS [Upgrade Record_1]
ON [Upgrade Record].[Web confirmation Num]
= [Upgrade Record_1].[Web confirmation Num]
WHERE ((([Upgrade Record_1].[Serial Number])
<=[Upgrade Record].[Serial Number]))
GROUP BY [Web Orders].RECORDID, [Web Orders].LASTNAME,
[Upgrade Record].[Serial Number]
ORDER BY [Web Orders].LASTNAME;
and (same data as before), the Datasheet View for this Query would look
like this:
RECORDID LASTNAME Serial Number CountOfSerial Number1
----------- -------- ------------- ---------------------
-1779165370 Brown 185 1
-1779165370 Brown 325 2
-1779165370 Brown 884 3
-1779165370 Brown 2855 4
-1779165370 Brown 18423 5
-1672414385 Doe 31415 1
(Actually, there's no need to display the [RECORDID] value, as it
doesn't mean anything to a human being.)
Now define [Q_NamesAndSerialNumbers_Crosstab] with the following SQL:
TRANSFORM First(Q_NamesAndSerialNumbers.[Serial Number])
AS [FirstOfSerial Number]
SELECT Q_NamesAndSerialNumbers.LASTNAME,
Q_NamesAndSerialNumbers.RECORDID
FROM Q_NamesAndSerialNumbers
GROUP BY Q_NamesAndSerialNumbers.LASTNAME,
Q_NamesAndSerialNumbers.RECORDID
PIVOT Q_NamesAndSerialNumbers.[CountOfSerial Number1];
and its Datasheet View will look like this:
LASTNAME RECORDID 1 2 3 4 5
-------- ----------- ----- --- --- ---- -----
Brown -1779165370 185 325 884 2855 18423
Doe -1672414385 31415
(Once again, there's really no need to include [RECORDID] here. You
could substitute other fields, such as [FIRSTNAME] or [COUNTRY].)
Of course, with this type of Query, the number of fields will depend on
your data -- you could find that you have dozens of fields here.
In your Mail Merge document, having specified
[Q_NamesAndSerialNumbers_Crosstab] as your data source, you can include
the fields as you wish, for example:
Customer «LASTNAME» has these serial numbers:
«M_1» «M_2» «M_3» «M_4» «M_5»
----- «Next Record»
Customer «LASTNAME» has these serial numbers:
«M_1» «M_2» «M_3» «M_4» «M_5»
----- «Next Record»
...
to create a document looking like this:
Customer Brown has these serial numbers:
185 325 884 2855 18423
-----
Customer Doe has these serial numbers:
31415
-----
...
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
:
Use a Query based on your Table [Customer] and use a function similar to
fConcateChild whose code is available from:
http://www.mvps.org/access/modules/mdl0004.htm
--
HTH
Van T. Dinh
MVP (Access)
I am trying to use mail merge with a query that returns customer info
associated with serial numbers. The problem is the query returns multiple
rows (one for each serial number record) for each customer.
That can happen if a relationship ("JOIN" operator) is missing from the
Query.
