Is it possible to use the result from a calculation in a query as data for a field in the table the query is based on?
Basically, I have an event that has a start date and an end date. The table stores individual events and the query only shows those events that have a start date but nio end date.
Also, each event can restart later with a new start date and end date.
I want to use the dates to calculate the number of days duration for the event's instance and store that in the table for later use - each event is allowed only a certain number of days.
Then, I'll need to add up the number of days for each instance of an event, to see if it has reached the maximum allowed.
Here's what I have. Three tables: data, EntityCurrency, and Rate. data and EntityCurrency has a 1 to many relation ship by vendor number(1 on entityCurrency and many to data). EntityCurrency has an intermediate relationship to Rate by Currency. I need to be able to take a field in data(field = amount) and devide it by a field in Rate(field = rate). Here's what I have come up with and it doesn't work. I tried this expression as one of the fields: Expr1: [Sheet1]![Amount]/[Rate]![Rate] and it gives me an error: You tried to excute a query that does not included the specified expression 'Sheet1!Amount/[Rate]![Rate]' as part of an aggregate function. I was just wondering anyone could help me with this.
I quess it should be simple. But I couldn't find an answer or example in Access books and online. I have an access 2000 database with a Accounts Receivable table. I am building a query that returns accounts what are 60 days past due and calculating total ballance of ONLY past 60 days accounts. I managed to write a code to display 60 days past due acounts, but when I use UNION query, it calculates total from entire Accounts Receivable table, but I need only total ballance of past due accounts.
Here is my SQL code
SELECT [Accounts Receivable].InvoiceID, [Accounts Receivable].[Patient Last Name], [Accounts Receivable].[Patient#], [Accounts Receivable].InvoiceDate, [Accounts Receivable].PaymentAmount FROM [Accounts Receivable] WHERE ((([Accounts Receivable].InvoiceDate)<Date()-60)) UNION SELECT 'TOTAL', "","","",Sum([Accounts Receivable].PaymentAmount) FROM [Accounts Receivable];
I need my query to look like this.
InvoiceID Patient Last Name Invoice Date Payment Amount
23 Smith 05/01/2007 $100 25 Doe 04/03/2007 $200 Total - - $300
I'm trying to build some queries with calculations in, and I'm not able to do what I had hoped. Am I doing it wrong?
This is about sales.I'd like to firstly, be able to have a quick query, to see how many items a seller listed, with total price (this seems fine, if I count the item IDs, and sum hammer price in a query).And vice versa, by buyers.But, I'd like to sum for sellers, number of items Sold, Returned or Relisted.
I have a status (blank for active, "sold, "relisted" or "returned", and thought, I could "count", by the status = "Sold" (ie), but it won't accept the expression.
for sellers, I had hoped to have one query, with total items listed, total money made, then totals for still active, sold, returned and relisted items.
Am sure I'm doing something basic wrong, but I can't think what.
Hi, I'm a total newbie at Access, and know nothing about scripts. I've been operating at the level of using the Wizards and drop-down menus. I am trying to create a report that does two things: 1. Displays the results of queries that sum data from a table (I think I have this figured) and 2. Displays those sums as a percentage of a number that is input each time the report is run. (This is only one number that comes from a totally different place and has no prior relation to the data.) Any help/advice that anyone could offer would be much appreciated! Thanks!
I've imported an Excel file into a table and now I've created a Query from it. I now need to Add Fields (names are not in the table) and calculate totals for these renamed fields some of the answers are going to be the result of two or three fields. Any help would be greatly appreciated. thanks
I'm trying to work out the difference between 2 records both of which have a call out date [bas start date]..basically the structure is
equipment number call number bas start date 12345678 112255 1/7/14 12345678 112256 3/7/14
What i'm after is the 4th column to work out the date diff... in this case 2 days the equipment can be multiple times so i might see equipment number 12345678 - upto 10 times with consecutive dates - all of which i need to know the difference between the current call date and the previous call date..
Just started doing access at work and have a problem.
I have a database. Each record has a bunch of different fields.
In one field, the record can either be DC or DS (different classifications).
I would like to run a query which would tell me the percentage of DCs and DSs.
I have managed to create a query that counts the total of each one, but for the life of me i cannot figure out how to do the percentages.
Can anyone help?
I am using Access 2000 and my query is being made in design view
The Query has 2 columns:
Column 1 Field: DC/DS Table: Main Total: Group By Show: yes
Column 2 Field: DC/DS Table: Main Total: Count Show: Yes
Can I add the percentage section as another column (using an expression?) or will it have to be another query, that runs the above query then uses data from that?
i am trying to run a query from a form which will bring up the no of days difference between the start and end date also on the same form. The query doesn't bring back any results can someone please guide in what i am doing wrong. Here is the query SELECT DateDiff('d',[start date],[end date]) AS [no of days] FROM [booked property] WHERE ((([booked property]![start date])=[forms]![booking]![booked property]![start date]) AND (([booked property]![end date])=[forms]![booking]![booked property]![end date]));
Is there a way to calculate three different rolling averages in one query?
I just inherited a database where someone is using three queries to capture the same information only with different time frames. They were calculating a rolling three month average, six month average, and twelve month average. I would like to combine these queries into one to reduce time spent running reports from the database. All three queries are based on one table. One of the columns in that table is called "Month Start Date". That field shows the first day of the month when a call was entered. I can get the query to tell me the first month in the three month period and the first month in the six month period, but I can't get it to calculate the averages of the calls that fall in those time frames. Here is the SQL for the query I have now. When I try to run this, I get the error message that my formula is not part of an aggregate function.
Code: SELECT DISTINCT DateAdd('m','-2',(Max([Month Start Date]))) AS ThreeMonthStartDate, DateAdd('m','-5',(Max([Month Start Date]))) AS SixMonthStartDate, Max([Month Start Date]) AS MaxStartDate, IIf([Month Start Date] Between [ThreeMonthStartDate] And [MaxStartDate],Avg([All Call Rate]),' ') AS ThreeMonthAverageCallRate, LIST_WITH_TNC.Device, LIST_WITH_TNC.Model, LIST_WITH_TNC.[Item Num] FROM LIST_WITH_TNC;
I have been working on a stock database for my company. It needs to hold stock data, Any adjustments in the storesperson may do (QtyIn or Out). And allow Customer PO's to be raised (just to show prices and quantites sold to customers)
I then want to create a query to work out the total stock.
At the moment i have two queries, one to Sum the adjustments and one to sum the PO's. (The stock is not removed from the Customer PO until invidual line is shipped)
I then need to combine both these queries to work out the total stock value.
There will always be a value in the Sum of Adjustments but not in the Customer PO. So when i run the query to =SumofAdjustments - SumofCustomerPO's, if there is no record in the SumOfCustomerPo's, no value will appear.
On my master form I have a subform called invoices this is tied by Site Number to the record on the form which is has the following important information, site code. group code, exp code an amount.
On another subform via a query I have a summary of all the invoices by exp code and group code.On another subform I need to create a Income an Exp.
I have two combo boxes 1 for the Group Code, and another for the expense code what I want it to now do is look up group code exp code = Total amount. There is currently only 1 line as you will see but there will be many rows of summary data...do i need a subform for the summary?
And I need to do it this way as not all exp appears on the income and exp and I want to be able to lay out the expenses for each site differently Attached is the zip file, of the database the only record that has data is site code 0289S
My training database requires me to identify each training record in the tblEmpTrainHist table as either "Compliant" or "Delinquent". I thought a simple calculation in my query:
would do the trick. However, I did not consider the records where the employee has not yet completed the training and the field [DateReceived] is Null. There are two considerations: those employees who have no [DateReceived], but have not yet reached the DateDue (Compliant); and those employees who have exceeded the DateDue (Delinquent).
I have fought hard to try and figure this out and I have gotten 50% there.
I have a query that generates the following calculated fields through numerous calculations etc....:
[De-Merit Marks] [No Del with Major Faults]
Now the way it should work is that a supplier gets a rating (A-D) depending on both their De-Merit Marks score and the amount of deliveries with major faults. I used the following function in the query and it does the de-merit mark grading.
However I have no idea how to integrate the Major Faults part.
Basically:
0-10 = A 11-30 = B 31-50 = C 51-100+=D
If the supplier has one major fault, the rating should drop one from that reported using just the de-merit score. Two means it drops two ratings (so if an "A" on just the demerit, they would drop to a "C" and so on. Obviously a supplier cannot drop below a "D".
Can anyone suggest how these maybe acheived? I fear it requring VB code, but I am not that good with VB hence why I have relied on queries for this.
Product (Table PDZRN1) Whse (Table PDZRN1) Description (Table PDZRN1) On Hand (Table PDZRN1) Unit Cost (Table PDZRN1) Unit Price (Table PDZRN1) LastDate: Date (Table Sales_History) (using the Max function)
Where I am stuck is I want 4 additional columns to pull the last 3 fiscal years and total sales for each Product for the 3 previous fiscal years. The Field with sales amount is "Sales" it is located in Table "Sales_History", and the dates for the invoices are in the field "Date". I tried the following query, but I'm sure it didn't work because I failed to push the data back to the date field:
Field - FY2013Sales: Sales Table - Sales_History Total - Sum Criteria - <= 04/01/2012 and <= 03/31/2013
how to link my criteria back to the "Date" field, or if I'm even going down the write path.
I don't use Access too often but I'm trying to connect a table to some business intelligence software I use.
A. Date B. Price C.Ydayprice 01/01/2015 101.45 02/01/2015 104.70 03/01/2015 103.00
Simple stuff. Once I've connected to the table, I can easily do what I want with Field 'Price' and 'Ydayprice'. However, I can't calculate column C. All I want is row 2 to say 101.45, R3 to say 104.7 etc. I've tried various things but I don't have the knowledge to write the action I require. I don't want to calculate the change in Access either.
Hi everyone I'm a very very new access user so many apologies in advance for when I have no idea what I"m talking about.
I'm working on creating a report that will display multiple expiration dates. Currently I have an employee database, not created by me, that has all of our employees professional licenses listed.
Prof license, auto license, liability, etc.
I want to create a report that will tell me what has already expired or will expire in the next 30 days.
I did use this Between DateAdd("d",-30,Date()) and Date() and it is bringing back info up to 2009. which isn't what I need. I'm sure I'm doing something wrong here.
I want anything that has expired regardless of the date from today, before today, and 30 days from today but I don't care about anything more than 30 days from today's date.
I noticed that when I looked at the existing database that the fields are set as text fields. I tried to change them to dates and it gave me a "deleting 106" records error message. Yikes!
Any help would be appreciated. I don't really understand expressions and I don't understand if I'm supposed to put the actual date in parentheses or what. Please pardon my ignorance.
I've can do this on excel but don't know how in Access. I'm calculating bonuses. My table has salaries, and my query simply multiples each salary amount by a % to get the bonus amount. But I need to calculate adjustments to the bonuses using the sum total of the bonuses my query calculated:
Salary (from table) Bonus (calc'd by query) Adjustment (to be calced)
100,000 1,000 Sum of total bonuses/salary*4% 90,000 900
How do I capture the total of my calculated bonus column to use to calculate the adjustments in my right-most column?
I have a data entry form [Resources] and I would like to display some information about holidays in the form footer. Once the user has picked a combination of Trainer_Name and Start_Date I would like the 'On Change' or 'On lost Focus' event (not sure which would be the best) to perform a datediff calculation.
The datediff calculation would compare the difference between the start date entered on the form and the most recent past Start_Date on a query called [Hours Holiday_P1].I could adapt the same code to also look for the difference between the End_Date on the form and the next Start_Date on the Query.The idea is that when resourcing trainers I know how long it is since and how long it is till their next holiday.The sql for the query is
Code: SELECT Resourcing.Start_Date, Resourcing.Trainer_Name, Resourcing.Duration, Time.Hours, [Hours]/7.4 AS Days FROM (Resourcing INNER JOIN Employees ON Resourcing.Trainer_Name = Employees.Trainer_Name) INNER JOIN [Time] ON Resourcing.Duration = Time.Time WHERE (((Resourcing.Start_Date) Between DateSerial(Year(Date())-IIf(Month(Date())<4,1,0),4,1) And DateSerial(Year(Date())+IIf(Month(Date())>3,1,0),3,31)) AND ((Resourcing.Activity) Like "Holiday*")) ORDER BY Resourcing.Start_Date;
I am using the following UNION QUERY to total up equipment tested for a report.
SELECT "Laptops Tested" AS PCEQUIP, Count(*) AS RECORDS FROM [LAPTOPS] WHERE (((Date)Between [Enter Start Date] AND [Enter End Date])); UNION SELECT "Workstations Tested" AS PCEQUIP, Count(*) AS RECORDS FROM [WORKSTATION]
[code]...
I have created a report using ACCESS 2000 for this union query and it satisfies the requirement. I am trying to add the proper code and syntax in this query to total the number of all of this equipment tested.In this case the total would be 86. Is this possible?
I am trying to create a running total query that aggregates project funding by fiscal year. The fiscal year is calculated based on a date time field that is never null. The totals field comes from 2 different number fields that are either 0 or > 0. The query is going to be linked to by Excel, so I have to do the running total in the query itself, vs. a report.It is close to working, except that it is not totalling the first fiscal year. The output surrently looks like this:
As you can see, the first row for FY 2010 is blank. I know there is data there, as this query is fed by a subquery that selects these rows based on contract signed date. Below is the SQL of each query:
Code: SELECT Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1 AS FYearExport FROM tblProject GROUP BY Year([DateContractSigned])-IIf([DateContractSigned]<DateSerial(Year([DateContractSigned]),4,1),1,0)+1, tblProject.ProjID, tblProject.FPAccepted HAVING (((tblProject.FPAccepted)=True));
I have a table with 2 coloums with nuberin them. I would like to add those 2 number together and display them in a 3rd coloum. I need to automatically change the 3rd coloums value should any others change. Is this possible?
age: DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))Dob is stored yyyyddmmI thought this above function looks ok. When I run it, I get a data type mismatch in criteria expression.What am I missing?Any help would be appreciated.Thanks
in the subform i have 2 fields: duur and subtotaal in duur i enter a number. in subtotaal a calculation has to occur: =20*([duur]/60) i tried to do this calculation in VBA but then i got the same value on every record in the subform.
But now the problem: in the mainform i have a control 'totaal' that needs to do a DSum of subtotaal. And here i am getting into troubles since subtotaal is not stored in the table but calculated.
The formulla for DSum would be: Me.Parent!totaal = DSum("subtotaal", "tblFacturenDetail", "[FactuurID] = " & Me.Parent!FactuurID)
