I have a relational system through which I sell different products. The
items that are sold are kept in a database called "Line Items." The orders,
themselves, are produced in a database called "Orders." The two are related
to each other through a sequence number.

I would like to send out a promotional letter that contains information
about a directory product sold in 2004 and a handbook sold in 2003. In the
letter I would like to include information about the total quantity of
directories and handbooks that were purchased a buyer. There will be cases
where a buyer bought only one or more directories; cases where a buyer
bought only one or more handbooks; and cases where a buyer bought one or
more directories and handbooks.

I can find all of the data in the Line Items database with no problem.
However, I'm not sure how to combine the data into a single record that
shows a buyer's combined purchase of directories and handbooks. For example,
if a person purchased 3 directories and 4 handbooks, there are two entries
in the Line Items database - one for the directories and one for the
handbooks.

Hal <HCK (AT) austin (DOT) rr.com> wrote:

First of all, you are referring to buyers, but you don't mention a
buyers file. I will assume that you have a Customers file.
So you need two extra relationships, also known as filtered portals, one
for the directories and one for the handbooks.
Define a calculated textfield in Line Items: categoryKey =
customerID&category, where category can be 'handbook' or 'directory'.
Define two calculated text fields in Customers: handbookKey =
customerID&"handbook", directoryKey = customerID &"directory".
Define two relationships from Customers to Line Items:
CustomerHandbooks, handbookKey>>categoryKey, and CustomerDirectories,
directoryKey>>categoryKey.
Now Count(CustomerHandbooks::categoryKey) gives you the number of
handbooks sold to this customer and
Count(CustomerDirectories::categoryKey) the number of directories.
Does this solve your problem?
--
Hans Rijnbout
Utrecht, Netherlands