Hi, I have a query which works perfectly and deletes the unnecessary records according to the criteria i have set , but i dunt want this null values in all column to display in the exported output file. how do i delete the records which are already null .
i tried delete query but it did not work . This is in microsoft Access.
Please help me
Hi, I have a query which works perfectly and deletes the unnecessary records according to the criteria i have set , but i dunt want this null values in all column to display in the exported output file. how do i delte the records which are already null .
i tried delete query but it did not work
Please help me
Hello all,
A bit of a weird one, I've got a query and the criteria for showing records is that one particular field is null. However the query is showing records with the values in the field chosen for the Is Null.
Not sure why this is happening, has anyone come across this problem before?
Thanks.
I have this small database, I would like to have your support to setup this query "QryResults" in order to remove the duplicate records, I can't find a way to get shown only true records, for some reason I'm getting duplicate rows and fake values, the query is calculating operations from two different queries and a table.
View 3 Replies View RelatedDepending on the answer of a question, i would like to get the records that have no data in that specific field, otherwise i want the query to give me all records.
I've tried this in "criteria" but it doesn't work: IIf([Question? J/N]="N";"Is Null";"")
Who can help me?
Thanks
Fred
Simple query, but I need to roll the results up with others and a null return is hurting me. I have been chasing my tail around with the nz(), input masks, IIF statements, only to prove my ignorance and possible insanity for expecting different results.
Here we go:
SELECT Count([LDB LOG].DSTHTL) AS ["tot120"]
FROM [LDB LOG]
WHERE ((([LDB LOG].[LDB Rcvd]) Between Now()-120 And #1/1/2008#))
GROUP BY [LDB LOG].[T-7 Date]
HAVING ((([LDB LOG].[T-7 Date]) Is Null));
This is being used to count open items into age buckets, the above is the greater than 120 days (discounting anything prior to 2008). If the T-7 Date is null the item is open. What is happening is that no records are being selected, resulting in the null value back.
Any help would be greatly appreciated as I have spent an unrealistic amount of time on this.
Thanks
Brendan
I have two questions, both the same query.
I need a date prompt and null records in the same line of criteria so I get all those within a certain date range under the field "CO_resp_rcvd" and those that didn't respond yet but need to -- is that possible to do both and if so how would you show me how?
This is what I have currently in my query
CO_resp_rcvd (date field)
Criteria: Between [Start Date] And [End Date]
(I need null values as well because there will be some if the CO has not responded yet but needs to)
Formula:
This formula gives me the number of bus days from the Review Date - CO_Resp_Rcvd Date and that works but if the CO-Resp-Rcvd date is null, I need it to calculate Review Date - Today's date to show the number of days outstanding for those that have not responded yet in the same formula?
Not sure how to combine it to work - the wrapper is a bus day function
This is what I have so far in the query
CO-Bus Days to Respond: Wrapper([Review Date],[CO_resp_recd]) but if CO_resp_recd is null then ([Review Date],Date())
I have what I think is a simple query returning the names of students that have been dismissed since September 2012 using a "WithdrawnDate" field. The query pulls a lot of information from other related tables (about 6 different ones), and has two expressions.
When the criteria is set to either "Is Not Null" or a date range (which is all I need), it does not return the complete set of records based on the data that fits the criteria in the main table?
Could there be some sort of join preventing all records from being returned?
I think the title pretty much sums it up....
I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.
I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.
The query is refusing to return any results...
Any ideas?
:confused: I am importing a file from excel into access. The table has about 6300 records. Some records are duplicates or even triplicates. How do I write a querie to leave only 1 of of these records and rmove the duplicates.
Second, I will be importing new files weekly that after I do the above I will need to append to the first table and then remove duplicates. How dod I do this.
thanks
I have a table t1 which contain many records, the field look like:
id f1 f2 f3 ---fn
Id is the primary key
I have some records which have different id but exact the same other fields.
like
id f1 f2 f3 ---fn
87 1 4 6 ---9
12 1 4 6 ---9
18 1 4 6 ---9
116 1 4 6 ---9
1287 1 4 6 ---9
98787 1 4 6 ---9
for those records, I only want to keep one record (any one) and remove all others. How can I do that?
Thanks.
Jeff
Can anyone suggest a way to remove duplicate records.
I've come up with this:
SELECT *, count(*) cnt FROM tableName GROUP BY fieldname1, fieldname2, .... HAVING cnt > 1
But this does not fully resolve my problem.
Thanks.
Hi,
I need to remove all duplicate records in a table. These are records that have matching Contract and Order fields. I can't do a DISTINCT query, because that still leaves one record. Thanks.
Michael
(Been a while since I had a question.) I have a table that stores a list of paper forms. the fields are ID#, display name, doccument name, and a code to determine if its a Word or Excel doccument, or an access report. Works fine; the user can click a form name (on the Forms/Reports tab on the main menu form) in a list box, set the # of copies and print the form. There are cases when a group of forms is needed (client admission is one). I have made a group table containing: GrpID, Grp Name, formID. I would like my "Form Group Edit" form to have 2 list boxes; the left one containing the (short) list of available forms; the right one displaying what forms are already (if any) in the selected form group. My idea is to have add/remove controls to manage the right list. I know this "wheel" is already invented and would like to plagerize one that works. Thanks in advance for your help.
View 2 Replies View RelatedI have a Datasheet view of a form and it displays a "+" symbol. When expanded it returns a list of related records. How do I get rid of the "+" in my form?
View 8 Replies View RelatedI have a table which has duplicate records so I want to write down the code so that when the user click on a button then it should remove the duplicate records from the table.
View 2 Replies View RelatedI have a table which has duplicate records so I want to write down the query that will remove the duplicate records from the table.
View 1 Replies View RelatedI have two tables, one is of departments, and one is of people (with a FK denoting what department this person is in). Now consider the fact that there are duplicates in the departments table, and I would like to remove these duplicates. However, the duplicates have related records (in the people table). So, before removing the duplicates, I must update the FKs in the table of people (this is the step I'm having trouble with).
Here's an example:
As you can see, the "Sales" department is there twice. And both have a related record. What I want to do is:
Update all DepartmentIDs (in tblPeople) to not point to duplicate records. In this example, that would be PersonID 2; Joe. His DepartmentID should update to "1" (as both "1" and "2" are "Sales").Delete the duplicates in tblDepartments (in this case, DepartmentID 2, "Sales").
The second step is no problem, it is only the first I am struggling with.
Also, the example posted here is just an example, the data I actually need to do this for is significantly more complex and there are many more records! In the attached database:
qry1: Simple query to find all duplicates (just used the query wizard)
qry2: Just the first row of each duplicated departments (duplicates that shouldn't be deleted). In the example above, this would be the "2", "Sales" row in the tblDepartments table.
qry3: Basically all qry1 rows that don't appear in qry2
qry4: All qry3 values, and their respective qry2 value.
This is what each of the (soon to be deleted) duplicate values' related records' DepartmentID should be updated to... There's no simpler way to phrase that, so using the example above, qry4 would return "2","1". This indicates that all people with a DepartmentID of "2" should be changed to "1" (so we can subsequently erase the department with the ID of 2.
This is as far as I have gotten. My next step is: Update all FKs in tblPeople based on qry4 (You can't set an update query's criteria to pull from another query, nor can you use the second query for the update value... or maybe you can, but I don't know about it).
I have table call "empTable" and it has an Attachment field called "Attachment". Some of the records has empty Attachment field and some record has one attachment and some has two and the attachment types are jpeg and pdf.
I want to remove all the pdf from all the records in this table automatically with VBA or something, is it possible? How to do it?
I'm trying to get a query to display only the records that have a null value in a certain field.
I know this has something to do with the Nz function, but I can't figure it out.
Hi
Been taking abreak from Access for a few years now and cant get my head round my problem.
I have been asked to manipulate data from a Training Recods database.
Basically, training consists of 26 modules, there are currently 180 people who need training. Each person has been assigned a unique number, once a module has been completed, the date it was completed is entered into the database. There are currently lots of modules which havent been completed, what I need is a way of pulling that data from the DB.
I need to know how many people need to complete each module. For example, Module 1 might need to be completed by 15 people, Module 2 might need to be completed by 27 people etc.
I have enclosed the DB with the table and the form that I am looking to populate with the data.
Anyone have any ideas, I just can't get my head round it!!!!
Hi guys,
I know how to count records so that null records would be counted but I do not know how to do this:
I have customers and works. I want to create a query that will show ALL customers and number of works done for that customer (sometimes there is no work done for customer).
How can I do it?
Thanks,
Matt
Hello everyone.
I have just joined the forum and spent the last day or so looking around for an answer that I wasn't able to find.
I have been asked by my employer to capture details of who has signed our confidentiality policy. I have made a very simple database which only has three tables, EMPLOYEE, POLICY and VERSION.
EMPLOYEE has three fields, NAME, NUMBER and BRANCH. POLICY has three fields, NUMBER, DATE and VERSION. VERSION has two fields, VERSION NUMBER and DATE. I won't worry about the VERSION table any further in this post as it doesn't figure in the problem query.
Primary key for EMPLOYEE is NUMBER. Compound key for POLICY is NUMBER and DATE (ie. the employee can sign the policy more than once and we want to record when this was, and whether the policy version changed).
I have an Access "type 3" join defined in table relationships from EMPLOYEE.NUMBER to POLICY.NUMBER.
I am trying to write a query that returns the employees that haven't signed the policy. I have no problem returning the employees that have (ie. employees whose numbers match the numbers in the POLICY.NUMBER field), but it is the EMPLOYEE.NUMBER entries that are not present in POLICY.NUMBER that are causing me grief.
I have tried using the expression builder in the query builder window (it gave me <>[tblPolicy]![Number]) which doesn't return any records (I have over 490 in the database and should be getting back at least 50 or so).
Sorry if this question is too basic.
Can anyone offer me any advice here?
Thanks in anticipation.
John
Hi All
I have a form for booking employee holidays and in that I have a subform that displays their current holiday entitlment, which work great until I enter in a new record on the main form to book some holidays and I'm ask for a Name from a parameter box?? which I press cancel or ok at which point the subform goes blank (which is whatI want) but when I enter the employees name from the combo list it doesn't update the subform?
my question is; how do i remove the parameter box and update the subform once I have selected the employee name??
cheers
I am trying to create a Membership report that shows member activity over several months.
My problem is that the software that gives me the Member Activity does not report on accounts that were not used during that month
and some members do not come in every month.
I have a Member's Details table as well as individual months activity and have queries set to show the results of each month individually (these work, obviously)
The Reports I am trying to get from Access only show the activity of Members that have some movement every month, but totally skips members that are not present for 1 or more.
The report uses these queries but fails to report any member that is not in the tables for all months.
Example:
September
Rank Member FirstName LastName Activity
1 100 Andy Todd 200
October
Rank Member FirstName LastName Activity
November
Rank Member FirstName LastName Activity
5 100 Andy Todd 50
The report needs to show: Member FirstName LastName Sept.Rank Sept.Activity Oct.Rank Oct.Activity Nov.Rank Nov.Activity
but will not because there are no values for that member in October.
I am teaching myself Access as I go, so I am sure there is an easy solution.
Any help greatly appreciated.
Andy
I recently acquired a database that is already established. When entering new date the primary key is null. I've looked at the properties and the best I can figure is it has something to do with a missing property called New Values.
View 12 Replies View Related