3 Queries, Need Records From Both Even When The Calculated Value = 0
Feb 19, 2008
(Phone usage report) I have 3 queries, Query 1 counts number of calls in for a phone extension. Query 2 Counts the number of calls out (dialed calls). The information is keyed on the phone extension. Query 3 runs the first 2 queries (By date via parameter). Each query is keyed on Extension, I only want to see the number of calls in and out for each extension. Problem, when a phone extension being counted has no calls (In or Out), it leaves the phone extension out of the report. I want to see all the phone extensions info regardless of the count (if it = 0). Does anyone know a way around this? Thanks in advance for your help.;)
I have a calculated field in a query. The field name is TotatPt (this is to calculate the total points students have earned during the term). The expression is as follows:
I think this should be no complicated expression (though a bit long) and should just add the fields together. But what I get is that the calculated field appears as expected for records with an odd primary key (1, 3, 5, 7, ...) and turns out blank for records with an even primary key (2, 4, 6, 8, ...)!
I have a query which returns a calculated percent. I have ordered that in descending order, and now want to see the top 50. So (In Access 2010) I entered 50 into 'Return'. But it returns all of the records!
Is this because pct is a calculated field? How can I correct this? The SQL seems to be correct.
Code:
SELECT TOP 50 HeciFail1.POHECI, HeciFail1.POQTY, HeciFail1.FAILQTY, IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) AS PCT FROM HeciFail1 ORDER BY IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) DESC;
I have read a number of these threads now where it says you should not generate new records based on calculated fields. So can some one help with my following problem.
I am creating a customer/contract database where when an order is created, a record set is generated to store dates for invoice periods. I have done this by using the start date of the contract and then adding onto this a value of x days that updates a table giving y records based until the end date of the contract.
Is this the best way to do this or can some one tell me a better way. Thanks
I have a table for storing vehicles. The table contains the following fields:
VehicleID Make Model Trim Build Date Begin Build Date End
Vehicle Name - a concatenation of 'Make' 'Model' 'Trim' 'Build Date Begin' and 'Build Date End'
The 'Vehicle ID' is the primary key, the rest of the fields can't be indexed with no duplicates. Is there a way of preventing one from saving a duplicate VehicleName on the table since it is a calculated field.
I have a table (tblStats). The user enters his current weight in a field named Wt. There are 2 other fields in the table named WkLoss and TTDLoss. These two fields are intended to be calculated. What I am trying to do is when the user enters his current weekly weight figure into the Wt field, I want the field WkLoss to get the user's weight from the previous record and place the difference in the field WkLoss. Also I would like to keep a total to date in the TTDLoss field.
So far this has been my attempt. Code:Public Sub setWeight()Dim rs As DAO.RecordsetDim Startval As DoubleStartval = 190Set rs = CurrentDb.OpenRecordset("tblStats")'Select records to be updatedSet rs = CurrentDb.OpenRecordset _("SELECT * FROM tblStats ") rs.MoveFirst Do rs.Edit'field to update rs.Fields("WkLoss") = Startval rs.Update rs.MoveNext'increment step value Startval = WkLoss - Wt Loop Until rs.EOF rs.Close Set rs = Nothing End Sub All I get is the value 190 placed in the first record and all zeros in the subsequent records. Also I don't have a clue as to how to get the Total To Date field to calculate.
Any help would be appreciated Thanks for your time.
I am new to Access and this is likely a very elementary question. I recently created a database to track status of projects including progress and funding. The table contains several fields that record various aspects of funding. These fields are added (very simply) and create a calculated field that is a summation of those fields. The table currently contains approx 60 records. The calculated field functions for 2 or 3 records and will not calculate further. I have tried this in a form as well as a query. The form will change the same number, 2 or 3 records. When the query is run the result shows only the 2 or 3 records. What I am doing that is only reading and modifying those records?
So I have a report with the following text box controls:
[Surname] & ", " & [Firstname] =Sum([Quarter1_A]) - Named "Quarter_Total" =Sum([Quarter1_T]) - Named "Quarter_Target" =Val([Quarter_Total])/Val([Quarter_Target]) - Named "%Target" (Percent Format)
The report is grouped by the expression '[Surname] & ", " & [Firstname]'.I am trying to sort the records by the %Target text box. I tried entering the expression into the sort function but it still sorts by the grouped expression. I also tried sorting by the name of the text box but got the same results. How can I sort by the desired control?
Fields in Query2 TestID from Table1 Select from Table 1
Run Query Can’t update “Select” field
So I’m thinking that I should create a 3rd query to update the summed Quantity Delivered into a new field in table 1. Which would in effect create a stored calculated field.
I have created two queries which calculates a total. I want to add the values of the two queries in a third query to give me a grand total.
When I try to create this third query, it gives me an error saying that the fields cannot be used from two different queries. So I'm just wondering whether this is possible or is there any other way of doing this.
I need some help. My brain just can't grasp how to do this.
I have an Access DB. I sell vinyl records. I have an order form. When a customer orders a record, each vinyl record has a unique ID#. No two vinyl records are alike. I have four tables: Customers, Orders, Inventory, & Order Details. I have an "Order Form" form where I input all the info. It's an all-in-one form where you can view and data entry in one screen. The form has two subforms in it. Here's the problem: If a customer orders more than one item (i.e. a vinyl record), each Inventory_ID (representing one vinyl record) is associated with one Order_ID which is, in turn, related to one Customer_ID. If I have one Order_ID that has multiple Inventory_IDs (i.e. has ordered two or more vinyl records) that belongs to one customer, how can I calculate that total cost? Currently, I have a calculated field within my all-in-one form that calculates only the price of the Inventory_ID that one currently sees on the subform. The Inventory_ID item has a set price, for instance $5.00. If I scroll through the ordered Inventory_IDs, the calculated field will only display the price of the product that you currently see. It's not really calculating anything, I guess. It's just bound to that field, but I need to sum up all the price fields for all the Inventory_IDs that are associated with one Order_ID & Customer_ID whether it's a query or calculated field...I just need it done so that it'll display on this form. Any help would be much appreciated. I can show you whatever you need to see. Thanks in advance. David
This is not the normal calculated fields in query's. What I want is different, I want it to be like
IFF(ShowID="A",[TotalSales],0)
Thing I have is, I want to know all the Total sales in on row that has Show A in it and then Total sales for Show B. Problem is, I have over 130 shows. How would do that?
I am working with fueling up cars. I have a table tblFuelEntry in which data associated with fueling up a car is entered. Information such as Fuel date/time, tankMiles, Gallons, CarID, GasStationID are all entered. In my qryMPG I calculate the MPG for each fill up. Here in lies my problem. The MPG that is calculated is actually indicative of the previous fill-up's gas station for that car.
How can I tell the computer to calculate the MPG and then associate that value with the previous gas station used to fill-up that car?
I've been playing around with the Date/Time serial values as I am guessing I could use those values to select on as the fill-ups aren't always entered in ascending date order...
I've been struggling with this for a while, and even though I understand the theory, I can't get my code to work correctly.
I have 2 fields: CallbackFrequency & Last Contacted.
I want to use these fields to populate a third field (callBackOn) so that we can have a list of candidates that need to be called from a certain date.
CallbackFrequency is added from a combobox, so all values are either 1 Month, 3 Months, 6 Months, or 12 Months.
I have tried creating a calculated field directly in the candidates table, but apparently this is not possible.I've therefore tried to create a query using IIF statement that will calculate this value.To get to grips with the code, I'm only dealing with 1 callbackFrequency at the moment. So far I have:
I have not factored in the date manipulation yet, as the query does not display the LastContacted date of the record it finds, it only shows a blank cell,
I have a several fields, calculated, that break apart a string of text at every open and closed parentheses. They work like they are supposed to with no errors. However, I want to use the result of this particular field in a combo box to populate another combo box with the other half of the string that will be split. The problem, I think, is that since the field is calculated, it will not allow me to select it in the combo box, saying that the field can't be edited.
So, I added the same field names to the table that the query was built on, however when I run the query, it says that there is a syntax error with a comma in the code. If I take out the table in the query, (not using the SQL side, just the query builder), it works again with no problem.The text I am breaking apart looks like this: 1.234(a)(1)(A)(2)(b)(i)(-a-)
I need this field to break the text up at the (1) mark, so the code I am using looks like this:
Code: PrinSubChap1: IIf([Sub2] Is Null,[PrincipleNumber],Left([PrincipleNumber],InStr(InStr(1,[PrincipleNumber],"(")+3,[PrincipleNumber],")")-0))
The other fields I have do the exact same thing, written the same way, with adjustments made to the position (+3,0).I have stared at this for awhile and can't figure out why it is giving me an error and research on the web doesn't appear to cover my question.
This is the error message: Syntax error (comma) in query expression '[mytable].[IIf([Sub2] Is Null,[PrincipleNumber],Left([PrincipleNumber],InStr(InStr(1,[PrincipleNumber],"(")+3,[PrincipleNumber],")")-0))]
I Have some calculated fields in a query and want to update it in the table .So,is there anyway to store these fields.Otherwise can i store this Query data every month in database so that when i re-run the query the previous month data will not be affected.
In a query i have setup, i work out when a tenant is next due to pay their rent.
Looks like this
Tenant Last Payment Date Payment Terms Next Payment Date User1 01/07/13 Monthly 01/08/13 User2 01/07/13 Weekly 07/07/13
The next payment date is calculated using an IF statement and DateAdd in the query, so if it is weekly it adds 7 days, if it is monthly it adds 1 month.
What i am trying to do is write sub query of this one which shows which tenants are due to pay within the next 7 days.
But the access query seems to ignore the criteria I'm putting in....in the next payment date criteria i have tried specifying >Now() + 7 or >Date() +7 but neither seem to work, it just shows up every record...
I having a problem with the expression builder in a table.I got a lot of fields with dates and I what a calculated field so I can see if any of the dates are newer than 7 days.I have been trying this formula:
I am having a problem with calculating a date field in a query. Prior to this posting I've done some research and made several changes to my query. This only resulted in fixing one problem but then creating another problem. Original problem was I had 2 fields, arrived (23:36) and stemi (0:07). I use the following calculation AT_ST: DateDiff("n",[arrived],[stemi]) which resulted in -1409. So my research showed me I had a problem with the date whenever the time went past midnight and trying to calculate a zero hour number. I changed my calculation to
AT_ST: IIf([stemi]>=#11:59:00 PM#,(DateDiff("n",[arrived],[stemi])),(DateDiff("n",[arrived],[stemi]+1440) Mod 1440))
This works fine and gives me the result of 31 minutes which is what I want, however the problems comes in when I change to this calculation any where there was a negative time now has a 1400+ plus value. Such as arrived (7:37) and 1st_eck (7:18) = 1426 where as before it would report -14 (yes, negatives are acceptable for my reporting because sometimes a call to the hospital is placed before the patient arrives so we want to report on the negative splits). I've tried using a nested IIF to calculate for stemi time being less than arrived time, this didn't work when I tried to use it on the calculated query field. I was wondering if I could write something to check the value of the calculated field if it is greater than 1440 and if yes - subtract 1440 from it. So in the example above 1426-1440 = -14. Is it possible to do this within the query or do I need to do it using VBA
I don't seem to find any query formula in the forum where the age changes on the date of birth. I tried all the once I could find but all of them seem to calculate the age as of 1 January. I find it a bit strange that it doesn't work.
I got BirthDate and Date in the table I want to update the age column back in the history with an update query.
