Dealer list:

Order form:

To use SQL query to get the following results:

Note: in the query results such as "access charges together" in the table for order the dealer "expenses course" of the total, that is, all kinds of fees will be subject of horizontal classification shows total, of sales accounted for the total subjects divided by the total revenue, and multiplied by 100,
No record in terms of query results in "* * * * *" fill in,
Query in Delphi use ADOQUERY data set,

CodePudding user response:

Process is as follows:
Step 1: you first create a query, the inside of the essentially similar views, put the order in accordance with the dealer ID inside collect, if for query 1,
Step 2: set up the second query, the dealer crosstab table and query 1 connection will get the results you want, the '*' to treat the case,
Very simple, it was I grope for the above steps for access to good at using query this
Additional access crosstab than used to use simple
The TRANSFORM Sum
The PIVOT
Check the usage

CodePudding user response:

Wrote you a promotion, the other is you add fields


Select t1. The dealer name, t1. Total revenue, the IIF (t3. Cx is null, '* * * * *, t3. Cx), the IIF (t3. Cx is null,' * * * * *, (t3) cx/t1) revenues) * 100) from t1
Left the join
(select the dealer ID, the sum (IIf (cost subject='promotion expenses, application amount, 0)) as cx from t2 group by the dealer ID) t3
On t1. The dealer ID=t3. The dealer ID