UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Placing Of Data In The Group Total, Access 2013    
 
   
msnarayanan
post May 17 2017, 09:51 AM
Post#1



Posts: 195
Joined: 28-May 13



dear all
I have created a report using report wizard for summary only.

When the report is viewed unwanted details are shown.

How these can be removed and to have the data posted at the various positions and in sequence of my choice instead of alphabetical order.

Can I type a formula as below:
=if(field="xxx",sum(amount)

your help please.
m s narayanan

Go to the top of the page
 
RJD
post May 17 2017, 11:17 AM
Post#2


UtterAccess VIP
Posts: 7,626
Joined: 25-October 10
From: Gulf South USA


Hi: To answer you last question first, >> =if(field="xxx",sum(amount) <<, given that this is in the report footer, should be more like ...

=Sum(IIf([field]="xxx",[Amount],0)

As to your other question, you should be able to put the report in design view and delete the controls you do not want and move the other controls where you want them. You may have to un-group the controls to do this. If you have problems with this, perhaps you could post the db (relevant objects, no sensitive data, zipped) and someone could take a look. And be sure to indicate the controls that need to be eliminated and the order of the others.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
msnarayanan
post May 17 2017, 07:56 PM
Post#3



Posts: 195
Joined: 28-May 13



dear RJD

thanks for your post.

I shall try your valid suggestions.

if I face any problem I will seek your assistance and send you the zipped file.

Regards

m s Narayanan
U
Go to the top of the page
 
RJD
post May 17 2017, 08:17 PM
Post#4


UtterAccess VIP
Posts: 7,626
Joined: 25-October 10
From: Gulf South USA


Good luck with your corrections. Let us know if we can be of further assistance.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
msnarayanan
post May 18 2017, 04:55 AM
Post#5



Posts: 195
Joined: 28-May 13



Dear sir
I tried the formula you suggested. This formula is not accepted by the system.
I think, some parenthesis is missing.
I tried the following but it returns 0 value
=iif(acc_head="donations,sum(debit_amt),0)

Should include root table name like group master. Acct_head and transaction table,debit_amt.

I have not so far tried ungrouping method.
I tried with unbound field an the results were as mentioned above.

Your help pl.

m s narayanan
Go to the top of the page
 
RJD
post May 18 2017, 06:30 AM
Post#6


UtterAccess VIP
Posts: 7,626
Joined: 25-October 10
From: Gulf South USA


QUOTE
=iif(acc_head="donations,sum(debit_amt),0)

Actually, this was not the syntax I suggested, and you also have a double-paren missing as well.

Try this instead in the report footer ...

=Sum(IIf([ac_head]="donations",[debit_amt],0))

If you are still having problems, please post your db (relevant objects, no sensitive data - examples only, zipped) and someone could take a look.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
msnarayanan
post May 18 2017, 07:56 AM
Post#7



Posts: 195
Joined: 28-May 13



dear sir

thanks for your prompt reply.

though the formula was accepted, the output report shows "error"

instead of sending db file which is very large, i am attaching a pdf of a report.

the report query was based on trading query and i have selected groups accpoint and acc heads.
the report is ok. but i wished to have the report in a format shown below:

Opening Stock 111111 Sales
Purchases 44444 Closing Stock
Brokerage
gross prfit
total Total

thank you

m s narayananAttached File  trading_query1.pdf ( 116.86K )Number of downloads: 3
Go to the top of the page
 
RJD
post May 18 2017, 10:49 AM
Post#8


UtterAccess VIP
Posts: 7,626
Joined: 25-October 10
From: Gulf South USA


Sorry, but without your database to work with I don't think I can help from here. I only need the relevant objects (tables, queries, report) and only example data. If you put only the objects that are needed for the report in a new database, and only include example records (no sensitive data), then someone could take a look and go from there. Be sure to compact and zip the new db.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
msnarayanan
post May 19 2017, 01:42 AM
Post#9



Posts: 195
Joined: 28-May 13



dear mr.rjd

thanks for your response.
as required by you i am attaching two zipped files (one db and the another xls)
as you may notice, i have created 3 queries and 3 reports.
the output in these reports is ok. but the format or layout of report is to be
amended.

i have exported the table to excel and then created a pivot table. from the
pivot table i copied to another sheet. this is the design i wanted to have in
access directly.

your help please

m s narayanan
Attached File  specimen1.zip ( 83.82K )Number of downloads: 3

Attached File(s)
Attached File  transaction_table.zip ( 26.03K )Number of downloads: 4
 
Go to the top of the page
 
RJD
post May 19 2017, 01:15 PM
Post#10


UtterAccess VIP
Posts: 7,626
Joined: 25-October 10
From: Gulf South USA


Hi again: Now that I see your report design in the Excel spreadsheet, it seems that you need two subreports to be included in a main report. For example, for "trading account", a subreport for debits and a subreport for credits. Then put these subreports side-by-side in a main report.

Try that and see if it gives you what you want.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
msnarayanan
post May 19 2017, 08:09 PM
Post#11



Posts: 195
Joined: 28-May 13



Dear Mr.rjd
Thanks a million for your assistance.
I shall try to create sub reports and to include them in the main report. I may take a little more time for me to understand how to create sub reports. I will do it referring to tutorials.
I hope I will be successful in this project.
I well get back to you sometime next week.

Regards

M S Narayanan
Go to the top of the page
 
msnarayanan
post May 24 2017, 07:19 AM
Post#12



Posts: 195
Joined: 28-May 13



dear mr.rjd

this is in continuation of my previous post.

i regret to state, that in making many attemps, i could not create sub-reports to have the format of the
report as i wanted. i am still trying.

in the meantime, is it possible to create "unbound" fields though it may involve some additional time.
if so what would be the formula to get the required result.
the following formula returned "error"
=iif(acc_head="Cash" AND debit_amount>credit_amount,debit_amount--credit_amount," ")

please correct the formula.

thanks

m s narayanan
Go to the top of the page
 
RJD
post May 24 2017, 11:14 AM
Post#13


UtterAccess VIP
Posts: 7,626
Joined: 25-October 10
From: Gulf South USA


Hi again: Addressing your IIf statement first, you have Null values you must deal with to make the calculations work. Try something like this...

IIf([acc_head]="Cash" And NZ([debit_amount],0)>NZ([credit_amount],0),NZ([debit_amount],0)-NZ([credit_amount],0),0)

(You also had two minus signs in what you posted.)

However, I do not see what you are applying this against. Perhaps you could give us the context of the statement, or where it can be found in your posted db.

As to your report with side-by-side subreports, you must create two queries, one for each subreport, then two subreports, one for each query you created. Then you must combine these two subreports in another report.

I did this for the Trading Account report (see the attached modification to your posted db). You can modify the display however you wish, but this is the idea. Then you can apply the same approach to the other two reports as well.

HTH
Joe
Attached File(s)
Attached File  specimen1_Rev1.zip ( 58K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
msnarayanan
post May 24 2017, 07:47 PM
Post#14



Posts: 195
Joined: 28-May 13



Dear Mr.rjd

Thanks for your detailed response.
Regarding iif formula, I thought I could place the data side by side and at the places wherever I wanted (when I did not succeed in creating sub reports.

Now that you have given me an idea for creating sub reports, I shall try and let you know sooner or later.

Once again thank you

M S Narayanan
Go to the top of the page
 
RJD
post May 24 2017, 08:19 PM
Post#15


UtterAccess VIP
Posts: 7,626
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Let us know when you complete your project, and if we can be of further assistance.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
msnarayanan
post May 26 2017, 06:16 AM
Post#16



Posts: 195
Joined: 28-May 13



dear mr.joe

i am very glad to inform you that with your valid and well-defined guidance, i am
able to generate various reports as i wished them.

i find no words to express my gratitute for having solved my problem.

finally, i require you advice in regard to the following points - on Balance Sheet Reports.
As you can notice i have created 4 queries and two reports:

queries: bs debit query reports: BS REPORT
bs credit query

queries: nett debit query reports: BS WITH NETT BALANCE

in the BS Report, account heads are shown with values on both sides.
in the other Report, balances are shown after deducting credits/debits as the case may be,
but account heads are shown even where there are no values.

i am attaching the file for your viewing.

is there any way to delete the row which has no value. i tried to create a query but the
result ws that no account is found.

if you can find a solution for this, please advise me.

sorry for having given you a lot of trouble.

thanks

M S NarayananAttached File  amended.zip ( 166.6K )Number of downloads: 2


Go to the top of the page
 
RJD
post May 26 2017, 11:20 AM
Post#17


UtterAccess VIP
Posts: 7,626
Joined: 25-October 10
From: Gulf South USA


Hi again: I am not completely sure what you want here, but see the revision to your db attached.

In the report [BS REPORT WITH NETT BALANCE] you included credit and debit in both queries, and you did not exclude 0 amounts. I corrected both queries. See if this is what you are trying to do.

You are also using a lookup field in table [transaction table] ... [acc-head]. This is a BAD idea, and I recommend you do not do this, but use a combobox on a form instead to make your selection.

You are also using spaces in your object names. While you can work around this by using square brackets around the names, the spaces can cause difficulties when you forget the square brackets.

HTH
Joe
Attached File(s)
Attached File  amended_Rev1.zip ( 65.89K )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
msnarayanan
post May 26 2017, 09:42 PM
Post#18



Posts: 195
Joined: 28-May 13



dear mr.joe

thanks for your reply.
the attachment meets my need.
i shall start creating combo/list box instead of look-up.

with regads

m s narayanan





Go to the top of the page
 
RJD
post May 26 2017, 10:54 PM
Post#19


UtterAccess VIP
Posts: 7,626
Joined: 25-October 10
From: Gulf South USA


You are very welcome. Glad that works for you.

Good luck with your project.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd August 2017 - 03:29 PM