Query That Reveals Anyone Who Appears In Database More Than Once
Mar 9, 2013
I have a large database of names, I would like to produce a query that reveals anyone that appears in the database more than once.
For instance in the image below "Sayprasith, Nick & S Kaye S" are listed twice in the database. I would like an output showing anyone that has their name repeat two times or more within the "owner_name" column. Ideally the output would be in such a way that preserves the "account number" field as well.
Based on the above image the ideal output would be:
Account Number Owner Name
00015121 Sayprasith, Nick & S Kaye S
00015148 Sayprasith, Nick & S Kaye S
Only it would be for the whole database that consists of many thousand records.
i have set up a search from that returns email addresses for contacts based on certain criteria. the only problem is that the query will return the same email address a number of times because the query is set up to respond to a number of queries each of which a contact might posses. therefore, i need to ensure that each contact will only reappear once in a query return, they are all assigned unique ID numbers so I am guessing something can be typed in the ID criteria of the query to ensure that each ID is only returned once.
i have set up a search from that returns email addresses for contacts based on certain criteria. the only problem is that the query will return the same email address a number of times because the query is set up to respond to a number of queries each of which a contact might posses. therefore, i need to ensure that each contact will only reappear once in a query return, they are all assigned unique ID numbers so I am guessing something can be typed in the ID criteria of the query to ensure that each ID is only returned once.
I have a log in table that counts every time a user logs onto the database. Each time a user logs in it adds another entry to a table.
I have built a query to display the user name and date of log in so I could display this information in a report. The report is now getting rather long, and I am looking for a way to display each users name and have the total number of times they have logged in, not display each time they have logged in separately.
The Query has two fields "Agent Name" and "Logger Date"
The report displays the data as follows
Agent Name Logger Date User 1 26/5/13 User 1 26/5/13 User 1 27/5/13 User 2 28/5/13
What I would like to do is have a report listing each users name, with a column showing how many times they have logged on. e.g.
Good Afternoon, Could some kind person please point me in the right direction as to how to stop the 'VB code' window appearing and remaining on my task bar when I initially open up my DB. When I look at whats in the window there seems nothing untoward, but I would simply like to get rid of it as it is annoying?? Other sample db's that I open up on a regular basis are fine, so I was just wondering what could be causing it and how I could avoid it re-appearing..
On this form I have a button to open another form, this works fine. On the second form I have a button to close the form. When I press this it closes, and the first form is still there which is what I want, but somewhere along the line it has unmaximised itself and is about half the desired size. Basically I want it to stay Maximized the whole time it is open.
The way I can think to get round this is to close and re-open the forms as I navigate through them but just wondering why this is occuring?
I want to stop the beep sound from coming up when i open a messagebox, i did already uncheck 'Provide Feedback With Sound' in options. Any other suggestions?
I have a calculated control on a form that is summing other controls and then a subtotal on a subform. This is access 2007
The calculated control does not display the value until I enter a value other then the default in all of the source controls. If I hit the "refresh" button, the value appears in the calculated control for a moment, then disappears.
I have set the default of the source controls to be 0, so none are null.
the calc control value does not stay until I put a value in the subform and its subtotal thus has a value
I have a form called frmElements which has its Data Entry and Allow Additions properties set to 'No' so that only existing records can be edited, this is all good and works fine when used from the Navigation Pane.
I have created a macro which opens this form from a button on the ribbon, this is where the problem lies because it opens the form and allows new records to be added, I know this because there is an empty record with an '*' at the bottom of the form. If I change to design mode the Data Entry and Allow Additions properties are still set to 'No', when I then change back to form view the form functions as it should.
It does not seem possible to set form properties using the macro builder and it appears to override the forms property settings.
I have a form for updating contact details of customers/suppliers. Upon clicking an edit button the user can update txt boxes (as opposed to updating the table data directly and making a mistake). When they press save the below code runs:
Code: If Not IsNull(Me.txtp_fname.Value) Then Dim strFirstName As String strFirstName = Me.txtp_fname Dim FirstName As String FirstName = Replace(strFirstName, "'", "''") End If
I have a simple dbase that shows names in a table as "Smith, John" for example. My query links two tables, and should show all names in the aforementioned table. However, certain names are missing. Here's where it get's weird - if I change the spelling of their name (Smith, J for example), suddenly they will show with the correct data. There are no common strings to the missing names, but on each, when I change the spelling, each missing name appears. I have made sure the relationship is joined to show all data in the "Names" dbase.
I'm trying to generate a query that can be used for a pareto chart (Bar Chart shows the count of a defect and a line chart as a second axis counts the cumulative percentage)
I've grouped my data, and sorted the Count of each time the record appears but I cant get my head around working the cumulative percentage. My datasheet currently looks like this:
Reason Count Per Expr1 A 35 47.9 Random Numbers B 11 15.1 " C 10 137 " D 9 12.3 " E 7 9.6 " F 1 1.4 "
I have windows 7 access 2013, I had a query made, and wanted to add fields to it, so i went into the SQL and added several fields. Only the first one i entered didn't work and on the query is called "Expr1003" when in datasheet view. Its right in layout view, and even when i tried adding the same field in layout view, it made it "Expr1004". spelling is correct, in both source table and query, i don't know why this field doesn't work especially when the others did.
On a data entry form bound to a single table, there is a subform displaying all the records in the source table.If the user accidentally adds a record that they didn't mean to add (or if I add a record to test the form's functionality), and then right-click and delete the record, "#Deleted" appears in all the input controls.
I have a form that contains a blank text box for each field in a record. Until very recently it functioned correctly for years. Lately data enterers tried to fill a ten character field with five characters (ANNNN format, example X9999). Some records require an entry in this text box, some do not, so their field is left blank. Lately when a new entry is tried in this field, only the first three characters are accepted, blocking character four through ten from being entered. It acts as if the text length for this text box has been changed to three characters, but it is still the correct length in the Properties box.
The system keeps track of clients who have attended a nutrition education class and the form involved is the location the class was taught.
I have migrated an A2003 application to A2013 and in one of the A2013 forms I get a calendar icon appear all ove the place.
I uploaded a short video at
[URL]
When the form opens (shows dentist appointments by chair) the icon appears that the very top in Chair 1. I can add a patient, no problem.
When I click on an appointment in Chair 2, the icon appears in the last clicked appointment on Chair 1. When I then click on Chair 1 the icon appears on the last clicked appointment on Chair 2.There is no problem with the A2003 version. I DO NOT use the Calendar icon in my application or any third party software.
I want to use to count how many times a record is displayed in the period and lesson field, in another table, so that this data can be used to show how many people are attending a class. e.g how many times people are attending art 1, by counting how many times art one appeared in the first table.
I'm working with a table of bird survival data I am trying to summarize in a query. I've got a bit of a roundabout way to achieve my goal, but I'm curious if there is a simpler approach.
Background : In my table, each row represents a day I check a given nest and includes a [Nest ID] (not unique, multiple visits to each nest), a [visit ID] (auto numbered, so it's a unique value for each visit at each nest), the calendar day I visited a nest [Date], and [Survive] (1 or 0) depending on whether a nest survived or failed.
I'm trying to convert this detailed table into one that is more concise. Instead of each visit to a nest being a row, each nest becomes a row with 4 fields: The Nest ID, the minimum date (the day I found a nest), the last day a nest was checked (Max[Date]), and the last day a nest was checked alive (essentially max date where survival=1).
My current solution is to run 3 separate queries. The first queries the max date where survival=1, the second queries the max and min dates regardless of any other criteria, and the third brings both queries together.
I am curious if there is a way to create the same final product in a single query rather than doing multiple ones as I have done?
How to count the number of times that the data appears in a certain field (which is [Ema!l]). I have a list of ema!l addresses and I want to find out whether that ema!l address has appeared once, twice or more. I want to add this as a field in a query. I don't want to delete it or anything because it's perfectly fine for the ema!l address to appear more than once, I just want to be able to identify when the ema!l address has already appeared.
If I was using Excel I would use a Countif function to count how many times the data in the specified cell appears in the whole column, and that would give me the number. I'm not use to the language of Access so I can't figure out how to achieve this.
I tried adding a Total row to the grid in the query and then changing the total to 'count' but this just returned 1 for every row.
I have a report that i can order through buttons on report or openargs. Is it possible to get a field that would count the number of time an item appears i.e., if I sort it by employee, it would count how many times each employee is on the report, if i sort it by TypeOfMalfunction, it would count how many times each appears.
I have created a Asset database using MS Access 2010 & the same was published in SharePoint portal. Post publishing, all the "Query & Forms" are working as excepted but the "Reports" are not working.
The "Reports" is designed with "Group & Sort" option. Post grouping am able to see the "=Sum([Expression])" (i.e. Sum of the Value) value in MS Access Locally but not in SharePoint. Once i published in SharePoint, am getting "#Error" message for all the below expression wherever i have used in my reports design.
Expression Used: Below expressions are works fine locally & getting #Error in SharePoint. 1. =Sum() 2. ="Page " & [Page] & " of " & [Pages] 3. =Now() 4. =Date()
