I am looking at my contacts database.
If you've been following my Learn Access lessons, you'll recognize some of the tables on the Relationships Diagram.
This is where the lessons are headed.
Hi, this is Crystal.
Recently, I helped someone with an database where
queries were nested several levels deep
and it was hard to figure out where the data was coming from to add new fields to reports.
Do you have calculated fields in queries of your database?
Do you want to see where they are? what they are?
Make a New Query
Switch to SQL view
Paste this SQL statement
Switch to Datasheet View to see the results.
You see the calculated field names,
which query they are in, and the equation.
You'll notice that I am not using the ribbon to do things.
I have customized by QAT, or the Quick Access Toolbar with icons I use a lot.
If you are interested in learning more about customizing the QAT,
there are videos on my channel you can watch to learn how.
When I look at the results I generally best-fit all the columns.
This database only has 41 calculated fields.
Some databases have hundreds.
Excel, which I love almost as much as Access,
makes a great reporting tool for Access.
And Excel workbooks are easy to share.
Here is my list of downloads on RogersAccessLibrary.
Document Calculated Fields in Queries was posted in May 2014.
It shows the SQL you need and also has a download link to generate an Excel workbook.
The download link is a ZIP file that contains a BAS file, which is a text file
If you open it with a text editor, you can see the code.
BAS files have an Attribute statement at the top with the name of the module.
I copy the path,
go back to Access,
press Alt-F11 to switch to the Visual Basic Editor,
Click on the File menu
Choose Import File...
Paste the path and press ENTER. Now you will see a list of BAS files in that directory.
Choose the one you want, then Debug, Compile your database, and Save.
Click in the only procedure this module contains, DocumentQueryCalculatedFields, and press F5 to run.
Alternately, you can choose Run from the Run menu.
Excel opens and you see the results being written and formatted.
When done, the file name will be in your CurrentProject.Path folder.
If you'd like to learn how to do this yourself, look at the VBA code;
It shows you how to write to Excel from Access.
Field names are stacked into an array,
which is used to write the labels across the top.
CopyFromRecordset is used to write the data.
Data filters are added.
Whatever value is in the first column is bolded each time it changes,
along with a top border (Line) stretching across the data.
Margins and Page Header are set.
Columns are best-fit.
Columns wider than 60 (you can change this of course)
are wrapped and the width is set to 60.
The top row and left column are frozen.
So next time, you have to trace query equations, I hope this will help.
Thanks for watching.
Through sharing, we will all get better.