SEARCH YOUR SOLUTION HERE  

Calculate Yes/No fields in a query

In a query that utilizes a table containing a yes/no field, is it possible to calculate the yes/no field and get a numeric return If so, can you share how this could be accomplished.

Posted On: Monday 22nd of October 2012 05:16:53 AM Total Views:  321
View Complete with Replies




Related Messages:

Format date in a calculated field   (182 Views)
I have a label control for a subform, which is a concatenation of fields derived from data in another subform. It'd be better if it reads as: "John Smith -- Start Date: Sunday, August 15, 2010;" but it comes out as: "John Smith -- Start Date: 8/15/2010." Is there a way to force the formatting of the date
Select only a Certain data to calculate in report   (92 Views)
I have a database that contained the following fields. VacEarned,VacUsed,VacPlan (Yes,No). In my report, I grouped VacPlan field (which is yes/no field) together and sum it up by VacUsed. However, I'd like to put another formula in my report to subtract the the Sum of the VacPlan from VacEarned...I have such a hard time doing that..Is there way that I can put if statement in the report option that would do the following: If VacPlan is Yes, than Sum VacUsed and use it to subtract from VacEarned..is this possible.. I hope this make sense...
How to Print only fields with data   (92 Views)
i am creating a report in access, but it shows a few titles without data in front, cause i left the fields blank in the table, is there anyway of making these titles visible only when some data is inserted and remain hidden when the fields are empty..
Concatenate name fields to create login name   (157 Views)
I need to link fields in different tables   (129 Views)
Hi Can you tell me if the following is possible and if so how to do it. I have several different tables which require a date to be entered. 99% of the time this date is the same ie (Table 1) Visit Date = (Table 2) Scan date 1 (Table 1) Visit Date = (Table 3) Scan date 2 etc..... However in some cases Scan date 1 and/or Scan date 2 may not be the same as Visit date so the input does need to be editable. Is there anyway I can link the information in Table 1 to automatically be displayed and stored in Table 2/3 and to be able to edit Tables 2/3 if the scan date is different to the visit date which it could be for a small number of records. Is this possible or is it simply impossible to do this.
Storing data in two fields without allowing Duplicate entries   (96 Views)
I need to know if I can store data in more than one field, without allowing Duplicate entries. Example: We have to send out letters with a claim number on it, sometimes we need multiple claim numbers on a letter, up to about 10 claim #'s is the most I've seen thus far. We mail merge into Word to create the letters. However, these claim numbers cannot be duplicated within the database and in order for this to work with the mail merge, I need several fields to store the claim numbers. Fields are: ClaimNumber ClaimNumber2 ClaimNumber3 etc....
Wierd Prob: Cannot update SQL Server text-fields for some records, but can for others   (150 Views)
, I have a very strange problem: I have a table linked to an SQL Server table in my MS Access 2000 Application containing 5 text-fields (SQL Server Type, in Access seen as MEMO-fields) and several other fields (in total about 140 fields). Updating any field works fine except for some records. In those records I can only edit the non-text-fields in my application. When I try to edit one of the text-fields I get a error message telling me that another user has edited the record and i have to discard my changes. The strange thing is that this problem only crops up for some records. (1 in a 100 or so) other records work fine. Does anyone know what happens here And how this can be fixed
How to return unique fields in Access s.t.   (87 Views)
Hi have a table which consists of 12 or so columns. One of the columns is a date, another a time and another a price - the other columns are miscellaneous. The data is stock price data over the course of year but with times taken at every few seconds. What I would like for each day return the price that is nearest to 4pm (not all days have times exactly 16:00:00). I start off with a statement like this: SELECT * FROM BP WHERE (((BP.Field4) Like '4:00*')) I want to go the next step and only have a unique date (field 3) so that the goal is to only only have one price for each day. Any suggestions
Add subfields in a form   (94 Views)
Working on MS ACCESS 2010 There is a field "Number of Hotels" in a FORM. If the user keys in "10" in the field "Number of Hotels," 10 new fields for Hotel Name (Hotel Name1, Hotel Name2, Hotel Name3... Hotel Name10) should be added to the FORM. If the user enters "24" in the field "Number of Hotels," then 24 new fields for Hotel Name (Hotel Name1, Hotel Name2, Hotel Name3.... Hotel Name 24) should be added to the FORM. Is this possible to do If yes, How do I do this PS: I didn't know what the subject of this topic should be... So pardon me for the lack of my technical knowledge.
Primary key - maximum number of fields   (182 Views)
Hi . I have a simple question which I can't really find the answer to. I'm studying for an exam, and one of the sample questions are "what is the maximum number of fields that can form part of the primary key in a MS Access database" The options are (a) 1 (b) 2 (c) 3 or (d) none of the above. I am thinking that is is not unlimited, but certainly greater than 3.
Subform control source from calculated textboxes   (113 Views)
I have what seems to be a simple problem but I am having trouble coming up with the solution. I have a subform (continous) with 3 textboxes: Qty, Price, Total. Qty and Price are from a table. I want the Total textbox source to be Qty*Price, thus not having to store a calculated field. I tried setting the source to Code: [Unit_Price]*[Quantity] but that gets me #Name. Is there a simple step I am missing here
How can I subtract two fields from a database table(Table1) in Microsoft Access 2002?   (132 Views)
Dear Friend I have two fields under a database table named as Table1 in Microsoft Access 2002. Table1 has got two fields purqty & suppqty which contains numeric values. I want to subtract suppqty from purqty and store result in another field named as balance in Table1. The SQL statement I have written is as follows. But it is not working. SELECT Table1.item, Table1.purqty, Table1.suppqty, Table1.balance FROM Table1 WHERE (((Table1.balance)=[Table1]![purqty]-[Table1]![suppqty])) GROUP BY Table1.item, Table1.purqty, Table1.suppqty, Table1.balance; Please help.
Ranking on multiple fields   (90 Views)
I am a self taught access user with limited sql knowlege so bear with me. I have a query where I want to Rank Sales $ by different classes. I have used the following "Rank: (SELECT Count(*) AS HowMany FROM [2006 year by brand] AS Dupe WHERE Dupe.[2006 Sales$] > [2006 year by brand].[2006 Sales$])+1' In My query i have fields named: Dept, Sub Dept, Class Name, Store Number, 2006 Sales$, 2006 Sales Units. I want to be able to create a rank by the '2006 Sales$' and also have the '2006 Sales$' ranked within each class So I would end up with something like this. Class_____ 2006 Sales$_____Rank 1__________10______________1 1__________15______________2 2__________25______________1 2__________30______________2 Is that possible to do Currently with what I have I can rank the Sales but not differentiate between class. -------------------- B
How to merg fields in MS Access 2003   (105 Views)
I've got a question about SQL query in MS Access, for example I have the following data: Table1: NAME _____ CITY _____ FAV vahid ______ NY _______ A vahid ______ NY _______ B hamed _____ LA _______ A hamed _____ LA _______ C Do I able to merge them like this in MS Access 2003: NAME _____ CITY ____ FAV vahid ______ NY _____ A, B hamed _____ LA _____ A, C can anyone give any solution to it
Elegantly hiding table key fields from users in form controls?   (133 Views)
I've got a form that's causing me some DBA-style headaches: I have a three-table query that my form is bound to. The reason for the joins is that the table being updated by the form simply holds foreign keys that reference the tables I'm joining to - those tables contain the detail data that an end user would understand. For example, I have an inventory table where each row simply contains an "CustomerID" field - an integer, and a foreign key into the Customer table. The Customer table contains the name of the customer, along with other details about that customer. This form is to allow the user to reassign ownership of inventory items (among other similar things). I need the user to be able to simply select customer names from a drop-down, but behind the scenes I'd like them to be making the CustomerID key available to the form [the one that corresponds to the newly selected Customer Name], so I don't need to go back to the database with a separate query. Of course, I don't want CustomerID to be displayed anywhere on the form (I don't mind having an invisible control though). Is this difficult to accomplish
Required fields in subforms?   (132 Views)
I have what I think would be an easy problem, but I'm new to Access (and databases in general, I have to admit), and I'm getting nowhere on my own. I have a database with a main form that corresponds to a table of contacts. This main form has a number of subforms containing secondary information for each contact: correspondence records, secondary addresses, education information, and the like. I'd like several fields in some subforms--or the subforms themselves, if possible--to be required before a new entry can be saved. In other words, I don't want users saving records without certain key information--for instance, a contact's year of college graduation (a field within an "education" subform). I've tried setting fields on the table level to Required (& setting ow Zero Length to No), but Access will only display a dialog box and require data entry if a user clicks on a subform that includes such required fields. Otherwise--if they fill out only the fields on the main form (first name, last name, company, etc)--they will not be prompted to fill out required subform fields, and the record will be saved with a number of blank fields. Is there any way to make these subforms, or certain fields within them, required
Calculatino fields automatically in a form   (126 Views)
I need to calculate a field using other fields in the query/form. Code: TARGET DAYS TOTAL 300 1 300 400 2 800 300 3 900 400 1 400 if the day=2 then target*2 and if day=3 then target*3 else target. For Day 1 the target will be entered. I need to calculate the target for the rest of the days. I'm not sure where to calculate this, in the form or at the query level.
Pop up fields   (101 Views)
Without using VBA or html or anything like that, would someone be able to tell me how to do the following please Depending on the value of a field on a form, I'd like another field to pop up. I'm creating a d-base for a friend who's a personal trainer. I have a field for the client's BMI. If it's
Generating a field with parts of other fields   (103 Views)
If I have three fields Surname - text Given name - text Dateofbirth - dd/mm/yyyy how can I generate a field consisting of surname(then)first letter of given name (then) year of birth (ie SMITHK66)
Size-changing fields in a report   (113 Views)
I've asked this question in different forums before, and though some people say it can be done, I have not found anyone who is successful yet. I have a report that puts down a person's address, however the address information is stored within the database in separate fields ('Address', 'City', 'State', 'ZIP'). The issue I have is with the City/State/ZIP. Some city names are longer than others, is it possible to have the fields adapt to the different word lengths. Say the address is, Albany, NY 10023. The database would output this alright, but then if the city name is San Francisco, CA 94143, the fields are in disarray. I have the same issue with names. Last name and first name are stored separately. I would like to display Last, First. My workaround has been to right-align the Last name, and put it adjacent to the left-aligned first name field. This works unless the person's last name is very long, disporportionately from the first name, in which case, it looks very much off center in the overall report.