Access 2003 subquery

[Guest]

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 Carlson]

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/wa.exe?SUBED1=ACCESS-L

 

[Guest]

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]

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/wa.exe?SUBED1=ACCESS-L

 

[Guest]

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]

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/wa.exe?SUBED1=ACCESS-L

 

[Guest]

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

 

[Guest]

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 .