Re: Aggregate string concatenation efficiency problem


fConcatChild("Consultants RIGHT JOIN ConsultantSales ON
Consultants.ConsultantID =
ConsultantSales.ConsultantID","SaleID","ConsultantFullName","Long",
[Sales].[SaleID]) AS Column5
<<

OK. I see your function call here. It looks like you are trying to
concatenate a consultant name. How about making this change:

...
FROM ((Customers INNER JOIN Sales ON Customers.CustomerID =
Sales.CustomerID) INNER JOIN VehicleSales ON Sales.SaleID =
VehicleSales.SaleID) Join Consultants t5 on t5.ID =
Customers.ConsultantID?

then replace fConcatChild with

Select ..., (t5.FirstName & ' ' & t5.LastName) As Column5...

or if this returns duplicates then

Select Distinct ..., (t5.FirstName & ' ' & t5.LastName) As Column5...

If the consultants table can't be joined to customers or vehicles
tables, you need to add a column so that you can join this table. I
will guess that there are many consultants to each customer? Then if a
customer has 10 consultants, you should have 10 rows for that customer.
This would be using 3rd Normal Form of the normalizing model (which is
what RDBMS is all about).

Note: (whith all due respect) if a sql statement is using a function
that calls a "Loop" -- that function needs to be scrapped. Loops should
be avoided in sql statements. Why? Do you have all day for your query
to run? The whole concept of normalization is to prevent redundant data
processing.

Once you have your resultset, then you can manipulate your data.


Rich

*** Sent via Developersdex http://www.developersdex.com ***
.

Relevant Pages

  • Re: Aggregate string concatenation efficiency problem
    ... If you have a lot of records, that loop will be called for each ... Column5 -- it looks like you want to list multiple consultants ... for each customer - on the same record. ... If this returns duplicate records then use the "Distinct" keyword ...
    (comp.databases.ms-access)
  • Re: How can I add a row from a form?
    ... >Jobs: Which customer gets which product, and which consultant is doing it ... >Consultants: Name and contact info for each one ... I have three friends here in Parma: Fred Brown; his son Fred Brown; ...
    (microsoft.public.access.forms)
  • Re: OT question about time tracking consultant software
    ... software that we can add many consultants and they can help to resolve ... customer IT issues and then we could know what the consult did and how ... long it took them to resolve the issue that might be in terms of hours, ... It is in the FC5 extras repository so ...
    (Fedora)
  • Re: Another lousy Borland experience
    ... How do you "sack" a customer who has already bought your product? ... simply "fire" the vendor without incurring huge business costs. ... hire any number of consultants or integrators and fire them for non- ...
    (borland.public.delphi.non-technical)