Question On Query To Flag A Field Based On Calculated Data
I have a table with numerous fields including timeIn and timeOut field (these are date/time format). I need to find out how many people were clocked in during each operating hour (based upon their time in and time out). How can I run a query that will allow me to use the times in and out to 'flag' each hour that each person was on duty (a new field for each hour) so that I can run a count on the completed data for every hour?
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Calculated Field Based On The Value Of The Next Record
Hi, I have a query that contains the results of repeated tests on a number of persons. The goal of treatment is to get those test results below 50. I need to find, for each person, the period of time (with a start and an end date) during which their test was below 50. The dataset looks like: ID + LabID + LabDate + Result + Undet ----+------+-----------+-------+------ 101 + 21 + 1/1/2000 + 780 + 0 101 + 21 + 2/2/2000 + 320 + 0 101 + 21 + 3/3/2000 + 50 + 1 101 + 21 + 4/4/2000 + 50 + 1 102 + 21 + 6/6/2005 + 1000 + 0 102 + 21 + 7/7/2005 + 900 + 0 102 + 21 + 8/8/2005 + 50 + 1 103 + 21 + 9/9/2005 + 50 + 1 103 + 22 + 9/30/2005 + 567 + 0 What I need is a query that produces the following: ID + StartDate + EndDate ----+-----------+--------- 101 + 3/3/2000 + 4/4/2000 102 + 8/8/2005 + 9/9/2005 I can also provide an attachment with more data, if that would help. Thanks in advance,
View Replies !
View Related
Calculated Field Based On SQL In Text Box
Hello, Bit of a newby to all this. Am trying to use a text box as a calculted field. I am doing so using an sql statment in control source The sql stat is built and tested using build query and then copied and pasted from sql view) I keep getting #name? as an error. I have managed using dlookup of the same saved sql to return the required value (which works) but find it to be a bit slow and clunky so wanted to give this a go instead. If somebody can have a look at the query below I would much apreciate. =(SELECT Sum(QryInout!CostExVATGBP) AS TTlCostExVATGBP FROM QryInout &_ WHERE (((QryInout.StockImportNoJoinToAll)=[Forms]![FrmSPurch]![ImportNoNew])) &_ GROUP BY QryInout.ProdType &_ HAVING (((QryInout.ProdType)="Freight"));) Note that I have tried with and without the &_ and also with or with out the brackets.. Thanks
View Replies !
View Related
Possible To Filter A Form Based On A Calculated Field...?
Hi. I have an Order form that is based on an Order table. In this form there are also a number of text boxes (not bound to table) with calculations, and one final text box with a deadline based on these calculations. Is it possible to filter this form based on the deadline? I can do this by making the form based on a query, but then again I'm not able to edit the information in the form.... Please anyone???
View Replies !
View Related
Calculated Field Based On Accounting Year
Within a query i am trying to run historical pension values based on 3 fields from a table: Value Year Period Period: Is aligned with the accounting year April to March rather than Jan to Dec. So I cannot define Year as 2005 and period 1 to 12. I am looking to define year as 2005/2006 and then calculated the sum of period 1 to 12. Has anyone experienced this before and found a solution? Thanks
View Replies !
View Related
Calculated Field Utilising Data From Previous Record
Somebody please help me!!! I am creating a database that will record the details of approx 30 shops and will store information on Annual Turnover, Profit, Return, etc. I need to create a field that will calculate the increase/decrease in annual turnover between the current year and the previous year. So far I have created a subform that will show the last 12 years of data for the particular shop, but now I need to add a calculated field that will get the current years turnover and also reference the turnover for the previous year, i.e. the previous record., and display the result. The other thing that I will need to do, is to have this field not calculated for the initial years trading, so this field will need to be hidden in the first record. Can someone please offer some advice on how to go about achieving this???
View Replies !
View Related
I Need To Update Data In A Table With Information From A Calculated Field In A Form
I've read over and over that calculated data is bad, that's fine with me... but here is my dillema... I have four fields that interact with my calculation in a single form. There is only one table in the database. AmountF (The stored data in the table) (amount financed) FC (calculated by =[amountf]*.25) (finance charge) Amount Paid (used in calculation of total due) total due (calculated by =[amountf]+[fc]-[amount paid]) The math already works, everything works. I can choose to print the form and it looks great. However, I would like for it to (when I'm done printing the form) somehow save the total due data to the AmountF column in the table. AmountF is in the same line as all the other information, so I would like it to not create a new record just update the old one. All four of these fields have a text box in the form for themselves.
View Replies !
View Related
How Do I Add Calculated Field To A Query
I have an inventory transaction file that has quantity on hand as a calculated field. I am trying to add this unbound field to a report using a query but I can't figure out how to add my quantity on hand field to the query since it is a calculated field and not a bound control. Any help would be appreciated. Thanks darrrellx
View Replies !
View Related
Using A Calculated Field In Another Query
I can't seem to find a problem like this already answered.... I have these two calculated fields: dtmNPacketDue: IIf([strRevTypeNIRB]="Full" Or [strRevTypeNIRB]="Termination(Full)",[dtmNIRBMtgDate]-28,[dtmNExpDate]-28) dtmCOIDueDate: IIf([strRevTypeNIRB]="Termination(Full)" Or [strRevTypeNIRB]="Termination(Expedited)","N/A",[dtmNPacketDue]-42) When I use the one in RED in a calculated field in a new query, things are peachy. But when I use the one in BLUE in a calculated field in a new query, the message is #Error My Calculated field (which produces an #Error) in the new Query is: 2ndCOINotification: [dtmCOIDueDate]-14 (if I insert [dtmNPacketDue] instead...it works Perhaps my problem is that dtmNPacketDue (a calculated variable) is part of the BLUE calculated variable....?? Any ideas greatly appreciated....
View Replies !
View Related
Calculated Field In A Query
Hola folks. I am trying to create a calculated field in a query that subtract 2 fields within the same query. That is, i have a FiscalWeekStart and FiscalWeekEnd pulling from a table. I created a CountofWeek field that count the weeks between the FiscalWeekStart and End using "DateDiff("w",[FiscalWeekStart],[FiscalWeekEnd])". I also have a HolidayWeek field that pulls from a table using a Count expression in the query. My goal is to subtract the CountofWeek and the Holiday week calling it NonHolidayWeek using "NonHoliday Week: IIf(IsNull([CountofHolidayWeek]),[CountofWeek],[CountofWeek]-[CountofHoldayWeek])" BUT IT IS ASKING FOR PARMETERS ON THE "CountofHolidayWeek". Thanks for the help in advance. TukTuk
View Replies !
View Related
Using A Query To Add A Flag From One Table To Another
Hi, just so you are aware, I am totally clueless when it comes to Microsoft Access. I tried finding tutorials and help online that I could use to do this but I'm having trouble understanding what I'm supposed to do. I have two huge tables linked to each other by an ID number. One table contains multiple records with the same ID number while the other does not. There are records in the first table that don't appear in the second one, and there MAY be entries in the second table that don't appear in the first table. The second table has a flag that I would like to add to records in the first table that appear in the second table. What is the easiestt way to do this? This may be an extremely stupid question or it may be very hard, I'm not sure which, though I'm leaning toward it being a stupid question. Thank you in advance for your help.
View Replies !
View Related
How To Sort By A Calculated Field In A Query?
I have a query that draws from two tables, and the field in question looks like this: X: [TableData]![FieldA]*[TableNumbers]![A]+[TableData]![FieldB]*[TableNumbers]![B] It all works fine and dandy, but once I set it to sort by this field and run the query, it gives me the parameter prompt, asking me to enter the Parameter Value of FieldA and then for FieldB. Is there a work-around for this within the query? The only other solution I have in mind is making another table from this query, and then creating another query just for sorting said table, but that seems inefficient at best.
View Replies !
View Related
Query Of Calculated Field Gives Blank.
I have the following calculated field in a query: LoanNo:IIf([Stats].[LoanCnt]>1,[Stats].[LoanCnt] & " Loans",[Loan].[LnNo]) Within its query, it correctly displays the loan number associated with a particular loan. However, when I run a second query SELECT LoanNo FROM qryLoanInfo it comes up blank for every record (other fields are OK) Does anyone have any idea what might be happening?
View Replies !
View Related
Criteria For Calculated Field In Query
Hi, I have a calculated field in my query called "outstanding". this is how the query looks: Expr: SubFormTotal
[qty]*[price] Discount
...[Discount] Expr:Total
.[SubFormTotal]-[Discount] Deposit
.[Deposit] BalanceToDate
. [B]Expr: Outstanding
..[Total]-[Deposit]-[BalanceToDate] PROBLEM: i want to search for all the "amount outstanding" that >0 HOWEVER when i put that as the criteria it asks me to enter values for other calculated fields what am i doing wrong and how can i solve it? thanks
View Replies !
View Related
Parameter Query On Calculated Field
I hope someone can help as I am stumped at present. I have created a calculated query using dateadd. I have a table that has date fields, ServiceContractAgreed and ServiceFrequency. The service frequency field holds a value of 3,6,9 or 12 in months. Using dateadd I can add the months to the ServiceContractAgreed field in a field called NextService, ie =IIf(IsNull([ServiceContractAgreed]),"N/A",DateAdd("m",[ServiceFrequency],[ServiceContractAgreed])) This works fine but what I want to do now is create a parameter query to search the NextService field, using the between operator to search between dates. I've created a new query to do this but get every record back, no matter what dates I put in. It's probably something really simple to do but I'd be really grateful for any advice! Cheers Dave
View Replies !
View Related
Like Operator In Calculated Query Field
Good afternoon, I have a field that contains semester info ie: F07 Sep. What I am trying to do is using an if statement to test for which month and returning a section number based on the month. I can make it work using an exact match but am trying to make it work on the partial match "Sep" so I don't have to change it next year as F07 stands for Fall 2007 This is what I've tried and various combinations. Section1: IIf([tblOnlineCoursesDetails]![Semester]="* Sep","70","") Having trouble getting this to work. Any help or suggestions would be appreciated. Thx Jerry
View Replies !
View Related
Specifying Criteria Within A Calculated Field In Query...
Am trying to create a calculated field in a query based on one table and one query. The table lists each client and their number of seats at each location, the query sums the total number of seats at each location. I want the calculated field (in a new query) to divide each client's seats at each location by the total seats at that location. Each location is defined by a 3 digit code in its own field in both the table and the query (the query is just a sum of the seats column in the data table grouped by location). Hope this makes sense. Pretty sure there is a simple way to do this, just cant seem to get it to work. Thanks in advance...
View Replies !
View Related
Updating Query Flag If Child Records Exists
I have a stock-type record and a stock-item record. I want to update a flag on the stock-type record called instock to be true if there are related stock-item records and false if there are no related records. Does anyone know how to run a updating query that will do this? I am fairly new to this so I'm sorry if this is a bit of a basic question. Thank you for your help in advance. John
View Replies !
View Related
Check Box Based On A Data Field
When a part number is set up in our manufacturing software, the user clicks a check box to mark the part as purchased. I'm creating a similar form and want to have a check box display the same information. The manufacturing database stores the check box as Y when checked and N when not checked. I tried 2 different ways to add code in the BeforeUpdate but neither is working. First Example: if Purchased = "Y" then check295 = True else checkbox295 = False Second Example If Purchased = "Y" then check295 = -1 else checkbox295 = 0 What changes do I need to make to this code to get it to work? Thanks for your help, Jeff
View Replies !
View Related
Basing A Calculated Field On Another Calculated Field
My query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables. I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25. Does anyone know how to correct this? Thanks in advance. :confused:
View Replies !
View Related
Auto Data In One Field Based On Other Fields?
Hi, I have a table that is used for project progress. One of the fields is named "Completed". I would like to know if it's possible to auto fill the data in the "completed" field based on three other fields within the same table. ex: If field 1 = Yes, and field 2 = Yes, and filed 3 = Yes, then field "completed" = Yes I'm open for any ideas, I'm new to access and am not sure how to get this started. Thanks
View Replies !
View Related
Restricting Data Entry Based On The Contents Of Another Field
Hello, I have two tables which contain the following fields (simplified); Table 1: ID Startdate Enddate Table 2: ID Unitdate Treatment The tables are related (one-to-many) through the ID field. In operation, the user first enters the Startdate and an Enddate for a patients' hospital stay in Table 1. Following this, the user enters the Startdate again in the Unitdate field in Table 2 and selects a Treatment from a drop-down list. After this, the user enters the following in the Unitdate field and selects a treatment. This is repeated in consecutive order until the Enddate is reached. The end result looks like this; Table 1: ID Startdate Enddate 123456 01/01/2005 04/01/2005 Table 2: ID Unitdate Treatment 123456 01/01/2005 Ventilation 123456 02/01/2005 Ventilation 123456 03/01/2005 Haemofiltration 123456 04/01/2005 Ventilation Does anyone know if there is any way to restrict the Unitdate field in Table 2 to only allow dates to be entered that are between the Startdate and Enddate fields in Table 1? Ideally, I would like the Unitdate to populate automatically with the dates, starting with the Startdate value and ending with the Enddate value and filling in all the intermediate dates. Also I would like to make sure that there is no way of having duplicate dates in the Unitdate field. If anyone can help, I would be extremely grateful. Best wishes Russell
View Replies !
View Related
Autofill Query Field Based Upon Other Fields Within The Same Query?
Hi, Baisically I got this query which controls the selection of Software within a system. i.e. It contains the fields Vender_ID, Application_ID, ApplicationEdition_ID and ApplicationVersion_ID. These auto-filter based upon the users selection (e.g. Vender=Adobe so Application=Acrobat/Photoshop/etc...) Right some from the user software/application selection i want it so that it will automatically assign a License_ID defined within a table tblLicense_Information. Baisically im asking whether i can put this in the 'criteria' box: "SELECT tblSoftwareOverview.License_ID FROM tblSoftwareOverview WHERE ((tblSoftwareOverview.Vender_ID=qryHardwareSoftwar e.Vender_ID) AND (tblSoftwareOverview.Application_ID=qryHardwareSof tware.Application_ID) AND (tblSoftwareOverview.ApplicationEdition_ID=qryHard wareSoftware.ApplicationEdition_ID) AND(tblSoftwareOverview.ApplicationVersion_ID=qryH ardwareSoftware.ApplicationVersion_ID));" I know the sql is not exactly right but you get the idea of what i want to achieve! But im guessing you cant really have an sql statement within an sql statement so any other suggestions on how i can do this would be much appreciated as the INSERT INTO command didnt like inserting into a query and when inserting straight into the table, the query failed... cheers
View Replies !
View Related
|
TRACKER
