Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Access Report

Posted on 2015-01-22
21
86 Views
Last Modified: 2015-01-23
Hi Experts,
I'm working on a report in Access 2010.  I have a question about the report.  This report has a field "Payment" is a Yes/No field.  for example, I have 50 records in this report that have either the payment is Yes or No, so all 50 will show Yes, No on each record, how do I do if I want all "Yes" in on left side of the report and all "No" on the right side of the report and get the percentage too.  Does any one have a sample of this kind of the report?

Thank you.
0
Comment
Question by:urjudo
  • 11
  • 9
21 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 40564550
One easy way: have your report contain two subreports, one each for "Payment = Yes" and "Payment = No".  Place the subreport objects as desired.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40564609
You did not specify if this report had any "grouping"

If I am understanding correctly, ...you can get a count of yes and no with something like this:
Put a textbox in the report footer.
Name it: txtCountYes
Make the cobntrolsource something like this:
=Dcount("*","YourTable","Payment=-1")

Put a another textbox in the report footer.
Name it: txtCountNo
Make the cobntrolsource something like this:
=Dcount("*","YourTable","Payment=0")
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40564658
Sorry, I did not read your post clearly.
If you need the percent yes/no, see the attached sample...(the report is named "Table1")
Database54.mdb
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:urjudo
ID: 40564820
Hi Jeffrey,
I like your idea, but when I put the text box and on the controlsource, I put
=DCount("*","qryCollectAmount","SUPAYMENT= Y") it gave me "error" not a count, did I do anything wrong?

Thanks,
0
 

Author Comment

by:urjudo
ID: 40565012
my query has parameters, I change to =count([SUPAYMENT]="Y")  but it gave me the wrong count.  I have one "Yes" and one "No" but when I use this :
Total of Yes:  =count([SUPAYMENT]="Y")
Total of No:   = count([SUPAYMENT]="N")
and I got both "2", it should be
Total of Yes:  1 not 2
Total of No:   1 not 2
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40565220
You stated that this was a yes/No datatype:
This report has a field "Payment" is a Yes/No field.
Yes/No fiels in Access set Yes=-1 and No=0
...so when you say that you are doing this:
=DCount("*","qryCollectAmount","SUPAYMENT= Y")
It implies that the actual stored value is "Y" or "N"
If so, then try your expression like this:
=DCount("*","qryCollectAmount","SUPAYMENT= 'Y'")
?

So lets be clear
What is the Yes/No field defined as in the table?

JeffCoachman
0
 

Author Comment

by:urjudo
ID: 40565245
Payment = SUPAYMENT (the SUPAYMENT is refer as Payment in the table), this field is store as either "Y"  or "N" in the table
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40565253
Just FYI
The Count() function does not accept criteria,
So you can really only do this:
=Count(YourField)
If you need to specify a Where argument (SUPAYMENT="y")
Then you have to use dcount:
=DCount("*","qryCollectAmount","SUPAYMENT= 'Y'")
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40565258
Payment = SUPAYMENT (the SUPAYMENT is refer as Payment in the table), this field is store as either "Y"  or "N" in the table

Then as I previously stated, this syntax should work:
=DCount("*","qryCollectAmount","SUPAYMENT= 'Y'")
0
 

Author Comment

by:urjudo
ID: 40565260
I did try this  =DCount("*","qryCollectAmount","SUPAYMENT= 'Y'"), it gave me #Error
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 40565311
Then check our spelling, ...This worked OK for me in this new sample
Database54.mdb
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40565317
correct count and percent of Y and N
0
 

Author Comment

by:urjudo
ID: 40565333
is a different that I use a Query instead Table?  coz I use a Query on the report instead a Table
0
 

Author Comment

by:urjudo
ID: 40565344
if I removed the parameter in the query, then it works.  but if I have a parameter for the date range then it does not work.  do I need to entered anything for the date range?  my parameter in the query is Between [begindate] and [EndDate]
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40565347
No difference
Make sure you have the spelling of the query correct.
...and make sure that "SUPAYMENT" is actually in the query and is spelled correctly as well.
0
 

Author Comment

by:urjudo
ID: 40565373
again, without the parameter in the query, it works but once I  put the parameter back to the query, then it gave me "#Error" for the count.  I think it's the parameter problem but I  have to have the parameter in the query.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40565381
if I removed the parameter in the query, then it works.  but if I have a parameter for the date range then it does not work.  do I need to entered anything for the date range?  my parameter in the query is Between [begindate] and [EndDate]
This is all new information...

In order to get this resolved a little quicker, ..can you post a small sample database that exhibits this behavior?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40565413
.
0
 

Author Comment

by:urjudo
ID: 40566552
Hi Jeffrey,
Thanks so much for your help!!!  It finally works because I open the report from the form instead run the report from the report view, that's cause the error count.  Thanks again.
0
 

Author Closing Comment

by:urjudo
ID: 40566555
Thank you!!!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40566841
ok
Great...
enjoy the weekend
;-)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question