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 have this database that do contains duplicate record (not duplicate field).What I'm trying to do here is to extract record that is unique to a new table And those record that were duplicate into a new table.the *Distinct that we used in sql will not work because it is not what I wanted. using distinc will give me unique record of the whole database.say i have this database that contain an ID:
2525 2658 2658 2658 2525 3678
so what i want is to extract unique record to a unique table and those that contain duplicate in another table.so the 3678 will be put in a new table and the 2525 and 2658 in a new table,,That way I will see which file will have duplicate
SELECT DISTINCT tblFile_Management_Received.Claim_Number, tblFile_Management_Received.Date_Checked_In, tblFile_Management_Received.Checked_In_By, tblFile_Management_Received.Copy_or_Original FROM tblFile_Management_Received;
The results would show as:
ClaimNumber Date Checked In Checked In By Original or Copy 111111111 2/16/2005 8:34:24 AM XXXX Yes 111111111 2/16/2005 1:23:19 PM XXXX Yes 222222222 2/16/2005 8:34:52 AM XXXX Yes 222222222 2/16/2005 1:24:49 PM XXXX Yes
I am trying to use "Distinct" to show the following records when the query is ran (the desired results would be each unique claim number based on the earliest check in date):
ClaimNumber Date Checked In Checked In By Original or Copy 111111111 2/16/2005 8:34:24 AM XXXX Yes 222222222 2/16/2005 8:34:52 AM XXXX Yes
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.
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 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?
in Table Ordbook there is two field ORD_NO and Location in Table Ownmas There is also Two field Ord_no and Sch_val;
but i enter the order no in table Ordbook normally.
suppose 7825,5855
but i enter the order no in table Ownmas as 7825/5855
so when im going to join these two tables by linking ORD_No and Ord_no. i find there is no match. bt my requirement is to match these fields by ANY no and want to retrieve the sch_val from Ownmas .
I'm trying to set up a query where it returns me only the items that are repeated.
ie...I have a table that has clients that are one time clients and other that are repeated. Each client has a client number. I would like to get only the clients whose client number appears more than once. Is that possible?
I try to find the ID corresponding to the maximum date+time grouped by Machine. In this case I would like to find 311 (maximum date/time for machine 115) and 308 (maximum date/time for machine 111)
The way to extract the maximum date + time is not difficult
SELECT Max([Date]+[time]) AS datevalue, tbl_TexControl.Machine FROM tbl_TexControl GROUP BY tbl_TexControl.Machine;
But how do I get the corresponding ID? Not with Max([ID]) because then ID 310 is selected which is not the maximum of the time.
Model Full Name: "Jones, John P." (this was the one not in MS' examples) Last Name Extract = Jones lastN = IIf(InStr(1,[Model Full Name],",")=0,"",right([Model Full Name],len([Model Full Name] - (InStr(1,[Model Full Name],",")+2)))First Name Extract = John firstN = IIf((InStr(1,[Model Full Name],",")+1)=InStrRev([Model Full Name]," "),Right([Model Full Name],Len([Model Full Name])-(InStr(1,[Model Full Name],",")+1)),Mid([Model Full Name],(InStr(1,[Model Full Name],",")+2),(InStrRev([Model Full Name]," ")-1)-(InStr(1,[Model Full Name],","))))
Full Name, Forename, Surname, Salutation. This data has been extracted from another database where the Surname field was not required but it is now. So i need to find a way to pull data from the salutation field into the surname field where salutation has data but surname is null. The next one when surname and salutation are null but full name has data i need to pull that across.
I have a query that I run to extract some values for calculations. Its run on an event based on a dropdown selection. At the moment, I run via:
Code: DoCmd.OpenQuery "CallMidprice"
and then close it. this seems to take more time than is necessary when I only want to refresh the results.Open query without close, switches to the table but doesn't update until I press F5.When I try requery I get an error. "Runtime error '2109': There is no field named 'CallMidprice' in the current record.
Requery without 'Callprice' seems to requery my form.
i would like to make a query in ma access can extract text between first and second character "/" and when there are not "/" in field it returns null.now data in my table are like below
Quote:
No option1 1 100 2 145/Mechanical/0800 3 120/electrical/1620 4 131/mechanical/0200/dw-001
Now I like to make a query can extract text between first and second character "/" like below: No option1 discipline
I am trying to extract the first instance of 3 numbers from a string using an update query to update another field in the same table(Master Equipment) titled "Code". the field is called "Equipment". the strings vary in length as well as the location of the 3 numbers needed, but I have examples of all possible locations the 3 number series could be located below. As you will notice, some fields do not contain 3 numbers together, for these I don't want to return a value.
i cannot build a simple query and save it as reference since the number of organizations may be 25 at the minimum. this is why i want to build it on the fly, so to speak.
I have set up a table for statistical information of students from our 8 campuses (campus, student first name last name, dob, parent info, grade, etc.). I have a query set-up for each campus.
I also have an excel spreadsheet when a student enrolls I change the number of students from each campus that are in grades 1-3, 4-6, 7-8 and 9-12.
Since I have already entered this information into my Access DB, I am wondering why do double-duty (thereby risking my forgetfulness) when I should be able to extract that information from these queries? Not sure how to do this in this grouping, per campus, giving a total count for each campus.
I have a query question . How can I extract a CHANGED value" in a column? Example here under table1. Time and TAG makes a record unique, No ID available ...
I want to have as output 10:04 Tag2 12 10:07 Tag1 11 10:08 Tag2 11 (so at 10:04 tag 2 changed in value and on 10:07 tag1 changed value and 10:08 changed Tag2)
Preferably with the previous value as well, but this is not the highest prio 10:04 Tag2 12 11 10:07 Tag1 11 10 10:08 Tag2 11 12
Is there a way I can take each entry in a memo field and put it into a text field in a separate table. The database is getting really big and the customer notes field for each record has lots of entries. The memo field looks like this:
I want to take each line and put put the date in a date field and the text in a text field in a separate table linked by CustID. Is there a way to do that?
I am having trouble extracting top 5 vendor with the sumOfSpend (actual Field name is Spend which is dis-aggregated) for each of 5 groups. Each group has thousands of transactions for dozens of vendors. The Table is designed as follow.
Table1: GroupName : Text VendorName : Text Spend : Currency.
