how to write an expression

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

Guest

i have the following table

year # loans type percentage
1982 5 conv
1982 4 fha
1983 1 conv
1983 5 fha
1983 3 pool
i want to divide, say the 5 loans for 1982 divided by the total which in
this case would be 9 loans for that year and get a percentage then the next
type the 4 loans by the total of 9 for that year. how could i do this in an
expression in the query?
 
dlb1228 said:
i have the following table

year # loans type percentage
1982 5 conv
1982 4 fha
1983 1 conv
1983 5 fha
1983 3 pool
i want to divide, say the 5 loans for 1982 divided by the total which in
this case would be 9 loans for that year and get a percentage then the next
type the 4 loans by the total of 9 for that year. how could i do this in an
expression in the query?
Click to expand...
With the structure you have, it's impossible to do in a query. Why? Because
you want an UPDATE query to put the percentage data in the percentage field,
and that would have to be based on a GROUP BY year and Sum([# loans]), which
requires a TOTALS query. By definition, totals queries are not updateable,
and any update queries based on them will therefore fail.

Besides, the percentage field, which is a composite of several records, does
not belong in this table at all, because the percentage will depend on how
you group your "# loans", "type", and "year" fields.

My suggestion? Remove the percentage from this table. Have a query, or a
report, which can be based on a totals query, to derive the information "on
the fly" - meaning when you need it, it's not hard-coded.

I put your five records into Table1 (in an existing database, which is why my
object names are a bit strange), and I created a query based on Table1 as
follows:

SELECT Table1.year, Table1.qty, Table1.type FROM Table1 GROUP BY Table1.year,
Table1.qty, Table1.type;

I called that query Query16.

Next I created a query that put everything together, as follows:

SELECT Query16.year, Query16.type, Query16.qty, [Query16].[qty]/Sum([Table1].
[qty]) AS percentage FROM Query16 INNER JOIN Table1 ON Query16.year = Table1.
year GROUP BY Query16.year, Query16.type, Query16.qty;

Running this query gave me the following datasheet view (after I set the
format property of the percentage field to Percent):

year type qty percentage
1982 conv 5 55.56%
1982 fha 4 44.44%
1983 conv 1 11.11%
1983 fha 5 55.56%
1983 pool 3 33.33%

It's unfortunate that you have 9 loans recorded for both years, but it'll
work on any number of loans. Try it.

Of course, you can print out the view after running the query, or format it
into a fancy report and print it there.

Good luck.
 
Try SQL like:

SELECT [Year], [# Loans], [Type],
[# Loans]/(SELECT Sum([# Loans]) FROM tblNoNameGiven N WHERE
tblNoNameGiven.[Year]= n.[Year]) as Percentage
FROM tblNoNameGiven;

How about next time you provide the real table and field names?
 
[# Loans]/(SELECT Sum([# Loans]) FROM tblNoNameGiven N WHERE
tblNoNameGiven.[Year]= n.[Year]) as Percentage
Click to expand...
in the first line what does N mean next to WHERE i understand WHERE
and the next line what does .[year]=n.[year] mean im confused on those
parts

Duane Hookom said:
Try SQL like:

SELECT [Year], [# Loans], [Type],
[# Loans]/(SELECT Sum([# Loans]) FROM tblNoNameGiven N WHERE
tblNoNameGiven.[Year]= n.[Year]) as Percentage
FROM tblNoNameGiven;

How about next time you provide the real table and field names?
--
Duane Hookom
MS Access MVP
--

dlb1228 said:
i have the following table

year # loans type percentage
1982 5 conv
1982 4 fha
1983 1 conv
1983 5 fha
1983 3 pool
i want to divide, say the 5 loans for 1982 divided by the total which in
this case would be 9 loans for that year and get a percentage then the
next
type the 4 loans by the total of 9 for that year. how could i do this in
an
expression in the query?
Click to expand...
Click to expand...
 
The denominator is a subquery. It uses the same table as the main query. You
need to give one instance of the table a different name so I named it "n".

--
Duane Hookom
MS Access MVP


dlb1228 said:
[# Loans]/(SELECT Sum([# Loans]) FROM tblNoNameGiven N WHERE
tblNoNameGiven.[Year]= n.[Year]) as Percentage
Click to expand...
in the first line what does N mean next to WHERE i understand WHERE
and the next line what does .[year]=n.[year] mean im confused on
those
parts

Duane Hookom said:
Try SQL like:

SELECT [Year], [# Loans], [Type],
[# Loans]/(SELECT Sum([# Loans]) FROM tblNoNameGiven N WHERE
tblNoNameGiven.[Year]= n.[Year]) as Percentage
FROM tblNoNameGiven;

How about next time you provide the real table and field names?
--
Duane Hookom
MS Access MVP
--

dlb1228 said:
i have the following table

year # loans type percentage
1982 5 conv
1982 4 fha
1983 1 conv
1983 5 fha
1983 3 pool
i want to divide, say the 5 loans for 1982 divided by the total which
in
this case would be 9 loans for that year and get a percentage then the
next
type the 4 loans by the total of 9 for that year. how could i do this
in
an
expression in the query?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top