###Divide in a select query###

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi folks,

This may be a simple one so forgive my lack of knowledge... I have a field
named [transaction amount], what I want to do is create a query to divide the
sum of all transaction amount's against each instance of tranaction amount.
Anyone help?

Thanks in advance,
Jim.
 
Hi Jim,

You could use DSum() to do this. In that case you would just need to enter
the following as the field source:

TransactionPerc: [[transaction amount]]/DSum("[transaction
amount]","YourTable")

You can also use a subquery to do this. It can be as simple as entering the
following as the field source of a query:

TransactionPerc: [[transaction amount]]/(SELECT Sum(VT.[transaction amount])
From YourTable AS VT)

Of course you would have to replace "YourTable" with the actual table name
in either case above.

In the subquery example above I have aliased the table name in the subquery
as VT (for Virtual Table). It isn't really necessary in this case, but I
alias tables in subqueries as a habit because it is easier to keep track of
where field sources are coming from and also because it is necessary if you
want to later correlate the subquery to the main query (You don't have to use
VT, you can use whatever alias you want).

For instance, say you wanted had a field CustomerID in the main query, and
you wanted to divide the main query transaction amount by the sum of all
transactions for the current record customer. You could do this by using a
correlated subquery such as:

CustomerTransPerc: [[transaction amount]]/(SELECT Sum(VT.[transaction
amount]) From YourTable AS VT WHERE VT.CustomerID = YourTable.CustomerID)

In that case the WHERE clause of the subquery would limit the records summed
to those matching the CustomerID of the current record.

You could also get the same result using DSum() by using:

CustomerTransPerc: [[transaction amount]]/DSum("[transaction
amount]","YourTable","[CustomerID] =" & [CustomerID])

I tend to prefer using subqueries in general because they offer more
flexibility when you start getting to more complicated cases, and also
because I don't have to worry about concatenation of the criteria. I think I
have also read that they may execute faster, but that may depend on the
circumstances.

HTH, Ted Allen
 
Oops, just noticed that in all of my examples I accidentally had double
brackets around transaction amount, such as [[transaction amount]]. Those
should have been single brackets such as [transaction amount]. Must have
made the error in copying/pasting your field name.

-Ted Allen

Ted Allen said:
Hi Jim,

You could use DSum() to do this. In that case you would just need to enter
the following as the field source:

TransactionPerc: [[transaction amount]]/DSum("[transaction
amount]","YourTable")

You can also use a subquery to do this. It can be as simple as entering the
following as the field source of a query:

TransactionPerc: [[transaction amount]]/(SELECT Sum(VT.[transaction amount])
From YourTable AS VT)

Of course you would have to replace "YourTable" with the actual table name
in either case above.

In the subquery example above I have aliased the table name in the subquery
as VT (for Virtual Table). It isn't really necessary in this case, but I
alias tables in subqueries as a habit because it is easier to keep track of
where field sources are coming from and also because it is necessary if you
want to later correlate the subquery to the main query (You don't have to use
VT, you can use whatever alias you want).

For instance, say you wanted had a field CustomerID in the main query, and
you wanted to divide the main query transaction amount by the sum of all
transactions for the current record customer. You could do this by using a
correlated subquery such as:

CustomerTransPerc: [[transaction amount]]/(SELECT Sum(VT.[transaction
amount]) From YourTable AS VT WHERE VT.CustomerID = YourTable.CustomerID)

In that case the WHERE clause of the subquery would limit the records summed
to those matching the CustomerID of the current record.

You could also get the same result using DSum() by using:

CustomerTransPerc: [[transaction amount]]/DSum("[transaction
amount]","YourTable","[CustomerID] =" & [CustomerID])

I tend to prefer using subqueries in general because they offer more
flexibility when you start getting to more complicated cases, and also
because I don't have to worry about concatenation of the criteria. I think I
have also read that they may execute faster, but that may depend on the
circumstances.

HTH, Ted Allen

Jimmy said:
Hi folks,

This may be a simple one so forgive my lack of knowledge... I have a field
named [transaction amount], what I want to do is create a query to divide the
sum of all transaction amount's against each instance of tranaction amount.
Anyone help?

Thanks in advance,
Jim.
Click to expand...
Click to expand...
 
Back
Top