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 Replies
ADVERTISEMENT
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 7 Replies
View Related
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
Jun 19, 2006
:confused: Hi everyone,
I have one table that I want to query. It's a basic table that contains:
MYKEY, AMOUNT
1234430, 100
1234435, 200
1234435, 100
1264435, 300
1264430, 200
1154435, 100
1154435, 219
1294430, 983
2010, 845
........ so on and so forth
Now, I simply want to GROUP MyKEY ENDING with 4430 and 4435 into one line and SUM the AMOUNT, thus showing like this:
MYKEY, AMOUNT
1234430and1234435, 100+200+100 --> (123 being the first common group)
1264430and1264435, 300+200
1154435and1154435, 100+219
etc etc.
i tried using simple sum and group by but does NOT work:
SELECT MYKEY, SUM(AMOUNT)
FROM MYTABLE
WHERE MYKEY LIKE ('*4430') OR ('*4435')
GROUP BY MYKEY
It does NOT ROLL-UP the 4430 and 4435 together.
QS: do i need to use VBA for this?
QS: do i need to have subqueries?
QS: please help?
Thanks!
View 4 Replies
View Related
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
Oct 27, 2005
Hello! I'm new here, and I'm back into Access after a few years of not using it.
What I'm trying to do seems simple, but I can't seem to get the sytax down.
I have 6 columns that have dollar figures in them. I want to get the totals for all 6 columns to show in one field. I'm using the sum([field]) to total the individual columns, but I can't seem to get all of them together in one sum. Is this possible?
Thanks!! :D
View 8 Replies
View Related
Mar 1, 2007
Hi all, sorry to bother you all, especially if this has already been answered, but I can't seem to find the solution to it. Assuming that what I've been doing has been correct that is.
I have three (3) tables with the following fields (where PK is primary key);
[tbl Ingred]: IngredID (PK), ProdID_fk, CompCode, IngredName, Weight, Symbol, RP01, RP02, RP03, RP04, RP05, RP06, RP07, RP08, RP09, RP10
[tbl Prod]: ProdID (PK), ProdName, FP, BP, SBU, ProdNotes
[tbl RPCalc]: RPs, RPs_long (PK), Sym, Limit, Sym_1, RP_1, Comp_1, Add, RP_01, RPNotes
[tbl Prod] has a one-to-many relationship with [tbl Ingred]; that is, for one ProdID, there can be many IngredIDs. The tables are designed so that the final user-displayed form is 'user-friendly'.
I've made a qry called [qry Qcalc] used to calculate the the Quotation (Q) of each of the 10 RPs (RP01 - RP10). Which simply for the first one is; QRP01: [Weight]/[tbl RPCalc_1].Limit, where the [tbl Ingred].[RP01] is linked to a corresponding [tbl RPCalc], by the [RPs] field. As for one [RPs] there may be multiple [RPs_long]. The way it is set up is so that the [RPs] and [RPs_long] are mapped correctly, to be used for calculations.
However, I am now stuck with a problem. I need to sum up these QRP values ([QRP01] - [QRP10]), based on (at least?) two conditions. The user can enter in their RPs; e.g. R10, R20, R21 in any of the [RP01] to [RP10]'s, and in any order.
What I need is to be able to obtain a sum of the Q values for each [RPs_long] (e.g. R10, R20, R21), rather than being the sum of the fields [RP01] to [RP10]. Also, each [RPs_long] may or may not be able to be summed up, depending on another field [Add] which has a "Yes" or "No". "Yes" is for it being able to be summed up, and "No" for it not being additive.
So the end result would be; for each [ProdID], there will be multiple [IngredID] where each will have up to ten (10) "RPs" (that being the [RP01] to [RP10] fields). These inputted "RPs" are then related to [tbl RPCalc] to be able to obtain the Q-values. If the Q-Value is not greater or equal to 1 (>= 1), then the sum of the Q-values for each [RPs_long], provided that the [Add] field is "Yes", will then be able to determine what are the final output RPs to be used, i.e. for the sum of the Q-values are greater or equal to 1 (>= 1), that [RPs_long] will be used.
I've been stuck on this for quite some time, and I know that if I change the whole table structure (like having a field for each RPs in the [tbl Ingred]), I might be able to make it much easier for myself. However, by doing so, it will not be able to offer as much flexibility in the calculations, and also, may not make it as 'user-friendly'. Another thought was maybe to create another table with just the [CompCodeID] (based on [tbl Ingred].[CompCode]), [Weight], [RP], but I'm not too sure how this will work either, but I might give it a shot if this current method doesn't work.
I hope that this made sense, as it is all quite complex (and confusing) to me. Any help on this matter will be much appreciated. Thanks in advance.
View 2 Replies
View Related
May 12, 2005
I'm trying to sum revenue by unit by program by segment but it's seem to be summing it by all units or all programs. my joins
are the first option egaual to both tables. it's a select query and only has two tables. the unit, program, segment are all 'group by' and the revenue is 'sum'. could my joins be wrong or is there another reason i'm not getting the sum by unit by program by segment?
View 2 Replies
View Related
May 13, 2013
I have a linked table. The table looks like:
Course 1 Name | Course 1 Grade | Course 2 Name | Course 2 Grade |
Student 1
Student 2
Student 3
I need to write a query that will consolidate all the courses (15 total) into multiple rows like this:
Course name | Course Grade
Student 1
Student 1
Student 2
Student 2
What is the best way to do this?
View 8 Replies
View Related
Apr 1, 2014
[AR&R Hours]+[PTO # of Hours]+[Meeting/Calls # of Hours]+[System Issues # of Hours]+[BAU Training # of Hours]+[Exception Processing Hours]+[AIG Related Activities]+[Admin Work Hours]+[Migration Hours]+[Project Hours]
Need the fastest and easiest way to auto calculate the above in a field on a form. i can get it to calculate in a query but need it to do in the form as well.
View 14 Replies
View Related
Aug 29, 2014
I have 4 revised ship date columns, an est ship date, and an actual ship date. I have a column summing days late for est ship date and one summing revised days late. The expression I am having problems with is to calculate the total days late for the revised. I want it to check the 4th revised and if it has a date calculate from that date. If not I want it to go to the 3rd and calculate from that, and so on. I have pasted the code I was trying to use. It just goes all the way to the bottom of the code and calulates from the 1st revised ship date. I have also attached a jpg of the layout of the columns. I also want to do this in the query using expression builder.
Days Late Revised: IIf( [4th Revised Ship Date] <>null,(DateDiff("d", [4th Revised Ship Date] ,
[Actual Ship Date] )-DateDiff("ww", [4th Revised Ship Date] ,
[Actual Ship Date] ,1)-DateDiff("ww", [4th Revised Ship Date] ,
[Actual Ship Date] ,7)),IIf( [3rd Revised Ship Date]<>null ,(DateDiff("d", [3rd Revised Ship Date] ,
[Code] ......
View 1 Replies
View Related
Sep 30, 2014
I have a transactions table which records a qty number (- or +) and references particular location details (location, room, rack, shelf etc). My question is how do I sum the qty for the same product at different locations. So my user sees that product A has a qty of X in location C and qty Y in location D. I can sum the transactions table qty which does give me a sum based on a single location but each location has multiple racks, shelves etc. I need to know the sum of transactions for the same product for exactly the same location, room rack etc
View 3 Replies
View Related
Jul 21, 2005
I am trying to display five columns from three seperate tables. I thought the GROUP BY operator would fix this, but I have more than one column to select. The query is fine but I have redundant fields showing. Oh, I'm not worried about the calculation "[employee.salary]*0.1 AS Salary_After_Raise." I just want to concentrate on narrowing the fields down first.
SELECT ID, FirstName, LastName, Salary, [employee.salary]*0.1 AS Salary_After_Raise, City
FROM worksat INNER JOIN store ON worksat.store = store.storenumber, employee
WHERE worksat.store = store.storenumber
GROUP BY city, id, firstname, lastname, salary
ORDER BY city;
View 1 Replies
View Related
Jul 21, 2005
I am trying to display five columns from three seperate tables. The query is fine but I have redundant fields showing. I thought the GROUP BY operator would fix this, but I have more than one column to select.
SELECT ID, FirstName,LastName, Salary,City
FROM worksat INNER JOIN store ON worksat.store = store.storenumber, employee
WHERE worksat.store=store.storenumber
GROUP BY city, id, firstname, lastname, salary
ORDER BY city;
Thank you.
View 2 Replies
View Related
May 30, 2006
I am trying to produce a report or query that lists 11 attributes for several projects measured each month over the duration of each project. i.e. It is required to have the attributes for project A listed (horizontally) for months 1,2,3,...etc (vertically) followed by those for projects B for months 1,2,3,4,5,6... etc. Can anyone advice on how to do this please?
View 2 Replies
View Related
Jan 21, 2015
I have a report with the fields
Firstname_
Surname_
[field1]
[field2]
etc etc (they're not called field1/2 but they're irreverent at present.
On the database people can be entered multiple times so I may have
Bob Smith
Jimmy Carter
Bob Smith
Jackson Smith
Tim Jones
Jackson Smith
Bob Holmes
Now I now with the group function I could group by the firstname_ field but the results would group like the following
Bob Smith
Bob Smith
Bob Holmes
Jackson Smith
Jackson Smith
Jimmy Carter
Tim Jones
And if I do it by surname, well I am sure you get the drift.
So how can I sort so I would get the following
Bob Smith
Bob Smith
Bob Holmes
etc etc.....
View 2 Replies
View Related
Mar 24, 2015
I'm trying to construct an SQL crosstab query that will output data like the picture I've attached in the .zip file.
The four variables from the data table would be [Client Accounting].[Marketer] (the left vertical column), [Client Accounting].[Closing Date] (the higher level horizontal column grouped by month), [Client Accounting].[Write Off] and [Client Accounting].[Refund] (the lower level horizontal columns as sums)
The totals column at the bottom and the two vertical columns at the right would be made in the report and wouldn't need to be in the query.
This is what I have so far but I don't know how to add a second TRANSFORM statement to be included and grouped by month!
Code:
TRANSFORM Sum([Client Accounting].[Refund]) AS SumOfRefund
SELECT [Client Accounting].[Marketer]
FROM [Client Accounting]
GROUP BY [Client Accounting].[Marketer]
PIVOT Format([Closing Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
