Calculations Query
Mar 25, 2008
Hope someone can help.
I have 3 tables. Table 1 contains Opening Balance (item ref is unique key). Table 2 contains qty in and Table 3 contains qty out (relationship between all 3 tables is via item ref which is only unique in Table 1).
I am trying to make a query to show the current balance and have tried the following:
Current stock: Nz([Opening Balance])+Nz([qty In])-Nz([Qty Out]) but this is not working.
I think its the link between the 3 tables. If I only put items from table 1 in my query it shows up, but the moment I add table 2 and/or 3 nothing happens.
Anybody any idea where I'm going wrong?
Thanks
Christine
View Replies
ADVERTISEMENT
Sep 28, 2015
I have solved the problems getting values on the subform. I have not in getting values on the subform.For instance, on the one titled phone use the formula in the tutorial is:
=[sbfCustomerRoomUse].[Form]![txtTotalPhoneUse]
#Error results when the doc is put into a form mode.
Now when I input each value in the equation above separately.I still get no entry.
For " = sbfCustomerRoomUse" , I get #Error;
and for "= txtTotalPhoneUse", I get #Name.
CustomerRoomUse and txtTotalPhoneuse are from the subform that was dropped into the customer form in a previous step. It shows that explicitly when designed sbfCustomerRoomUse on the main that CustomerRoomUse come from a subform. This does not seem to need to be done with txtTotalPhoneuse, and I am not sure why. Neither one gives me a desired calculation result.
View 14 Replies
View Related
Sep 7, 2005
Hi, firstly I just want to put I'm a beginner in SQL so don't laugh too loud when you read this.
I have a query that takes two field sfrom seperate tables. Lets call the fields X and Y.
The X field contains an ID number (there are 6 possible instances) which can be duplicated many times. The Y field holds individual pieces of data(sample data below)
........X....................................Y
......1202..............................ABCDE
......1202..............................FJDUS
......1202..............................HFDYH
......1402..............................JHFJGF
......1402..............................GHDDF
......1506..............................FVHJFG
......1609..............................JHFHGF
......1609..............................JJVVJHV
......1609..............................BJHHGG
......1609..............................VJXGGJ
......1609..............................VJXGJKH
I need to be able to find out how many instances of Y occur for each ID number in the X field so the query would show something like
..........X.............................Total
.......1202..............................3
.......1402..............................2
.......1506..............................1
.......1609..............................5
Once you have stopped laughing can you please let me know what criteria I need to put in to carry this out, thanks
View 1 Replies
View Related
Aug 8, 2007
Thanks mainly to John K I have the following code from a previous post which identifies when a 'Unit' has been entered at 2 different 'JettyNumber' on the same 'StartDate'
SELECT Qry_All_Ops.StartDate, Qry_All_Ops.Unit, Count(Qry_All_Ops.Unit) AS CountOfUnit, Sum(Qry_All_Ops.JettyNumber) AS SumOfJettyNumber, [SumOfJettyNumber]/[CountOfUnit] AS Result, [result]=CInt([result]) AS Expr1
FROM Qry_All_Ops
WHERE (((Qry_All_Ops.Operation)<>"Boat Transfer" And (Qry_All_Ops.Operation)<>"Diving"))
GROUP BY Qry_All_Ops.StartDate, Qry_All_Ops.Unit, Qry_All_Ops.Status
HAVING (((Count(Qry_All_Ops.Unit))>1) AND ((Qry_All_Ops.Status)="approved") AND ((Sum([Qry_All_Ops].[JettyNumber])/Count([Qry_All_Ops].[Unit])=CInt(Sum([Qry_All_Ops].[JettyNumber])/Count([Qry_All_Ops].[Unit])))=0));
Unfortunately the maths bit of it highlighted below...[SumOfJettyNumber]/[CountOfUnit] ...is not cleaver enough to find all of the occasions where a Unit has been put in the wrong place.
I think I need to use CountOfUnit x JettyNumber <> SumOfJetty. If that equation is TRUE then there is a conflict, if FALSE then there isn't.
I think I can get this to work if I can get [JettyNumber] into the same query that I already have [CountOfUnit] & [SumOfJetty].
Any clues please ?
View 1 Replies
View Related
Feb 14, 2013
I want to perform a calculation that returns a value that is the difference between current month and previous month.I have a data base for trucks, mileage, hours etc.
So the calculation will be on the Mileage feild. I know how to write and expression to perform a calculation between one field and another, but, is it possible to get calculate the differnce between values using the same feild. I.E
Month January February
Mileage 109000 101000
View 4 Replies
View Related
Jan 21, 2007
Within my "Products" form, I have a "Transactions" subform whose records are
categorized in five different type of inventory transactions - let's say "A", "B", "C", "D", and "E".
I'm using linked queries (instead of DSum) to calculate the net inventory amount (A+B+C-D-E) per product - sort of a running total.
The problem is that transaction type "E" may not appear for a particular record!
Consequently, the query calculations omit those products without transaction
type "E".
How can I include all the "Products" records into my inventory query calculations even though a product may not contain all the transaction types in their records?
Thanks, in advance, for responding.
Tony:confused:
View 4 Replies
View Related
Apr 24, 2015
I am using access to gather data about individual products. I use this data to estimate manufacturing times for production planning. There are 85 different times and each is calculated in a different way. To calculate the times I pull from various tables.
My final query has the following headers:
Model, Category, Code, Time
Model: is the product
Category: is the department that the process occurs in
Code is the: individual step that occurs in the process
Time: is the time that the step takes
Right now I have five separate categories and I have split the process up into five separate querys
DFM_SA (25 process steps = 25 individual querys unioned together)
DFM_HA (2 process steps = 2 individual querys unioned together)
DFM_GA (22 process steps = 22 individual querys unioned together)
DFM_WE (10 process steps = 10 individual querys unioned together)
DFM_FI (26 process steps = 26 individual querys unioned together)
I will then combine the five querys using a Master query for a specific model. I will then dump the resulting data into a table so that I am not constantly rerunning this massive query. I use a delete and append query to update the table, but only for the specific model.
The query runs okay, but not as fast as I would like (which is to be expected due to the size). If I run this query while I have some other tables open I get the alarm that Access cannot open more databases. I imagine this is because of how massive the query is. I am only accessing four databases, but I am guessing that each query that I have unioned together is accessing each database separately and perhaps it is overloading the system.
Is there a way I could utilize a macro or some other method for streamlining this process? I am concerned about the stability of the way I am currently doing this.
Example of one of my querys
SELECT ITEMS_RAWMAT.MODEL, "WE" AS AREA, "WESGSAMT" AS CODE, Sum([QTY]*[WESGSAMT]) AS [TIME]
FROM (ITEMS INNER JOIN ITEMS_RAWMAT ON ITEMS.ID = ITEMS_RAWMAT.[RAW MATERIAL]) INNER JOIN [P/N Times Table] ON ITEMS_RAWMAT.MODEL = [P/N Times Table].[PART NUMBER]
GROUP BY ITEMS_RAWMAT.MODEL, "WE", "WESGSAMT", ITEMS.CATEGORY
[Code] .....
View 3 Replies
View Related
Jun 4, 2007
I have 2 calculations in a single query.
1st - ProdCost: [TBLinvqty]*67
2nd - Gross: [TBLinvamt]-[ProdCost]-[TBLfrtamt]-[TBLocamt]
The query runs and works fine but it keeps prompting me for ProdCost but if I hit ok it runs fine.
How do I get it to quit prompting me for Prodcost
BTW, Prodcost is not a field in a table its only the 1st calculation in this query
View 8 Replies
View Related
May 20, 2013
I need to do a calculation on the RESULTS of a query. Each record in the table I'm querying has a date stamp. I have a query that produces a list of the newest records for each machine. I want to calculate how old each of those records is based on today's date.
So far when I try to include the calculation in that query, it returns all the records instead of the newest ones. This makes me think I'm doing something fundamentally incorrect. The calculation is affecting the query results where I just want to do the calculation on the results. What I'm trying to ultimately do is have a way for the users to see a list of machines that are overdue for a certain procedure. I was thinking of putting a button on a form that will run the query and present the list, perhaps with some conditional formatting highlighting the machines that are overdue, etc. Do I need to just use 2 queries?
View 14 Replies
View Related
Nov 7, 2014
I've been trying unsuccessfully for weeks to try and query a usable data set that would later be used for calculated values in a report.
I'm building a Hospital Acquired Infections Database for my facility to record data and process reports automatically. Previously, I built the system in Excel using VBA and userforms, but the size of the file has become too cumbersome, and now takes too long to process my reports.
So here's the issue.
UNIT_DATA TABLE:
UNIT
INF_MONTH
INF_YEAR
PT_DAYS
CVC_DAYS
IUC_DAYS
VENT_DAYS
APV_DAYS
PT_LEVEL TABLE (There are many fields in this table for recording purposes but I will only list the ones I need for reporting purposes):
UNIT
INF_STE_MAJ
SPECIF_SITE
INF_MONTH
INF_YEAR
So the important numbers I can't seem to collect are the total number infections per month from the PT_LEVEL Table for only select SPECIF_SITE infections.
the Tables are joined Left to Right as Follows:
UNIT_DATA.UNIT - PT_LEVEL.UNIT
UNIT_DATA.INF_MONTH - PT_LEVEL.INF_MONTH
UNIT_DATA.INF_YEAR - PT_LEVEL.INF_YEAR
The UNIT_DATA Table is a monthly collection for the 5 metrics (PT_DAYS, CVC_DAYS, IUC_DAYS, VENT_DAYS, APV_DAYS) regardless of whether or not the number for each is zero. Every unit in the database will have a record for each month and year.
The PT_LEVEL Table will only have records if and when a patient develops an infection. This means there is no cumulative monthly data for the PT_LEVEL Table Data based on SPECIF_SITE infection type.
I need to first do a roll up count for every month and year in the database, for which I was initially trying to use the UNIT_DATA table for since it contains every month and year. The problem is when I try to query the SPECIF_SITE from PT_LEVEL, I can get the number of Infections for months where infections where present for each unit and null values, for each month, and each year in UNIT_DATA, but when I include a where condition to narrow the view to only selected SPECIF_SITE's it only shows data for that SPECIF_SITE for months where they occured.
I need a cummulative monthly aggregation of the totals for a selection of SPECIF_SITE infections, for every unit, for every month, and every year, since July 2010.
View 14 Replies
View Related
Feb 12, 2014
I am designing a database in Access 2010 to handle customer discounts. The problem I am facing is that the discount can vary by customer. One could be a straight percentage based on certain sales totals, even the percentages can vary by customer, and some customer discounts are tiered. I was hoping there was a way to handle all through one query, because there are so many variations, it would require several queries if I separate by each calculation available.
Is there a way to set up a table listing the customer and their corresponding calculation expression then running a query using that table and the sales data table to do all the calculations. Or is there another way to handle this level of complexity in Access? The end result I would like is one data set with each customer and their calculated discount.
View 1 Replies
View Related
Dec 18, 2014
All I'm after is, to sum in all records "where" reconcileDate (on the sub=form) = today in both the credit and debit fields and minus one from the other. I have a simple form and sub-form. On the sub-form I have a query based calculation that returns the following..."if ReconcileDate (on sub-form) = today, then show Debit value in yndebit" textbox..Likewise with the credit box, and all works perfectly fine.
However, I can't seem to sum these two boxes. Because the circled textboxes are query based, I've used the query as the control source (and not the text boxes) and all I get is an #ERROR? when placed in the form footer. I've tried to do this using the textboxes as the control source, and still nothing. I've tried to add a calculated control on the form itself, and still #ERROR? or #NAME? despite knowing exactly how to reference subforms within forms (this I've become quite an expert at as I simply use query design to make sure I have the correct path and control)anyway.
View 13 Replies
View Related
Apr 5, 2013
I am trying to keep records of GST transactions.
I have a problem in figuring out how to do calculations in a query.
How to have the calculation in the attached MDB and Document for field #105_SubTotal and the field #115_PaymentEnclosed.
View 7 Replies
View Related
Jun 8, 2005
I have an odd problem that has stumped me for several days. I'm working on a form that contains a chart. The chart is based on a query, which is based on another query, which is based on a table. In the top query, I need to put some calculated fields that operate on other fields. But when I try to multiply two fields together, I get Null. I'm pretty confident that the fields I'm operating on are numerical.
The fields of the query should be:
MaxOfEverInSchool
MaxOfSurvivalToGrade
MaxOfEverInSchool
With these being the calculated fields:
MaxOfSurvivalToGradeCum: MaxOfEverInSchool*MaxOfSurvivalToGrade/100
MaxOfCanReadCum: MaxOfEverInSchool*MaxOfSurvivalToGrade*MaxOfCanRea dCum/10000
Any insight would be greatly appreciated--I just can't understand why Access would suddenly refuse to do math.
View 1 Replies
View Related
Feb 18, 2014
I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.
Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 CohortQ([InputDate]))
In the VBA, InputDate is defined as a date
Code:
Function CohortQ(InputDate As Date) As Integer
If InputDate = 0 Then
CohortQ = 0
Exit Function
End If
[Code] .....
But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows.
If I put the function into the query,
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 Year([InputDate])*10+DatePart("q",[InputDate]))
it works.
View 3 Replies
View Related
May 3, 2005
Hi ive made a query but instead of calculating the total at the end of each line I want it to add up the total quantity at the end.
For example:
ID Description Quantity
ID Description Quantity
ID Description Quantity
ID Description Quantity
ID Description Quantity
How do i get the query to add up all of those quantities?
View 5 Replies
View Related
Oct 30, 2005
I'm hoping this is a no brainer...I am a complete newbie and self taught so apologies for not knowing the correct techie terms!!
I have a db with products...
Each product has a price (to customer) and cost (to me the seller)
What I want to work out is the margin i.e. price minus cost divided by proce multiplied by 100 to give a margin in percentage terms.
The data is held in a table with a record per product with the price and cost..
I want to build in another fieled per record that calculates the margin...
Thanks very much
Jessica
(I would also like to be able to work out decreasing margins if I was to extend say a 10% or 20% discount to the customer but I guess I shoudl walk before I run!!)
View 3 Replies
View Related
Nov 15, 2005
Hi, I have a database and on one table I have dates stored for certain jobs. I was wondering if anyone knew how to automatically calculate an end date if the start date and duration is entered!
Do I use a certain query?
Hope you can help, Tobi :)
View 2 Replies
View Related
Jul 26, 2006
Hello all,
I'm after some advice. Access is not one of my strong points (even though I use it alot in work!)
Anyway, is it possible to create calculations in access similar to those in excel????
View 2 Replies
View Related
Mar 18, 2008
Ok I'm really new at using access so go easy on me. I am trying to write a database to keep 30 measurements of a part grouped by lot number and then calculate the standard deviation, mean and a few other calculations, however I am unsure about how to do the calculations in access.
I would like the database operator to be able to enter the data then print out a report per lot, with the entries and the calculated answers.
I'm just unsure where and how to do the calculations... any help or suggestions?
Thanks in advance!
View 14 Replies
View Related
Mar 30, 2007
Hi,
I've read plenty of advice warning against performing calculations in tables. What is the best way for me to achieve this.
I need to calculate the product of a number of fields.
Field1 * Field2 * Field3 * Field 4 = Value
Field1 and Field 2 change periodically
Field3 and Field4 change less frequently
I want to be able to perform filter queries using the resulting value
i.e return all items with a value greater than 100
Thanks in advance.
Al
View 2 Replies
View Related
Apr 13, 2008
I'm having a problem with this age formula in the query :
AGE: DateDiff("yyyy",[birth_date],Date()) & " " & " years" & " " & "and" & " " & DateDiff("m",[birth_date],Date()) Mod 12 & " " & "months"
Need your help guys in 2 probs :
1. age calculated is not right.
2. need to compute what w'd be age on the sep 1st 2008 - how do I do it?
Appreciate your help.
Thanks.
View 2 Replies
View Related
Apr 26, 2005
Hi,
I'm trying to a create a report to list results for a running club based on series-to-date. To do this, I allocate points to the first 100 runners to cross the line in each race. The winner gets 100, second place 99, and so on with the 100th person getting 1.
In the database i have 1 table holding member details, then one table holding races. I also have a table linking the 2 called race_entry so i know what members have entered for which races. I have one other table called race_result that links the runner to a race and holds the position they finished in the race.
How could i use the data in the race results table to add up all the points they earned by looking at their finishing position in the races they run and then listing the points in order in a report.
I assumed it wouldnt be necessary to have a point field in the table as its deriveable.
Any help urgently needed!!!
View 1 Replies
View Related
Jul 13, 2006
This is more than likely simple for a lot of people out there but it has me stumped. I have a form that has a two combo boxes with dates in both ( so the user to pick from and to dates) now I am always going to be adding more dates as time progresses. I need the next form to add the info with the dates no mater what the user picks.
For example I am going to (once a week) be adding new info to the table.The end user is going to pick 06/24 (to indicate the end of the week) in both forms or 06/24 in the from box and 07/01 in the to box. In all situations I need the next form to add the data together.
Sorry for the long post but didn't know how to state it any simpler.
View 10 Replies
View Related
Dec 6, 2007
Hi everyone!
I am a beginner with Access and was wondering if someone would be so kind to give me some pointers
I have created a form where the user can select which dates they require a hotel for the evening. I was looking for some advice on how I can go about putting an area on the same form which calculates a sub price for the days they have selected. If I am not being clear then ask for more details.
Thanks
View 2 Replies
View Related
Aug 24, 2005
I need to be able to use some functions available in excel in an access 2000 database. These include SUM and IF functions. If any one can help me figure out how to do this any help help wouold be greatly appreciated
View 6 Replies
View Related
