MSACCESS Summary page

Simply create a query in your database that gives you the numbers you want and then point your report to that query.

Something like:

select count(*) as TotalCount from MyTable (say this query is named Query1)

In your report, have the ControlSource of the field point to TotalCount (after you set the report to point to your query: RecordSource=Query1)

To add two fields in your report, simply use the syntax: =Sum([NameOfYourTextbox])

ok, so I have a subscriber database.  

there are 1000 records.  I want to count how many actually subscribed.  The way I know if they are a subscriber or not is if there is a current year in the date field.

how could I count how many subscribers there are?

The other way I would know is if there is an amount in the "amount paid" fiield.

if they didn't subscribe, I still have a record of the address and name but these fields are either blank (amount paid) or 2005 (date field)

thanks

is a query the best way to do this?  I am summerizing the whole table.  

so I want to know how many records,  how may of the subscribed, what precent subscribed, total amount collected.  blaa, blaa, blaa....

thanks

query best way to go

Just Ex.

Select count(ID) as CountALLrecords,sum(Amount) as AmountCollected,sum(iif(Suscribed=true,1,0)) as HowManySubscribed,sum(iif(Suscribed=true,1,0))/count(ID)*100 as PercentSuscr from yourTableName

so 1 query that does all of this?  then have the report look at the query??

Yes.If you need just this.One query will be enough

ok, I will try it and let you know...

I think I am going to need a little more help on this.


i created the query but none of the counts are working...

Could you post your query

that is just it, I am not sure how to write the query.

I know I can go into builder, but I don't know the Santax for SQL

when you in query design view goto view SQL and copy/paste it

what I am doing is creating a report

in the report I am creating text boxes.

I am doing stuff like  text1    [field1]/[field2]

it seems to be working for the easier functions.  just not sure how well it will work on the other stuff

it will work but better to do it in query.But if you fill more comfortable to do it in textbox, you can do it.

one more may be it good idea to cneck for null as well

nz([field1],0)/[field2]

ok, I think I am getting the hang of it...

one thing.

when I run the report it wants to print a page for ever record.  all of the pages are the same.  

any ideas?

I'm glad you followed my advice.

The only thing you need to do is in your Database window:
go to "Queries"
then go to "New"
then "Design View" > "OK"
close the "Show Table" box
go to "View"
select "SQL View"

in the SQL View pane type your query:

Select count(*) as TotalRecords, sum(AmountColleted) as TotalAmountCollected.... etc, etc.

Save the query with a distintive name (i,e: TableSummaryQuery)

Now set your RecordSource property of your report to "TableSummaryQuery"

Add your text boxes to the report and set the ControSource of each box to the fields in the query (i,e: TotalAmountCollected, TotalRecords)

Got the idea?

Select count(*) as TotalRecords, sum(AmountColleted) as TotalAmountCollected....

this is the exact santax?   do I put the actual field names between the (   )

It is ms access.

put actual field in [] like

[FieldName]

Select count([IDFieldName]) as CountALLrecords,sum([AmountFieldName]) as AmountCollected,sum(iif([Suscribed Field Name]=true,1,0)) as HowManySubscribed,sum(iif([Suscribed Field Name]=true,1,0))/count([Field Name ID])*100 as PercentSuscr from yourTableName

Select count([PSMID]) as CountALLrecords,sum([Sub Amt Paid]) as AmountCollected,sum(iif([Date]=true,1,0)) as HowManySubscribed,sum(iif([Date]=true,1,0))/count([PSMId])*100 as PercentSuscr from Subscription Table

I get an "Syntax error in From Clause


The table is called "Subscription Table"


I think the space beteen Subscription and table is doing it.  Do you know of a work around besides changing the name?  

What I did is made a copy of the table and tested the SQL.  it counted the records, but it promps me to enter something for Date and forSub Amt Paid

I am going to work this both ways.  using the sQL as you suggested and also by just ding the computing in the report.

Any idea why it would create the report but then make a page for each record?

if you have space in name you need to put []
[Subscription Table] and date is not boolen,you need to check for null

Select count([PSMID]) as CountALLrecords,sum([Sub Amt Paid]) as AmountCollected,sum(iif(not isnull([Date]),1,0)) as HowManySubscribed,sum(iif(not isnull([Date])=true,1,0))/count([PSMId])*100 as PercentSuscr from [Subscription Table]

Ok, so now i get no errors, but when I run the query it promps me for something on sub amt paid and date.

right now the database is blank, or those fields are becase it hasn't gone out yet.  does that matter?

Sub Amt Paid -does name of the field?
same for Date   -does it name of the field?

yes, they both have field names..  

I double checked to make sure they were correct and that is exactly how they appear.

Try to add records and see what you get.

I did that think that it was causing the issue because there was no data in the field.  But that wasn't the case.

The only thing that works is that it counts the PSMID field.  everything else is blank.

the first way I was doing it was working for me.  The only problem was that it was creating 20,000 pages.  that all looked alike.  if there is a way to work around that issue, I can NOT use the SQL query and just do the counting in the reports.

one of the problems is also that this database has been worked on at different levels.  I started it when I didn't know a think about databases and instead of redoing it I keep adding to it becase it is so functional.

that is why there are spaces in names of fields and some other unconventional things going on....

If I understood you correctly for reduce qty of pages click on detail Go to property and mike detail visible false.

For sql try

Select count([PSMID]) as CountALLrecords,sum(nz([Sub Amt Paid],0)) as AmountCollected,sum(iif(isdate([Date]),1,0)) as HowManySubscribed,sum(iif(isdate([Date])=true,1,0))/count([PSMId])*100 as PercentSuscr from [Subscription Table]

when you make detail visible false you can see anything at all.

The header is there and the footer is there, but the report is blank.

it soved the problem with the pages, but no I can't see anything...

Now move all your textboxes in footer

ok....

ok, i think we are getting close..

all of the text boxes seem to be giving me the #error

The labels of course are all working.  

is there something I need to change in my text box now?

so what I did is I made a query

I added the "total" line to the query.  The things I wanted counted, I selected "count"

then it creates a fields called  "suboffieldname"

on the report I did a =sub("suboffieldname")  

this would work in the details section, but the down fall is it would create a report for each record.

so I moved the stuff down to the footer like you said and turned off the details.  

now the fields can't do the calculations..

Could you post what you are puting in control source for textboxes

Ok I see you are using subreport.
Let return back.
Make detail visible true,return textboxes back to detail.Make subreport width=0
Height=0,and make detail smaller as posible

how do I adjust subreport?

Right Click on subreport object,goto property.

I don't see a subreport object anywhere..  

Just details, header and footer...

ok