Queries :: Merge Unique Data With Same Persons Record
Jun 14, 2013
As mentioned before on a different topic I am building a database to sort through publication authors. I currently have the table sorted in a way that only the main author is listed for the 3000 records. Now I need a way to remove duplicate entries on a new table. Each entry is a different publication. Each record has a DOI code that is unique and an author which might be a duplicate. I want a new table listed by author once and a new field that list all of the DOI numbers for that author.
I know how to do a query to show only the unique names, but how to I also get the database to combine the records that share the same author.?
I have a query with over 85,000 lines of data where each row has a unique id but the unique id appears multiple times within the table. For example, there is a unique numeric id tide to various types of pets across four columns. So the id number 1 could appear in four different rows each with different pets. See the first attachment "Original Table"
I want to be able to create a table that has one for for each unique id with all of the corresponding pet data. See the attached "Desired Table".
I have a query with over 85,000 lines of data where each row has a unique id but the unique id appears multiple times within the table. For example, there is a unique numeric id tide to various types of pets across four columns. So the id number 1 could appear in four different rows each with different pets. Please see the first attachment "Original Table"
I want to be able to create a table that has one for for each unique id with all of the corresponding pet data. Please see the attached "Desired Table".
I'd like to extract just one TestPos record (TestPos = 1) per Patient ID. And I always want to select the record with the earliest date. So the result would be:
I can do this but it requires 3 queries: a SELECT query where TestPos = 1, a GROUP BY PatientID query and MIN(VisitDate) to get the desired record per patient, and then another query that links the two so I can get the additional variables (e.g., Complaint).
Is there a way to do this without using 3 queries? Seems inefficient.
I am using a software drawing program that intelligently link information from a drawing to a database and vice versa. I have many tables and queries that I have created over time that enable auto populating of tables, creation of tables and so on. All of which enable me with tabular documents for ordering purposes.
The problem I currently have is: - I want to create a table that looks at an existing table, analysis various fields for duplicate information and then adds one record to a new table. This is to enable me to create a spares list based on the entire list.
I have a full valve list with various items, some duplicates some not. I am required to produce a spares list that considers only one of each type.
I Have one table which have 4 column ,i.eID , NAME, PAN, DOB..in this table same id having diffrent data of Name or PAN or DOB except DOB all are text format when i m trying to select unique id which having more than 1 name it shows 0 data, but table having these,,
Right now i have a qry that takes ID - Name - Order - RepeatOrder - Month - Department.
Now when i run the qry what i want is a grand total of Order/Repeat Order by Name for the Month, what is happening is the qry is creating multiple entries for different departments. I even have Department "Group by" but still causing the issue, For example:
For APR:
4/1/14 Dep A 12 2 4/1/14 Dep B 10 2
However i do not want them separated, how to i merge the data into 1 entry? 4/1/14 22 4?
I am new to Access and am trying to reduce a dataset I am working with to make it managable for Excel. I have three columns which have three unique parameters and one column with numberical content.
Column A Column B Column C Column D NJ Red Monday 10 NJ Red Tuesday 20 NJ Yellow Wednesday 30 NY Red Monday 35 NY Green Tuesday 40 NY Green Wednesday 60
I want to elimiate column C from the dataset, and sum column D for every unique combination of Column A & Column B. I have figured out how to reduce down the dataset to get a list of all unique combinations of Column A&B,what formula I can use in a query to sum column D on my table for every unique combination of column A&B. This is a simple SUMIF in excel, but my dataset is too large for excel.
I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:
Start Date/Time End Date/Time Employee
At the moment this is what the format of my report looks like (I removed other unnecessary fields):
StartTime----------EndTime---------------Employee 12/06/2014 01:00--12/06/2014 03:00------John Smith 12/06/2014 04:00--12/06/2014 06:00------Jane Doe 13/06/2014 02:00--13/06/2014 05:00------John Smith 13/06/2014 08:00--13/06/2014 08:00------Jane Doe
I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?
Hi all I have another code Problem, Can someone help me with this. I have two fields One called cust_birthday ( date field ) And one called cust_age ( text field ) The code below I put in a module name age function.
The Problem, is I get no return from this code.
What is wrong with this code ????
Option Compare Database
Public Function Age(cust_birthday As Date, Optional cust_age As Variant) As Integer Dim dteBase As Date, intCurrent As Date, intEstAge As Integer If IsMissing(SpecDate) Then dteBase = Date Else dteBase = SpecDate End If cust_age = DateDiff("yyyy", dteDOB, dteBase) cust_age = DateSerial(Year(dteBase), Month(dteDOB), Day(dteDOB)) Age = cust_birthday + (dteBase < cust_age) End Function
I am in the process of creating a database in access to search for a persons name. However, the results will give many ansers. For example if I search for the srname 'jones' I get many names. I eed then to be able to select one of these jones's with the information relavent information.
It has many years since I have done this although i did have quite a good knowledge at the time. Plesae can you make any suggestions and answers as basic as possible !
Many thanks in advance http://forums.aspfree.com/newthread.php?do=newthread&f=18# Mad
I am using 2010 and have a bound field called Policyholder ID number in a table called Policy Information. I would like to calculate how old a person is by using the identity number which is 13 digits (South African ID) and the format is as follows - 7009215069084. Using the first 6 digits this person was born on 21 Sept 1970 and is 42 years old ( Current year of 2012 minus year of birth of 1970 = 42)
How I can include a calculated field so once I type in the identity number the age of the person reflects in a field called Current Age.
I need to add two previous record fields together in a query. I have attached a picture to illustrate.
The "StudyYears" filed is the primary key of the table the data is being pulled from. The zero's are placeholders for the sum of the previous record's data.
I have a list of people in a list box. In the list box I only have the people names listed. (example below)
1. John 2. Frank 3. Tim 4. Jessica
I want to click on one of the names and have a form pop up on the same screen that lists the persons contact info. If I had a table like listed below, could the info auto populate on a separate form.
[ID] [Name] [Number] [Address] 1. John 456-4567 123 Elm Rd. 2.
Using the following code to merge current record from an Access 97 form to a Word doc. It works fine except where there is no data in some of the address_line fields when it leaves the postcode orphaned by as many as three lines. Any ideas how to ignore empty fields and move the postcode to the line after the last populated adress_line?
Public Sub C12DocMerge(lngAppID As Long)
Dim dbs As Database Dim strSQL As String Dim rstMerge As Recordset Dim AppWord As Word.Application strSQL = vbNullString
' Return reference to current database. Set dbs = CurrentDb
I currently have a database set up with three basic forms:
Form 1 = Main menu with options to go to Form 2 and Form 3
Form 2 = Employee information form which includes email address
Form 3 = Employee document upload form
My goal is once the Employee is registered in Form 2 they can then upload a document in Form 3. When they upload this document and fill out other parameters Including their bosses name (which is captured as a record in Form 2) they click "Submit Form." I would like for this submit form button to populate an email that is updated to send to the selected "Bosses name"
Currently on the Submit form button I have:
Private Sub Submit_Record_Click() DoCmd.SendObject _ , _ , _ , _ "email .com", _ <-- this is what I want to autopopulate with the correct persons email (as well as their name below) , _ , _ "***A new Lab Report has been submitted for your review***", _ "Bosses name," & vbCrLf & vbCrLf & vbCrLf & "Please log into the Report Database and review the latest pending report. If you have any questions please contact the sender." & vbCrLf & vbCrLf & "This is an automated response generated from Microsoft Access." & vbCrLf & vbCrLf & vbCrLf & "Sincerely," & vbCrLf & "ESBU Lab Report Database", _ False DoCmd.Close End Sub
I have a query which displays two fields: client ID and order type. I want to be able to display the client only once per order type. I tried to use GROUP BY order type but that gives me a missing expression message. I also thought of using UNIQUE as YES in qry properties but I'm not sure as to which row that property applies (all of them?). What is the best way to display a unique client in one row and order type in the other? So for example for order type "CASH" I wil then have a list of unique individualIDs.
I have a list of 20 hospital patients that I am trying to use to try and establish a list of supplies or services billed on Day of Surgery, Post Op Day 1, Post Op Day 2, Post Op day Three and Post Op Day 4. My problem is when I ran Crosstab Summaries on their accounts the column headings were the actual dates of services. The dates range from January 2014 to July 2014. Is there a way I can change the dates object to the text objects listed above and then merge the accounts so the items and quantities are tabulated. If I can't do this with Access I'll have to tabulate them manually by either creating a new table and data form or by setting up an Excel Spreadsheet.
Current Crosstab Item # Description Total Qty Jan 1 Jan 2 Jan 3 Jan 4 Jan 5 201 Gauze 3 2 1 203 Misc 1 1
What I want is:
Item # Description Total Qty DOS POD1 POD2 POD 3 POD 4 201 Gauze 3 2 1 203 Misc 1 1
Keep in mind that there are 20 accounts with varying dates that have to combined into one table or query.
Hi, Is there a way to merge only the current record to Word? I was going to have access create a temp table to merge, but then realised that would stuff up my autoref numbers (which I need).
I will only be merging one record at a time, and would love to hear any advice.
I don't know VB so other instructions (or VB for dummy instructions) would be most useful.
I am having issues with MS Word mail merge. Issue is i am selecting the recepient list from access database. then i filter the list as per my need and send emails. But filter is not working properly.It is sending email to all the of records not to only selected ones. Can it be any issue with MS Access?
I have an access .adp project which is linked to a sql server tables (2008 r2), I have a button on an access form which I click and successfully gets customer details based on a query. What I wanted to know is when I am viewing a specific record i.e. details for Patient Number X, when I click on a mail merge button from their record form can the mail merge document only open with their details not all customers?
I have tried to delve into vba and modules to acheive this but I am a newbie.
Hi, I'm not sure where to post this, maybe VBA or Forms would be better, but I think it's fundamentally an index problem, so I'm posting here. I've searched both the net and here, but most of the questions are about preventing duplicate records.
I want to prevent duplicate records but save changes to existing records.
I have a form based on a table that has a 3-field-unique-index (but not the primary index, which is autonumber). I've written a function to check for existing records with the given combination of the three fields, as long as all have data (it bails if any are null). I'm calling the function from BeforeUpdate for each of the textbox controls. I was also calling it from the form's BeforeUpdate, but commented it out as it seemed redundant. I have other code there verifying that the user wants to keep the changes.
I'm trying to save the users from getting all the way to the end before discovering there's a problem. To test it, I added then deleted one character from one of the three relevant fields in an existing record, and bing, up pops my alert that I'm violating the index. Then I can't leave the field, I'm stuck there because I can't save the record.
I want it set up so that other fields in the record can be changed/updated AND so that a new record for the same person can't be entered (a dupe).
Oh, and I'm running into all kinds of problems with acCmdSaveRecord not being available. I feel like an idiot that I haven't been able to make sense of the posts about that. I can just comment it out and let Access save the record when it closes the form. But my bosses really want the users prompted about saving changes.
Any thoughts or suggestions or insights greatly appreciated. I really tried to search this out, so any tips for better searching are also appreciated.
Here's the code for the function:
Public Function CheckForClientDupes() Dim response As Variant Dim strFamID, strLName, strFName As String Dim strFilter As String
If IsNull(Me!FamilyIDNo) Or IsNull(Me!strLastName) Or IsNull(Me!strFirstName) Then Exit Function End If
response = MsgBox("There is already a client with this combination of FamilyID, Last and First names in this database. Would you like to Continue Anyway (Yes) or cancel data entry and Erase Your Changes (No)?", vbYesNo, "Duplicate Client Alert") If response = vbYes Then ' DoCmd.RunCommand acCmdSaveRecord Else response = MsgBox("You have chosen to cancel your changes. Your changes will be erased.", vbOKCancel + vbQuestion, "Data Entry Cancelled") If response = vbOK Then Me.Undo Else Exit Function End If End If End If
This is probably dead simple, but I am brain dead today.
I have two tables: Requests with fields (ID, Cust, Amount, Ref, Date)
and
Actuals with fiels (ID, Cust, Amount, Ref, Date)
Now, if Requests.Ref is null, then update Requests.Ref = Actuals.Ref and Requests.Date = Actuals.Date if and only if there is only one record in Requests and only one record in Actuals where Requests.Cust = Actuals.Cust and Requests.Amount = Actuals.Amount.
Currently, I am just doing an inner join between the two tables, but if there are two requests with a given cust/Amount, but only one Actual, then both Requests will get the same Actual Ref and Date.
