Help With Duplicate Records!! Please!! I Need It For A Morning Meeting!
Jan 18, 2005
I have a table where for every customer I have they have two lines of data. Both lines of data contain their customer number and then one line has the qty of tickets they have ordered and the second line of data has their name. How am I able to combine this data into one line? I would really appreciate anyone's help. I need this for the morning and I am looking at combining it manually so any help would be appreciated. Thank you.
In my simple database (attached), I need to mass duplicate Tasks and their Notes.
I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.
The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.
I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?
Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.
My company has a meeting every tuesday where we discuss the status of all our construction jobs. I am trying to set up a database which will allow me to build a report which shows the job number and job status of all our jobs on that particular meeting date. I would like to be able to view the entire job list and pull up the job list by date. The fields I'm trying to store are job # and job status. But I'd like to be able to go back 3 weeks and pull all the job status notes for the entire job list. I can do this by entering a job status field in the job list table but I'll end up having alot of fields in the table which I want to avoid. Is there another way I can enter this info into the database?
I have built a qry that initially shows the correct information. For example.
tblContent has 289 records with a Type = Class.
I built a Query to select from tblContent Type = Class and I get 289 records. I add additional criteria of Progress <>"Not Scheduled", I then get 206 records. I then add additional criteria Last Name <>"Demo" And <>"Care" And <>"Support". This brings up 200 records, but the query appears to duplicate each record 3 times. I do not have 3 of the same types of records.
The SQL Statement is below
SELECT tblProfile.LoginName, tblProfile.FirstName, tblProfile.LastName, tblProfile.Organization, tblProfile.CostCenter, tblContent.Title, tblContent.Type, tblContent.Code, tblContent.[Date Assigned], tblContent.[Date Started], tblContent.[Last Accessed], tblContent.Progress, tblContent.[Date Completed] FROM tblProfile INNER JOIN tblContent ON tblProfile.LoginName = tblContent.LoginName WHERE (((tblProfile.LastName)<>"Demo" And (tblProfile.LastName)<>"Care" And (tblProfile.LastName)<>"Support") AND ((tblContent.Type)="Class") AND ((tblContent.Progress)<>"Not Scheduled"));
The qry is named qryPhysical Class. I have provided the link to view the database. Can you help me?
I have some code that creates appointment that i can send to colleagues, when I run the code first time it work all ok but the second time i run it i get a run time error see pic below
But I don't get any error's if i leave outlook open have also try the code on 2 pc's but stiil same problem
1392336756_tmp_run_time_error[1].jpg
Code: Shell ("Outlook.exe") Dim outMail As Object Set outMail = Outlook.CreateItem(olAppointmentItem) outMail.Recipients.Add (Me.txtsupervisor)
I would like to design and build a database that can match one to one meetings between companies with specific time slots.
For example, we have two sets of people: buyers and sellers. A buyer could specify which sellers they would be interested in meeting. The database would then work out which buyers could meet with which sellers, and when.
I want to find duplicate records based on FirstName and LastName and delete the duplicate. Also, I want to delete any records which have a blank FirstName and LastName.
I am trying to create a command button in Access to generate a Meeting Invite. I found code for creating an Appointment and it works great but cannot get it to invite attendees.
Private Sub AddAppt_Click() On Error GoTo AddAppt_Err ' Save record first to be sure required fields are filled.
I have linked a Shared Calendar from MS Outlook in MS Access. The fields that I need which are Appointment/Meeting Date and Time are not there, only creation date/time. Is there anyway for me to get the Appointment/Meeting Date and Time from Outlook into the table?
I'm trying to have a command button send info on the form straight to Outlook as a meeting. So far, the code below works apart from the address bit. I need the code to automatically add the same 2 email addresses as recepients everytime.
Error message highlights the red line below with the following message:
Runtime error '438' Object doesn't support this property or method
I'm new to VBA, so don't understand what is wrong here. The reference is set, I've tried different permutations of the "To:" & "Recipients.Add" for the email address with no joy.
Code: Private Sub cmdSend_Click() Dim outApp As Object Set outApp = CreateObject("Outlook.application") Set outmail = outApp.CreateItem(olappointmentitem)
I have a table (which is populated by people filling in a form) which contains two fields:
Section WeekCom (e.g. week commencing)
I want to make sure that the form will not allow someone to make a double entry. For example, if Sally goes in and says enters "Benefits" as the section and "WeekCom" as 15/10/06, and then Ben tries to do the same - the form will not allow Ben to enter it.
How do you do this please? The table does not have a primary key at the moment....is there any way of doing it without setting one of the fields as a primary key?
I have a form in Access which has event date and then also an event time slot such as "Morning, Afternoon, Evening" . I need to make it so that you cannot have a duplicate time booking, i assume i will need to code some VBA but if anyone could tell me what it would be much appreciated. Thanks
I have a table that has many duplicate records. I tried importing the table and setting the field to no duplicates but did not work. How can I delete duplicate records. The primary Id id fine I have another field that I can use. it is a BAC number that is repeated many times. Here is the table I need fixed.
I hope someone can help with this. I have a table, "Blasthole Submission" which is populated by input in a form, using the code below:
Const MyTable As String = "Blasthole Submission" Const MyField As String = "Sample Name" Dim db As DAO.Database Dim rs As DAO.Recordset Dim intCounter As Double Set db = CurrentDb Set rs = db.OpenRecordset(MyTable) For intCounter = Me.txtStartValue To Me.txtEndValue rs.AddNew rs.Fields(MyField) = "TP" & intCounter rs.Fields("Submission #") = Me.SubNum rs.Fields("Sample Type") = "Blasthole" rs.Fields("XRF") = "True" rs.Fields("LOI") = "True" rs.Update Next intCounter rs.Close db.Close Set rs = Nothing Set db = Nothing
What I am hoping to do is to place a random duplicate in the table, called, for example TP111152 DUP, approximately every 50th record. Is there any easy way of doing this?
Be advised that I'm a complete novice with Access, and have used it only to view Excel files with more than 64,000 records. Any help would be appreciated.
I have an Excel file of 10,000 records entered by salesmen of possible prospects, with salesmen, prospect name, address fields. Some salespeople have entered the same prospect and address information; the only thing different in these records are the salesman name.
I know how to import the file into Access. But from this point, how do I construct a query and generate a report that shows me ONLY THE DUPLICATE RECORDS? We would like to use this report to identify the double entries and the salespeople so they can clean up the database. I know Access (I have Access 2003) can create this query, I don't have a clue on how to do it.
One hint: A post on another forum suggested " Make a group-by query (grouped on all fields except salesperson). Add a count field. Select if count >1. This should do the trick."
I'm sure this is the answer, but I don't know how to "make a group-by query", how to "add a field", etc. Like I said, I know how to import the data. That's it.
This is extremely frustrating to be this close to the answer and not know how to finish it. Any assistance would be appreciated. Thank you.
I have an order database using Access 2000, I need a report to list all agents that have ordered in the past year, how many times they ordered and sort by Office city.
I have SELECT tblCustomers.Type, tblAgentList.AgentID, tblAgentList.LastName, tblAgentList.FirstName, tblAgentList.Agency, tblAgentList.AgencyCity, tblAgentList.DeliveryAddress, tblAgentList.DeliveryCity, tblAgentList.DeliveryState, tblAgentList.DeliveryPostalCode, tblAgentList.[AgentOffice#], tblCustomers.InspectionDate FROM tblAgentList INNER JOIN tblCustomers ON tblAgentList.AgentID = tblCustomers.AgentID GROUP BY tblCustomers.Type, tblAgentList.AgentID, tblAgentList.LastName, tblAgentList.FirstName, tblAgentList.Agency, tblAgentList.AgencyCity, tblAgentList.DeliveryAddress, tblAgentList.DeliveryCity, tblAgentList.DeliveryState, tblAgentList.DeliveryPostalCode, tblAgentList.[AgentOffice#], tblCustomers.InspectionDate, tblCustomers.[Job#] HAVING (((tblCustomers.Type)="P") AND ((tblCustomers.InspectionDate)>[Enter starting Date:])) ORDER BY tblAgentList.AgentID, tblAgentList.AgencyCity, tblAgentList.AgentID;
It list all agents sorting by AgentID, with a line for each order. I would like to list each agent once with the number of times ordered, and then sort by AgentCity. Any help would be appreciated... Thanks in advance!:D
I have two table. Deliveries Created (DC) and Goods Receipt (GR).
I have a query which links the two by PO number, PO Item, Unit code and Delivery quantity.
This works fine until one PO Number has more than one delivery quantity which is equal. For example.... The PO quantity is 4,000 but, because the units can only be shipped in quantities of 1000, there are four different entries in both the DC and the GR tables each with the same PO number, PO Item number, Unit code and delivery quantity (and shipped on the same date). This of course gives 16 results for the query using this PO number.
There is one unique field in the DC table this is the posting number field. I've tried grouping by this number and taking the First values from the GR table. This works OK in that it produces Four results but, two of the deliveries were received on Day 1 and two were received on Day 2. If I take First of GR date I get DAY 1 for all, If I take Max I get Day2 for all. What I need is the correct date.
I thought I had just about finished my DB but now Ive printed out and checked my reports I notice I have several records in different types of reports showing duplicate records. Iv'e gone back over the queries and there are one or two duplicates in several of my queries that I did not notice before because there are are only the odd one or two. No matter what I do I can't stop this happening. I think it must be something to do with the dates in my payment table. PaymentID is key field - foreign key is MemberID. One member can have several paymentID's a new one every year when membership renewed. The problem seems to be when the member has two payment dates in the same year. This shouldn't be anyway but the database has not been used properly with entry dates missing, written over of wrong dates etc. Ive tried to correct this but do not want to tamper with past payment date records. I already have 'select distinct' in my queries and have tried 'distinct row' which seems to return even more duplicate records. Im pulling my hair out over this there must be a way to return the records from members showing just 1 only of their very latest payment record/date. Im using this expression in the query.
LastPaid: (SELECT MAX(PaymentDate) FROM S_Payments_Table WHERE S_Payments_Table.MemberID =S_Members_Table.MemberID)
I've had to export data from an old third party database to Access with the task of reporting on the data. I have a client table and work tables, one for each year going back to 1992. In the original database, the clients do not have a date of when they were set up. I need to find out how long clients have been with us, so I thought of going back through the work tables 2006 to 1992 and finding out where they first occurred, by looking for duplicate records in the tables. Is this a simple enough thing to do? The client table and work tables are linked through the field Client.
I am creating an ordering system which comprises of amongst others, these fields:
ID OrderHdr OrderLine
I want to ensure that if a user enters an ID, OrderHdr and OrderLine that this combination does not already exist elsewhere within the same table.
I have written a query like this:
COUNT OrderLineDetails.id, OrderLineDetails.orderno, OrderLineDetails.orderline FROM OrderLineDetails WHERE (((OrderLineDetails.id)=?) AND ((OrderLineDetails.orderno)=?) AND ((OrderLineDetails.orderline)<>?));
I will be replacing the "?" with the relevant form fields from the form, but how do I run the query and store the result in a variable and prevent the user from carrying on until they change any of the fields until there is no duplication.
On the below data I need to delete records where dep is blank or the same for the same ID. So I need to find and del recNr. 1,4,5. Any help will be appreceated!Thanks
