Queries :: Unique Record Table Query
Oct 22, 2013
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.
View Replies
ADVERTISEMENT
Feb 19, 2015
I have a table like so:
Code:
PatientID VisitDate Complaint TestPos
1 4/5/2003 Coughing 1
1 1/2/2007 Sneezing
1 5/1/2008 Unknown 1
2 2/1/1988 Unknown
2 4/2/1988 Unknown 1
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:
Code:
PatientID VisitDate Complaint TestPos
1 4/5/2003 Coughing 1
2 4/2/1988 Unknown 1
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.
View 3 Replies
View Related
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.?
View 7 Replies
View Related
Mar 4, 2013
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
View 1 Replies
View Related
May 28, 2013
I have a table with data like this
Field_1,Field_2,Field_3
A,B,5
A,B,3
A,C,7
A,C,6
X,Y,4
X,Y,3
I need a report where I can only see
A,B,5
A,C,7
X,Y,4
That is Group by Field_1 and only show the records which has Field_2 with the max. value of Field_3
View 1 Replies
View Related
Feb 21, 2015
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,,
View 2 Replies
View Related
Aug 4, 2015
I have a unique query which lists all the films that we are screening over the next 3 months. I have added a COUNT field so that I can see how many of each films we are screening.
The problem is that i get duplicates of some films - and this may be because we may hold several copies of some films. I have attached two images which might explain this better!
What I could do with is knowing how to make it so that i get a list of films booked and how many of each, regardless of which copy of the film is used.
The SQL is:
Code:
SELECT DISTINCTROW dbo_Films.[film name], Count(dbo_Films.[film name]) AS [CountOffilm name]
FROM ((dbo_Films INNER JOIN dbo_filmCopies ON dbo_Films.ID = dbo_filmCopies.tblFilms_ID) INNER JOIN dbo_EventsFlicks ON dbo_filmCopies.ID = dbo_EventsFlicks.filmCopyID) INNER JOIN dbo_Venues ON dbo_EventsFlicks.venueID = dbo_Venues.ID
WHERE (((dbo_EventsFlicks.datefield)>=#8/1/2015# And (dbo_EventsFlicks.datefield)<#1/1/2016#))
GROUP BY dbo_Films.[film name], dbo_Venues.southhub, dbo_Venues.northhub, dbo_Films.Specilaised
ORDER BY dbo_Films.[film name];
View 4 Replies
View Related
Sep 11, 2013
I'm attempting to create an append query that will add new records only if there isn't an equivalent record already existing. Typically I would add the existing table to the query, and only add new records if the same do not exist. In this case, the table is maintaining records over time (start date and end date).
I have 4 tables:
[t_employees]
employee_ID
employee_name
[t_cities]
city_ID
citiy_name
[t_city_assignment]
city_assignment_ID
start_date
end_date
employee_ID
city_ID
[t_temp_employees]
effective_date
employee_ID
city_ID
I'm checking if [t_temp_employees].[effective_date] <> [t_city_assignment].[start_date]. However, if the employee has historical entries it will still add a record (in fact, it'll add multiple records).
How can I append a new record only if one for the same time period does not exist?
View 1 Replies
View Related
Jan 16, 2014
I have a basic query off a currency table :
Quote:
SELECT tblCurrencies.CcyID, tblCurrencies.Ccy
FROM tblCurrencies
ORDER BY tblCurrencies.Ccy;
Now - I have a separate table of balances, which is linked to the currency table by the same CcyID, and which also has identifiers to link it to other tables (e.g. AccountID)
As part of the above query, I want to return the count of unique AccountID's in the balance table for each currency. So in other words, I want to know, for each currency, how many unique accounts exist?
Each AccountID could appear one or more times in the balance table (one-to-many relationship), so I only want to count the number of unique ID's.
So I started with the following :
Quote:
SELECT tblCurrencies.CcyID, tblCurrencies.Ccy, Count(tblBalances.AccountID) AS NoOfAccounts
FROM tblCurrencies INNER JOIN tblBalances ON tblCurrencies.CcyID = tblBalances.CcyID
GROUP BY tblCurrencies.CcyID, tblCurrencies.Ccy
ORDER BY tblCurrencies.Ccy;
But this just gives the number of AccountID's per currency (regardless of duplication within them)
I found this article which informs me that a Count(Distinct) query won't work in Access and to use subqueries instead.
View 14 Replies
View Related
Oct 1, 2013
One Manifest can have many line items. One line item can only have 1 designation. Each manifest may have many line item with same designations.
For example:
Manifest #0001
Line Item 1: N
Line Item 2: H
Line Item 3: U
Line Item 4: N
Line Item 5: P
Line Item 6: H
Table set-up
CurrentCY
CurrentCYIDPK
WasteCategoryIDFK
LineItemInformation [Line Item 1, Line Item 2, etc]
ManifestDataIDFK
ManifestData
ManifestDataIDPK
ManifestNumber [0001]
WasteCategory
WasteCategoryIDPK
WasteCategory [H, N, U, etc]
I'd like to query the line items for each manifest so the end result has the manifest number (Manifest #0001) in a field and the designations (N, H, P, U) in fields on a report.
View 14 Replies
View Related
Mar 22, 2015
I have created a linked Excel table in Access 2010 called 'tblExcelLinked' and I have a form called 'ASB Log Form' for the purposes of presenting the data in a more readable manner that is easier to view, plus link other fields of data that are not directly related to the 'tblExcelLinked'.
Because there is no unique ID in the 'tblExcelLinked' to create a relationship, I have created a table called 'tblASB', which allows me to add other table data linked from same d/b.
I now want to update the 'tblASB' with data from the 'tblExcelLinked', but only append new records from 'tblExcelLinked', but my inadequate append query is duplicating the records each time I run it, rather than just adding the new ones.
Once sorted my next challenge is a macro so that this runs automatically rather than being manually triggered.
View 3 Replies
View Related
Nov 30, 2013
Based upon a specific date (varDate), I want to select the record that is active (who drives the lease car).
row 1 user X from 13/11/2013
row 2 user Y from 15/11/2013
row 3 user Z from 17/11/2013
I want to find the active user on a date. So on date 13/11/2013 (=varDate), user X is active, on 14/11/2013 (=varDate), user X is still active, on 15/11/2013 (=varDate), user Y is active, on 16/11/2013 (=varDate), user Y is still active and on 17/11/2013 and later, user Z is active.
View 1 Replies
View Related
Aug 4, 2014
I have a database used to track my personal assignments, created about six years ago using Access 2003 on Windows XP. Recently upgraded to Access 2010 on Windows 7. At some point thereafter, I started having the following issue:
When a new record is created, that record gets added to the table, but doesn't show-up in any query, form, or report until after another new record has been added. The most recently added record cannot be located to view or update, except in the table, until after another new record has been added to the table. Queries, forms, & reports now always lag behind by one record.
None of the queries, forms, or reports tested contain filters. I have several multi-user databases that I also support and none of those users have reported having this problem. This is only happening on my personal database.
I've re-created this database once or twice in the past to resolve other issues, but would like to avoid that route this time around, if possible.
View 7 Replies
View Related
Aug 1, 2014
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?
View 2 Replies
View Related
Jul 1, 2014
I am trying to determine the best method for how to handle this query using Access 2013. I have a clients table that contains the following:
clientID fName lName admissionDate dischargeDate
1 John Doe 05/06/2014 06/27/2014
2 Jane Doe 04/24/2014 05/15/2014
3 Steven Smith 05/15/2014 NULL/Empty
4 Chris Davis 06/12/2014 NULL/Empty
Then there is a WeeklyProgressNotes table that is there for the person that is responsible for auditing the clients charts. It does not contain the actual weeklyprogressnotes, it only contains a Yes/No field and a date field for the date the weeklyprogressnote was completed. Like below:
noteID completed dateCompleted clientID
1 yes 05/08/2014 1
2 yes 05/14/2014 1
3 yes 04/25/2014 2
I am creating a form that the auditor can open to determine what weeks she needs to check for each client to see if they have their weeklyprogressnotes completed that week. The weeks run Mon - Sun and there will be no record in the WeeklyProgressNotes table if she has not yet checked and confirmed for that week. So the form would basically look like this:
fName lName week completed date clientID(hidden)
John Doe 5/19/14-5/25/14 Checkbox Null 1
John Doe 5/26/14-6/1/14 Checkbox Null 1
John Doe 6/2/14-6/8/14 Checkbox Null 1
John Doe 6/9/14-6/15/14 Checkbox Null 1
John Doe 6/16/14-6/22/14 Checkbox Null 1
John Doe 6/23/14-6/29/14 Checkbox Null 1
Jane Doe 4/28/14-5/4/14 Checkbox Null 2
and so on.......
I have thought about creating an SQL statement to select all of the clients and then creating a function that determines their admission date within the specific week and their discharge date withing the specific week and then create a loop with another SQL statement with a BETWEEN clause for all the weeks and determine if there is an entry in the WeeklyProgressNotes table or not. If not then I would display out the above info. I'm not sure if there is an easier, less search intensive way of doing it. Maybe an SQL query that can cut done on some of the looping.
View 5 Replies
View Related
Nov 14, 2013
I have table which store set of number
table: parameter
field: Branch
550
660
770
880
I want to use enter query criteria so that it can filter all record from parameter table, How can I do? or any VBA code can serve same purpose?
View 6 Replies
View Related
Jun 20, 2013
I have a database that is used to allocate appointments to our staff. It has 2 tables, one that lists the clients we need to call in that day, and another that stores details of each contact attempt. I'd like to design a query that find all clients who we have not dealt with so we can easily get their details in a list. I know what the criteria for the query would be, but I'm stuck for how to actually execute it. Here are the details.
Table tClients stores the current clients - primary key is named "clientRef"
Table tContactEvents stores each contact attempt and the date/time is stored in a field named "dateTime".
When an entry has been dealt with successfully a yes/no field named "completed" will be set to "Yes".
There may be many attempts to contact a specific client on a given day, unsuccessful attempts will not have the completed flag set.
Once the completed flag is set that client will be ignored so no further entries will appear.
So I need a query that searches tContactEvents for the most recent match to each number in tClients.clientRef and checks if the completed flag is set. If the completed flag is false, or if the number has no match (i.e. no contact attempts made yet) then the clientRef should be displayed. I also need this to be restricted to the current date, as the same client could have rebooked their appointment to a different day.
View 10 Replies
View Related
Aug 2, 2013
I have a query run that gives me a list of records that I view on a continuos form. What I want is to press a button and run a macro/Append Query to add a Single Summary record to another table.
For example my query spits out this data
Part # Quantity Serial Number
GO2 1 123
GO2 2 456
GO2 2 789
What I'm looking to get is
Part Number Total Quantity Serial Number 1 Serial Number 2 ..
GO2 5 123 456
I'm stuck on a couple of things.
1. Getting a new single row to append.
2. Getting Serial Numbers from several records to save on to a single record.
View 4 Replies
View Related
Aug 3, 2006
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.
thanks,
M
View 3 Replies
View Related
Apr 4, 2007
I want to select unique country names from a recordset and now my code is
strSELECT = "select distinct country"
The problem is that ASIA and Asia are different, but it only selects one.How can I include both of them?
View 5 Replies
View Related
May 22, 2007
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
strFilter = "[strFamilyIDNo] = """ & Me!FamilyIDNo & """ And " & "[strLastName] = """ & Me!strLastName & """ And " & "[strFirstName] = """ & Me!strFirstName & """"
If DCount("*", "tblClients", strFilter) > 0 Then
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
' Me.FamilyIDNo.SetFocus
End Function
View 3 Replies
View Related
Nov 23, 2006
Is it possible to create a combo box to list all record ID, when selected that record appears on the form?Ifso how would i go about this?
Thanks in advance?
Andrew
View 1 Replies
View Related
May 11, 2006
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.
So how do I structure this SQL?
Thanks,
David
View 3 Replies
View Related
Oct 23, 2004
Hi I have a very simple table, with say 5 fields (all text). There is only 1 table. The first field is name, and I want to enforce its uniqness across all other table.name values. I am completely new to Access database and am not sure how to do this.
Any help would be greatly appreciated.
Thanks, Edin
View 3 Replies
View Related
May 23, 2012
I am trying to assign a unique two letter code to a set of record. From AA..AB..BA....all the way to ...ZZ, how do i go about doing this ?
View 14 Replies
View Related
Sep 23, 2011
Example 1:
2011-1
.......
2011-3893 etc.
Currently I have an Access form which produces a new unique number to identify each new record created. To do this I use the unique ID autonumber from a table to identify the new records. I would like to change from this simple number to the the above format per example 1. The four digits to the left of the hyphen would always be the current year and digits to the right of the hyphen would be the unique auto incrementing numbers such as from my table. I need the year to auto increment by 1 each September 30th (new business year) and I need the numbers to the right to auto reset to 1 to start uniquely identifying records again for the new incremented year. As each record is closed I need the number to be written as a single entity in the new format to my database.
Example 2: After September 30th.
2012-1
.......
2012-447 etc.
View 3 Replies
View Related
