Aggregate fields using query

  • Thread starter Thread starter Clementius
  • Start date Start date
C

Clementius

Hi,
I have two tables linked by one-to-many relationship: tblCustomer &
blackout. One customer can have many accounts. I would like to create labels
that include Customer information and also an aggregate of account numbers
for that customer as in:
John Smith
765 - 766 - 785
Address 1
City, State, Zip
I'd like to create a query that select all needed info from tblCustomer and
aggregate all Account from blackout for that customer. Any suggestions on
how I can accomplish this? Thank you. C
 
Dear Clementius:

It looks like your "aggregate" is to concatenate all the account
numbers with "space hyphen space" between them. There is not, and
cannot be such an aggregate function. There is nothing to keep it
from having thousands, or millions of account numbers for one
customer. The string created in this way would be of indefinite
length, and could be expected to be longer than limits allow.

For this reason, the rule in databases is "vertical, not horizontal"

You could do this as:

John Smith
765
766
785
Address1
City, State Zip

Where the account names are listed down in a sub-form or sub-report.
This allows for an indefinite amount of growth - that is, it is
guaranteed to work.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top