So, I have two tables that, cut down, look like this:
Table1: Code budget 100 5 110 7 120 3 150 6
Table2: Code actual 100 4 110 9 130 2 150 1
I have another table that is all the codes plus a description.
I thought I was being clever because I realised that there are items in Table1 that do not appear in Table2 and vice versa. I need a query that is, in effect, Table1 minus Table2.
I linked all three tables via “code”, created my query with the minus calculations and thought it had worked.
One problem. Where there is a “code” in one table but not in the other, the query puts a blank or “null” into that field. Then the minus calculation gives, say:
null - 2 = null
I’m used to Excel where:
blank - 2 = -2
Is there any way to get round this please? This must be a common problem, no?
I'm using Access 2010 and I'm a novice with databases.I have a table that contains water flow readings taken 4 times a day from multiple wells. I am trying to create a select query that will generate the daily average of the 4 flow readings.
Also, my table also has several 0 values and NULLS meaning the well was turned off - I want to exclude the zeros and nulls when I average. How to average these numbers? Do I need to restructure my table?
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"
In my table I have a numerical field for case numbers. Our case numbers are in the format of YYMM000000 (YY = 2 digit year, MM = 2 digit month, remaining 0's are consecutive case numbers). In order to properly list the case numbers in descending order, I have
caseyr: Left([CaseNo],2) (which pulls the first 2 digits being the year) officeno: Right([CaseNo],3) (which pulls the last 3 for our part of the case number)
This was working fantastic until I had to enter cases from 2009, ex: 0911000587
I have set the format for the CaseNo field to 000000000 everywhere it is. It displays the 09 cases perfectly, retaining the zero, however, my left function ignores it. In the case number of 0911000587, it pulls 91 as the first 2 digits. So, in the form that I list my cases in order, the 2009 cases are showing up first (because they're pulling as 91 instead of 09).
Is there anything I can do to force it to NOT ignore the leading zero?
I have a table "MansComps" which includes two fields, the "Index" field which contains a number, e.g "5" and the "Tabletype" field which contains a letter e.g "T". i have created a query based on this table, which combines these two fields and displays the result to a third field which I call "Components".
field: Index 1, 2, 3,....
field: Tabletype T
field: "Component" has to be like this: T00001, T00002,...
I have managed to combine the fields in my query using the expression:
Component: [Tabletype] & " " & [Index]
but the results appear without the leading zeros, like T1, T2..I have set my Index content to be a number and as a primary key and the Tabletype as a text. I also tried to change the format of Index to "00000" which works for the Index field separately but not for the Component field.Should I do something in the format of the Component field?
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.
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 .
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.
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?
(value=0 AND name <> HOT) OR (value=Null AND name <>HOT)
I also have some records (4 specifically) that have a name = nullvalue. When I run this query it picks out all the records with a name, and skips HOT(which is correct), but skips the nulls which I also want to include. Can someone help me in finding the reason for this error?
I already posted this problem in the query section, but got no where with it... here is the problem now...
I have a form with three combo boxs - cboStatus - cboFunder - cboResource on my form. Depending on what you chose as an option the subform, which is based on a query will show the data with the chosen criteria.
Only the Status field is a required field, so there are lots of nulls in the funders and the resource fields.
In the all the fields I have "ALL" or "*" as an option.
So in the criteria in the query I use this...
Like fCboSearch (forms!form name!combobox)
This calls up this function...
Public Function fCboSearch(vCboSearch As Variant)
If IsNull(vCboSearch) Or vCboSearch = " " Or vCboSearch = "*" Then fCboSearch = "*" Else fCboSearch = vCboSearch End If
End Function
This works great to show me all the records or just records with a certain criteria selected, BUT BUT BUT I cannot get it to show my the NULLS, they are no where to be found. How can I change this to show me NULLS??
I have a table which records are appended to in batch format. I would like to update a column called batch number for newly appended records with the Max of that column + 1 but I keep getting errors such as "operation must be an updateable query". Anybody have any suggestions. Mind you I am not doing this in VB or SQL formats, I use the query design format. Thanks, Jim
