Help On Sums - Dates This Time + Rewriting Of Data
May 30, 2006
The Database is coming along nicely now, but in need of guidice with this one:
On the main table of the contacts I have what you could call a date of 01/01/06 and a combo box next or below that that I select what risk levels to asign the user.
Each user would have a no of year review based on the risk level:
High = 1 Year
Med = 2 Years
Low = 3 Years.
There is another date box that is blank at the moment, what I am looking for query wise or another way is on change/update the risk level is to add the start start in this case 01/01/06 (if user is Med Risk - then add 2 years) and adding risk level giving me a new date of 01/01/08 in the blank date box (this option will make the main table update.
Can anyone point me in the right direction for this one?
Thanks
Mike
View Replies
ADVERTISEMENT
May 23, 2014
I have 2 tables and one query as below
1- Order Table
OrderID | CustomerID | CusromerName | OrdDate
2- Payment Table
OrderID | Amount | PYMDate
3- Order Deatails Query
OrderID | ProductName | Quantity | Price | Total ([Quantity]*[Price])
I want to group the orders based on the sum of total and sum of payments. I made another query as following :
OrderID (From order table) | Payment (from payments table) | Total (From Order Details Query )
At the Total field I set the above fields to
Group By, Sum
and Sum respectively
I got true payments sum, however, the sum total are always duplicated.
Note that the three objects (two tables and query ) have relation (type : one to many) by the order ID
View 10 Replies
View Related
May 10, 2013
I just recently started programming in MS Access. I have some forms with lots of combo boxes. I do not want users to type anything in the boxes, but to only select stuff that is contained in the drop down menus.I found this useful code and I am using it in my combo boxes on the key down event to do what I want.
Private Sub cmb_a_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode <> vbKeyTab And KeyCode <> vbKeyReturn Then KeyCode = 0
End Sub
Private Sub cmb_b_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode <> vbKeyTab And KeyCode <> vbKeyReturn Then KeyCode = 0
End Sub
Private Sub cmb_c_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode <> vbKeyTab And KeyCode <> vbKeyReturn Then KeyCode = 0
End Sub
The issue is because I have so many combo boxes, is there a better way to rewrite the code so I don't have to repeat the same code in 20 places?
View 5 Replies
View Related
Nov 15, 2011
I have a master table which shows all transactions per record (person) over a financial year.
Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.
I need to be able to create a field which sums all expenditure per record between the start and end dates
Name Start Date End Date Invoice Date Amount
Matt 15/5/11 15/9/11 1/11/11 £100
Matt 15/5/11 15/9/11 7/7/11 £200
Matt 15/5/11 15/9/11 12/12/11 £200
In this case I would only want to sum 7/7/11 as this is between the start and end dates
I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly
(The start date and end date will always be the same per person)
Is this possible in access?
View 10 Replies
View Related
Feb 13, 2006
How can I make a query that will take a field that I already have set to record the last contact date and check it against today's date to give me the span between the two?
View 1 Replies
View Related
Nov 23, 2007
Dear all,in my table I have the following fields:- fldstartdate- fldstarttime- fldenddate- fldendtimeCan I calculate the time that is between those variables using an expression?fldenddate.fldendtime - fldstartdate.fldstarttime = "x days and x minutes"Kind regards,Hans B.
View 4 Replies
View Related
Feb 22, 2006
I have attached a really simple db where in a query I'm trying to determine the elapsed time between A sent date and a received date. Can some tell me what I'm doing wrong? Thanks..
View 1 Replies
View Related
Feb 29, 2008
Hi,
This is my first post here and I seem to like the activity on this forums. Now to my issue.
I have a table with schedules: Event and it's schedule in actual date:
Ex.
Event1 1/1/2007
Event2 5/1/2007
Event3 7/1/2007 (MM/DD/YYYY)
and I have another table which has the event name, time steps and numbers:
Ex.
Event1 1 1000
Event1 2 1500
Event1 3 1500
Event1 4 2000
Event1 5 1500
Event2 1 1000
Event2 2 1500
Event2 3 1500
Event2 4 2000
Event2 5 1500
Event3 1 1000
Event3 2 1500
Event3 3 1500
Event3 4 2000
Event3 5 1500 (I kept the numbers same for all, time steps are monthly)
Now, I can't think of away to write a query to link the dates and the time steps, eventually reaching to some output as this:
Event1 1/1/2007 1000
Event1 2/1/2007 1500
Event1 3/1/2007 1500
Event1 4/1/2007 2000
Event1 5/1/2007 1500
Event2 5/1/2007 1000
Event2 6/1/2007 1500
Event2 7/1/2007 1500
Event2 8/1/2007 2000
Event2 9/1/2007 1500
Event3 7/1/2007 1000
Event3 8/1/2007 1500
Event3 9/1/2007 1500
Event3 10/1/2007 2000
Event3 11/1/2007 1500
Thanks, let me know if there is anything not clear. Pointing me to the right direction to do this helps a lot since I am kinda lost.
View 3 Replies
View Related
Oct 6, 2013
The below function returns correct time difference between workdays. However, it is excluding Saturday as per the code.
It is calculating 06:30 am to 22:00 pm time for weekdays but I also want it to calculate the time from 10:00 to 13:30 on a Saturday.
I am trying to use the NetworkMinutes function to achieve this. However, there is a problem getting the time for Saturday.
Code:
Option Compare Database
Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : NetWorkMinutes
' Author : Rod
' Date : 13/12/2012
' Purpose : Returns the number of work minutes between two date-time arguments.
[code]...
View 14 Replies
View Related
Oct 26, 2006
I have searched the forum for this answer but no luck. :(
I'm trying to calculate the amount of WORKING time between two dates in an Access database. At the moment i am just subtracting one date from another but this gives me all of the time in between including weekends and evenings.
I need this time to be calculated in hours.
In Excel i know there is a NETWORKINGDAYS function which does something similar but with days rather than hours.
At least if i could get the working days i could then convert it into hours.
Please help!
View 7 Replies
View Related
Nov 11, 2012
We are collecting data from client files that have multiple dates (start_date, event1_date, event2_date, end_date.) For client protection purposes, we are absolutely not allowed to leave the facility with the actual dates. However, we want to know how many days have passed between each event. I thought about having the data collectors enter each date, have a calculated field store the time between dates, and then have the actual dates cleared when the record is saved or when the person moves to the next record.
View 14 Replies
View Related
Jul 17, 2013
The project I am currently working on requires me to build a database for checking off if a certain person has submitted a time sheet for a certain date. I have a table for the dates that has the fields "date" and "Submitted time sheet?". The time sheet field is a checkbox.
I also have a table for employees with fields "employee last name", "employee first name" and "location".What I want to know is how to link each employee with the dates and whether or not they submitted a time sheet.
View 4 Replies
View Related
May 6, 2015
how to be able to enter time in access form the same way as if would be an access table (1p = 1:00 PM; 1.25 = 1:25 AM etc)
View 1 Replies
View Related
May 11, 2006
Hi
I have a table with
"starttime", "endtime", "agent", "phonenumb" and a datediff calculation that calculates the diff between "starttime" and "endtime"
the "starttime" and "endtime" columns are in "10/05/2006 12:57:55" format, i.e. the column holds both the date and time
The data is stored in an sql DB and is accessed by an odbc link using Access 2003
I want to be able to run a query within access that lets me chose "agent" and a specific date or a range of dates.
Any help would be greatly appreciated
Thanks
Ben
View 1 Replies
View Related
Oct 13, 2005
I have a query which shows employee Totals it has the following fields:
Emp: [EmployeeLastName] & ", " & [EmployeeFirstName]
LogHours
Total: Sum
TotalWorkedPgs
Total: Sum
WorkDays: Sum([LogHours])/8
AvgPgs: (Sum([TotalWorkedPgs]))/(Sum([LogHours])/8)
JobType
JobEffort
-----
Job Effort is a has 3 values: My Corrections, Others, YOYO
I would like to know the:
Sum of TotalWorkedPgs when Job Effort is My Corrections
Sum of TotalWorkedPgs when Job Effort is Others
Sum of TotalWorkedPgs when Job Effort is YOYO
Because I need all of this information on a report.
Any help will be appreciated.
Thanks
View 5 Replies
View Related
May 6, 2006
I've been trying to use a sum in a query to add up the currency of several records in a field from another table but as yet have had no success. The sum simply shows all the records but on there own and not in one total :confused:
I also need to use a count to total up the number of records in a table with a certain piece of data, in this case a 1, but again it does the same as the totals and simply shows each record on its own
If anyone knows what I am doing wrong I would greatly appreciate some help and advice
Thankyou
View 4 Replies
View Related
Jul 13, 2007
This has got to be really simple but I just cant figure it out because Im a novice.
I have a dbase based on clients and each client has a subform for where you can enter payments owed and payments received.
The queries run smooth when there is data but if there are no records for the payments owed or payments received, I get an nothing and #errors when I try and manipulate the data.
Im guessing this is a null value error and Ive tried the IsNull and Nz fuctions but I cant get them to work for me.
Should these functions be placed in the query which calculates the sum?
If so, how?
For example, my query is
Field:Hearingwcb
Table:HearingShowQuery
Total:Group By
Criteria [Contacts]![wcb]
This extract the appropriate records for the current client
Field: HearingFee
Table: HearingShowQuery
Total: Sum
This sums them up.
Im guessing because the sum is null, I am getting nothing. How can I get this to return a 0. I think that should take care of my problem.
Thanks so much...its driving me crazy!
View 2 Replies
View Related
May 26, 2005
I have a report that is broken up by invoice number. I want to create a text box that will sum the lines for each invoice number. I have the text box set to add the lines, but currently it is grabbing the first value for each multiple line invoice. Confusing, I know. See below:
Invoice number Date Amount
123456789 5/23/05 23,100.00
123456789 5/23/05 1,000.00
Amount: 23,100.00 <--- I can get this to = the first line for each invoice number but not total (should = 24,100.00)
Invoice number Date Amount
987654321 5/24/05 525.69
Amount: 525.69
Can anyone help me?
View 8 Replies
View Related
Jul 17, 2007
I'll see if I can make this clear and concise.
I basically want to sum totals(or rather subtract totals) from groups in a report. The problem is that all my numbers are positive and need a way if possible to change some to negatives.
I have 3 categories set up e.g
Account Payable , Accounts Receivable and Non-Revenue
In the form used for input , there is a drop down box in which you select 1 of the three account types. There is second box in which you input the amount (always positive). This second box is common to all account types therefore no matter the account you input the dollar values into this second box.
Right now the report is summing all values in the group so that it's not subtracting account payable amounts from account receivable amounts...Any help would be appreciated, and hopefully it will be a simple solution. NOTE: i would like to stay away from writing or editing any code. Thanks in advance
View 3 Replies
View Related
Jun 10, 2005
I did a search for Gouping and sums, but nothing really helped me.
What I am trying to figure out: I have a table where I am trying to use 4 fields in a query.
Vendor, items, Cost, Date1
- I am trying to Group by Vendor, Sub-Group by Items (Easy, I did this)
- Now I am trying to Filter this by date1 (Easy, I did this)
But I would like to sum the Cost for the Groups, but not list every repeated item. For Example:
Vendor A Brush 100 1/1/2005
Vendor C Book 50 1/2/2005
Vendor A Brush 100 1/3/2005
Vendor B Pencil 10 1/4/2005
Vendor A Hat 50 1/5/2005
Vendor B Pencil 10 1/6/2005
Vendor B Pen 10 1/7/2005
Vendor B Eraser 10 1/8/2005
Vendor A Shoe 40 1/9/2005
Vendor D House 1000 1/10/2005
Doing a search from 1/1/2005 - 1/9/2005 we get:
FORM OUTPUT:
Vendor A Brush 200 2
Hat 50 1
Shoe 40 1
Vendor B Pencil 20 2
Pen 10 1
Eraser 10 1
Vendor C Book 50 1
View 3 Replies
View Related
Jul 27, 2005
I have a query calculating the sum of checked boxes of field 'A' ('letters sent') and the sum of checked boxes of field 'B' (letters received). My sum field is counting the boxes that are checked but showing the values with a negative sign in front?? How do I correct this? Also - could I create an additional field in this query to calculate the percentage% (success rate) #B's checked/#A's checked?
View 4 Replies
View Related
May 11, 2006
Hi,
I'm having a lot of trouble creating a parameter query that will work when i sum a column.
I can get it to work without a seum (code below)
SELECT "Daily VDNs".Date, "Daily VDNs".ACD, "IVR VDN Filter".NSWCTP, "Daily VDNs"."Inbound Calls"
FROM "RSS Data".RssUserRW."Daily VDNs" "Daily VDNs", "RSS Data".RssUserRW."IVR VDN Filter" "IVR VDN Filter"
WHERE "Daily VDNs".ACD = "IVR VDN Filter".ACD AND "Daily VDNs"."VDN Number" = "IVR VDN Filter".VDN AND (("Daily VDNs".Date=?) AND ("IVR VDN Filter".NSWCTP Is Not Null))
But I cant get it to work when I change "Daily VDNs"."Inbound Calls" to a sum (code below)
SELECT "Daily VDNs".Date, "Daily VDNs".ACD, "IVR VDN Filter".NSWCTP, Sum("Daily VDNs"."Inbound Calls") AS 'Sum of Inbound Calls'
FROM "RSS Data".RssUserRW."Daily VDNs" "Daily VDNs", "RSS Data".RssUserRW."IVR VDN Filter" "IVR VDN Filter"
WHERE "Daily VDNs".ACD = "IVR VDN Filter".ACD AND "Daily VDNs"."VDN Number" = "IVR VDN Filter".VDN
GROUP BY "Daily VDNs".Date, "Daily VDNs".ACD, "IVR VDN Filter".NSWCTP
HAVING ("Daily VDNs".Date={?}) AND ("IVR VDN Filter".NSWCTP Is Not Null)
However, I can use the above code without a parameter query and define a value. But life would be so much easier with a parameter query.
Any assistance is greatly, greatly appreciated!!
View 5 Replies
View Related
Aug 9, 2006
well i created a relationship between 2 tables where:tableA = invoice datatableB = company IDsi have 2 questions that i have been unable to resolve on my own. 1. i want to create a query where i am able to find all of the invoices from a single company using it's ID and add up all of the account payable/recieveables i learned how to add all of the values on a field but i don't know how to add the values on a specific row. so while i can add all of the ap/ar on the table with all of the information, i can't add the ap/ar of a single company despite the fact that my table sorts them apart (because i don't know how ><)would a code like iff([company id]= "#",add) be close to what i'm looking for? because that's all i'm been fiddling with to try to get my query to do what i want. so far i'm stuck with: AR: IIf([company ID]=1,[AR$$],0) whereas 1 is the ID of the specific company i'm trying to pull records for and AR$$ is the field that i'm trying to pull the information from.2. find a way so that i can get my table to print all of the records for a specific company based on the ID. so for example, if i wanted all of the invoices from company #9 then i would pull them out and print them. i tried expanding the + sign on the company ID table and printing it, but it obviously failed -.-"i would really appreciate any help that can be provided to me! i just started access so anyone that can link me to an article related to this would be greatly appreciated because i don't know the terminology/keywords that i should be searching for in the article page that i'm using for this case.
View 7 Replies
View Related
Aug 14, 2007
I'm struggling with a non-updatable query issue. I have a table at the category level that should allow entry but it needs to pull in information from another query. In this 2nd query, its doing group by/counts/sum's from an item level table. From reading about the dynasets and trying to fix it, it looks like this the major problem.
The item level table has information that is changing based on other user input so I don't think using a make table would work. I tried setting the queries/form to dynaset (inconsistant updates) but no luck there. It still wouldn't allow any changes to the entry field. Any clue on how I could get around this?
Main Table
Category Entry
A. . . . ._____
B. . . . ._____
Item Table
Item Category Sales
1. . . . . .A . . . . $10
2. . . . . .B . . . . $15
3. . . . . .B . . . . $10
This query built using the main table joined with a group by/sum of the item table.
Goal Query for Updates
Category Sales Entry
A. . . . . . . $10. ____
B. . . . . . . $25. ____
View 4 Replies
View Related
Oct 29, 2007
I have a query which returns records in 5 different fault categories; mechanical, service, electrical, damage and retrofit. The values the query return are based on another query and are a sum of the amount of money spent in each category.
I have put a sum fuction in the 'field' box like this; total: ([Mechanical]+[Service]+[Electrical]+[Damage]+[Retrofit])
I also have a criteria set up where every time the query runs a specific id has to be entered.
The problem is when i run the query with the function shown above i have to enter the id to run the query then it asks me to enter the id again for the 'Retrofit', it then runs the query but adds the value i have entered in the criteria for retrofit to the totals of the other fault categories!!!
If i remove the retrofit part of the sum funtion it works fine
Please help this is very frustrating
Thanks
View 2 Replies
View Related
Sep 24, 2007
Good afternoon,
I am trying to figure out how to create a cumulative sum field for a test report I am working on. On a very simple level, I can run queries to get my data into the following format:
Test # | Article # | Test Time | Test Parameters
1 | 1 | 8 | A,B,C
2 | 2 | 5 | A,B,C
3 | 2 | 7 | A,B,C
4 | 2 | 9 | A,B,C
For each test number, I want a separate report page showing the article number, test time, and test parameters. Also on each page, I want to display the cumulative test time for all test numbers up to the displayed test number, but only for the article used in that test.
For this example, page three would have test number 3, article number 2, test time 7, test parameters A,B,C, and cumulative test time on article 2 of 12 (5+7), while page four would have a cumulative test time on article 2 of 21 (5+7+9).
How can I convince Access to do this for me? So far I've only been able to have a cumulative test time for all test articles, not just the test article related to the test number. To make this more complicated, Access needs to be able to have another article added at any time without having to rework the code to create the test reports.
I appreciate any help you can give me that will get me on my way.
Thanks!
Erik
View 4 Replies
View Related