Pulling a report on JUST one record

[tgdavidson]

Setting up an Access 2007 database to track fundraising for a HS marching
band (I didn't duck fast enough). In particular, we want to track student
deposits to their INDIVIDUAL accounts (from fundraisers) and withdrawals (for
band tours, supplies, etc).

I've adapted a general ledger template for the overall structure, and it's
working fine. What's tricky (translation: what I'm too inexperienced to know)
is figuring out how to produce a single page report (for printing or
otherwise sharing with the student) that shows JUST that student's account
status. I know there's probably a simple way to do this, but my SQL and
Access skills are far too rusty.

I've got two major tables (and a couple subtables to handle some generic
data, like students' year in school, etc.):

STUDENTS table has fields for First name; Last name; Graduation Year;
Section/instrument (to help disambiguate if we ever have two with the same or
similar names); and their start-of-the-year account balance (hand-entered
from the - wait for it! - paper records the previous treasurer used).

GENERAL LEDGER table has fields for Student Name (concatenated from the
first & last names in table: STUDENTS); Transaction Date; Credit Amount;
Debit Amount; and Detail (to record the memo details of the transaction.

I've got some queries to pull general, aggregated reports for ALL students -
but can't for the life of me figure out how to generate a stack o' reports
for every student individually. Anyone able to stop laughing long enough to
point me at the probably-obvious solution?



Kind regards,



-tgd

 

[KARL DEWEY]

In the report group on student name and force new page for each group.

 

[John Spencer]

First problem would be to fix your data structure.

I would add an autonumber field as the primary key to the STUDENTS table
(field name = StudentID) and a long integer field to the GENERAL Ledger table
as the foreign key (field name = StudentID) pointing to the STUDENTS table
record. And I would drop the Student name from the General Ledger table.

WHY? Because right now you are storing FirstName + LastName in the general
ledger table and if you should have two students with the same name you won't
be able to figure out which entries go with which student. If you don't use
the artificial key, you are going to have to store First Name, Last Name, plus
the other fields you are using in the students table to identify one specific
individual.

As far as printing one specific individual, you would need a query as the
source for the report.

If you make the table modifications, then your query might look like:

SELECT S.StudentID, S.[First Name], S.[Last Name]
, Sum([Credit Amount]) as Credit
, Sum([Debit Amount]) as Debit
, First(StartingBalance) as StartBalance
FROM STUDENTS As S LEFT JOIN [GENERAL LEDGER} as G
ON S.StudentID = G.StudentID
GROUP BY S.StudentID, S.[First Name], S.[Last Name]


Build a report based on the above query.

Next I would create a form with a combobox control based on the Student table.
I would return the StudentID in the first column (hidden) and concatenate
the Last Name, First Name, Graduation Year, and Section into the second column.

Add a button to the form to print the report and give it code in its Click
event like the following

Private Sub btnPrintReport_Click
Dim rptName as String
Dim strCriteria as String

strCriteria = "StudentID = " & Me.NameOfComboBox
rptName = "NameOfTheReport"
Docmd.OpenReport rptName,acViewPreview,,strCriteria

End Sub

Or if you don't want to use VBA code and set up Access 2007 to allow that, you
could build a macro to print the report.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County