Queries :: How To Replace A Field With A Different Word Based On Its Value
Mar 15, 2013
I have a seating chart with 33 tables. Under each table there is a number that represents seats taken. I want to do a query that changes the number when it is a certain value. For example, when the value is null I want it to say "Empty", when the value is 10 I want it to say "Full" and when the value is over 10 I want it to say "Over". If the value is 1-9 I want to leave it as is.I know how to accomplish one at a time but not all three together. I've been useing:
Nz([Table#],"Empty")
IIf([Table#]=10,"Full",[Table#])
IIf([Table#]>10,"OVER",[Table#])
[code]....
View Replies
ADVERTISEMENT
May 6, 2014
I have this working query:
Code:
INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA )
SELECT Left([dbo_BACKUP_ACESSOS.LOGIN],255) AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA
FROM dbo_BACKUP_ACESSOS
WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>"ACTIVE DIRECTORY") AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));
But Iwant to be able to use a set of data to be used in the Replace Statement, so I create a table to add each string I would like to have replaced by "nothing", and trying to make the replace query to look there in order to find what to replace.I also created a table where I will list the systems that I dont want in the select, so I removed the "ACTIVE DIRECTORY" and replaced by the colum that have the list of system I dont want listed.This is the result:
Code:
INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA )
SELECT Replace((Left([dbo_BACKUP_ACESSOS.LOGIN],255)),[PREFIXOS_E_SUFIXOS]![Valor],"") AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA
FROM dbo_BACKUP_ACESSOS
WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>[SISTEMAS_EXCLUIDOS]![Sistema]) AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));
The thin is that this keeps asking me to enter the parameter value for "PREFIXOS_E_SUFIXOS!Valor" and for "SISTEMAS_EXCLUIDOS!Sistema"
View 6 Replies
View Related
Apr 8, 2013
Is it possible for an update query to simply replace all the data in a table's field with the results from a query?
For example, I have Table A with the field Years. This table performs various calculations within based on the field Years.
I would like an update query that simply replaces the Years field data in Table A with a new set of years based on a query's search criteria.
When I run the below query, I get an error saying the query must be updateable- but Im not trying to update the query, rather the Table
I've attached a photo ......
View 1 Replies
View Related
Dec 2, 2014
I have a column named "stuffno" that i should enter the id of my stuff.I should enter "no 111".I did this because some stuff dont have id's so if i make a calculated field. "No " & [stuffno]I will get in the field of stuffno1 "no " for the stuff without id..Ok now i want to make another column that contains just the id of the stuff..Is there anyway to take just the id numbet the contents of "stuffno" field without "no " so the "stuffno1" will be empty or it will contain just the id.
View 3 Replies
View Related
Jun 28, 2013
I have two tables that are joined - called A and B. A has two fields, "PLACE" and "RAND" and is joined to B via field "RAND". Other than "RAND", B has several fields named 01 TOTAL, 02 TOTAL, 03 TOTAL, etc...for about 60 fields.
The "PLACE" field in table A has data that is 01, 02, 03, ect.... How do I structure a query so that whatever "PLACE" is, I can match the XX TOTAL value? In other words, i need to have the query field header be somehow dependent on the value in "PLACE".
Is this even possible?
View 4 Replies
View Related
Jul 23, 2015
I have a query with a Date field for EndDate (the dates for end-of-week, Fridays in our case) and another field for Sales (number of sales, not dollars).I want to add 4 calculated fields that represent weeks and have the Sales appear in the correct column (field) for that date.So I will have columns for 10 July 15, 17 July 15, 24 July 15 and 31 July 15 and I want the Sales for each record to land in the correct date column, based on the EndDate column. (The 4 fields is just for the sake of the example, I will actually be having dozens of these calculated date fields).I tried to do it by setting up the 4 calculated fields like:
10Jul15: Sales
and then adding Criteria like:
EndDate = #10/07/2015#
It doesnt work.
View 3 Replies
View Related
Jul 8, 2014
I was wondering if it is possible to determine weather to show a field based on the values from another field.
Field1, SHOW_Field1, Field2, SHOW_Field2
Field1 and Field are text, SHOW_Field1 and SHOW_Field2 are Boolean values
So if SHOW_Field1 is true, then show Field1 in the query else hide. I was wondering how to do this, I seem to not be able to do this for some reason.
View 3 Replies
View Related
Aug 1, 2013
I'm trying to build a query that groups data by a code that is six digits. however, I only want to consider the first 4 digits when grouping by. (ex. table has code 100023 and I only want to group by 1000). Is there a way to do this within a query instead of modifying the table itself?
View 1 Replies
View Related
May 14, 2013
Is Access SQL able to do this:I want the value from a particular column, whose name is derived from the value in the field of another column?
Eg.
Col1 Col2 Col3 Col4 Col5
1 ABC 234 Col2 ABC
2 BCD 345 Col3 345
3 CDE 456 Col2 CDE
4 DEF 567 Col2 DEF
5 EFG 678 Col3 678
6 FGH 789 Col2 FGH
As you can see, I want the value in column 5 to be derived from the value in Column 4.Is this possible to be done in SQL.
View 7 Replies
View Related
Jul 2, 2015
I tried writing a SQL query to produce the list of details in year wise DESCENDING order for the input - Agent ID.
In addition to that I need to get the sum of amount fields year wise in the report. How to achieve it???.
Find the attached report for better clarification.
First three column in the attachment is the outcome of the below listed query. Whereas the fourth column is the expected result which needs to be included..
Code:
SELECT CessioneCredito.Data_Movimento, CessioneCredito.Anno, CessioneCredito.Importo FROM CessioneCredito
WHERE (((CessioneCredito.ID_Agente)=[Reports]![R_StoricoCessCredAg]![ID_Agente]))
ORDER BY CessioneCredito.Data_Movimento DESC;
How to alter the code in order to provide the SUM of Importo field every year.???
View 3 Replies
View Related
Aug 7, 2015
I have a report that filters members by their type. On the form there is a field that shows the member's class they are assigned to, but currently it shows the class ID and I want it to show the actual class name. How do I make the report show the actual name instead of the class ID?
View 2 Replies
View Related
Aug 13, 2013
In the following simplified query, in some months(MonthOf) there were no warranties so I have a null field for AcceptedWarranties:
SELECT qryWarranty.MonthOf, qryWarranty.AcceptedWarranties
FROM qryWarranty;
In order to make my Warranty Trends graph work I need 0s. Other postings show the following statement should get the results I need:
SELECT ISNULL(AcceptedWarranties, 0 ) FROM qryWarranty
But no matter how I try to work this into the original code, the compiler finds reason to reject it.
View 2 Replies
View Related
Jan 12, 2015
Here are the 2 tables that I am working with:
Account
Account
0041980041
0051670051
0052330052
0053490053
Conversion
Account Conversion
0041 10000151
0051 10000152
0052 10000153
0053 100001524
I need to update the Account table using the Conversion table by replacing the first 4 digits in the Account table with the Conversion value. For example, the first record should be updated from 0041980041 to 10000151980041.
View 4 Replies
View Related
Dec 8, 2014
I'm familiar with writing a replace query to replace characters or strings but what I'm trying to do this time is a bit unique to me. I have a string of numbers that will either be 8 or 9 digits in length. The first 1 or 2 digits will be the State code (1-50 hence the discrepancy in number of total digits), then a 2 digit agency code, then the last 5 digits are a producer code. What I need to do is change the 2 digits for the agency part both to 0. So basically characters 6 and 7 if you're counting from the right. To me it feels like I'm trying to do it backwards so I'm having a hard time writing it.
View 12 Replies
View Related
Jul 31, 2013
I have always relied on Design View to create my queries, usually with great success, but in so doing, I have not become familiar with SQL view, so can't solve this problem:
I have a very simple query whose only function is to sum the payment amounts received for each contract number:
SELECT Payments.[Contract Number], Sum(Payments.[Payment Amount]) AS [SumOfPayment Amount]
FROM Payments
GROUP BY Payments.[Contract Number];
It is then used in subsequent queries to create an accounts payable report. It works great as long as at least one payment has been received on the contract, but if NO payments have ever been received, the Sum is actually null, so that record doesn't get included. I need a way for this query to return zero or currency zero 0.00 for all contracts with no payments yet, so those contracts will be included and the next query in line will see a zero.
In my search, it seems I need to make use of the IsNull function in my SQL (the check expression would be the sum of payments, the replacement value would be zero) OR I need to include a WHEN or WHERE in my SELECT statement above? I just don't know enough about SQL to make this happen .
View 8 Replies
View Related
May 31, 2014
I need to remove 1 final - from my string. Here is my sql.
Quote:
SELECT tblmaintenance.sernu, Right([sernu],10) AS m10digit, InStr([m10digit],"-") AS [Space]
FROM tblmaintenance;
View 3 Replies
View Related
Jul 31, 2013
the relevant fields look something like this:
TellerNum - Date - Field1 - Field2 - Field2
I need a way for my query to take all rows with the same TellerNum and add Field1, Field2, and Field3 together for those records. So, there may be 20 records with the same TellerNum, and I need Field 1, 2, and 3 to be added together and then totaled for all 20 records to give me 1 grand total. I need this to be done for each TellerNum, so that the results will be a grand total for each TellerNum. So, the results may look something like this:
TellerNum 1: 486
TellerNum 2: 300
TellerNum 3: 240
etc..
View 3 Replies
View Related
Apr 8, 2013
I have a field in my query which returns results based on a formula that is a function of other fields. The results are: Pass and Fail. I want to make a query that returns only Fail rows. When I enter Fail as the criteria, a parameter box pops up requesting information be entered before continuing.
View 1 Replies
View Related
Dec 15, 2014
I would like queries to do some calculations for me based on a field. The field in the form is a combo field and the user can choose either 1, 2 or 3. Based on the selection, I would like queries to generate some numbers for me.
This is what I have in mind but its not working.
Code : TEST RESULT: If([tlkpTEST].[RESULTID])=1, ([tlkpTEST].[RESULTID])*2, 100)
View 2 Replies
View Related
Jan 24, 2015
I would like to know what criteria is used to return records based on a field with data type attachment.
I am using "IsNull" and "IsEmpty" but neither of these work.
View 2 Replies
View Related
Jul 9, 2014
I can do it using multiple queries but I would love to find a way to do it in a single query..I have formulated a couple sample tables to show what my source data looks like and the result I would like to get. (See below)
I have a table with items and pricing. Each item will have multiple lines showing the various prices with another field determining what the price is related to.I would like to query the table to output a table showing a single item with the various pricings each in a field. To toss another variable into the mix there are multiple sets of data per item based on a dataid field. So the source has , Item,, Type, Price and dataID. I want to show Item, Price1, Price2,Price3, for a specific itemID.
Source Data
itemtypepricedataID
10115.01aaa
10126.75aaa
10139.95aaa
10227.25aaa
102313.25aaa
103119.95aaa
10327.25aaa
[code]...
I would like to filter for only the aaa.how to do it in a single query.
View 11 Replies
View Related
Jan 3, 2007
Thought this would be a good solution, but having trouble with the syntax.
=DLookUp("[StartDate]","[tblJobHistory]","Me.[EmployeeID] =" & tblJobHistory!EmployeeID & " AND Min([tblJobHistory]![StartDate])")
I am getting the dreaded #Name? can I use the Min command like this?
Any help appreciated:)
View 6 Replies
View Related
Aug 27, 2013
Right now, I'm working with an SQL code of
Code:
"UPDATE Individuals SET [ShareholderOf] = " & Me.CompanyNo & " WHERE [Name] = '" & PerName & "';"
However, doing so will obviously change the "Shareholder of" field into what the user inputs (Me.CompanyNo). What should I use if I want it to ADD the user input rather than REPLACING the old [shareholderof] value?
View 6 Replies
View Related
Apr 24, 2015
I have a table of sales information. Part of my products are kits that are a combination of individual products. (Think of it as a value meal at a fast food restaurant. You can order a hamburger, a soda, and a small french fry separately. But if you get the Value Meal #1 you get all three at a discounted price.)
What I would like to do is make a new table/query that extracts out the kits out of the original sales table and replaces the kit record with records that show the components.
I have attached a file to show as an example of how the records current look and how I would like it to look.
View 8 Replies
View Related
Feb 14, 2014
I am running a query for an apparel manufacturing facility. In my query I have a table called 'OrderForm' which is where the orders are put in. There are more than one type of fabric that can be a part of an apparel item, so as a result, there are multiple fields pulling from the 'FabricType' table. In order to get this to work in my query I created 'SubTables' for the different fabric fields. For example, I have tblFrontfab, tblbackfab, tblsleevefab, and tblcollarfab which are just extra copies of the 'FabricType' table. The actual question is that when I want to replace characters like ,./& in the fabric field but I can't use the replace function. It says that it is too complex to calculate.
I used this notation
Frontfab: Replace([tblFrontfab].[fabric],".","")
and the error was that it is to complex to evaluate.
View 1 Replies
View Related
Sep 22, 2014
For my study on academic research I need to match patents that refer to academic research as prior work with the actual prior work.
I have two tables (see attached images below).
One regarding AcademicPublications (AP), which is neatly organized with title, year, journal, volume, pages, first author, etc... 480,000 rows
One regarding Patentswhere all this information is hidden within one field, in the most messy way possible... for instance, a field could have:
Quote:
Sugita et al, "Nonsurgical Implantation of a Vascular Ring Prosthesis Using Thermal Shape Memory Ti/Ni Alloy (Nitionl Wire)," Trans. Amer. Soc. Artif. Intern. Organs, vol. 23, pp. 30-34.
or
Quote:
Willingham et al., Cell 13, 501-507 (1978).
Or many other ways.
I want to create a new table that is set up like this:
Patents.PatentNumber | AP.ID | Patents.Reference | AP.Title | AP.Year | AP.Volume | AP.PageStart
The question is: How do I match different fields from one table on one field of another and make it return another field (the ID)? Some references are too horrible to match, but I need as many as I can get.
I can imagine two queries would give me the bulk:
A match in [Title] AND [Year]
A match on ([SourceTitle] OR [AbbreviatedSourceTitle] ) AND [Volume] AND [Year] AND [PageStart]
I understand that I have to make use of the Like "*"&[value]&"*", but how do I make it return the matching ID?
View 10 Replies
View Related
