Summing By Name With Dollars Amounts

Jan 10, 2005

Heelo all:

I have a form based on a query. This form has a combobox with 4 names in it. When a person is selected form the combo box, other fields automatically populate IE: room number, address, etc. Another field is a currency box which also populates automatically.

This is what I wish to happen: When I select a person from the combo box, can it show the TOTAL amount received for THAT PERSON in a separate text box?

Any help is most welcome.

Regrds,

Dee

View Replies


ADVERTISEMENT

Summing Different Multiple Amounts With Grouping

Jul 13, 2006

hey all, thanks to anyone that can help

i have 5 fields facid custid empid amount invdate.

i need to be able to get totals on the amount based on fac, cust & emp based on a date range given by user, but here's the kicker.. i need to get the current month totals, month totals - 1year (so same month but for last year)
the date range totals, last yeardate range
current ytd, last ytd

i did it using code, but the performance isn't that great. there are 2800 customers it has to cycle through and figured one of the resident experts can help with a query.
i've used dsum() but not quite getting the results i should.
i think one of the problems is the user has the option of filtering the data based on the 3 fields.

anyways if you can help i'd greatly appreciate it..

View 2 Replies View Related

Queries :: Complex Query Sorting And Summing Amounts Breakdown

Jan 28, 2015

I have a shell of my database in the below link for reference. I am trying to create a query for a report that will allow me to track charges and payments per client. Each charge has a ChargeID and each payment has a PaymentID. Multiple payments can be applied to one charge so the same ChargeID can show up multiple times with a different PaymentID. Payments that are catagorized as Third Party Payments will have not only a PaymentID, but a TPPaymentID.

What I need to do in the query and final report is track the total charges (even though the ChargeID may show up multiple times, I only want the charge itself to be calculated once and the charge to only be listed once for each client) and track the total payments as well as sum the total payments for each charge for each client.

View 9 Replies View Related

Modules & VBA :: Summing Multiple Queries And Summing Time

Oct 4, 2013

I have a sales form. The sales form has many Call, Meetings and Emails linked to each sales record. I want to total all the number of calls, meeting and email records related to the sales record to give a total- Touches.I've created 3 queries;

1 - Counts Calls
2 - Counts Emails
3 - Counts Meetings

These all work fine however when I combine them to attempt to count the results it doesn't produce the correct results.I have a second query as well (no pun intended).

I am trying to sum together a column that has values in Time. The results displays as a decimal. How can I have the result display as a Time i.e. 1:20 (1 hour 20 minutes).

View 1 Replies View Related

Currency Control Won't Show Dollars

Oct 17, 2005

I have a form with various controls that reflect dollar amounts.

One of those controls, despite deleting and replacing it, despite deleting the field whose value it displays, despite changing it to 'general number', saving, then changing back, despite comparing its properties with that of numerous normal-behaving controls, refuses to show a dollar sign, nor zero cents values. I've compressed and repaired in between deleting fields and controls, too, to no avail.

Otherwise it is working correctly in that calculations based on it work fine and it shows the number of dollars it's supposed to. Just no dollar sign and refuses to show zero cents...

Can anyone suggest to me what's up with this thing?

View 4 Replies View Related

Forms :: Currency Rounding For Retail Dollars

Dec 14, 2013

I need to create a SIMPLE solution for Retail rounding. Below are is an Example of Detail lines in a DataSheet Form that represents the price for an Item.

QuantityXUnitRate = Quantity * (UnitRate*Pct)
$14.0049 = 1 * $10.53 * 1.33
$14.0049 = 1 * $10.53 * 1.33

I need the price to end with a Currency Decimal of $$$$$.x9 for all prices. The example above rounds to $14.00 for with 2 digit rounding or if I use 3 digit rounding $14.01.

I need it to be rounded with a .x9 either $13.99 or $14.09 or to the nearest .x9.

Subsequently, every Detail line we calculate in the Datasheet needs to end with an .x9 for Retail Pricing.

There are thousands of different UnitRates (costs), and the Pct (Percent of Markup) can also change.

Additionally, I sum the Detail Lines and the Totals need to match. One of the Sum Fields is Calculated as

=Sum(IIf([taxable]=True,Nz([QuantityXUnitRate]),0))

This ends up Totaling to $28.01 which is wrong for 3 Digit rounding of the Detail Line, because $14.01+ $14.01 = $28.02.

I also tried Rounding

=Sum(IIf([Taxable]=False,Nz(Round([QuantityXUnitRate]),0),0))

And after that I still got the wrong amount $28.00.

I tried this with 2 instead of 0 with no difference

=Sum(IIf([Taxable]=False,Nz(Round([QuantityXUnitRate]),2),2)),

I know one of 2 refers to the Nz and the other to Rounding, but I not sure which is which at this point.

If the last significant digit need to be .09 or .19 or .29 or .39 or .49 or.59 or.69 or.79 or.89 or.99 then everything should Sum up correctly or I hope.

Here is another Example
QuantityXUnitRate = Quantity * (UnitRate*Pct)
$14.0049 = 1 * $10.53 * 1.33
$22.2642 = 1 * $16.74 * 1.33

However, I get the correct Detail Line of $14.01 and $22.26
and using =Sum(IIf([taxable]=False,Nz([QuantityXUnitRate]),0)) same as above, I get the correct amount $36.27.

How to make a with a SIMPLE solution where all Decimals end up with a .x9 and then I'll tackle the Sum if necessary.

View 14 Replies View Related

General :: Desktop Services Template Changing Currency To Pounds From Dollars

Nov 13, 2013

How would I go about doing this?

View 6 Replies View Related

Positive And Negative Amounts

Dec 20, 2005

I need to make a coupel of statements that display the total (sum) of negative amounts and positive amounts from a single list of results which comtains both negative and positive values. The two answers need to be displayed in the footer of a report.

View 12 Replies View Related

Adding Amounts Per Record

Jun 25, 2006

Hi to all!
I face the following problem: I cannot add amounts per line!
I have one table consisting of many different columns (fields), some of them having amounts. I want to update this table by doing the following:
let's say I have the following records:
1/6/2006 A 12
1/5/2006 B 20
15/6/2006 C 21
22/6/2006 D 17
25/6/2006 E 30.
I want to use a filter (e.g. the date being between 1/6/2006-30/6/2006), sort the records descending according to the amounts and then add in another field of the table A, B or C, according to whether the total amount including the specific record is above 70% of the total or above 90% of the total. In the specific example I want the following result:
25/6/2006 E 30 A
15/6/2006 C 21 A
22/6/2006 D 17 B
1/6/2006 A 12 C
Their total amount is 30+21+17+12=80 and the percentage of each record is: E:0.375 , C:0.2625 , D:0.2125 and A:0.15. Their adding percentage is
E:0.375 , E+C:0.6375 , E+C+D:0.85 and E+C+D+A:1.
How can I do that? I calculate the percentage of each record in a query, but how can I calculate their adding percentage, so that I can use an if-clause in an appending query?
Please help!!
Thanks in advance for your time

Panagiotis

View 4 Replies View Related

Rounding Dollar Amounts

Nov 9, 2006

Hello Everyone,

I'm using the following on a form in my database:

=(-Int(-Sum((IIf([LineTaxExempt]=Yes,(([Qty]*[Price])+([ShippingHandling])),
((([Qty]*[Price])+[ShippingHandling])*1.06))*100))))/100

In this case, I want to calculate a total for an order. If the
LineTaxExempt field is checked (meaning tax exempt), the total is
figured by the ([Qty] field X [Price] field)+the [ShippingHandling]
field.

If the LineTaxExempt is not checked, then the above total, including
shipping, is multiplied by 6% sales tax or 1.06. I then want this
value rounded up to 2 decimal places. This control is a running total
of the order.

This works most of the time, but I have run into a few rounding errors.

For example:

With exempt checked

1(Qty)X27.90(Price) + 6.44(ShippingHandling) gives me $34.35
instead of $34.34

With tax exempt checked (and multiple lines)

2(Qty)X141.02(Price) + 0.00(ShippingHandling)
10(Qty)X4.12(Price) + 0.00(ShippingHandling) gives me a
grand total of $323.25 instead of $323.24

It's frustrating because it seems like it works part of the time and
rounds wrong the other part of the time. By "rounds wrong", I mean I
set it wrong to round.

Is there a better formula to use in a form or query to round up to 2 decimal places other than the
=-Int(-Sum([Fieldname]) * 100) / 100 method that will give me the right results?

Any help/examples would be greatly appreciated on how to
modify this to give me the correct result each time.

Here are my field types:

QTY
-------
Field: Double
Format: Standard
Decimal Places: 2

Price
----------
Format: Currency
Decimal Places: 4 (some unit prices are 4 digits--Example $1.2525)

ShippingHandling
-------------------------------
Format: Currency
Decimal Places: 2

Thanks,

-Chad

View 3 Replies View Related

Linking Massive Amounts Of Files

Jun 10, 2005

Hi, it's me again, the guy who works with the Amusement ride industry. I'm trying to set up a way to ogranize all our saftey bulletins that we get.

I've already looked into indexing but the major problem is that the user will need to be able to search on multiple criteria (for example the Tilt A Whirl was made by three different manufacturers). Also I can't download that nifty google tool bar because we're running win 2000 without service pac 3.

Access seemed like a great alernative, but I have about 300 or so files I need to link. Is there a faster way of linking these files than going through and doing them one by one?

I have a pre-made index the guy before me made in exel. Most all the files are in PDF format if it helps at all.
Thanks.

View 4 Replies View Related

Amounts In Text For Invoices....Urgent....

Feb 14, 2005

Is there any way we can convert the amount in numeric form to text format Eg: $ 1000 as Thousand Dollars.
I need this for generating invoices.

Thanks in advance for the help...

Rajesh

View 2 Replies View Related

Queries :: Only A Certain Amounts Of Row With Update Query

Feb 14, 2014

possible to have an update query to only update 20 rows with a new data in the fields? And, how do I pass the information that I want to update only data from a certain date.If I have the word Cat and want to update it to Dog, but only with the dates 12-01-2014, where do I need to put the date part (also possible to do this for dates that are greater than 12-01-2014).

View 8 Replies View Related

Queries :: Show All Amounts Past Due Date?

May 10, 2013

I have a table in this manner:

Customer | Debit Amount | Credit Amount | Date of Transaction

My goal is to have the customer's name | (Sum of Debit amount MINUS Sum of Credit Amount) and it will only show if it is past a certain due date.

View 1 Replies View Related

Tables :: Avoiding Currency Format When Storing Amounts

Feb 15, 2013

I want to store currency amounts but not use the currency format. My DB is set to use all currencies and provide in all currencies.

Do I use the SINGLE data type?

View 1 Replies View Related

General :: Adding Decimal Amounts To A Percentage Expression

Jan 4, 2013

I'm having difficulty in adding decimal amounts to a percentage expression.On a form in a text box I have a percentage expression of 73.38%. In a combo box I have a drop down menu of: 0.75, 0.5, 0.25, 0, -.25, -.5, -.75With another text box I would like to have the decimal amounts added to the percentage. For example, 73.38% + .5..Which will equal in the text box: 73.88%

View 2 Replies View Related

General :: Formatting Columns To Not Round Dollar Amounts

Mar 7, 2013

I've run into a situation with our Access Database where sometimes when we import information into it from an Excel sheet the dollar amounts get rounded out. For example, the amount of $726.68 shows as that in the database but when you click on the field/cell it's in it shows as $726.6799. It doesn't do this for every field which is weird. The data from the excel sheet is not roudned out either, it shows the amount as $726.68 so it appears to be something funny going on with Access. Under the formatting area, the data type is set as currency and format is currency.

View 8 Replies View Related

General :: How To Sum Amounts In List Box Column And Show Total On Main Form

Oct 19, 2012

How can I sum the amounts in the list box column and show the total on the main form?

View 2 Replies View Related

Summing Queries.

Jul 14, 2005

i'm going to start this by telling what i am working toward in the end as it is where the problem obviously stems from.

i am trying to create a report which will tell me how many times 'hcapp' is in a table. there are three different fields where 'hcapp' can be placed...i can easily create three different queries which return how many times 'hcapp' is mentioned in one field, but i have yet to figure out how to look through all three fields in one query...is it possible? if not, how can i have a report call all three of these queries and then sum the total?

View 4 Replies View Related

Summing Queries.

Aug 3, 2005

i've a question that hopefully has a simple answer.

i have a query that returns three different numbers (each in its own column)...anyway, how can i make it sum the number of these three numbers...the best case scenario would be if i could have a fourth column appear in the results of the query with the sum...

it would look something like this

num1 num2 num3 sum
1 2 3 6

View 1 Replies View Related

Summing Query.

Aug 18, 2005

hello there...ive a question...i have a summing query which searches for the word "processed" in a specific column of a table...currently if "processed" is not found within the column the query will not return anything...is there a way to have the query return a zero instead?

View 3 Replies View Related

Query On Summing

Feb 15, 2007

When i'm summing up values in a column like "Hours:[production]+[nonproduction]" how can i have access treat blanks as zeros? I tried
"Hours: IIf([nonproduction]="",0,[nonproduction])+IIf([production]="",0,[production])" and it just gave me a #value error

View 2 Replies View Related

Summing Many Yes/no Fields

Jan 9, 2008

Hello, I am a newbie access user, so this might be a stupid question, but here it is:

I have a lot of yes/no fields labeled "10/1", "10/3", "10/5", ... (dates)
They are to record attendance on particular days.
I want to make a query that gives me the sum of a student's attendance and I have no idea to go about that. Any help is much appreciated.

Thanks!!

View 1 Replies View Related

Sum Function Not Summing?

Mar 17, 2005

Can't believe I'm posting this :o Have tried every permutation I can find, including the previous requestes from other members, but it still won't work.
I have a subform on whose footer I've placed a text box to sum a valu, [SubTotal]. On the main form I've set the control source of a text box to the text box on the subform, and it just won't work :mad: Can someone please have a look at this and put me out of my misery?

Thanks, Lol :D

View 13 Replies View Related

Summing #s In A Query

Sep 8, 2004

Hello,

I'm trying to sum numbers in a query. The table that Im summing from is set up as follows. The 1st column is just an autonumber. The 2nd column is an individuals. The third column is a date. The fourth column is a transaction (Buy or Sell). The fifth column is an amount (in $). and the 6th column is an Account (RRSP or RESP).

I would like to sum all the buys for each individual for each account. However I'm finding this difficult to do. Here is what i have so far. It sums all the 'buys' for the entire table.

SELECT Transactions.[Customer ID], DSum("Amount","Transactions","Transactions.Transaction=1") AS Expr1, Accounts.[Account Type ID]
FROM Transactions INNER JOIN Accounts ON Transactions.[Account ID] = Accounts.[Account ID];

Thanks very much,

C

View 7 Replies View Related

Summing Rows?

Nov 13, 2006

Hello All

I am making an attendance program for my college. When a user swipes their ID a record is set in an Access database. The column headings are the users name, classID and so forth followed by a column for each week of class. What I need to do is write a dynamic query that will sum the the values through the weeks for a particular student. Any suggestions?

View 2 Replies View Related







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