I trying to change empty values in a query/Table to zeros so that I can use them in calculations.
I try to use the Nz function and the IsNull function but both give me the same result: "Compile Error" when I try to switch from design view to datasheet view.
This is what I put in a field: 'Expr1: nz([cancelamount],0)'
(IsNull has he same effect).
I went to multiple sites and this is how they explain the function should look like.
My question is:
do I have to enable something to use this function?
Am I typing it in wrong?
(using access 2002)
I know there a many posts about eliminating duplicate records because I did search, but I did not see anything that really answers my question.
I have a query in a local Access database that is using a Linked table from an SQL database managed by our corporate IT department. I have read access to only certain tables / fields in the SQL database. My query is based on 3 tables from the database and I have them linked together in my query.
When managers move from one site to another, they are given a different TeamID number. However, until they are replaced, the manager will have more than one TeamID assigned to him in the SQL database. Evidentally, the way our IT has the SQL database structured, when I query open items it duplicates the record for that manager because he has 2 TeamID numbers. So the only field that comes up differnt in the duplicate records is the manager's TeamID number.
I would like for my Select Query to ignore the duplicate record. I know I can use an Append Query to copy the data temporarily to a local table and set the proper fields as Primary Keys to do this. However, it would be nice if I did not have to go to all that trouble.
Is there a way to eliminate duplicate in my Select Query?
I have a query with a calculated field as follows: Age: (Now()-[DOB])/365 Even though I have set the property sheet to '0' decimal places, the results are showing about nine decimal places. Is there a way I can get it to just show the age in years without all the decimal places? I'm pretty new to DB and have seen a couple of postings, but couldn't understand how to apply them to this simple situation.
Basically i have a mortgage application system which im running a query on to see which applications have been completed in the last week or period up to now ie. last week, last month, last 3 months.
basically each application or CaseID has one or more applicants. The majority of them have two applicants. A few have one.
Problem is when i run this query which shows me: - CaseID, CLIENTNAME, LENDER, PRODUCT, MORTGAGE RATE, LOAN AMOUNT -
it brings up these fields but i only want one occurence of each CaseID????
Get the picture? Both Sipho and Nothando are applicants on the same mortgage but i only want to show one name. Anybody know how to help me out???
Ive taken up this existing system, so i think there's possibly a normalisation problem thats causing this. But i need a quick fix for now rather than redesign the whole system.
I get a negative value when i run the below expression.
The expression picks the month from the field txtmonth in form "frmYearToMonth" and search's the crosstab query "ctbYearToMonth" for the corresponding value in txtMonth.
On a click of a button from the Form the following should execute. I have created 50 queries. Each query has more than 1000 recordsets.
1. I want to count the number of rows for each query (50 queries = 50 different values) 2. create one table and populate those 50 different values into the table 3. Using those values in the table; a Stack column chart has to be created.
In simple explanation; on a click of a button the data should be processed and chart has to be created.
I am currently creating an Access 2007 database for calculating salesperson commissions. I have a table with 5 fields I'm working with: SalesRep, SOWRep, TerritoryRep, Period and Commission. I am trying to build a query that will calculate commission for a salesperson for each record where their name appears in one of the first three fields. Each time their name appears, they get the commission listed in the Commission field for the stated Period. I have managed to do this part successfully. My problem now is that I am creating a query that will sum all of their commissions by Period. I have run into a situation where sometimes a salesperson will not appear in one of the rep fields, resulting in NULL values in the previous queries. How can I create a final query that will sum correctly even if there are NULL values returned on the previous queries?
Here is what I have so far:
November: Avg((SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin1] WHERE [Period] Like "November*")+(SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin2] WHERE [Period] Like "November*")+(SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin3] WHERE [Period] Like "November*"))
NOTE: Im working in MS Access 2003, only results in this SQL/database (not mysyl, MSSQL etc)
I want to be able to ORDER an SQL query and put the NULL values last.
I have a basic databse:
Table name: PeopleTable Field names: TableID, PersonField, PersonID
The aim is to order by PersonID and put the NULL values last
See pic1.jpg;
The picture (pic1.jpg) shows the database as it is without a query.
See pic2.jpg;
Picture 2 (pic2.jpg) shows the query results when I use the orderby statement (SELECT * FROM PeopleTable ORDER BY PersonID As you can see it shows the results ordered by the PersonID however the NULL values are first.
See pic3.jpg
Desired results, it is ordered by the PeronID and the NULL values are last (NOTE this is an editied screenshot).How can i achieve this is MS Access 2003?
The time difference in access query works very fine in 18:00 (Short Time) format , but when i export to excel it came out with "########" and the value is -0.14679132479
How to solve this problem , i keep trying but it doesn't work ....
Access doesn't seem to recognize the values in a table I am updating. I am using an update query to revise prices in a column Titled "Price". I am comparing the prices in the Price column to the prices in another column with a simple IIF statement (i.e., IIF([Price]=[Column2Price],"Yes","No"). However, oddly, the formula produces a "No" result, even when the prices are plainly the same values, and all formatting for both columns is the same. Whenever I manually type over the price with the same value, the formula works and Access seems to work as intended. For example, I simply type "12.95" over the same value that is displaying in the column that I updated that is already showing "12.95". Then, after I do that, the formula works and produces a "Yes" value.
I have 2 txtBoxes and a button (txtA, txtB, btn). When opening the form I fill in txtA and txtB. Now when clicking on the button "btn" (OnClick event), the program is supposed to compare the values in the 2 txtboxes. If they are equal (txtA=txtB), then I have to do something which is not relevant to my question. In the code I type:
if (Me.txtA.Value = Me.txtB.Value) then ' DO WHATEVER End if
and it does not even go through the conditional statement if to do whatever I want the program to do inside the if. I would really appreciate anyone's help.
Thank you very much PS: By the way, thank you boblarson and rainman89 for the past question, both solutions worked, I used boblarson's but I have to click on the form when it's opened in order for the changes in the table to take effect.
I've been struggeling with this problem for a couple of days now and finally decided to post here and kindly ask for assistance. I've got a pretty strong OO background, but haven't worked with databases much...
I'm trying to set up a small client database for my company. The client companies have staff members (StaffClients). Client staff can attend Events and work on Projects, which I've managed via junction tables. Our own Staff can also attend Events and work on Projects.
I've uploaded my first draft of tables and relationships here: http://img514.imageshack.us/img514/3937/snapjl9.jpg (sorry for the blurred bits)
Now I think I've done a fairly good job in normalising the tables, but one thing that I keep stumbling across is that I need two almost identical tables: one for our Staff and one for StaffClients.
They are subtly different, e.g. StaffClients have some extra attributes, such as JobTitle, Department and one StaffClient can be the assistant of another StaffClient (also done via a junction table). The other difference is that client staff can have different roles in a project (MainContact or Assistant) compared to our own staff (Manager or TeamMember).
As a result of these differences I have set up separate junction tables for both Staff and StaffClients, but are obviously very similar.
My question is: Is that structure sound? Or can I simplify it somehow before setting up the forms for the data entry?
I have a problem with one of my tables and don't know where to start to fix it. This is where I stand: I have a table with 5 columns, there is no single primary key. I have duplicate entries of 4 of the 5 columns and a count number as the last column. I need to eliminate the duplicates of the combination of the 4 columns but add the count of the duplicates. Let me illustrate for you to understand better:
this is the original table:
col1 col2 col3 col4 col5 A B C D 1 A B C D 5 A B C D 2 W X Y Z 3 W X Y Z 5 W X Y Z 4
I need to obtain this in a new table:
col1 col2 col3 col4 col5 A B C D 8 W X Y Z 12
Oh, and keep in mind that the original table has almost 6 million rows that will probably get reduced to about 500 000 after this process.
Hello All, I am looking for any advice on the following dilemma. I have a table with new monetary transactions that come in every day. I would like to have the transactions(one debit and one credit) that net to zero eliminated from the table automatically. What I was thinking was to have an abs amount column added and run the duplicates query as a delete query based on this column, but that would not only eliminate the amounts that net to zero...but similar debit and credit amounts. Does anyone have any thoughts on the most efficient way to do this? Im sure the answer is staring at me in the face, but I cant get my head around it. Thanks in advance!
Hi i was hoping someone could help me with this. I’ve spent the last hour trying to figure it out. I'll keep trying, and let you know if i have any luck.
I have a calculation box which is based on a sub form,
(Long story short it counts stock items) when there is no stock available there is nothing to count so it returns the #Error message.
My starting expression is simply =[Child96].[Form]![QtyCalc]
I have read in this forum there is a "hasdata" function that can remove the Error#, but it may only apply to reports?
ASP Forums Link
I have tried this in an IIF statement but can't get it to work. So far I’ve been looking at variations of the following
I have made a query from different tables; however, my query is returning about 5 rows for each person because some fields in the query return more than 1 row.
This is an intersting question that my friend ask me... hope someone can help to solve it.
In a combo box i use to list out all the company in a table that with the duplicate of the company is allowed. Do someone have a good idea to filter the combo box when detect a duplicate data and just showing one of each company only? or can i create a query with just listing one of each company from the table with all those duplicate company data?
I recently MERGED two columns in a database file using the following code:
SELECT "(" & RESULT & ") " & QUALIFIER AS MERGED FROM SOIL_STL3_MERGE AS SOIL_STL3_MERGE;
The RESULT field was a number which I had to change to a TEXT field since my QUALIFIER was a TEXT field. Anyway the result was the field called MERGED which depicted the data as follows:
(100) U (500) U ...... ....... I use the Find and Replace twice to get rid of the first ( and run it again to get rid of ) resulting in a field that looks like this:
100 U 500 U
My question is can I solve this through an UPDATE query statement through SQL or some other way with a function.
I would rather automate the removal of the ( ) or change my original code to NOT put brackets around my result.
[Solved] I have a report that is based a query formed from 2 tables. The query pulls the address from one table for a client from the other table. This address then appears on the report. There are 6 lines for the address: Company Name, Contact Person, Person's Title, Address 1, Address 2, City/State/Zip. I have this working fine, until an address is shown where there is no Title, or Address 2. Then a blank line is shown. Any suggestions on how I can eliminate the blank line if there is no information to show?
I am using Access 2013 and have created a music inventory database.I have a question about formatting some text in a table. One of my tables has the following fields:
tblArtist Artist ID DVD Number Artist Last Name Artist First Name Artist Full Name
In my form, I want the artist's name to appear like this:
Mozart, Wolfgang Dylan, Bob Beatles Aerosmith
I know how to format the table to do this, and I know how to construct the form for this. To format the name, in the tblArtist table, I use the following expression in the Artist Full Name field:[Artist Last Name] & ", " & [Artist First Name]
If an artist has only one name, such as Aerosmith, I enter it in the Artist Last Name column. Artists such as The Beatles or The Rolling Stones, I just omit "The."When a one-name artist's full name appears in the form, there is a comma after their name.
Is there a simple way to eliminate the comma if there is no text in the Artist First Name column? If so, what is the simplest, easiest, most practical novice-friendly way to do this?
I am trying to figure out a logical WHERE condition. I have a Query that gives me data,
Code: Name | Start | End --------+-----------------+------------- Paul | 30-May-15 | Eugin | 21-May-15 | 28-May-15 Francis | 04-Mar-15 | 08-May-15 Samuel | 10-May-15 | 13-May-15
I want to have only Paul and Francis. As Eugin start is 21-05-2015 and End is the same month, so is Samuel's. So I want to eliminate those two records.I have checked pbaldy's web of overlapping records. But unlike that, I need something customised.
Begins and ends before range - we don't want - Yes Begins before, ends during - we want this one - Yes Begins and ends during - we want this one too - NO Begins during and ends after - we also want this one - Yes Begins and ends after - we don't want this one - Yes Begins before and ends after - we want this one -NO
The main form has a drop down box for the user to find the client record he wishes to add new visit data in the client visit history subfile.
When the client is found, a history subfile is loaded which has all of the prior client visits.
The history in the subfile contains two pieces of data:
date of the visit mm/dd/yyyy # members in clients' family NNNN
The subfile history is sorted from oldest date to most current date.
For the user to add a new visit for this client, he must scroll down to the last record in the subfile and then add the new visit for this client.
Is it possible to eliminate the need for he user to scroll down to add a new history record.
I tried to sort the history subfile in descending order of date, but that only resulted in having the old history data in the correct order, but the place to add a new visit still remained on the bottom. The user still had to scroll down to add the new visit.
I have a one-to-many query which I would like to add a subquery to eliminate all but the TOP 1 of a field.
Here is the SQL:
Code: SELECT QrySitesBatteries.SiteKey, QrySitesBatteries.SITEID, QrySitesBatteries.Battery FROM QrySitesBatteries WHERE (((QrySitesBatteries.Battery)=[Forms]![FMHome]![Battery])) OR ((([Forms]![FMHome]![Battery]) Is Null)) ORDER BY QrySitesBatteries.SITEID;
And I would like to only show the TOP 1 of the SiteKey field.
So, I think I have to add the subquery before the ORDER BY, but how to do it?
SELECT tblCase.CaseId, tblCase.ReqReceived, tblCase.Letter_AMPI FROM tblCase WHERE (((tblCase.Letter_AMPI) Between #4/1/2014# And #3/31/2015#)) OR (((tblCase.ReqReceived) Between #4/1/2014# And #3/31/2015#)) ORDER BY tblCase.CaseId;
I am looking for all records where either the field Letter_AMPI or the field ReqReceived falls between the specified date range, EXCEPT as below.
However there are cases where first there is a date for Letter_AMPI and then a few weeks letter, we get a special request for the same case which is the ReqReceived date. So for example we may get a case which has an Letter_AMPI date of 01/01/2014 (not in the date range), but a ReqReceived date of 05/10/2014 (in the date range). So I need to be able to eliminate this record because if the Letter_AMPI does not fall in the date range, it should not appear in the results.
I know that it does not work because of the OR but I need the OR to pick up the other records.
