Access 97 - Query Help - Newbie - Removing Duplicates
Jan 25, 2007
Hi,
just finding my way in Access 97 with no real support. I have made a query that creates duplicate records when I only need to see one for a certain criteria (lets say criteria X). This is due to the data feed involved in making the query where criteria X is found in numerous records.
What I want to display is only one record per entry containing an exact match for criteria X.
Finding it hard to articulate this but I would need something that defines:
If criteria X is the same, just display one record.
Hope this is somehow clear as I am struggling a bit. Would be gratefull of some help.
Thanks
View Replies
ADVERTISEMENT
Aug 14, 2006
I am running a query to return records from a table. I want to eliminate records from the query if the values contained in one field are duplicated. I have tried using the DISTINCT keyword but it only works if the entire record is unique or if I was retieving just one field
Any suggestions appreciated
View 1 Replies
View Related
Aug 19, 2004
Hi,
I'm running MS Access (2002 SP-2)
I have a table that has the following columns in it:
Sent NBR (text) / Sent Date (date/time) / PIC (text)
It's a customer listing of our customers and which long distance carrier they have selected. Unfortunately my data also shows OLD customers that no longer own that phone number. Because of this, various phone numbers are listed numerous times, but only the Sent NBR with the most current Sent Date is the one I need to see. The others are irrelevant and need to be deleted. I'd manually delete the duplicated Rows but I have over 115,000 duplicates!!
Do you guy know of a way to eliminated the duplicates for each Sent NBR and only show me the Sent NBR's with the Most current date?
Any help would be greatly appreciated!
Scott
View 9 Replies
View Related
Dec 12, 2011
I have data that I want to combine. However the separate data sets have some duplicates.
Let's say it is Customer Num field.
What I am saying is I am trying to combine the data sets to make a table with Customer Num and Customer Name.
I want to eliminate the duplicates. What is the best way to do this? Is there a feature in Access for this?
View 5 Replies
View Related
Aug 9, 2013
I have a table with few Employee ID re-appearing over and over again for two different job status.
Data Example
EMP ID Job Code Emp Name Country Job Status Time Spent (In Hrs)
222 9250 XYZ ABC Complete 6
222 1059 XYZ ABC InComplete 12
222 5050 XYZ ABC Complete 7
222 1050 XYZ ABC InComplete 9
111 2750 MNM IJK Complete 2
111 2150 MNM IJK InComplete 3
111 6550 MNM IJK Complete 7
111 8250 MNM IJK InComplete 9
I need an output which merges the data based on Emp ID, however, adds the time spent for completed and incompleted job status in 2 rows.
Output Requirement:
EMP ID Emp Name Country Job Status Time Spent (In Hrs)
222 XYZ ABC Complete 13
222 XYZ ABC InComplete 21
111 MNM IJK Complete 9
111 MNM IJK InComplete 12
Which query to use? I use Access 2010.
View 1 Replies
View Related
May 10, 2005
High everyone I've had a look around the FAQ's and other threads but haven't been able to find what I'm looking for.
I have a table that contains two fields, one being 'part number' and the other being 'quantity'. Unfortunately there are multiple duplicate 'part numbers'. I am looking at keeping only one instance of the duplicate 'part numbers' but at the same time I want to add the 'quantities' together to keep a running total.
For example:
Before
Part Number Quantity
a1 1
a2 2
a3 2
a1 1
a2 1
a1 4
a1 1
After
Part Number Quantity
a1 7
a2 3
a3 2
Any help would be greatly appreciated.
Cheers
View 5 Replies
View Related
Jun 17, 2013
I have created a database which is used as a sign in and out machine for students. I have 2 forms a 'sign in' which has a lookup list containing all student names. as well as date and time and a 'sign out form' which uses a lookup list from the sign in form.
It collects data fine but when a student signs in and out a second time there name is duplicated in the 'sign out' drop down list. The student must select the right name out of the duplicates otherwise it wont record.
Im just wondering if there is some sort of fix to this.
View 1 Replies
View Related
Dec 23, 2014
i have queries and tables with several duplicated "username" records. What should i do to remove the record with a smaller value of "win/loss" and keep the duplicated "username" with a bigger value of "win/loss".
View 1 Replies
View Related
Mar 24, 2007
Hi
I have a Customer table with a number of fields including ID (unique primary key), Name, Address and LabelCount (integer).
What I want to do is produce a mailing list report/query which will print out the number of address labels (taken from the field LabelCount) for each record in Customer.
So, for example
1, John Smith, 2 Long Drive, 5
2, James Jones, 4 Some St, 2
3, Billy Bob, 8 High St, 0
The report will print out 5 address labels for John Smith, 2 for James Jones and none for Billy Bob.
Thank you all for any help you can give.
Jaconat
View 2 Replies
View Related
Apr 3, 2006
I think this is an easy one...
I have two tables - one is a Master. I want to delete the records in the Master if they are in the second table. Here's my SQL and I can't get it to work:
DELETE from TestMaster
INNER JOIN on JoinedDupList
ON TestMaster.ID=JoinedDupList.ID
WHERE JoinedDupList.ID = TestMaster.ID
I'm being told I need to define the table to delete from... I thought I defined it.
Thanks!
View 2 Replies
View Related
Aug 24, 2005
Hi all. I am fairly new to access and am trying to do something that shouldn't be that hard. I have a simple form with one text box and a command button on it. I have the command button set to run an update query.
My problem is that when I enter data and press the button, I don't know how to get the entered data into the query?
I know this is simple to do, but since I am new to access, I can't figure it out. Does anyone have a simple example or step by step details that they can share with me?
Thanks
Chris
View 2 Replies
View Related
May 1, 2008
I get a very strange error when trying to create a find duplicates query. When i try to select a table that has duplicate values it brings up a message box and it says that i must pick a table that has at least one field that's not a memo or OLE data type. I look at my table and I have niether of those in it...pretty much just text and number fields. Does anyone know what's going on?
View 3 Replies
View Related
Jul 7, 2005
Hi all, i tried searching, but didn't really come up with an answer
i have an access database I'm trying to link up dynamically with a website, but everytime i try to link up to fields from tables in the databse, i'm getting an error claiming i dont have permission. i have administrator access to the files, yet it's always happening. im under the impression the only thing is remove all the security levels and groups/users so there are none in place, but i wouldnt want to have to copy+ paste everything
is there an easy way to remove the security so i can use it dynamically on a webpage? if i have to make a new database, is there an easy way to copy it all?
thanks in advance
adam
View 1 Replies
View Related
Aug 22, 2004
i once saw an access database that gave the user to the option to hide the access screen that is usually behind the windows of your actual database. how do i do this? also if anyone has any other useful tips for making a database look more professional. is there an easy way to do splash screens? thnx for the help.
View 1 Replies
View Related
May 1, 2006
The short Questions:
1) How do I safely remove replication from a copy of an MS Access 2003
database? Safely as in without affecting any of the other replicated copies?
2) How can I find all the copies of this database still being replicated to
after the design master has been lost?
The long Questions:
Hello All, I’m new to Access replication and have started at a company that
uses several Access databases with replication. We are creating a new site
using a similar database structure. I’ve made a copy and would like to start
by removing replication both because it is not necessary in our environment.
Also I’m about to make some database changes and am worried they changes or
record deletions will be propagated. As mentioned I do not believe there is
any reason to use replication for this project so I would like to remove it
for all databases, unfortunately several of the databases are very critical
so I want to be as informed as I can before making any changes since if I
break it and do not know enough to fix it my company would loose a lot of
money. Is there anyway from a given database (besides the replication master,
it has been long lost) to find all the databases that are replicated?
Lastly before undertaking this project is there any advice anyone here can
give me to better my chances of success? Any lessons learned?
-Brian Sky
View 1 Replies
View Related
Feb 6, 2008
I did not know where to ask this question or how to ask, so please excuse me.
We have an Access 2007 database that updates an Access 2003 database. My problem is, the data in 2007 is formatted and when the 2003 table is updated, it displays the html formatting tags. Is there a way to remove the formatting (convert to plain text from rich text?) during the update?
View 1 Replies
View Related
Jan 23, 2014
I am currently using the code
Code:
<customUI
xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon startFromScratch="True">
[Code]...
To remove the file button from access. however the more commands button in the quick access toolbar renders this method pointless unless i can remove the more commands button. How to remove the "customise quick access" button or failing that how to remove the more commands button?
View 1 Replies
View Related
Jun 18, 2014
I am very new to access less than 1 week since i started trying to build an horse racing database, i am trying to link data from 2 different sources via the horses name however one source displays this with the horses country of origin in parenthesis foe example FRANKEL(GB) and the other source displays the name as just FRANKEL, to be honest i haven't yet tried the link but guess it will fail.
I am therefore looking to get rid of the parentheses and their contents from an access table field and create another field without them.
In Excel i use the formula B1:
=TRIM(REPLACE(A1,FIND("(",A1&"("),FIND(")",A1&")")-(FIND("(",A1&"(")-1),"")) and that works fine.
I guessed the solution for Access would be newname:
TRIM(REPLACE([frhorse.NAME],FIND("(",[frhorse.NAME]&"("),FIND(")",[frhorse.NAME]&")")-(FIND("(",[frhorse.NAME]&"(")-1),""))
But this doesn't work as in returns undefined function "FIND" in expression error.
View 7 Replies
View Related
Oct 3, 2005
I have a date field that shows the date like 12-Sep-05 (medium date). I have a report in which the date has to display without the placeholders (e.g., 12Sep05).
I believe what I need to do is create a field in the query to remove the placeholders but I don't know how to write the code. Will anyone be willing to show me? Any help would be appreciated.
View 2 Replies
View Related
Mar 21, 2007
Hi,
I'm designing a database for work and am using buttons instead of the navigation features already standard with access.
I need some form of code?? for my button. I want a user to be able to click this button when finished entering data; access will then save the data, close the form and return the user to a main menu, like a switchboard.
Any suggestions or indeed code, lol, would be very helpful
Regards
Chris D
View 3 Replies
View Related
Oct 11, 2005
Ok, I've been given a task to create a hierarchy diagram for my company, unfortunately the diagram has to be created using one excel spreadsheet. I would prefer it if the database could be created using the smallest amount of user input, so all he user has to do is import the spreadsheet to access and hit a button then the hierarchy is created.
I'm currently only going one step at a time, trying to sort the data from the spreadsheet using queries. But this is the problem:
Raw Data table
ID, Firstname, Lastname, Status, SupervisorFirstName, SupervisorLastName
Table1
ID1, Firstname1, Lastname1, Status1, SupervisorFirstName1, SupervisorLastName1
The idea is that the hierarchy diagram should start with the boss and move down to the mere siblings like me, and everyone will be connected. But for one reason or another this isn't the case, so at the moment I am trying to move the persons record if their supervisors name is not in the tree, so the SupervisorFirstName and SupervisorLastName are not in the Firstname and Lastname column.
I have been using the following SQL
INSERT INTO Table1 ( ID, LASTNAME1, FIRSTNAME1, Status1, SUPERVISORLASTNAME1, SUPERVISORFIRSTNAME1 )
SELECT ID, LASTNAME, FIRSTNAME, Status, SUPERVISORLASTNAME, SUPERVISORFIRSTNAME
FROM RawData
WHERE (((RawData.SUPERVISOR_FIRST_NAME)=FIRST_NAME) AND (RawData.SUPERVISOR_LAST_NAME)=LAST_NAME);
When I change the LastName to "SMITH" I know how many records I should get and it works, but how can I go through both columns finding any matches??
I may have described this quite badly so if you need any more info please let me know.
View 3 Replies
View Related
Jun 29, 2006
Hi all,
I'm just having a dabble in VBA for Access so I'm fairly newbish!
I have a field [Reference Number] that increments in the table by 1 everytime - but the user has to enter this manually (its not auto-incrementing). How can I do the following: when the user selects a new row in the table it will automatically look in the table for the last entered [Reference Number], increment it, and put that value in the Userforms TextBox??
Hope you understand this,
TIA
View 5 Replies
View Related
Jul 12, 2006
I hope this is the correct place for this type of question.
I have two tables, let's call one the "record table" that holds each record of interest. Let's call the other one the "rule table," as each entry is a "rule." The two fields of the rule table are rule and description.
I have a form with a list box and a text box. I'd like the user to be able to choose a rule from the list box and this would control the "rule" field in the current record. However, I'd like the text box to display the description corresponding to that rule, by looking it up in the rule table.
I know I've probably made it sound very complicated, but I assure you I'm trying to do something very simple here. Thanks for any help anyone can give me!
View 10 Replies
View Related
Oct 5, 2006
I am fairly new to Access, so please bear with my hopefully easy to answer questions. I am trying to create a database for a university where we input all of our student leaders information (name, email, student ID number, etc) along with the groups they are involved with so we can track and see who is involved with alot, and so we have easy access to their contact info if we need to do that. Anyway, I have already created a database to take in all the information and spit out the reports. It is probably pretty messily put together, but like i said, this is my first time working with Access on this scale.
My question is, when I created a report to display all the students involved with a specific activity, I ran into a bit of a problem. There are two students with the same last name, and for the activity in question, they both should be shown. However, it only shows one of their names (I have it set to show the last, first) but it shows both of their activity status. What am I missing to be able to split the names show its shows both names?
Here is a link to download the access file, and perhaps someone can tell me how to fix it.
http://us.f13.yahoofs.com/bc/440a2a54me2cd2c62/bc/access/Involvement+DB.mdb?bf8mTJFBardSN6ge
All of the sensitive information has been removed. It will still work without it though.
Thanks!
View 3 Replies
View Related
Apr 27, 2007
I am trying to learn first how do i lock a db so that it will keep users from tampering with the forms and queries, second i am trying to learn how can i transfer the form, queries, etc. to a new ms access database without having to recreate them all over again. I recieved updates from a source of mine and it contain new information, the old database is archived. And the new db with new information is keep separate from the old information in the new database so i had to recreate the form everytime.
View 1 Replies
View Related
Apr 24, 2014
Im trying to write a query that shows all the container movements. Yet when I run the query qryFullHistory I get a duplicate value for container Off Island. Ive tried adding some criteria that says that the DateRequested has to be between the ImportDate and ExportDate but that doesnt seem to work. There are duplicate entries for container Off Island in tblContainerDetails as the same container has arrived and left and then returned on another voyage. Yet there is no entry for the second voyage in the tblMEMRContainer.
A brief description of the tables is:
tblMEMR Movement requests details
tblMEMRContainers the containers that were moved on the movement request. There can be more than 1 container for each request.
tblContainerDetails details and dates for the container when it arrived and when it left
There are other tables but these are the 3 that are used in the query.
View 8 Replies
View Related
