MSACCESS Summary page

I am trying to create a summary page for a table in my database.

I want to create a report that

counts the total records
 
adds the total of a couple of fields.

how would I do this?  Do I need to make the report look at  query or just at the table?

I am going to be doing this from one page and basicly want to keep looking back at the same table  

so in the "text" box I want to do stuff like =sum[fieldname]  
???

thanks

LVL 1
lefty431Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fefo_33065Commented:
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])
lefty431Author Commented:
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

lefty431Author Commented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

dmitryz6Commented:
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
lefty431Author Commented:
so 1 query that does all of this?  then have the report look at the query??
dmitryz6Commented:
Yes.If you need just this.One query will be enough
lefty431Author Commented:
ok, I will try it and let you know...


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


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



dmitryz6Commented:
Could you post your query
lefty431Author Commented:
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
dmitryz6Commented:
when you in query design view goto view SQL and copy/paste it
lefty431Author Commented:
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
dmitryz6Commented:
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]
lefty431Author Commented:
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?
fefo_33065Commented:
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?


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

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

dmitryz6Commented:
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
lefty431Author Commented:
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


lefty431Author Commented:
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?
dmitryz6Commented:
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]
lefty431Author Commented:
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?
dmitryz6Commented:
Sub Amt Paid -does name of the field?
same for Date   -does it name of the field?
lefty431Author Commented:
yes, they both have field names..  

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

dmitryz6Commented:
Try to add records and see what you get.
lefty431Author Commented:
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.

lefty431Author Commented:
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.

lefty431Author Commented:
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....


dmitryz6Commented:
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]
lefty431Author Commented:
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...

dmitryz6Commented:
Now move all your textboxes in footer
lefty431Author Commented:
ok....

lefty431Author Commented:
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?

lefty431Author Commented:
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..

dmitryz6Commented:
Could you post what you are puting in control source for textboxes
dmitryz6Commented:
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
lefty431Author Commented:
how do I adjust subreport?

dmitryz6Commented:
Right Click on subreport object,goto property.
lefty431Author Commented:
I don't see a subreport object anywhere..  

Just details, header and footer...


dmitryz6Commented:
OK. If you like, you could import Report and Table into new database and send it by Email(In my profile).Please just sample data .

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lefty431Author Commented:
ok

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.