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.
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.
SHORT_DESCRIPTION unable to view unable to send out some stuff what is going on
Is it possible for access to query through each field, and tell me the total number of occurence for each word? In this small example, the output would be:
Unable 2 to 2 view 1 send 1 out 1 some 1 stuff 1 what 1 is 1 going 1 on 1
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"
That does the job nicely or the first bulletpoint but where from there? There must be a neater way than having a load of query columns each updating the last. ie.
I have a very simple query to determine the gender ratio of an associations membership. My SQL code neatly calculates the number of females, viz
SELECT [Mail List].[GENDER], Count([Mail List].[GENDER]) AS TOTAL FROM [Mail List] WHERE ((([Mail List].[GENDER])="F")) GROUP BY [Mail List].GENDER;
However, I wish to present this result as a percentage of total membership.
My main Table has a column titled [Member Name] so my requirement is to produce a calculation of the form "Females"/"Member Name Total" all multipliied by 100.
I sometimes get a last name with two names not connected by a dash (i.e. Smith-Jones), instead it is entered as "Smith Jones". When I get it from Excel into Access is there a way to replace the space with an underscore in Access? I know I can do it in Excel, but I need that Excel file the way it is for other purposes.
I need to loop through text files and replace the new lines following the from, to, copy and subject fields with tabs and place the associated text on the same line as the from, to, copy and subject fields.
I start out with this. See below.
From:
ABC COrp@abc.com To: XYZ Inc.@xyz.com Copy:
Me@abc.com; Myself@abc.com; Irene@xyz.com
Subject:
NOthing much
I need it to be like this.
From:ABC COrp@abc.com
To:XYZ Inc.@xyz.com Copy:Me@abc.com; Myself@abc.com; Irene@xyz.com Subject:NOthing much
As my screen name implies, I am a new to Access and I’m seeking some input to resolve an issue. I have tried numerous queries, read MSDN forums and Access help w/o success. I hope someone would have some ideas.
See ATTACHMENT for screen shot of the following scenario:
Two tables in a one-to-many relationship. The parent table (tblAdmByWard) contains the autonumber primary key (PatientID) and additional fields that will not change while the child table (tblContStayEval) has a corresponding field (i.e. PatientID) to secure referential integrity PLUS additional fields as shown that are evaluated and updated daily. During the update the current date is entered (DateOfReview) along with additional info thus creating a new daily record in the child table.
I would like to query the child table (tblContStayEval) to find and return ONLY the first occurrence (DateOfReview) that the criteria (Variance) was selected…i.e.2/24/2008 as illustrated in the screen shot. This needs to occur for all (PatientID) records but exclude any subsequent and/or similar selections. By “similar” I mean the date will be different for each record in the child table while the data in the other associated fields may or may not be.
I hope I explained this okay as I’m not sure how to go about this. Any ideas or thoughts would be appreciated.
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 .
Hi, i have a query which looks at a table named "attendance" the query includes the fields: "employee number", "absent code" (a 3 letter code), "absent reason" (list of different reasons) "shiftdate" (weekly shift dates in format: DD/MM/YYYY). If possible i would like to be able to promt the user to enter a employee number, then a absent reason (ideally from a list box) and show all occurences (with all the shiftdates) only of that particular absent reason within the last 12 months, and ideally calculates in another field the number of times that employee has been absent for that particular reason for the last 12 months.
Does this make sence?, i know how to promt the user to enter a employee number but not sure how to go about the rest, any help with what seems to be quite a challenging task to me would be excellent.
I am making a database that has to do with Preventive maintenance. Everything is going good and I came to a problem. There are recurring events. Some are weekly monthly quarterly etc all the way to 5 years. When I put in the task and a start date and the occurrence. So Say I put in a date of a task for today and its set to monthly I want the next record to be 1/13/14 for the task and go all the way up to infinity until a box is checked making it inactive.
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?
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:
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.
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.
My database contains a table that has two fields named 'occurance1' and 'occurance2', both containing a range of different dates. In a form I have in that database, I would like to have a field displaying the total counts for both 'occurance1' and 'occurance2', but only the count for both occurances that have 'Today' as a date. Preferably without having to run yet another query, so if possible I would like that as soon as an occurance that has 'today' as a date is entered in the table, the total count shows correctly in the form.
I've made a simple form to Login/Logout with radio buttons but the buttons only allow me to push a number as a value, in my case 1 or 2 for Login or Logout.
How would I make an update query to change those numbers to the equivalent text? Or is that not possible in the same field because that is 2 different data types?
The company I work for is consolidating regional server space onto a single server. I have relinked all of my tables, but my ~180 queries are still pointing to the old server.
My question: Is there a script or other process that can be executed that will search through all my queries within the database to find the string "dbo_tbl" and replace with "dbo_vwtbl"? I would very much like to avoid taking each individual query to a notepad...
I have a Table1 served by Form1..It is a list of: UnqID, process, quantity, totaltime(in seconds).I want to click on a record to bring up a filtered Form2 with the chosen record on it.What I want to be able to do is to now split the quantity (and the time) and put these new records back into Table1 and delete the original record
EG
ID1,10,write a report,2400
I want to delete this and replace it with two (or three/four etc) replacements, but still adding up to 10 quantity and 2400 seconds so that the new data could be:
ID2,5,write a report,1200 ID3,5,write a report,1200
My initial thoughts are to create a holding table to:Append filtered data on Form2 to a holding Table1hld (i don't know how to do this) delete data in Table1.then enter the new quantities into a holding Table2 (that I will input myself) and then append (through a series of queries back into Table1).The first problem is how to append (and subsequently delete) the filtered record from Form2 to Table1hld.
I have a field that contains 12 numbers. I need to replace the middle four numbers with a character so that the entire number is not readable. How do I do that?
