Hello!
I am fairly new to Access and wondered if someone could help me with some
syntax.

I would like to create a totals query to average the values from about 50
fields in several tables, after selecting records where assets (the value in
Fs220_Curr.ACCT_010) > $399,999,999. The original tables have about 9000
records. I wrote a query to find the records where Fs220_Curr.ACCT_010 >
$399,999,999 and then a totals query from the results, but I would like to
combine them into one query, if possible. I think that a subquery would fit
the bill, but I am not sure how to write it correctly.

Any help would be greatly appreciated.

Thanks!

Diana

Create your Totals query based on the original table and add a Where clause.
In the query builder, add the field to a column, choose Where for the
Totals: Row, then put your criteria in the Criteria row.

BTW, it is helpful when asking for help with a query to list the SQL.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L


"Diana" <(E-Mail Removed)> wrote in message
news:43006F54-FF9E-43D3-96CA-(E-Mail Removed)...
> Hello!
> I am fairly new to Access and wondered if someone could help me with some
> syntax.
>
> I would like to create a totals query to average the values from about 50
> fields in several tables, after selecting records where assets (the value
in
> Fs220_Curr.ACCT_010) > $399,999,999. The original tables have about 9000
> records. I wrote a query to find the records where Fs220_Curr.ACCT_010 >
> $399,999,999 and then a totals query from the results, but I would like to
> combine them into one query, if possible. I think that a subquery would
fit
> the bill, but I am not sure how to write it correctly.
>
> Any help would be greatly appreciated.
>
> Thanks!
>
> Diana

Roger -
Sorry, I didn't include the SQL because it was so long. I have already
tried what you suggested but I get an overflow error. I think (but it's a
guess) that I am getting the error because Access is trying to average the
values for 52 separate fields for 9000 records. I want to select the records
where assets are > $399,999,999 before I average the values. I cannot find
any help on the error. Here is my SQL for the averages query. Sorry it is
so long. I am very new to Access and any help would be appreciated.

Diana

SELECT Avg(Fs220_Curr.ACCT_300) AS AvgOfACCT_300, Avg(Fs220_Curr.ACCT_550)
AS AvgOfACCT_550, Avg(Fs220_Curr.ACCT_551) AS AvgOfACCT_551,
Avg(Fs220_Curr.ACCT_719) AS AvgOfACCT_719, Avg(Fs220B_Curr.ACCT_731) AS
AvgOfACCT_731, Avg(Fs220_Curr.ACCT_020B) AS AvgOfACCT_020B,
Avg(Fs220_Curr.ACCT_021B) AS AvgOfACCT_021B, Avg(Fs220_Curr.ACCT_022B) AS
AvgOfACCT_022B, Avg(Fs220_Curr.ACCT_023B) AS AvgOfACCT_023B,
Avg(Fs220_Curr.ACCT_025B) AS AvgOfACCT_025B, Avg(Fs220_Curr.ACCT_041B) AS
AvgOfACCT_041B, Avg(Fs220_Curr!ACCT_041B/Fs220_Curr!ACCT_025B) AS
DQLoansToTotalLoans1a,
Avg((Fs220_Curr!ACCT_550-Fs220_Curr!ACCT_551)/Fs220_Curr!ACCT_025B) AS
NetChargeOffsTotalLoans, Avg(Fs220A_Curr.ACCT_396) AS AvgOfACCT_396,
Avg(Fs220A_Curr.ACCT_397) AS AvgOfACCT_397, Avg(Fs220B_Curr.ACCT_971) AS
AvgOfACCT_971,
Avg((Fs220A_Curr!ACCT_396+Fs220A_Curr!ACCT_397)/Fs220_Curr!ACCT_025B) AS
UnsecToTotal1b, Avg(Fs220B_Curr!Acct_682/Fs220_Curr!ACCT_025B) AS
BkChgOffsToTotalLoans, Avg(Fs220A_Curr.ACCT_420) AS AvgOfACCT_420,
Avg(Fs220B_Curr.ACCT_784) AS AvgOfACCT_784, Avg(Fs220B_Curr.ACCT_745E) AS
AvgOfACCT_745E, Avg(Fs220B_Curr.ACCT_945) AS AvgOfACCT_945,
Avg(Fs220_Curr.ACCT_799C1) AS AvgOfACCT_799C1, Avg(Fs220_Curr.ACCT_799C2) AS
AvgOfACCT_799C2, Avg(Fs220B_Curr.ACCT_799D) AS AvgOfACCT_799D,
Avg(Fs220A_Curr.Acct_799I) AS AvgOfAcct_799I,
Avg(((Fs220_Curr!ACCT_799A*1)+(Fs220B_Curr!ACCT_799B*2)+(Fs220_Curr!ACCT_799C1*4)+(Fs220_Curr!ACCT_799C2*7.5)+(Fs220B_Curr!ACCT_799D*10))/(Fs220_Curr!ACCT_799A+Fs220B_Curr!ACCT_799B+Fs220_Curr!ACCT_799C1+Fs220_Curr!ACCT_799C2+Fs220B_Curr!ACCT_799D))
AS WeightedAvgMat1f, Avg(Fs220B_Curr!ACCT_801/Fs220B_Curr!ACCT_796E) AS
MV_Book1c,
Avg((Fs220_Curr!ACCT_799C1+Fs220_Curr!ACCT_799C2+Fs220B_Curr!ACCT_799D)/Fs220A_Curr!ACCT_799I)
AS LongMat_TotalInv1c, Avg(Fs220B_Curr!ACCT_745E/Fs220A_Curr!ACCT_799I) AS
NonSFAS_TotalInv1c, Avg(Fs220B_Curr!ACCT_784/Fs220A_Curr!ACCT_799I) AS
NonAuth_TotalInv1c, Avg(Fs220_Curr.ACCT_143A) AS AvgOfACCT_143A,
Avg(Fs220A_Curr.ACCT_900A) AS AvgOfACCT_900A, Avg(Fs220A_Curr.ACCT_900B) AS
AvgOfACCT_900B, Avg(Fs220A_Curr.ACCT_900C) AS AvgOfACCT_900C,
Avg(Fs220A_Curr.ACCT_900D) AS AvgOfACCT_900D, Avg(Fs220A_Curr.ACCT_961A) AS
AvgOfACCT_961A,
Avg((Fs220A_Curr!ACCT_133-Fs220A_Curr!ACCT_135)/Fs220_Curr!ACCT_400A) AS
NCOBus_TotalBus1d, Avg(Fs220_Curr!ACCT_400A/Fs220_Curr!ACCT_025B) AS
BusLoans_TotalLoans, Avg(Fs220A_Curr.ACCT_618A) AS AvgOfACCT_618A,
Avg(Fs220C_Curr!ACCT_619/Fs220_Curr!ACCT_025B) AS PartLoans_TotalLoans1e,
Avg(Fs220C_Curr!ACCT_690/Fs220_Curr!ACCT_025B) AS PurPart_TotalLoans1e,
Avg(Fs220C_Curr!ACCT_691/Fs220_Curr!ACCT_025B) AS SoldPart_TotalLoans1e,
Avg(Fs220B_Curr!ACCT_615/Fs220_Curr!ACCT_025B) AS PurchLoans_TotalLoans1e,
Avg(Fs220B_Curr.ACCT_812) AS AvgOfACCT_812, Avg(Fs220B_Curr.ACCT_815) AS
AvgOfACCT_815, Avg(Fs220A_Curr.ACCT_881) AS AvgOfACCT_881,
Avg(Fs220A_Curr.ACCT_882) AS AvgOfACCT_882,
Avg(Fs220B_Curr!ACCT_811/Fs220_Curr!ACCT_708) AS UnFundHELOC_HELOC1f,
Avg(Fs220B_Curr!ACCT_812/Fs220A_Curr!ACCT_396) AS UnFundVISA_VISA1f,
Avg(Fs220B_Curr!ACCT_816/Fs220_Curr!ACCT_902) AS UnFundCP_Draft1f
FROM (((BasicCUData_Curr INNER JOIN Fs220B_Curr ON
BasicCUData_Curr.CU_NUMBER = Fs220B_Curr.CU_NUMBER) INNER JOIN Fs220_Curr ON
BasicCUData_Curr.CU_NUMBER = Fs220_Curr.CU_NUMBER) INNER JOIN Fs220A_Curr ON
BasicCUData_Curr.CU_NUMBER = Fs220A_Curr.CU_NUMBER) INNER JOIN Fs220C_Curr ON
(Fs220B_Curr.CU_NUMBER = Fs220C_Curr.CU_NUMBER) AND
(BasicCUData_Curr.CU_NUMBER = Fs220C_Curr.CU_NUMBER)
WHERE (((Fs220_Curr.ACCT_010)>399999999));


"Roger Carlson" wrote:

> Create your Totals query based on the original table and add a Where clause.
> In the query builder, add the field to a column, choose Where for the
> Totals: Row, then put your criteria in the Criteria row.
>
> BTW, it is helpful when asking for help with a query to list the SQL.
>
> --
> --Roger Carlson
> MS Access MVP
> Access Database Samples: www.rogersaccesslibrary.com
> Want answers to your Access questions in your Email?
> Free subscription:
> http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
>
>
> "Diana" <(E-Mail Removed)> wrote in message
> news:43006F54-FF9E-43D3-96CA-(E-Mail Removed)...
> > Hello!
> > I am fairly new to Access and wondered if someone could help me with some
> > syntax.
> >
> > I would like to create a totals query to average the values from about 50
> > fields in several tables, after selecting records where assets (the value
> in
> > Fs220_Curr.ACCT_010) > $399,999,999. The original tables have about 9000
> > records. I wrote a query to find the records where Fs220_Curr.ACCT_010 >
> > $399,999,999 and then a totals query from the results, but I would like to
> > combine them into one query, if possible. I think that a subquery would
> fit
> > the bill, but I am not sure how to write it correctly.
> >
> > Any help would be greatly appreciated.
> >
> > Thanks!
> >
> > Diana
>
>
>

Well, there IS a limit on the complexity of an Access query and you may have
reached it. Does it work as two queries? If so, you may have to be happy
with that.

However, the REAL reason for you problem is in your table design.
Apparently, you have a column (field) for EACH account. While this is the
correct way to design a spreadsheet, it is totally incorrect for a database
table.

Instead, you should have a field called Account in which you store the
account number (300, 550, 551, etc.) and a second for storing the Value for
that account. So instead of going across your screen like this:

ACCT_300...ACCT_550...ACCT_551...ACCT_719
200...............392...............221................543
400...............665...............443................221

You go down your screen like this:

Account AcctValue
300............200
300............400
550............392
550............665
551............221
551............443
719............543
719............221

This way, you can create a GroupBy clause in your totals query, which will
average your values by Account.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L



"Diana" <(E-Mail Removed)> wrote in message
news:704F3410-0349-4293-BF0F-(E-Mail Removed)...
> Roger -
> Sorry, I didn't include the SQL because it was so long. I have already
> tried what you suggested but I get an overflow error. I think (but it's a
> guess) that I am getting the error because Access is trying to average the
> values for 52 separate fields for 9000 records. I want to select the
records
> where assets are > $399,999,999 before I average the values. I cannot
find
> any help on the error. Here is my SQL for the averages query. Sorry it
is
> so long. I am very new to Access and any help would be appreciated.
>
> Diana
>
> SELECT Avg(Fs220_Curr.ACCT_300) AS AvgOfACCT_300, Avg(Fs220_Curr.ACCT_550)
> AS AvgOfACCT_550, Avg(Fs220_Curr.ACCT_551) AS AvgOfACCT_551,
> Avg(Fs220_Curr.ACCT_719) AS AvgOfACCT_719, Avg(Fs220B_Curr.ACCT_731) AS
> AvgOfACCT_731, Avg(Fs220_Curr.ACCT_020B) AS AvgOfACCT_020B,
> Avg(Fs220_Curr.ACCT_021B) AS AvgOfACCT_021B, Avg(Fs220_Curr.ACCT_022B) AS
> AvgOfACCT_022B, Avg(Fs220_Curr.ACCT_023B) AS AvgOfACCT_023B,
> Avg(Fs220_Curr.ACCT_025B) AS AvgOfACCT_025B, Avg(Fs220_Curr.ACCT_041B) AS
> AvgOfACCT_041B, Avg(Fs220_Curr!ACCT_041B/Fs220_Curr!ACCT_025B) AS
> DQLoansToTotalLoans1a,
> Avg((Fs220_Curr!ACCT_550-Fs220_Curr!ACCT_551)/Fs220_Curr!ACCT_025B) AS
> NetChargeOffsTotalLoans, Avg(Fs220A_Curr.ACCT_396) AS AvgOfACCT_396,
> Avg(Fs220A_Curr.ACCT_397) AS AvgOfACCT_397, Avg(Fs220B_Curr.ACCT_971) AS
> AvgOfACCT_971,
> Avg((Fs220A_Curr!ACCT_396+Fs220A_Curr!ACCT_397)/Fs220_Curr!ACCT_025B) AS
> UnsecToTotal1b, Avg(Fs220B_Curr!Acct_682/Fs220_Curr!ACCT_025B) AS
> BkChgOffsToTotalLoans, Avg(Fs220A_Curr.ACCT_420) AS AvgOfACCT_420,
> Avg(Fs220B_Curr.ACCT_784) AS AvgOfACCT_784, Avg(Fs220B_Curr.ACCT_745E) AS
> AvgOfACCT_745E, Avg(Fs220B_Curr.ACCT_945) AS AvgOfACCT_945,
> Avg(Fs220_Curr.ACCT_799C1) AS AvgOfACCT_799C1, Avg(Fs220_Curr.ACCT_799C2)
AS
> AvgOfACCT_799C2, Avg(Fs220B_Curr.ACCT_799D) AS AvgOfACCT_799D,
> Avg(Fs220A_Curr.Acct_799I) AS AvgOfAcct_799I,
>
Avg(((Fs220_Curr!ACCT_799A*1)+(Fs220B_Curr!ACCT_799B*2)+(Fs220_Curr!ACCT_799
C1*4)+(Fs220_Curr!ACCT_799C2*7.5)+(Fs220B_Curr!ACCT_799D*10))/(Fs220_Curr!AC
CT_799A+Fs220B_Curr!ACCT_799B+Fs220_Curr!ACCT_799C1+Fs220_Curr!ACCT_799C2+Fs
220B_Curr!ACCT_799D))
> AS WeightedAvgMat1f, Avg(Fs220B_Curr!ACCT_801/Fs220B_Curr!ACCT_796E) AS
> MV_Book1c,
>
Avg((Fs220_Curr!ACCT_799C1+Fs220_Curr!ACCT_799C2+Fs220B_Curr!ACCT_799D)/Fs22
0A_Curr!ACCT_799I)
> AS LongMat_TotalInv1c, Avg(Fs220B_Curr!ACCT_745E/Fs220A_Curr!ACCT_799I) AS
> NonSFAS_TotalInv1c, Avg(Fs220B_Curr!ACCT_784/Fs220A_Curr!ACCT_799I) AS
> NonAuth_TotalInv1c, Avg(Fs220_Curr.ACCT_143A) AS AvgOfACCT_143A,
> Avg(Fs220A_Curr.ACCT_900A) AS AvgOfACCT_900A, Avg(Fs220A_Curr.ACCT_900B)
AS
> AvgOfACCT_900B, Avg(Fs220A_Curr.ACCT_900C) AS AvgOfACCT_900C,
> Avg(Fs220A_Curr.ACCT_900D) AS AvgOfACCT_900D, Avg(Fs220A_Curr.ACCT_961A)
AS
> AvgOfACCT_961A,
> Avg((Fs220A_Curr!ACCT_133-Fs220A_Curr!ACCT_135)/Fs220_Curr!ACCT_400A) AS
> NCOBus_TotalBus1d, Avg(Fs220_Curr!ACCT_400A/Fs220_Curr!ACCT_025B) AS
> BusLoans_TotalLoans, Avg(Fs220A_Curr.ACCT_618A) AS AvgOfACCT_618A,
> Avg(Fs220C_Curr!ACCT_619/Fs220_Curr!ACCT_025B) AS PartLoans_TotalLoans1e,
> Avg(Fs220C_Curr!ACCT_690/Fs220_Curr!ACCT_025B) AS PurPart_TotalLoans1e,
> Avg(Fs220C_Curr!ACCT_691/Fs220_Curr!ACCT_025B) AS SoldPart_TotalLoans1e,
> Avg(Fs220B_Curr!ACCT_615/Fs220_Curr!ACCT_025B) AS PurchLoans_TotalLoans1e,
> Avg(Fs220B_Curr.ACCT_812) AS AvgOfACCT_812, Avg(Fs220B_Curr.ACCT_815) AS
> AvgOfACCT_815, Avg(Fs220A_Curr.ACCT_881) AS AvgOfACCT_881,
> Avg(Fs220A_Curr.ACCT_882) AS AvgOfACCT_882,
> Avg(Fs220B_Curr!ACCT_811/Fs220_Curr!ACCT_708) AS UnFundHELOC_HELOC1f,
> Avg(Fs220B_Curr!ACCT_812/Fs220A_Curr!ACCT_396) AS UnFundVISA_VISA1f,
> Avg(Fs220B_Curr!ACCT_816/Fs220_Curr!ACCT_902) AS UnFundCP_Draft1f
> FROM (((BasicCUData_Curr INNER JOIN Fs220B_Curr ON
> BasicCUData_Curr.CU_NUMBER = Fs220B_Curr.CU_NUMBER) INNER JOIN Fs220_Curr
ON
> BasicCUData_Curr.CU_NUMBER = Fs220_Curr.CU_NUMBER) INNER JOIN Fs220A_Curr
ON
> BasicCUData_Curr.CU_NUMBER = Fs220A_Curr.CU_NUMBER) INNER JOIN Fs220C_Curr
ON
> (Fs220B_Curr.CU_NUMBER = Fs220C_Curr.CU_NUMBER) AND
> (BasicCUData_Curr.CU_NUMBER = Fs220C_Curr.CU_NUMBER)
> WHERE (((Fs220_Curr.ACCT_010)>399999999));
>
>
> "Roger Carlson" wrote:
>
> > Create your Totals query based on the original table and add a Where
clause.
> > In the query builder, add the field to a column, choose Where for the
> > Totals: Row, then put your criteria in the Criteria row.
> >
> > BTW, it is helpful when asking for help with a query to list the SQL.
> >
> > --
> > --Roger Carlson
> > MS Access MVP
> > Access Database Samples: www.rogersaccesslibrary.com
> > Want answers to your Access questions in your Email?
> > Free subscription:
> > http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
> >
> >
> > "Diana" <(E-Mail Removed)> wrote in message
> > news:43006F54-FF9E-43D3-96CA-(E-Mail Removed)...
> > > Hello!
> > > I am fairly new to Access and wondered if someone could help me with
some
> > > syntax.
> > >
> > > I would like to create a totals query to average the values from about
50
> > > fields in several tables, after selecting records where assets (the
value
> > in
> > > Fs220_Curr.ACCT_010) > $399,999,999. The original tables have about
9000
> > > records. I wrote a query to find the records where
Fs220_Curr.ACCT_010 >
> > > $399,999,999 and then a totals query from the results, but I would
like to
> > > combine them into one query, if possible. I think that a subquery
would
> > fit
> > > the bill, but I am not sure how to write it correctly.
> > >
> > > Any help would be greatly appreciated.
> > >
> > > Thanks!
> > >
> > > Diana
> >
> >
> >

Roger -

This does work as two queries. However, I want to do it the right way to
avoid problems down the road.

How difficult would it be to create the tables the way they should be?
There are 7 tables in text files that I get each quarter from the NCUA web
site. Could I open the files in Excel, transpose the rows and columns, save
as comma-delimited files and use these files to create the Access tables?

If you were assigned this project, what approach would you take? I need to
download the files from the web site each quarter (they are in a .zip file),
report on specific items for specific credit unions and, down the road, make
it so that any VP or the CEO can enter up to 6 CU #s and retrieve and print a
multi-column (side-by-side comparison) report for the selected CUs.

I would greatly appreciate any insight that you can give me. I have not
done very much Access programming and want to do it right. I got the Access
2003 Bible and Access 2003 Inside Out, but I would really like a different
type of book, one with real-world examples. Do you have any suggestions?

Thanks so much for your help.

Diana


"Roger Carlson" wrote:

> Well, there IS a limit on the complexity of an Access query and you may have
> reached it. Does it work as two queries? If so, you may have to be happy
> with that.
>
> However, the REAL reason for you problem is in your table design.
> Apparently, you have a column (field) for EACH account. While this is the
> correct way to design a spreadsheet, it is totally incorrect for a database
> table.
>
> Instead, you should have a field called Account in which you store the
> account number (300, 550, 551, etc.) and a second for storing the Value for
> that account. So instead of going across your screen like this:
>
> ACCT_300...ACCT_550...ACCT_551...ACCT_719
> 200...............392...............221................543
> 400...............665...............443................221
>
> You go down your screen like this:
>
> Account AcctValue
> 300............200
> 300............400
> 550............392
> 550............665
> 551............221
> 551............443
> 719............543
> 719............221
>
> This way, you can create a GroupBy clause in your totals query, which will
> average your values by Account.
>
> --
> --Roger Carlson
> MS Access MVP
> Access Database Samples: www.rogersaccesslibrary.com
> Want answers to your Access questions in your Email?
> Free subscription:
> http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
>
>
>
> "Diana" <(E-Mail Removed)> wrote in message
> news:704F3410-0349-4293-BF0F-(E-Mail Removed)...
> > Roger -
> > Sorry, I didn't include the SQL because it was so long. I have already
> > tried what you suggested but I get an overflow error. I think (but it's a
> > guess) that I am getting the error because Access is trying to average the
> > values for 52 separate fields for 9000 records. I want to select the
> records
> > where assets are > $399,999,999 before I average the values. I cannot
> find
> > any help on the error. Here is my SQL for the averages query. Sorry it
> is
> > so long. I am very new to Access and any help would be appreciated.
> >
> > Diana
> >
> > SELECT Avg(Fs220_Curr.ACCT_300) AS AvgOfACCT_300, Avg(Fs220_Curr.ACCT_550)
> > AS AvgOfACCT_550, Avg(Fs220_Curr.ACCT_551) AS AvgOfACCT_551,
> > Avg(Fs220_Curr.ACCT_719) AS AvgOfACCT_719, Avg(Fs220B_Curr.ACCT_731) AS
> > AvgOfACCT_731, Avg(Fs220_Curr.ACCT_020B) AS AvgOfACCT_020B,
> > Avg(Fs220_Curr.ACCT_021B) AS AvgOfACCT_021B, Avg(Fs220_Curr.ACCT_022B) AS
> > AvgOfACCT_022B, Avg(Fs220_Curr.ACCT_023B) AS AvgOfACCT_023B,
> > Avg(Fs220_Curr.ACCT_025B) AS AvgOfACCT_025B, Avg(Fs220_Curr.ACCT_041B) AS
> > AvgOfACCT_041B, Avg(Fs220_Curr!ACCT_041B/Fs220_Curr!ACCT_025B) AS
> > DQLoansToTotalLoans1a,
> > Avg((Fs220_Curr!ACCT_550-Fs220_Curr!ACCT_551)/Fs220_Curr!ACCT_025B) AS
> > NetChargeOffsTotalLoans, Avg(Fs220A_Curr.ACCT_396) AS AvgOfACCT_396,
> > Avg(Fs220A_Curr.ACCT_397) AS AvgOfACCT_397, Avg(Fs220B_Curr.ACCT_971) AS
> > AvgOfACCT_971,
> > Avg((Fs220A_Curr!ACCT_396+Fs220A_Curr!ACCT_397)/Fs220_Curr!ACCT_025B) AS
> > UnsecToTotal1b, Avg(Fs220B_Curr!Acct_682/Fs220_Curr!ACCT_025B) AS
> > BkChgOffsToTotalLoans, Avg(Fs220A_Curr.ACCT_420) AS AvgOfACCT_420,
> > Avg(Fs220B_Curr.ACCT_784) AS AvgOfACCT_784, Avg(Fs220B_Curr.ACCT_745E) AS
> > AvgOfACCT_745E, Avg(Fs220B_Curr.ACCT_945) AS AvgOfACCT_945,
> > Avg(Fs220_Curr.ACCT_799C1) AS AvgOfACCT_799C1, Avg(Fs220_Curr.ACCT_799C2)
> AS
> > AvgOfACCT_799C2, Avg(Fs220B_Curr.ACCT_799D) AS AvgOfACCT_799D,
> > Avg(Fs220A_Curr.Acct_799I) AS AvgOfAcct_799I,
> >
> Avg(((Fs220_Curr!ACCT_799A*1)+(Fs220B_Curr!ACCT_799B*2)+(Fs220_Curr!ACCT_799
> C1*4)+(Fs220_Curr!ACCT_799C2*7.5)+(Fs220B_Curr!ACCT_799D*10))/(Fs220_Curr!AC
> CT_799A+Fs220B_Curr!ACCT_799B+Fs220_Curr!ACCT_799C1+Fs220_Curr!ACCT_799C2+Fs
> 220B_Curr!ACCT_799D))
> > AS WeightedAvgMat1f, Avg(Fs220B_Curr!ACCT_801/Fs220B_Curr!ACCT_796E) AS
> > MV_Book1c,
> >
> Avg((Fs220_Curr!ACCT_799C1+Fs220_Curr!ACCT_799C2+Fs220B_Curr!ACCT_799D)/Fs22
> 0A_Curr!ACCT_799I)
> > AS LongMat_TotalInv1c, Avg(Fs220B_Curr!ACCT_745E/Fs220A_Curr!ACCT_799I) AS
> > NonSFAS_TotalInv1c, Avg(Fs220B_Curr!ACCT_784/Fs220A_Curr!ACCT_799I) AS
> > NonAuth_TotalInv1c, Avg(Fs220_Curr.ACCT_143A) AS AvgOfACCT_143A,
> > Avg(Fs220A_Curr.ACCT_900A) AS AvgOfACCT_900A, Avg(Fs220A_Curr.ACCT_900B)
> AS
> > AvgOfACCT_900B, Avg(Fs220A_Curr.ACCT_900C) AS AvgOfACCT_900C,
> > Avg(Fs220A_Curr.ACCT_900D) AS AvgOfACCT_900D, Avg(Fs220A_Curr.ACCT_961A)
> AS
> > AvgOfACCT_961A,
> > Avg((Fs220A_Curr!ACCT_133-Fs220A_Curr!ACCT_135)/Fs220_Curr!ACCT_400A) AS
> > NCOBus_TotalBus1d, Avg(Fs220_Curr!ACCT_400A/Fs220_Curr!ACCT_025B) AS
> > BusLoans_TotalLoans, Avg(Fs220A_Curr.ACCT_618A) AS AvgOfACCT_618A,
> > Avg(Fs220C_Curr!ACCT_619/Fs220_Curr!ACCT_025B) AS PartLoans_TotalLoans1e,
> > Avg(Fs220C_Curr!ACCT_690/Fs220_Curr!ACCT_025B) AS PurPart_TotalLoans1e,
> > Avg(Fs220C_Curr!ACCT_691/Fs220_Curr!ACCT_025B) AS SoldPart_TotalLoans1e,
> > Avg(Fs220B_Curr!ACCT_615/Fs220_Curr!ACCT_025B) AS PurchLoans_TotalLoans1e,
> > Avg(Fs220B_Curr.ACCT_812) AS AvgOfACCT_812, Avg(Fs220B_Curr.ACCT_815) AS
> > AvgOfACCT_815, Avg(Fs220A_Curr.ACCT_881) AS AvgOfACCT_881,
> > Avg(Fs220A_Curr.ACCT_882) AS AvgOfACCT_882,
> > Avg(Fs220B_Curr!ACCT_811/Fs220_Curr!ACCT_708) AS UnFundHELOC_HELOC1f,
> > Avg(Fs220B_Curr!ACCT_812/Fs220A_Curr!ACCT_396) AS UnFundVISA_VISA1f,
> > Avg(Fs220B_Curr!ACCT_816/Fs220_Curr!ACCT_902) AS UnFundCP_Draft1f
> > FROM (((BasicCUData_Curr INNER JOIN Fs220B_Curr ON
> > BasicCUData_Curr.CU_NUMBER = Fs220B_Curr.CU_NUMBER) INNER JOIN Fs220_Curr
> ON
> > BasicCUData_Curr.CU_NUMBER = Fs220_Curr.CU_NUMBER) INNER JOIN Fs220A_Curr
> ON
> > BasicCUData_Curr.CU_NUMBER = Fs220A_Curr.CU_NUMBER) INNER JOIN Fs220C_Curr
> ON
> > (Fs220B_Curr.CU_NUMBER = Fs220C_Curr.CU_NUMBER) AND
> > (BasicCUData_Curr.CU_NUMBER = Fs220C_Curr.CU_NUMBER)
> > WHERE (((Fs220_Curr.ACCT_010)>399999999));
> >
> >
> > "Roger Carlson" wrote:
> >
> > > Create your Totals query based on the original table and add a Where
> clause.
> > > In the query builder, add the field to a column, choose Where for the
> > > Totals: Row, then put your criteria in the Criteria row.
> > >
> > > BTW, it is helpful when asking for help with a query to list the SQL.
> > >
> > > --
> > > --Roger Carlson
> > > MS Access MVP
> > > Access Database Samples: www.rogersaccesslibrary.com
> > > Want answers to your Access questions in your Email?
> > > Free subscription:
> > > http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
> > >
> > >
> > > "Diana" <(E-Mail Removed)> wrote in message
> > > news:43006F54-FF9E-43D3-96CA-(E-Mail Removed)...
> > > > Hello!
> > > > I am fairly new to Access and wondered if someone could help me with
> some
> > > > syntax.
> > > >
> > > > I would like to create a totals query to average the values from about
> 50
> > > > fields in several tables, after selecting records where assets (the
> value
> > > in
> > > > Fs220_Curr.ACCT_010) > $399,999,999. The original tables have about
> 9000
> > > > records. I wrote a query to find the records where
> Fs220_Curr.ACCT_010 >
> > > > $399,999,999 and then a totals query from the results, but I would
> like to
> > > > combine them into one query, if possible. I think that a subquery
> would
> > > fit
> > > > the bill, but I am not sure how to write it correctly.
> > > >
> > > > Any help would be greatly appreciated.
> > > >
> > > > Thanks!
> > > >
> > > > Diana
> > >
> > >
> > >
>
>
>

Diana,

If I were assigned the project, I would import (or possibly link) the Excel
files into the database and then transform them (through VBA) into properly
normalized tables. The precise code would be specific to each table.

I do this quite frequently with data that is scanned from a form. Forms
rarely lend themselve to a normalized table structure, so the easiest thing
to scan them into a denormalized table, then transform them into a
normalized table.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "NormalizeDenormalize.mdb" which illustrates how to do this
with one particular kind of denormalized file. Yours would be different,
but the idea is the same: Open a recordset of your denormalized table,
march throw each value across the record and write it to a normalized table.
Then move to the next record and repeat.

I would recommend two books:
"Database Design for Mere Mortals" by By Michael J. Hernandez
"Access Cookbook" by Ken Getz, Paul Litwin, and Andy Baron

"Mere Mortals" is a great book for understanding relational database design.
This is the most important step to a database project. If you follow his
process, you can model almost any system into a database. After reading the
book, I have a number of tutorials on my website:
http://www.rogersaccesslibrary.com/Tutorials.html in database design that
use his process.

The "Cookbook" book is built around how-to's. Each section shows you how to
do a particular thing.

You also might want to look through the other samples on my website. Each
sample shows one or more solutions to a single programming problem. Similar
to the Cookbook.

Sorry for the delay in responding. Hope this helps.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L







"Diana" <(E-Mail Removed)> wrote in message
news:1B27F5C2-D82B-458E-B316-(E-Mail Removed)...
> Roger -
>
> This does work as two queries. However, I want to do it the right way to
> avoid problems down the road.
>
> How difficult would it be to create the tables the way they should be?
> There are 7 tables in text files that I get each quarter from the NCUA web
> site. Could I open the files in Excel, transpose the rows and columns,
save
> as comma-delimited files and use these files to create the Access tables?
>
> If you were assigned this project, what approach would you take? I need
to
> download the files from the web site each quarter (they are in a .zip
file),
> report on specific items for specific credit unions and, down the road,
make
> it so that any VP or the CEO can enter up to 6 CU #s and retrieve and
print a
> multi-column (side-by-side comparison) report for the selected CUs.
>
> I would greatly appreciate any insight that you can give me. I have not
> done very much Access programming and want to do it right. I got the
Access
> 2003 Bible and Access 2003 Inside Out, but I would really like a different
> type of book, one with real-world examples. Do you have any suggestions?
>
> Thanks so much for your help.
>
> Diana
>
>
> "Roger Carlson" wrote:
>
> > Well, there IS a limit on the complexity of an Access query and you may
have
> > reached it. Does it work as two queries? If so, you may have to be
happy
> > with that.
> >
> > However, the REAL reason for you problem is in your table design.
> > Apparently, you have a column (field) for EACH account. While this is
the
> > correct way to design a spreadsheet, it is totally incorrect for a
database
> > table.
> >
> > Instead, you should have a field called Account in which you store the
> > account number (300, 550, 551, etc.) and a second for storing the Value
for
> > that account. So instead of going across your screen like this:
> >
> > ACCT_300...ACCT_550...ACCT_551...ACCT_719
> > 200...............392...............221................543
> > 400...............665...............443................221
> >
> > You go down your screen like this:
> >
> > Account AcctValue
> > 300............200
> > 300............400
> > 550............392
> > 550............665
> > 551............221
> > 551............443
> > 719............543
> > 719............221
> >
> > This way, you can create a GroupBy clause in your totals query, which
will
> > average your values by Account.
> >
> > --
> > --Roger Carlson
> > MS Access MVP
> > Access Database Samples: www.rogersaccesslibrary.com
> > Want answers to your Access questions in your Email?
> > Free subscription:
> > http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
> >
> >
> >
> > "Diana" <(E-Mail Removed)> wrote in message
> > news:704F3410-0349-4293-BF0F-(E-Mail Removed)...
> > > Roger -
> > > Sorry, I didn't include the SQL because it was so long. I have
already
> > > tried what you suggested but I get an overflow error. I think (but
it's a
> > > guess) that I am getting the error because Access is trying to average
the
> > > values for 52 separate fields for 9000 records. I want to select the
> > records
> > > where assets are > $399,999,999 before I average the values. I cannot
> > find
> > > any help on the error. Here is my SQL for the averages query. Sorry
it
> > is
> > > so long. I am very new to Access and any help would be appreciated.
> > >
> > > Diana
> > >
> > > SELECT Avg(Fs220_Curr.ACCT_300) AS AvgOfACCT_300,
Avg(Fs220_Curr.ACCT_550)
> > > AS AvgOfACCT_550, Avg(Fs220_Curr.ACCT_551) AS AvgOfACCT_551,
> > > Avg(Fs220_Curr.ACCT_719) AS AvgOfACCT_719, Avg(Fs220B_Curr.ACCT_731)
AS
> > > AvgOfACCT_731, Avg(Fs220_Curr.ACCT_020B) AS AvgOfACCT_020B,
> > > Avg(Fs220_Curr.ACCT_021B) AS AvgOfACCT_021B, Avg(Fs220_Curr.ACCT_022B)
AS
> > > AvgOfACCT_022B, Avg(Fs220_Curr.ACCT_023B) AS AvgOfACCT_023B,
> > > Avg(Fs220_Curr.ACCT_025B) AS AvgOfACCT_025B, Avg(Fs220_Curr.ACCT_041B)
AS
> > > AvgOfACCT_041B, Avg(Fs220_Curr!ACCT_041B/Fs220_Curr!ACCT_025B) AS
> > > DQLoansToTotalLoans1a,
> > > Avg((Fs220_Curr!ACCT_550-Fs220_Curr!ACCT_551)/Fs220_Curr!ACCT_025B) AS
> > > NetChargeOffsTotalLoans, Avg(Fs220A_Curr.ACCT_396) AS AvgOfACCT_396,
> > > Avg(Fs220A_Curr.ACCT_397) AS AvgOfACCT_397, Avg(Fs220B_Curr.ACCT_971)
AS
> > > AvgOfACCT_971,
> > > Avg((Fs220A_Curr!ACCT_396+Fs220A_Curr!ACCT_397)/Fs220_Curr!ACCT_025B)
AS
> > > UnsecToTotal1b, Avg(Fs220B_Curr!Acct_682/Fs220_Curr!ACCT_025B) AS
> > > BkChgOffsToTotalLoans, Avg(Fs220A_Curr.ACCT_420) AS AvgOfACCT_420,
> > > Avg(Fs220B_Curr.ACCT_784) AS AvgOfACCT_784, Avg(Fs220B_Curr.ACCT_745E)
AS
> > > AvgOfACCT_745E, Avg(Fs220B_Curr.ACCT_945) AS AvgOfACCT_945,
> > > Avg(Fs220_Curr.ACCT_799C1) AS AvgOfACCT_799C1,
Avg(Fs220_Curr.ACCT_799C2)
> > AS
> > > AvgOfACCT_799C2, Avg(Fs220B_Curr.ACCT_799D) AS AvgOfACCT_799D,
> > > Avg(Fs220A_Curr.Acct_799I) AS AvgOfAcct_799I,
> > >
> >
Avg(((Fs220_Curr!ACCT_799A*1)+(Fs220B_Curr!ACCT_799B*2)+(Fs220_Curr!ACCT_799
> >
C1*4)+(Fs220_Curr!ACCT_799C2*7.5)+(Fs220B_Curr!ACCT_799D*10))/(Fs220_Curr!AC
> >
CT_799A+Fs220B_Curr!ACCT_799B+Fs220_Curr!ACCT_799C1+Fs220_Curr!ACCT_799C2+Fs
> > 220B_Curr!ACCT_799D))
> > > AS WeightedAvgMat1f, Avg(Fs220B_Curr!ACCT_801/Fs220B_Curr!ACCT_796E)
AS
> > > MV_Book1c,
> > >
> >
Avg((Fs220_Curr!ACCT_799C1+Fs220_Curr!ACCT_799C2+Fs220B_Curr!ACCT_799D)/Fs22
> > 0A_Curr!ACCT_799I)
> > > AS LongMat_TotalInv1c,
Avg(Fs220B_Curr!ACCT_745E/Fs220A_Curr!ACCT_799I) AS
> > > NonSFAS_TotalInv1c, Avg(Fs220B_Curr!ACCT_784/Fs220A_Curr!ACCT_799I) AS
> > > NonAuth_TotalInv1c, Avg(Fs220_Curr.ACCT_143A) AS AvgOfACCT_143A,
> > > Avg(Fs220A_Curr.ACCT_900A) AS AvgOfACCT_900A,
Avg(Fs220A_Curr.ACCT_900B)
> > AS
> > > AvgOfACCT_900B, Avg(Fs220A_Curr.ACCT_900C) AS AvgOfACCT_900C,
> > > Avg(Fs220A_Curr.ACCT_900D) AS AvgOfACCT_900D,
Avg(Fs220A_Curr.ACCT_961A)
> > AS
> > > AvgOfACCT_961A,
> > > Avg((Fs220A_Curr!ACCT_133-Fs220A_Curr!ACCT_135)/Fs220_Curr!ACCT_400A)
AS
> > > NCOBus_TotalBus1d, Avg(Fs220_Curr!ACCT_400A/Fs220_Curr!ACCT_025B) AS
> > > BusLoans_TotalLoans, Avg(Fs220A_Curr.ACCT_618A) AS AvgOfACCT_618A,
> > > Avg(Fs220C_Curr!ACCT_619/Fs220_Curr!ACCT_025B) AS
PartLoans_TotalLoans1e,
> > > Avg(Fs220C_Curr!ACCT_690/Fs220_Curr!ACCT_025B) AS
PurPart_TotalLoans1e,
> > > Avg(Fs220C_Curr!ACCT_691/Fs220_Curr!ACCT_025B) AS
SoldPart_TotalLoans1e,
> > > Avg(Fs220B_Curr!ACCT_615/Fs220_Curr!ACCT_025B) AS
PurchLoans_TotalLoans1e,
> > > Avg(Fs220B_Curr.ACCT_812) AS AvgOfACCT_812, Avg(Fs220B_Curr.ACCT_815)
AS
> > > AvgOfACCT_815, Avg(Fs220A_Curr.ACCT_881) AS AvgOfACCT_881,
> > > Avg(Fs220A_Curr.ACCT_882) AS AvgOfACCT_882,
> > > Avg(Fs220B_Curr!ACCT_811/Fs220_Curr!ACCT_708) AS UnFundHELOC_HELOC1f,
> > > Avg(Fs220B_Curr!ACCT_812/Fs220A_Curr!ACCT_396) AS UnFundVISA_VISA1f,
> > > Avg(Fs220B_Curr!ACCT_816/Fs220_Curr!ACCT_902) AS UnFundCP_Draft1f
> > > FROM (((BasicCUData_Curr INNER JOIN Fs220B_Curr ON
> > > BasicCUData_Curr.CU_NUMBER = Fs220B_Curr.CU_NUMBER) INNER JOIN
Fs220_Curr
> > ON
> > > BasicCUData_Curr.CU_NUMBER = Fs220_Curr.CU_NUMBER) INNER JOIN
Fs220A_Curr
> > ON
> > > BasicCUData_Curr.CU_NUMBER = Fs220A_Curr.CU_NUMBER) INNER JOIN
Fs220C_Curr
> > ON
> > > (Fs220B_Curr.CU_NUMBER = Fs220C_Curr.CU_NUMBER) AND
> > > (BasicCUData_Curr.CU_NUMBER = Fs220C_Curr.CU_NUMBER)
> > > WHERE (((Fs220_Curr.ACCT_010)>399999999));
> > >
> > >
> > > "Roger Carlson" wrote:
> > >
> > > > Create your Totals query based on the original table and add a Where
> > clause.
> > > > In the query builder, add the field to a column, choose Where for
the
> > > > Totals: Row, then put your criteria in the Criteria row.
> > > >
> > > > BTW, it is helpful when asking for help with a query to list the
SQL.
> > > >
> > > > --
> > > > --Roger Carlson
> > > > MS Access MVP
> > > > Access Database Samples: www.rogersaccesslibrary.com
> > > > Want answers to your Access questions in your Email?
> > > > Free subscription:
> > > > http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
> > > >
> > > >
> > > > "Diana" <(E-Mail Removed)> wrote in message
> > > > news:43006F54-FF9E-43D3-96CA-(E-Mail Removed)...
> > > > > Hello!
> > > > > I am fairly new to Access and wondered if someone could help me
with
> > some
> > > > > syntax.
> > > > >
> > > > > I would like to create a totals query to average the values from
about
> > 50
> > > > > fields in several tables, after selecting records where assets
(the
> > value
> > > > in
> > > > > Fs220_Curr.ACCT_010) > $399,999,999. The original tables have
about
> > 9000
> > > > > records. I wrote a query to find the records where
> > Fs220_Curr.ACCT_010 >
> > > > > $399,999,999 and then a totals query from the results, but I would
> > like to
> > > > > combine them into one query, if possible. I think that a subquery
> > would
> > > > fit
> > > > > the bill, but I am not sure how to write it correctly.
> > > > >
> > > > > Any help would be greatly appreciated.
> > > > >
> > > > > Thanks!
> > > > >
> > > > > Diana
> > > >
> > > >
> > > >
> >
> >
> >

Roger -

Thank you for all of the good ideas. I will definitely check out your site.
Actually, I just heard about The Access Cookbook the other day.

You've been a big help. Thanks a lot!

Diana


"Roger Carlson" wrote:

> Diana,
>
> If I were assigned the project, I would import (or possibly link) the Excel
> files into the database and then transform them (through VBA) into properly
> normalized tables. The precise code would be specific to each table.
>
> I do this quite frequently with data that is scanned from a form. Forms
> rarely lend themselve to a normalized table structure, so the easiest thing
> to scan them into a denormalized table, then transform them into a
> normalized table.
>
> On my website (www.rogersaccesslibrary.com), is a small Access database
> sample called "NormalizeDenormalize.mdb" which illustrates how to do this
> with one particular kind of denormalized file. Yours would be different,
> but the idea is the same: Open a recordset of your denormalized table,
> march throw each value across the record and write it to a normalized table.
> Then move to the next record and repeat.
>
> I would recommend two books:
> "Database Design for Mere Mortals" by By Michael J. Hernandez
> "Access Cookbook" by Ken Getz, Paul Litwin, and Andy Baron
>
> "Mere Mortals" is a great book for understanding relational database design.
> This is the most important step to a database project. If you follow his
> process, you can model almost any system into a database. After reading the
> book, I have a number of tutorials on my website:
> http://www.rogersaccesslibrary.com/Tutorials.html in database design that
> use his process.
>
> The "Cookbook" book is built around how-to's. Each section shows you how to
> do a particular thing.
>
> You also might want to look through the other samples on my website. Each
> sample shows one or more solutions to a single programming problem. Similar
> to the Cookbook.
>
> Sorry for the delay in responding. Hope this helps.
>
> --
> --Roger Carlson
> MS Access MVP
> Access Database Samples: www.rogersaccesslibrary.com
> Want answers to your Access questions in your Email?
> Free subscription:
> http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
>
>
>
>
>
>
>
> "Diana" <(E-Mail Removed)> wrote in message
> news:1B27F5C2-D82B-458E-B316-(E-Mail Removed)...
> > Roger -
> >
> > This does work as two queries. However, I want to do it the right way to
> > avoid problems down the road.
> >
> > How difficult would it be to create the tables the way they should be?
> > There are 7 tables in text files that I get each quarter from the NCUA web
> > site. Could I open the files in Excel, transpose the rows and columns,
> save
> > as comma-delimited files and use these files to create the Access tables?
> >
> > If you were assigned this project, what approach would you take? I need
> to
> > download the files from the web site each quarter (they are in a .zip
> file),
> > report on specific items for specific credit unions and, down the road,
> make
> > it so that any VP or the CEO can enter up to 6 CU #s and retrieve and
> print a
> > multi-column (side-by-side comparison) report for the selected CUs.
> >
> > I would greatly appreciate any insight that you can give me. I have not
> > done very much Access programming and want to do it right. I got the
> Access
> > 2003 Bible and Access 2003 Inside Out, but I would really like a different
> > type of book, one with real-world examples. Do you have any suggestions?
> >
> > Thanks so much for your help.
> >
> > Diana
> >
> >
> > "Roger Carlson" wrote:
> >
> > > Well, there IS a limit on the complexity of an Access query and you may
> have
> > > reached it. Does it work as two queries? If so, you may have to be
> happy
> > > with that.
> > >
> > > However, the REAL reason for you problem is in your table design.
> > > Apparently, you have a column (field) for EACH account. While this is
> the
> > > correct way to design a spreadsheet, it is totally incorrect for a
> database
> > > table.
> > >
> > > Instead, you should have a field called Account in which you store the
> > > account number (300, 550, 551, etc.) and a second for storing the Value
> for
> > > that account. So instead of going across your screen like this:
> > >
> > > ACCT_300...ACCT_550...ACCT_551...ACCT_719
> > > 200...............392...............221................543
> > > 400...............665...............443................221
> > >
> > > You go down your screen like this:
> > >
> > > Account AcctValue
> > > 300............200
> > > 300............400
> > > 550............392
> > > 550............665
> > > 551............221
> > > 551............443
> > > 719............543
> > > 719............221
> > >
> > > This way, you can create a GroupBy clause in your totals query, which
> will
> > > average your values by Account.
> > >
> > > --
> > > --Roger Carlson
> > > MS Access MVP
> > > Access Database Samples: www.rogersaccesslibrary.com
> > > Want answers to your Access questions in your Email?
> > > Free subscription:
> > > http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
> > >
> > >
> > >
> > > "Diana" <(E-Mail Removed)> wrote in message
> > > news:704F3410-0349-4293-BF0F-(E-Mail Removed)...
> > > > Roger -
> > > > Sorry, I didn't include the SQL because it was so long. I have
> already
> > > > tried what you suggested but I get an overflow error. I think (but
> it's a
> > > > guess) that I am getting the error because Access is trying to average
> the
> > > > values for 52 separate fields for 9000 records. I want to select the
> > > records
> > > > where assets are > $399,999,999 before I average the values. I cannot
> > > find
> > > > any help on the error. Here is my SQL for the averages query. Sorry
> it
> > > is
> > > > so long. I am very new to Access and any help would be appreciated.
> > > >
> > > > Diana
> > > >
> > > > SELECT Avg(Fs220_Curr.ACCT_300) AS AvgOfACCT_300,
> Avg(Fs220_Curr.ACCT_550)
> > > > AS AvgOfACCT_550, Avg(Fs220_Curr.ACCT_551) AS AvgOfACCT_551,
> > > > Avg(Fs220_Curr.ACCT_719) AS AvgOfACCT_719, Avg(Fs220B_Curr.ACCT_731)
> AS
> > > > AvgOfACCT_731, Avg(Fs220_Curr.ACCT_020B) AS AvgOfACCT_020B,
> > > > Avg(Fs220_Curr.ACCT_021B) AS AvgOfACCT_021B, Avg(Fs220_Curr.ACCT_022B)
> AS
> > > > AvgOfACCT_022B, Avg(Fs220_Curr.ACCT_023B) AS AvgOfACCT_023B,
> > > > Avg(Fs220_Curr.ACCT_025B) AS AvgOfACCT_025B, Avg(Fs220_Curr.ACCT_041B)
> AS
> > > > AvgOfACCT_041B, Avg(Fs220_Curr!ACCT_041B/Fs220_Curr!ACCT_025B) AS
> > > > DQLoansToTotalLoans1a,
> > > > Avg((Fs220_Curr!ACCT_550-Fs220_Curr!ACCT_551)/Fs220_Curr!ACCT_025B) AS
> > > > NetChargeOffsTotalLoans, Avg(Fs220A_Curr.ACCT_396) AS AvgOfACCT_396,
> > > > Avg(Fs220A_Curr.ACCT_397) AS AvgOfACCT_397, Avg(Fs220B_Curr.ACCT_971)
> AS
> > > > AvgOfACCT_971,
> > > > Avg((Fs220A_Curr!ACCT_396+Fs220A_Curr!ACCT_397)/Fs220_Curr!ACCT_025B)
> AS
> > > > UnsecToTotal1b, Avg(Fs220B_Curr!Acct_682/Fs220_Curr!ACCT_025B) AS
> > > > BkChgOffsToTotalLoans, Avg(Fs220A_Curr.ACCT_420) AS AvgOfACCT_420,
> > > > Avg(Fs220B_Curr.ACCT_784) AS AvgOfACCT_784, Avg(Fs220B_Curr.ACCT_745E)
> AS
> > > > AvgOfACCT_745E, Avg(Fs220B_Curr.ACCT_945) AS AvgOfACCT_945,
> > > > Avg(Fs220_Curr.ACCT_799C1) AS AvgOfACCT_799C1,
> Avg(Fs220_Curr.ACCT_799C2)
> > > AS
> > > > AvgOfACCT_799C2, Avg(Fs220B_Curr.ACCT_799D) AS AvgOfACCT_799D,
> > > > Avg(Fs220A_Curr.Acct_799I) AS AvgOfAcct_799I,
> > > >
> > >
> Avg(((Fs220_Curr!ACCT_799A*1)+(Fs220B_Curr!ACCT_799B*2)+(Fs220_Curr!ACCT_799
> > >
> C1*4)+(Fs220_Curr!ACCT_799C2*7.5)+(Fs220B_Curr!ACCT_799D*10))/(Fs220_Curr!AC
> > >
> CT_799A+Fs220B_Curr!ACCT_799B+Fs220_Curr!ACCT_799C1+Fs220_Curr!ACCT_799C2+Fs
> > > 220B_Curr!ACCT_799D))
> > > > AS WeightedAvgMat1f, Avg(Fs220B_Curr!ACCT_801/Fs220B_Curr!ACCT_796E)
> AS
> > > > MV_Book1c,
> > > >
> > >
> Avg((Fs220_Curr!ACCT_799C1+Fs220_Curr!ACCT_799C2+Fs220B_Curr!ACCT_799D)/Fs22
> > > 0A_Curr!ACCT_799I)
> > > > AS LongMat_TotalInv1c,
> Avg(Fs220B_Curr!ACCT_745E/Fs220A_Curr!ACCT_799I) AS
> > > > NonSFAS_TotalInv1c, Avg(Fs220B_Curr!ACCT_784/Fs220A_Curr!ACCT_799I) AS
> > > > NonAuth_TotalInv1c, Avg(Fs220_Curr.ACCT_143A) AS AvgOfACCT_143A,
> > > > Avg(Fs220A_Curr.ACCT_900A) AS AvgOfACCT_900A,
> Avg(Fs220A_Curr.ACCT_900B)
> > > AS
> > > > AvgOfACCT_900B, Avg(Fs220A_Curr.ACCT_900C) AS AvgOfACCT_900C,
> > > > Avg(Fs220A_Curr.ACCT_900D) AS AvgOfACCT_900D,
> Avg(Fs220A_Curr.ACCT_961A)
> > > AS
> > > > AvgOfACCT_961A,
> > > > Avg((Fs220A_Curr!ACCT_133-Fs220A_Curr!ACCT_135)/Fs220_Curr!ACCT_400A)
> AS
> > > > NCOBus_TotalBus1d, Avg(Fs220_Curr!ACCT_400A/Fs220_Curr!ACCT_025B) AS
> > > > BusLoans_TotalLoans, Avg(Fs220A_Curr.ACCT_618A) AS AvgOfACCT_618A,
> > > > Avg(Fs220C_Curr!ACCT_619/Fs220_Curr!ACCT_025B) AS
> PartLoans_TotalLoans1e,
> > > > Avg(Fs220C_Curr!ACCT_690/Fs220_Curr!ACCT_025B) AS
> PurPart_TotalLoans1e,
> > > > Avg(Fs220C_Curr!ACCT_691/Fs220_Curr!ACCT_025B) AS
> SoldPart_TotalLoans1e,
> > > > Avg(Fs220B_Curr!ACCT_615/Fs220_Curr!ACCT_025B) AS
> PurchLoans_TotalLoans1e,
> > > > Avg(Fs220B_Curr.ACCT_812) AS AvgOfACCT_812, Avg(Fs220B_Curr.ACCT_815)
> AS
> > > > AvgOfACCT_815, Avg(Fs220A_Curr.ACCT_881) AS AvgOfACCT_881,
> > > > Avg(Fs220A_Curr.ACCT_882) AS AvgOfACCT_882,
> > > > Avg(Fs220B_Curr!ACCT_811/Fs220_Curr!ACCT_708) AS UnFundHELOC_HELOC1f,
> > > > Avg(Fs220B_Curr!ACCT_812/Fs220A_Curr!ACCT_396) AS UnFundVISA_VISA1f,
> > > > Avg(Fs220B_Curr!ACCT_816/Fs220_Curr!ACCT_902) AS UnFundCP_Draft1f
> > > > FROM (((BasicCUData_Curr INNER JOIN Fs220B_Curr ON
> > > > BasicCUData_Curr.CU_NUMBER = Fs220B_Curr.CU_NUMBER) INNER JOIN
> Fs220_Curr
> > > ON
> > > > BasicCUData_Curr.CU_NUMBER = Fs220_Curr.CU_NUMBER) INNER JOIN
> Fs220A_Curr
> > > ON
> > > > BasicCUData_Curr.CU_NUMBER = Fs220A_Curr.CU_NUMBER) INNER JOIN
> Fs220C_Curr
> > > ON
> > > > (Fs220B_Curr.CU_NUMBER = Fs220C_Curr.CU_NUMBER) AND
> > > > (BasicCUData_Curr.CU_NUMBER = Fs220C_Curr.CU_NUMBER)
> > > > WHERE (((Fs220_Curr.ACCT_010)>399999999));
> > > >
> > > >
> > > > "Roger Carlson" wrote:
> > > >
> > > > > Create your Totals query based on the original table and add a Where
> > > clause.
> > > > > In the query builder, add the field to a column, choose Where for
> the
> > > > > Totals: Row, then put your criteria in the Criteria row.
> > > > >
> > > > > BTW, it is helpful when asking for help with a query to list the
> SQL.
> > > > >
> > > > > --
> > > > > --Roger Carlson
> > > > > MS Access MVP
> > > > > Access Database Samples: www.rogersaccesslibrary.com
> > > > > Want answers to your Access questions in your Email?
> > > > > Free subscription:
> > > > > http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L
> > > > >
> > > > >
> > > > > "Diana" <(E-Mail Removed)> wrote in message
> > > > > news:43006F54-FF9E-43D3-96CA-(E-Mail Removed)...
> > > > > > Hello!
> > > > > > I am fairly new to Access and wondered if someone could help me
> with
> > > some
> > > > > > syntax.
> > > > > >
> > > > > > I would like to create a totals query to average the values from
> about
> > > 50
> > > > > > fields in several tables, after selecting records where assets
> (the
> > > value
> > > > > in
> > > > > > Fs220_Curr.ACCT_010) > $399,999,999. The original tables have
> about
> > > 9000
> > > > > > records. I wrote a query to find the records where
> > > Fs220_Curr.ACCT_010 >
> > > > > > $399,999,999 and then a totals query from the results, but I would
> > > like to
> > > > > > combine them into one query, if possible. I think that a subquery
> > > would
> > > > > fit
> > > > > > the bill, but I am not sure how to write it correctly.
> > > > > >
> > > > > > Any help would be greatly appreciated.
> > > > > >
> > > > > > Thanks!
> > > > > >
> > > > > > Diana
> > > > >

Thanks, Roger. I knew the concept of how to denormalize, but I never learned
Access2003's object structure (e.g., tabledefs, recordset syntax). I
downloaded your sample and was able to denormalize my client's table in about
10 minutes. You're a good man .

"Roger Carlson" wrote:

> On my website (www.rogersaccesslibrary.com), is a small Access database
> sample called "NormalizeDenormalize.mdb" which illustrates how to do this
> with one particular kind of denormalized file.