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 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
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
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
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
Aug 31, 2014
I have 2 tables:
-tblServices
-tblPricesPeriods
Each of the tables has fields for:
-StartDate
-EndDate
The logic behind the database is that each service is contracted for certain period, from StartDate until EndDate (if EndDate is not given - null - it means it is a permanent contract). For each service, we need to input prices. As prices may change during time, we may have different prices records for a service - we need to have the service period broken down into subperiods with different prices. So we need StartDate and EndDate for each period for which particular prices are valid (again, if EndDate is not given - null - it means the prices are valid "forever" into the future from the StartDate).
My problem is: I want to have a check that would return the number of services which period (tblServices) is not fully reflected in the prices periods (tblPricesPeriods). Those would be for example:
-gaps between subperiods (subperiod1 finishing 30.09.2014 and subperiod 2 starting 3.10.2014)
-service period (tblServices) limited by EndDate (not null), while prices period for this service (tblPricesPeriods) having null as EndDate
-start date for service (tblServices) earlier than earliest start date for prices periods for this service (tblPricesPeriods)
-...
Preferably, I'd like to use a normal query for that (or set of queries), but I'm opened to other options.
View 7 Replies
View Related
Jul 24, 2013
I have a query which displays sales and cash deposits for the day. I want a sum of all cash deposits.
So it looks like this..
Date________________Amount_______________SumOfAmou nt____________Exp1
17/7/2013___________$55___________________$55__________ __________$55
17/7/2013___________$22___________________$22__________ __________$22
I tried doing it by two methods.. neither of which are working.
In the Amount field, I put it as a Total: Sum. I also tried creating a new field which is an expression which sums up the Amount field. Neither are summing it and just displays the value.
View 1 Replies
View Related
Mar 17, 2014
I have a number of querys which search by month and year, I want to be able to sum up 3 of the querys together and display this in a text box on a form... I have tried dsum etc but I can't seem to do it...
So basically I want the totals which are dsumed into the same form to calculate together in a different query if required... This would be simple except one of the months if from the previous year...
View 2 Replies
View Related
Jan 28, 2014
Creating a Query that would give me results showing in the attached file column D. I am trying to build volume incentive database to track how much we are going to save once we hit certain volume and rate drops.
View 2 Replies
View Related
Jan 15, 2015
I have a a dataset that has recorded encounters with a number of Banded (identified) animals. It is currently setup so that each encounter is its own row; with all the information, including "Band_Num" repeated, but "Encounter_Date" being unique. I wanted to query the table and get it to display the results as "Band_Num" on a row, followed by x columns for with each unique "Encounter_Date" represented. I thought a "Group By" function would work, but that requires a sum, which is not useful.
Complicating this is the fact that not every individual is seen a set number of times, so Animal 1 might be seen 3 different dates, but Animal 2 only on 1 date.
View 4 Replies
View Related
Nov 19, 2013
I have a query that is working, but would like to add some extra complexity to it.
The query currently uses a search form for input with multiple fields, and displays all the results perfectly. I have a tick box which I can then filter the results so it only displays results where there is an entry in "PhoneMobile1". This is working, however I want to expand this filter so it can use a combo box if possible.
Currently I have the following code from a tick box:
IIf([forms]![ISISNavigationMain]![navigationSubform].[Form]![txtHasMobile]=-1 And [Candidates].[PhoneMobile1] Is Not Null,True,False)=[forms]![ISISNavigationMain]![navigationSubform].[Form]![txtHasMobile]
The issue is that there are three fields for Phone Numbers (PhoneMobile1, PhoneMobile2 and PhoneMobile3), and I'd like this query to be able to filter on these columns also.
e.g.
Combo box values:
Has Mobile, No Mobile, All
the "Has mobile" choice should display all results if there is an entry in any one or more of the PhoneMobile fields, "No Mobile" selected should display all results where there is no entry in any of the three Mobile fields, and All should display all results.
View 3 Replies
View Related
Nov 29, 2013
I've got two tables in my Access 2010 database - both are identical in structure, the difference being one (Dove) contains old data and the other (Dove Data File) contains updated data. The primary key for these tables is "TowerBase".
I want to use an update query to update only the changes from the Dove Data File table into the Dove table - but for certain records only.
I've managed so far to get a query which displays only records that have changed in a particular field (Bells). Up to here I think is all good...
SELECT [Dove Data File].*
FROM [Dove Data File] INNER JOIN Dove ON [Dove Data File].TowerBase = Dove.TowerBase
WHERE (((Dove.Inactive)<>True) AND ((Exists (SELECT NULL
FROM Dove
WHERE [Dove Data File].TowerBase = Dove.TowerBase
AND [Dove Data File].Bells <> Dove.Bells))=True));
But now I want to add in criteria and this is the bit I'm struggling with. I need this query to now display only records where there is no associated record in my Visit Dates table. In other words, the Visit Dates table has the "TowerBase" ID along with a visit date. These records I do not want to see in the query, as I don't want to update any changes for these from the Dove Data File table to the Dove table.
View 1 Replies
View Related
May 20, 2015
All using access 2010. I've have a query in a database that is given an error: query too complex or system exceeded resources. I've been running this query once a week and all of a sudden; errors. I have changed one unrelated table. I've gone back to an old database and its giving the same errors. database on the server is giving off this error and it's only when I run this particular query.
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 5, 2014
I have a query that pulls info from a table. One of my fields is frequency and in that field i have weekly, annually, daily, etc.
How can I make the query sort starting at the daily one and move out to annually?
View 2 Replies
View Related
Aug 1, 2015
I want to sort the results of a query as follows.
The query returns 4 fields
ID, Field1, Field2, Field3
For the first 500 rows I want to sort by Field1 with the remaining rows sorted by Field3.
I can identify the first 500 rows bi ID being less than 501.
I've done something similar where I've displayed a value of a field in a different order by coding something like
ORDER BY Iif([field3]=9, 0.5, [field3])
which sorts the 9's first followed by the rest in ascending order but can't work out what to do here.
View 14 Replies
View Related
Mar 21, 2013
i intially, I wish to have attendance stats for 3 groups of people over 2 days in a report
i have the two queries for the raw data, but wish to combine into a report that will ultimately become a "dashboard" report for half a dozen queries., and can only get one or the other to work do i need to combine the queries into a summary query of sorts?
View 1 Replies
View Related
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
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
Nov 6, 2004
I was wondering if somone could clear something up for me.
I understand that if there is a database with for example:
table_Cars(reg_num)
table_Customers(cust_num, cust_name)
there is a many to many relationship and so the inclution of
table_Order(hire_date, reg_num, cust_num)
would eliminate this.
I understand this but where I get confused is what would be done if a customer could hire out more than one car on the same order.
a Car can have MANY orders and and order can have MANY cars.
back to square one.
any guidence on this subject would be appriciated!
thanks
View 2 Replies
View Related
Oct 24, 2006
Ok I know people are tired of code breakdowns but I got this code in the company's database and it is suppose to calculate 2 days before the mailing date. now to Text98 is not suppose to land on a Saturday or Sunday. The problem is in the code it is somehow ending up with the same date as the mailing date. I didn't write this and I have a module from the good people here that I use for my date calculation but I am not allowed to add to the company's database. Though I can only edit.
Can someone let me know why this code is pulling the same date as the mail date?
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If WeekDay(Mail - 4) = 7 Then
Text98 = (Mail - 6)
End If
If WeekDay(Mail - 4) = 1 Then
Text98 = (Mail - 6)
End If
If WeekDay(Mail - 4) = 6 Then
Text98 = (Mail - 6)
End If
If WeekDay(Mail - 4) = 5 Then
Text98 = (Mail - 6)
End If
If WeekDay(Mail - 4) = 2 Or 3 Or 4 Then
Text98 = Mail
End If
End Sub
View 7 Replies
View Related
Aug 30, 2007
Hi,
I am trying to build a database to keep records of devices that I have.
The basic ERD is as so:
hXXp://img50.imageshack.us/img50/3082/erdkt3.gif
I want to create a form where the user selects the building, then is given a list of rooms in that building, the is given a list of Racks in that room.
I have tried added fields Bldg and Room in the device table. The bldg drop down would
SELECT Bldg_ID, Bldg_Name
FROM bldg;
Then in Room i did a:
SELECT DISTINCT [Room].[Room_ID], [Room].[Room_Name] FROM Room, Device
WHERE Device.Bldg_ID=Room.Bldg_ID;
Then in Rack I did:
SELECT DISTINCT [Rack].[Rack_ID], [Rack].[Rack#]
FROM Rack, device
WHERE Rack.Room_ID=Device.Room_ID;
This work except for one flaw. there where statement limits what room/rack for all bldg's or rooms in the table not just that current row.
I then started to think I need to do this in a form. But i run into the same problems. I hope this makes sense.
Send me a PM and I can send you a copy of the file.
Thanks,
Mitchell
View 5 Replies
View Related
Mar 27, 2013
I have a table called Products which has a field called "Product ID" and a few others. I have another table called "Sales", which has a field called "Sales Date".
There will be different dates where the products are sold. Here's my problem: I'm trying to make a query to find out which products make the most sales. However, doing so will also display the products separately by their sales dates. So for example, it'll show...
Product #10 / Jan 1, 2013 / 100 Sales
Product #10 / Jan 14, 2013 / 21 Sales
Product #10 / Feb 11, 2013 / 44 Sales
Product #11 / Jan 5, 2013 / 201 Sales
Product #11 / Mar 13, 2013 / 444 Sales
I'd like the products to be together so product #10 would give 165 sales for example. In the Query, Product ID from both tables are connected.
View 3 Replies
View Related
Nov 3, 2007
Hi,
I have the following situation:
I have two fields: Start Date and Stop Date and I need to create a procedure to break them down and create records.
Start Date Stop Date
01/25/07 03/05/07
Results:
01/25/07 01/31/07
02/01/07 02/28/07
03/01/07 03/05/07
Thank you in advance for any help.
View 2 Replies
View Related
Feb 12, 2007
Alrighty...
My DB contains the following tables:
VacTab - Keeps track of crew members on vacation and people covering their shifts.
CrewRost - List of all the crew members and what crew they belong to.
CrewSched - The complete schedule of what crew is working which shift (Currently programmed out until Feb 2008)
Now, I need a query which totals regular crew hours for individuals and the overtime hours they cover for other crew members on vacation. And puts the overtime hours in the correct day/shift.
I have a working query which equates how many hours an individual works with their crew, based on the CrewSched Table. This was rather complex, because we work a combination of 8 hour and 12 hours shifts.
I also have a working query which puts the overtime hours of each crew member into the correct day/shift that they cover for crew members on vacation.
What I need is a way to combine these into a single report, where I can use conditional formatting to highlight the overtime hours. The problem is, if I use a union query, the report will treat these values as such, and not highlight the overtime hours.
If anyone has an idea as to where I can start, it would be greatly appreciated.
Thanx :) ,
Matt
View 2 Replies
View Related
Dec 2, 2014
I have 4 queries that run and prepare a temporary table for which I then have a sales report generated . THis report has been active for over a year exactly as is but now the past week I am getting an error message that says "The expression is typed incorrectly or is too complex to be evaluated".I used the query designer in access to create them, here they are in SQL VIEW
My Make Table Query:
SELECT [Job Table].[Job Number], [Job Table].[Date Sold], Commission.originalCRate, Customers.[Last Name], [Job Table].Salesperson, [Job Table].Split, [Job Table].[Job Type], Commission.SaleAmount, Commission.JobCost, Commission.SPR INTO [New Sales Report Temp Table]
FROM (Customers LEFT JOIN [Job Table] ON Customers.[client id] = [Job Table].[Client id]) LEFT JOIN Commission ON [Job Table].[Job Number] = Commission.[Job Number]
WHERE ((([Job Table].[Date Sold])>#12/31/2012#) AND (([Job Table].Split)=No));
Query 2:
INSERT INTO [New Sales Report Temp Table] ( [Job Number], [Date Sold], [Last Name], Split, SaleAmount, Salesperson, [Job Type], OriginalCRate, SPR, JobCost )
SELECT [Job Table].[Job Number], [Job Table].[Date Sold], Customers.[Last Name], [Job Table].Split, [saleAmount]*[SPr] AS TotalSale4, [Job Table].Salesperson, [Job Table].[Job Type], Commission.OriginalCRate, Commission.SPR, [jobCost]*[SPR] AS JCost
FROM (Customers LEFT JOIN [Job Table] ON Customers.[client id] = [Job Table].[Client id]) LEFT JOIN Commission ON [Job Table].[Job Number] = Commission.[Job Number]
WHERE ((([Job Table].[Date Sold])>#12/31/2012#) AND (([Job Table].Split)=Yes));
[code]...
When I run the queries as a query and view data in datasheet they all work, however when I run the report I get the error message.My Access DB is a front end connected to an SQL server backend. I have never created queries in SQL just in Access.
View 1 Replies
View Related
