Help Writing Reports for MS Access

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!

0
michael_quackenbush
10/23/2007 10:10:14 PM
access.reports 4434 articles. 0 followers. Follow

1 Replies
644 Views

Similar Articles

[PageSpeed] 41

On Tue, 23 Oct 2007 15:10:14 -0700, michael_quackenbush@yahoo.com
wrote:

> 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
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
10/23/2007 10:43:15 PM
Reply:

Similar Artilces:

Remote data access
As a new .net developer, I would like to know how a VB.net Windows application can access a SQL Server database residing on a web server. In other words, using the Visual Studio IDE, is there a way to develop a Visual Basic, Windows application that can access a SQL Server database over the internet. Thanks for suggestions, John C. John C. wrote: > As a new .net developer, I would like to know how a > VB.net Windows application can access a SQL Server > database residing on a web server. > > In other words, using the Visual Studio IDE, is there a > way to develop...

forecast function help
This might seem a bit newbie but im having trouble with the forecas function. Say for example i have a collection of data for sales of each item ove a number of years: item 2000 2001 2002 2003 1 3 4 5 2 4 3 2 3 2 2 4 4 3 1 5 5 4 1 6 6 2 2 4 i am asked to forcast the values for the year 2003. I have read an looked at many examples on how to do this and still can't work ou where to start? Any help would ...

Help with SQL (Access2007)
Hello. I am trying to integrate data from several sites into 1 (new) table. In order to distinguish the data from each site in the new table I have a field (InstID) which holds the Instution number of the site. The fields from the old site tables and the new table are identical except for the InstID. InstID and ClientID are Primary Keys. The path to the old table is asked, then the number for the InstID is asked and placed as a variable - varInstID. I have an append sql as follows: Private Sub UpdateDB_Click() ' populate the clients table strSql = "INSERT INTO tblClients ( In...

Images in reports that change with each record
I am building a database of the inventory in a store. My database is working splendidly but then the manager's said to add a thumbnail of all the items to their corresponding record. I thought that by simply adding an ole object field would do the trick but apparently when I look at the report and print it; the images don't appear, only their pathname. I need the pictures to be visible and that I can print the report with the images showing. Please help! hey people I really need help with this one. "Disorganized Rusty Officemeister" wrote: > I am building a databas...

CRM 3.0 Reports:Error when trying to open reports on client m/c
I am unable to access CRM Reports from any other machine except the CRM Server (Physical) box. I logged in as different users on the CRM server and was able to open and see all of the reports based on roles. But I am not able to view reports from any of the client machines. I am accessing Reports through a web-client. Upon trying to access reports through a client machine, I get the message " An error has occurred. For more information, contact your system administrator". This does not tell me anything I do not see anything unusual in the Web server (CRM Server), or the Da...

Need help with Excel Form & ComboBox Tutorial
At http://www.excel-vba.com/v-forms-controls.htm I have followed instructions... my code on the form is below but it won't run... I've marked the error... Can anybody give me any help with this? thanks Code is below-------------- Private Sub cmdBtnSubmit_Click() shReport.Range("C4").Value = cbxCity.Value cbxCity.Value = "Select a City" frmCity.Hide End Sub Private Sub cmdCityCancel_Click() cbxCity.Value = "Select a City" frmCity.Hide End Sub Private Sub UserForm_Activate() shParameterst.Activate '<-----Run Time Error 424 - Object Required...

OWA limited access for Board of Directors
I have a question about my exchange server and OWA. I need to customize it to have limited access for my broad of director via web with easy access for the Board relations manager to maintain the information. The requirements to me was to give the broad access via the web to a calendar,contact and a document folder(with the restriction that the BOD can not delete items plus not be able to email out). I wonder this can be done with Exchange OWA or is ther a better software product would help me On Thu, 20 Apr 2006 06:32:01 -0700, JCS <JCS@discussions.microsoft.com> wrote: >I h...

Customer Address Help
Hi, I was wondering if anybody knew of a way to run a customer report that invluced the customers address, city, state, and zip in it. I am not very familiar with crystal reports, so if there is another way that would be awesome. Thanks, -Bill H On CustomerSource there is a section called the "Report Library"- I think it's under downloads. In the RMS Report Library MS has provided several new or modified reports, including one with customer address. On Sat, 17 Jul 2004 08:40:10 -0700, Bill H <bill@platinumpools.com> wrote: > Hi, I was wondering if anybody k...

Macro Help/Duplicate Items + Insert Rows + Sum
I am trying to create a template that will do the following: 1. Find Duplicate Entries (AlphaNumeric) In A Column 2. Insert 2 Rows Between The Duplicate Entries Then: 1. Sub-Total(Another Column With Random Numbers) Of The Duplicate Entries 2. Format the Sub-Total In Bold I have gotten to the point of writting a macro that will identify the duplicate entries; does anybody know how to do the rest? This is a changing set of data, transferred to excel from a relational database (Lotus123 Rel2, which contains anywhere between 3000 to 5000 rows. I cannot spend time grouping the data ...

Problems with MS-DateTime-Picker 6.0
My userform has a Multipage control on top. My Microsoft Date-Time-Picker 6.0 Control is sitting on "Page 1" of the Multi-page control. If I change to "Page 2" and try to access the ".Value" of the DTPicker control, it will return an error value. (It is returning the year 1899). What is wrong here? Does the DTPicker need to be on the visible page to be used?? Are there any other things I should know about this DTPicker control? Robert, You could assign the DTPicker's value to a public date variable in its change event. HTH, Be...

Help with graph / chart
I have a graph for weeks 1-52, I have split this into 4 seperate graphs each showing a quarter (13 weeks) I cant remember exactly how I created them but possibly using some sort of copy paste as each chart show weeks 1 - 13 along the bottom. This should read......... for chart 1 1-13 for chart 2 14 - 26 chart 3 27 - 40 chart 4 41 - 52 How do I change this on each chart to read the week numbers indicated.? thanks Hi, You need to define the Category labels for the chart. Chart 1 is fine as it defaults to the values 1 to 13. For the other 3 charts you will need to create...

I lost my Access (Office) 2003 disk
I have MS Office 2003 loaded on my computer, but someone stole all my program disks just over a year ago, and now I'm finding that things like the subform wizard doesn't load automatically with the program, but has to be loaded later from the program disk (I'm working out of some tutorial-type books for Access 2003). Is there some way of getting around this? Either just using Access workarounds, or is there some way of getting a hold of these (apparent) add-ins to the program, either on line or on disc? Thanks Rob, I answered your question in the Forms newsgroup, this morning. -...

Report Load Error via CRM
I am loading a very basic report that had a complex query but works fine when running it in VS2005. When I attempt to load the report via the CRM 4.0 GUI, I get the following error message: An error has occurred. Try this action again. If the problem continues, check the Microsoft Dynamics CRM Community for solutions or contact your organization's Microsoft Dynamics CRM Administrator. Finally, you can contact Microsoft Support." This report does not have any parameters at all, and the most intense thing are embedded select statements which I have done on other reports. I have instal...

unable to load help topic
Using Money 2004 deluxe. Asking for help I get "unable to load topic" try again. No help, same responce. I went to MS Knowledge base article 812755. Which says 'clear the cache' Which I did. No help, still 'unable to load topic' Tried asking a 'Microsoft pro', could not get a screen to ask my question. Any suggestions? I cleared both MS IE and my default browser, and tried again, still no help. Seems like I should be able to get 'HELP' I even reloaded the Money program, still no HELP. Thanks for any 'HELP" Walt In microsoft.public.money...

Need to populate a Report with several records
In short, I have one report containing 5 records from the same table. The individual records are layed out to support proper printing. I need to populate each record with from the same table. I can not give them all the same pointer to the table or I get 5 copies of the same record. How can I populate those 5 records with 5 records from my table? I think you need to have a group by in your report. Thus you will get 1 group of data for each of the 5 that you are refering to as 5 records. "Bill" wrote: > In short, I have one report containing 5 records from the same t...

distribution group / delivery report exchange 2003
an originator from outside my exchange 2003 organisation is sending an email to a distribution list, but he never receive a delivery report global config, message format for domain * (is ok) [x] delivery report [x] non delivery report "Ronald" <rasch@ltsa.de> wrote: >an originator from outside my exchange 2003 organisation >is sending an email to a distribution list, >but he never receive a delivery report Did he request one? -- Rich Matheisen MCSE+I, Exchange MVP MS Exchange FAQ at http://www.swinc.com/resource/exch_faq.htm ...

Crystal Report error: information is needed 02-03-04
After reinstalling CRM I get this error when I try to get a report. I know there is information since I'm just asking for the user list. ---------------------------------------- CrystalReportViewer Information is needed before this report can be processed. Information is needed before this report can be processed. ---------------------------------------- Any Ideas? Thanks Also recieved this error, and have logged an issue with MS, no resolution yet. One tip they did provide, remove any underscores ("_") from the server name. This has resolved the issue for a lot ...

What does the "E" in 5E
I am used MS EXCEL to perform a scatter plot. When the information was graph, Excel provided me with the formula 5E - 71e^0.085x. What does the "E" in 5E mean? The E is the way Excel represents 10 to the power. So 1.2E3 is 1.2X10^3 or 1200 and 1.2E-3 is 1.2 x 10^-3 or 0.0012. "College Professor" -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "College student" <College student@discussions.microsoft.com> wrote in message news:8F20E8A3-BDC6-42E5-A5D0-508D57C40F42@microsoft.com... >I am used MS EXCEL to perform a scatte...

Is there something like Cond and In in Access?
#1) Cond In old PC FAND, which was base on Pascal, we had the following: SomeValue:=cond(SomeField=1 And OtherField="A": "Excelent", SomeField=2 And OtherField="A": "Almost excelent", SomeField=3: "Hmmm...", else: "Ooops") Is there an equivalent in Access 2007? IMO: 1) Select Case will not do that. 2) Select Case can't be used in computed fields. 3) With IIf it's quite complicated. 4) The easiest way: Public Function. But do I really want a ...

Can not create Matrix Item please Help RMS 2.0
RMS 2.0 Can not create Matrix Item please Help When trying to create any new items I receive error message This is the message (-2147217864) Row Cannot be located for updating. Some values may have been change since it was last read. Manger still creates standard items but still receives message with out this number in message -2147217864 ...

Massive Report: Have you ever done this? Help Please
Hi, I am compiling the results of a survey in ACC2003 as a paper appendix. I have about 60 report objects which are about 2 to 4 pages of text each. I have about 60 Pivot Charts and tables as separate form objects. I want to have one report which has the charts and tables and text in it since this would be easy to layout and the page numbering would flow right through. Is this the correct way to do it? I have made a start and the first few pages are fine with charts and tables. However, Access seems to have space restrictions on the height of a report group? When I increase the ...

SMTP Help!!!
I have a customer with a new Exchange 2003 server Single AD domain on one server DNS server local and seems to be working correctly Cable internet through Comcast Had been receiving 2012 and 2013 app log events Increased the DNSErrorsBeforeFailover as suggested in a knowledgebase article 2012 & 2013 Errors have stop but replaced with 4006 events No mail flow inbound or outbound for past 2 days!! SMTP appears busted, cannot telnet in or out on Port 25 even though firewall has port forwarding on that port Switched firewalls with same result Noticed periodic Back Orifice attack attem...

Modify Access 97 tables in Access 2003
How do I modify an Access 97 table using Access 2003 without converting the database? Is there any tool available? Rick This is only one person's experience... There is only one tool I'm familiar with that would let you do that, and it's called ... Access '97<g>! You've described HOW you want to do something. Now, if you'll describe a bit more about WHY you need this done, the folks here in the newsgroup may be able to offer more specific suggestions. Regards Jeff Boyce Microsoft Office/Access MVP "Rick" <Rick@discussions.microsoft.com...

Cashiers changing prices: "access pricing" and discounts
Hi: We are a wine shop and give 10% discounts when customers purchase six bottles of wine, and 20% when they purchase 12 bottles. I've got the discounts to automatically kick in when 6 or 12 bottles are scanned into the system using mix and match. However, I've noticed we get an error about "cashier cannot change prices" when the minimum quantity for discount is entered into the system. The reason is b/c the cashier does not have the "access pricing" check box turned on in their detail setup. The problem is we don't want the cashier to have the &quo...

MS Query #4
First, does someone know of a document that provides details on MS Query functions and their syntax. For example, I know I can use left, right, etc. What other functions exist? I want to do some parsing on some data fields and need to determine the length of the field and the place where certain characters are located within that field. Thanks. Hi ODBC query syntax is determined by driver, not by Excel. Usually it's like the syntax, used in database system, from where you get data. P.e. the syntax for FoxPro/VisualFox ODBC query is much like to syntax, you use with queries in F...