I have a crosstab query that pulls revenue the first month after the start date of the program.
Some of that revenue is in January, some February, etc. I am trying to combine them to be in one field, either in
the query itself or in the resulting report. How do i do this? Currently I have tried this: =Sum(Nz([1],0)+(Nz([2],0)+Nz([3],0)+Nz([4],0))) in the report but it hasn't worked.
Thank You!
I am creating a database in order to log in the location of certain files based. I began by creating an entry form with fields for "Individual", "Committee", "SubCommittee", "Issue", and "Location". Once entered into the form, the data is stored in a table. While I managed to do this with relative ease, I also created a search form that mimics the entry form in requested information.
However when creating the query to conduct a search I am missing data. Basically the query pulls information from the "Location" table, the "Individual" table, and the "Issues" table. Unfortunately if the "Individual" is left blank on the "Location" table, even if I am searching by issue, the query will exclude this entry in the final output. In otherwords, if I am searching by an issue, say education and there is an entry that is not related to an individual (in the Location table), that entry will on be displayed by the query, eventhough it falls under the parameters of the search
Any suggestions?
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).
Hi all, I am utterly unsure if what I want to do is even possible:
I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up).
There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa.
I want to make another crosstab query which performs a calculation. To keep it simple:
If (RewCOCredit>WrapCOCredit) Then
5-RewCOCredit
Else 'WrapCOCredit>RewCOCredit
5-WrapCOCredit
Please help!!!
I have been all over the internet and trying different things for hours to no solution. I have created a form (Code) which I am using to auto fill two categories in a form using dlookup. Both categories fill perfectly but I cannot get the information to transfer to a query in order to capture the information in a report.
These are the dlookups I've been using and the categories I'm attempting to capture are "Description" and "Category"
=DLookUp("Description","[Code]","[CodeID]='" & [ViolationCombo] & "'")
=DLookUp("Description","[Code]","[CodeID]='" & [ViolationCombo] & "'")
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 RelatedWhen 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
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
SELECT Commissions.TransactionID, Commissions.OriginatorID, Commissions.AmountPaid, Commissions.DatePaid
FROM Commissions
WHERE ((Commissions.DatePaid)> DateAdd("d", -32, Date()));
I want to add up commissions paid in a month using a simple query and im not sure how to proceed from here. This example will pull records for the last 32 days, but now how do I do the sum commissions.amountpaid to work?
Thanks for any help.
Scott
I have 2 fields, Accounts, Accounts CMO. I need those two fields to show in the same table in a new field called Total Accounts. I am summing them, but getting irregular results, some are right, and some are 4 times more than they should be? How would you go about doing this, maybe I am missing something.
View 1 Replies View RelatedI have a query and one of the fields is numbers. When I run the query I would like for that column to total the numbers. How can I do this and the have the total appear in the Query?
View 1 Replies View RelatedHello! 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
Hello Everyone,I am trying to perform a sum of records for the table belowSeatType| Ext rent | |Internal rent | Work Transfer 2 2VendorOther 2 3LOB BRF GTI BRF 2 5Displacement so that when I run the query I get the following followingSeatType| Ext rent| |Internal rent| | Total |Work Transfer 2 2 4VendorOther 2 3 5LOB BRF GTI BRF 2 5 7Displaceme What I mean is that from the first table how is it possible to get the results below. The query would generate a new column and place the sum of the data in the each row(record) in the same row in the total column. I would be really grateful. Have been bashing my head all day over this. PS I dont know any VBA. Is it possible without VBA?Thank you.
View 5 Replies View RelatedI have a sum query issue I am trying to work out and I have trouble trying to coordinate it.I am trying to build sum queries inside one select query that would allow me to sum point values in a separate table. The problem I am foreseeing is that I have three tables all connected with realationships. The structure with the fields is below:
Table1:
Test Case
Description
...
Table2:
Run
Description
test_case
...
Table3:
Task
Points
Group (Run)
Date1
Date2
Date3
...
Relationships:
Table1.Test_case-->Table2.test_case ( 1-many)
Table2.Run--> Table3.Group ( 1-many)
I want to be able to sum the total amount of points in a test case. Currently I am able to sum the number of points per run by doing a sum query on SUM(Table3.Points) in a select query that queries Table2 and Table3. But I try to do that with select query for Table1 and Table2 the query returns the same sum for all test cases. That sum is equal to the point total for the 1st entry in Table1.
Can I add a criteria somehow to make the sum work for each Test Case entry in Table1?
Also in regards to this same summing issue I spawned a second issue. I wanted to run a sum query on Table3 that would allow me to sum all the entries in that table that had valid entries in the Date1 field. I tried the following criteria.
Not IsNull([Date1])
The query came back with the same output as the above sum query.
What I am trying to accompolish essentially is the to get two sums out of the query. The first sum would be to sum all of the points totals for each Test Case. The other being able to sum point totals based of if a Date1 Field being filled in for each Task.
I am pretty sure I can do this in one query and just manipulate the data in the form of expressions and sum queries.
I am still searching through this forum for valid suggestions. But any thoughts will be welcome.
Thanks in advance.
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?
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.
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...
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 RelatedI 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.
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.
:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....
First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.
When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)
The SQL was written by Access not by me. :)
Here is an example of the Crosstab SQL (which is using a previous query):
TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;
-----------
qryTest2 SQL: (Grouping by to remove dups)
SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;
Thanks for you time! :)
I have a database which among other things records how jobs are received i.e.: Telephone, Email, Mail, Facsimile or Web.For each client I want to identify the percentages of each method of receipt against the total of jobs received and during different time periods.I have created a make table query for all jobs received between variable dates for a client entry of the name of the client and the start and finish dates are required to run the query.
I have a crosstab query set up to count each method of receipt and a final query to work out the percentages using the total from the crosstab query fields divided by the total of all methods.I have a macro set up to replace the table with new data when I want the stats for a different client between new dates, therefore the different methods of receipt may vary for the less active clients i.e.: they may only have telephone and email .
My problem is if I choose a client where we have not received a job by a particular method (say web or facsimile), the last query working out the percentages has fixed names to cover each method but naturally produces an error when it cannot find a corresponding method of receipt. I have experimented with NZ() without success.My question is can I either have preset standard names of the column field in a crosstab query? Alternatively in the query calculating the percentages, can I include code to ignore a non-existent field in the crosstab query.
Is it possible for someone with access xp to look at reports made in an .mde file.
View 3 Replies View RelatedI want to create a report that will generate from a specific date then have as the second criteria i want the date to change to what ever the current date is
Is this even possible or am i just wishing????
Anyone able to help? :)
I've got a table with CAR_ID(pk), supplier_id, Company_name, KPI, and warehouse. I need it to do a count total for me.
I.e.
query gives me this:
s4001, 1111, ibm, ship, atlanta
s2001, 1111, ibm, ship, atlanta
s3001, 1111, ibm, price, atlanta
s5001, 1111, ibm, price, atlanta
s6001, 1111, ibm, quality, atlanta
I need it to do this
1111, ibm, ship, atlanta, 2
1111, ibm, price, atlanta, 2
1111, ibm, quality, atlanta, 1
So if the supplier_id, KPI, and warehouse all are the same then it just counts total number of CAR_ID
Hello
I have a report reading off of a query, the query shows an #Error
whenever the 0 spend is divided by the 0 potential. In the report i would like
this #Error to show as N/A. I tried =IIf([BY PROGRAM BY UNIT 30 DAYS.AdminRate]="#Error","N/A",[BY PROGRAM BY UNIT 30 DAYS.AdminRate]) but it doesn't work. can you suggest something else?
thank you