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.
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
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?
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
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.
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".
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.
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.
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?
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?
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?
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.
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
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?
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?
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:
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.
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
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
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.
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??
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!
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.
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.
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.
