Calculate Debit And Credit - Sum Separately

May 5, 2013

I have debit & credit column. Which I use this in the report -->

=IIf([PaymentMet_trans]=2,"",[Amount])
debit = 1 , credit = 2

Now, i would like to have the total sum of it. according to the debit & credit separately instead of the whole total sum.

View Replies


ADVERTISEMENT

Randomize 2 Columns Separately.

Nov 25, 2006

I have read a lot about the rnd function to succesfully randomize the order of records in a db.
I want to randomize 2 columns separately . These are Q's and A's so if the db is 2 fields (the second -small- letter is supposed to be under Field 2!)
Field1 Field2
A a
B b
C c
D d

etc...
I want it to look like
Field1 Field2
B c
A d
D b
C a

I have managed to construct two separate queries which obviously give 2 random lists for each field but how to put them both together to achieve the above?
I have tried applying a rank order to each list and then linking them on that but the rank number keeps changing hence no link there. All I want is to link them and keep each column in the same order as it has been randomized.
Thanks

View 6 Replies View Related

Email Each Name From A Query Result (separately)

Feb 12, 2007

I have searched and searched... but I just cannot find this. I know it has to be in here somewhere. Any help is greatly appreciated.

I have a query called "MyLateProjects" that shows results based on a specific number of days since the "project" was entered in to the database. I need to be able to send a "You are late X days" email to each of the project submitters from the values in the records of the query result. I have the fields, for example, of "emailaddress" and "Projectnumber" that I need to include in the To: line and Subject line, respectively. So, if I get five results, I need it to send five separate emails.

Like I said, I have searched and searched, and I cannot find a way of getting this done. I can send the first one from a button if I show the results in a form... but I don't know to automate the send operation as part of the query, nor how to get a separate email sent for each record (would this be a loop of some sort??).

THANKS!! ALL HELP IS APPRECIATED!!

Tom

View 3 Replies View Related

Queries :: Lookup Multiple Values And Use Both Separately

Jun 24, 2014

I have two tables one with all the personnel (tblPersonnel) I'm tracking, another with different report names (tblReports).

For each report, there are different years that it's valid for.

On the reports table, I have three columns, ReportID, Report_Title, and Years_Valid

I'm using a lookup on the personnel table that stores the report name as the ReportID.

What I want to do is in a query use the Years_Valid column to automatically generate review dates bases on dates already in the database.

View 6 Replies View Related

Forms :: Layout View - Move Labels Separately

Feb 4, 2014

There is a light border that is still there after I remove the text that was in the box. I want to remove the ghost image, for lack of a better name, that is left.

Also all the images, fields, are locked together. I want to remove some individually,without moving everything.

I'm in the "LayOut View" of Access 15.0.4551 Office 365 Home Premium.

View 2 Replies View Related

Receipts/Credit Control

Jan 7, 2006

Hi,

I am currently building a database which holds information on insurance policies. Each policy holder has the option of paying their annual policy either monthly, quarterly or annually.

At this stage I won't get too in depth, but I have built a table (TblReciepts) which the User enters each receipt as they arrive.

I would like to be able to match off each receipt against the outstanding balance on a realted policy, in this instance the policyholder should give their policyno as a reference in the receipt so that is where the relationship should be.

Can anyone suggest the most effective method of allocating a recipt to the outstanding balance in the table (TBLPolicy)

Regards

View 5 Replies View Related

Checking Credit Limit

Apr 5, 2005

Hi, I'm trying to do up a database for a furniture company.
I used Northwinds as a guide and made a similar Orders form.

However, I need another functionality which Northwinds do not seem to have. I want to check whether the currect Order would cause the customer to exceed his credit limit.

WHAT I DID
For this, I made a query to find the amount any single customer owes to date and the amount that he can still borrow(creditlimit-amountowed+amountpaid). I then made a button which launchs this query.

PROBLEM
The problem is I want when i click this button, the query prompts me for CustomerID, which i have to key in again. Then, it launchs the datasheet showing me the credit he would have remaining if this order went through.

It would be better if I do not need to key in his ID again, since it is already on the form when i keyed in the Order.
Further, it would be much better if I could get the button to display APPROVED/REJECT(postive/negative balance) and also display the amount of the remaining credit.

Sorry for the long description. Thanks for reading and please let me know if I missed out any crucial details.

View 2 Replies View Related

Credit Code Get No Return ?

Oct 1, 2006

Can some one help me with this code, I get no return, ???

. Private Sub cust_credit_score_1_AfterUpdate()

If [Forms]![Customer Form]![cust_credit_reply_1] = " [Bad Credit]"Then
[Forms]![Customer Form]![cust_credit_score_1] = 5

ElseIf [Form]![Customer Form]![cust_credit_reply_1] = "[PoorCredit]"Then
[Forms]![Customer Form]![cust_credit_score_1] = 10
Else
[Forms]![Customer Form]![cust_credit_score_1] = 15
End If

End Sub


Thank You For Your Help

Johnny C.

View 14 Replies View Related

Credit Card Processing

Apr 18, 2005

Does anyone know of any software or have any code that would allow to process credit cards.

View 4 Replies View Related

Forms :: Form Creating Credit Invoice

Dec 24, 2014

I have a form with a subform. The form shows an invoice and the subform the items to be invoiced. On this form I want to have a button that creates a credit of this full invoice.

My question is now how to add the existing invoice and details again to the same invoice table (query) and same details table (query).After clicking this button in the two tables should be the original invoice/details and its credit.

View 4 Replies View Related

Adding A Credit Note To A Statement Report?

Aug 20, 2012

I need to add a credit note to my statement report which minuses a wrong invoice. For example, if a wrong invoice is given to a customer, then the user uses a credit note form which updates in the credit note table and this shows in the statement report as a minus. I hope that's not too confusing. The problem comes when I need to add it to my report. It will have the same date as the original invoice and the same price, it will just be shown as a minus.

My statement starts as a form which the user has to enter a start date and an end date and select a customer from a combo box, they then click generate statement which opens the report. How would I add the credit note in? I want it to be in the same date selected but the query for the report only contains the invoice date and not the credit note date. If I add this into the query, it clashes and brings up a blank report. Is there anyway to do this? Will I need to create another query for the credit note and add it in as a subreport? Will I have to add another startdate and enddate for it too as a subform in the statement form?

View 10 Replies View Related

How To Deduct Order Price From Prepaid Credit Value

May 23, 2013

I'm new to access... I've been creating a cash register, that has the following tables:

Customer
Credit
Order_main
Order_sub
Products.

I've gotten so far that I can set up and order, consisting of several products, choosing the customer from a dropdown list and display the subtotal of the order.

But I can't, for the love of god, figure out how to deduct the subtotal from the credit value and get it updated.

View 5 Replies View Related

Validation Text For An Expiry Date On A Credit Card

Nov 20, 2005

Basically, i have a field where the expiry date of a credit card is input, but i want to set an input mask so that it loks something like this:

MM/YY but i cant find a way of doing this!

I then have validation text which makes sure that this value is either equal to or more than todays date! this looks like this:

>=int(now())

So the input mask has to accept this validation...

If you could help, i would be very greatful!

Cheers

View 1 Replies View Related

Calculate A Value

Dec 16, 2004

How would i go about getting the field 'GMargin' (Gross Margin) to be calculated from subtracting 'SellingP' from 'PurchaseP' (Selling and Purchase Prices)?

How do i do this? Help me please :)

View 5 Replies View Related

Calculate

Oct 6, 2005

I have a field which I enter a short time such as 3:13, 5:45, etc

Now i am creating a report which at the end of the report I would like to include an unbound text box, which displays the total amount of time for that field (pls note that the total will never exceeds 24 hours) What is the best formula to use in order to calculate this ?

Cheers

View 4 Replies View Related

Calculate Hh:mm To Mm

Jan 18, 2006

Hello,
I am trying to sum minutes from a database using an Access query, but the format in the database is hh:mm. I know the query to use in Oracle:
ROUND(SUM((substr(actual_time,1,(instr(actual_time ,':' )-1))) *60 + (substr(actual_time,(instr(actual_time,':' )+1),2)))
but not sure of the syntax to use in an Access expression.
Any ideas?:confused:

View 1 Replies View Related

Excel Can´t Calculate.

Aug 6, 2006

HI,

Excel is having difficulties calculating a workbook of mine. It complains about cirkular references, so I had to set the iterations manually.

The workbook holds about 5000 records and I´m using a cosinus function.

The thing is I don´t know what to do. Excel calculates the workbook, but it doesn´t do it right. What setting should I use with the iterations?

I´m thinking of running it in excel 97. Will that help?

I really need to be able to run this workbook. Does anyone have an idea?

Fuga.

View 6 Replies View Related

One PC Not Able To Calculate Formulas Ect.

Jul 10, 2007

Hello, I didn’t know how to explain this or where to post this but here we go! I have two databases and one of the PC's here at work can’t use some of the items in the database like on one of the databases there is some formulas in text boxes and that PC won’t calculate what’s needed in them and every PC in the plant will except for this one PC. Also the one PC won’t bring up the Allen Browne's Calendar. When you press the start or end button to bring up the calendar it never pops up but if I enter the start date and end dates and use it that way ect then it works. I have no clue why this is just happening to this one PC. Any ideas?

View 2 Replies View Related

Date Calculate

Jan 19, 2006

I want to calculate a data from anthony table, like (20 Jan 2005) + 365 days
will be 20 Jan 2006, how can i do it.

The Table setting is just like below:
Constituent (DataID,...);
Student (DataID, DOB,...);
School (DataID, 4YearsOld(As a Date Format),...)

Anyone can help me, Thank you.:)

View 3 Replies View Related

Calculate Age Using Queries

May 12, 2007

i have a table with fields "[DATE OF BIRTH]", "age", "temp-age"

i have a query that calculate the age:
example; the query will get user to input the "temp-age" and upon "temp-age" is input and "ok" on the query, it will calculate a person's age and store the value inside table "age".

however, the calculated value for the age have some data problem. for example, when a person whose age is yy:mm 24:05 the age inside the table will be 1924:05. there will be a additional "19" at the age.

what cause the additional "19" problem?
---------------------------------------------------------------------
my query in design view is like this; (see attachment)
it uses " [temp-AGE]-[DATE OF BIRTH] " to calculate the age and store into table "age".

View 1 Replies View Related

Calculate Months

Nov 10, 2006

Hello,

I'm trying to calculate months not days between

EX: 1/1/06 to 6/30/06
I tried to use end date (-) begin date, but is only give me the difference in days. What expression do I use to get 6 months?

:confused:

View 2 Replies View Related

How To Calculate Total?

Nov 19, 2006

Hi, i'm trying to calculate the amount of employee for each company giving me a total in the queries.. how do i go about this? I'm not an person who uses Access to solve this. I have amount 20 Companies stored and over 800's employee.. Here are the field in the table:EmployeeIDEmployeeNameNumberofEmployeeThanks! Hope this helps.

View 1 Replies View Related

Calculate Commissions

Jan 15, 2007

Hi

I am very new to databases, and think i am a bit over my head. I am doing a database for vacation rentals, and am trying to calculate commissions, basically i wanted to have a set of checkboxes, labeled 10%, 15%, 2.5% and $25, with any combination of these checkboxes being able to be checked.

i want the query to work like this:

If the 10% checkbox is checked i want the query to take the SubTotal for the reservation, and calculate 10% of that number. if the 2.5% checkbox is also checked, i want the query to calculate 2,5% of the reservation and add it to the commissions total (10% + 15%)

I think(hope) that i explained that clearly? Can this be done?

Thanks for your help!

Ben

View 2 Replies View Related

Calculate Median Value

Mar 16, 2007

What is the easiest way to calcuate a median value for a group of values?

View 4 Replies View Related

Calculate Median

Apr 19, 2007

I am attempting to calculate the Average & Median on a particular field in a query.

=Median("qry2007_KPI_AvgJan_A","ContractSignedToReceived")

query name is qry2007_KPI_AvgJan_A
field name is ContractSignedToReceived

I am getting #Name? error.

Any ideas why? Thanks for any help troubleshooting this.

View 1 Replies View Related

Calculate Percentage

Mar 22, 2008

In a report, I need to calculate the percentage of clients that respond "Yes" to a question on a survey. I need to create a query that will return the count of the number of "Yes" responses, and the number of total responses.
For example, if 10 clients complete the survey, and seven respond "Yes", I need the 2 fields in the query to be 7 and 10. So far, I have only been able to do this using multiple queries.
Thank you.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved