Help Writing Reports for MS Access

[michael_quackenbush]

Hi, I am struggling to generate reports via MS Access for a simple
application I am trying to run.

I have a table built that has four (4) fields with multiple records:
Last Name, First Name, Date, and Currency

What I would like to do is generate reports based upon the following
criteria:

Report based upon each user's contribution with a breakdown of each
date. For example a user with the same LastName and FirstName may
have made contributions to a fund on multiple dates, thus I would like
to have a report generated for each user that has their respective
dates and amounts displayed.

Any help would be greatly appreciated. Also if I need to reformat my
tables, that is fine too, so please advise.

Thanks!

 

[fredg]

Hi, I am struggling to generate reports via MS Access for a simple
application I am trying to run.

I have a table built that has four (4) fields with multiple records:
Last Name, First Name, Date, and Currency

What I would like to do is generate reports based upon the following
criteria:

Report based upon each user's contribution with a breakdown of each
date. For example a user with the same LastName and FirstName may
have made contributions to a fund on multiple dates, thus I would like
to have a report generated for each user that has their respective
dates and amounts displayed.

Any help would be greatly appreciated. Also if I need to reformat my
tables, that is fine too, so please advise.

Thanks!

You have a basic problem with the design of your database.
It is not unusual to have more than one person with the same last and
first names. Just open your phone book and see how many John Smith's
there are listed. Nor is it unusual to have the same person make more
than one contribution over a period of time.

You should have at least 2 tables, one (the UserName table) with the
first and last Names, Phone and Address and a prime key NameID field.

The prime key field uniquely identifies that User. It would be wise to
include Phone and Address information as well.

The second table (let's call it the 'DetailsTable' has the Date and
Currency as well as a field that includes the NameID field.
The relationship would be UserName table NameID on the One side with
the DetailTable NameID field as the many side.

This way, any one user can have many records in the details table, yet
still be identified with the same NameID.

You would use a query as the record source for the report.
Then it's a simple matter, in the report, to group your data on that
NameID field (in the Report's Sorting and Grouping dialog) so that all
of John Smith 's (NameID 123) data is combined, and shown separately
from John Smith (NameID 365).

In addition to all of the above, note that Date and Currency are
reserved Access/VBA/Jet words and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html