• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • Last Modified:

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

0
lefty431
Asked:
lefty431
  • 23
  • 15
  • 2
1 Solution
 
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])
0
 
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

0
 
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
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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


0
 
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...



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


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

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

0
 
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
0
 
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


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

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

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

0
 
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.

0
 
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....


0
 
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]
0
 
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...

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

0
 
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?

0
 
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..

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

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

Just details, header and footer...


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

0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 23
  • 15
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now