Access :: 'Unique' Query

Creating Unique ID With A Query

Aug 22, 2004

i was wondering how to go about doing this, i currently have a query which returns all the payments due in the next month, going to a report which acts as an invoice. I was wondering how to create a unique id for each invoice and store the last id so that i can automatically itterate it later

View 4 Replies View Related

Unique Query Result

Nov 15, 2011

I have a query of 11 employees and their pay for jobs done. Only problem is there are 15 completed jobs with some of the employees doing more than one. When I run my query its only displaying unique name results so I'm only getting 11 results of the 15 jobs instead of all 15.How would I change the query to that the results are only unique employee names?

View 1 Replies View Related

Query To Get Unique Counts

Feb 13, 2013

I have a roster of people where each person may work on a specific task during a given period (a month, or a quarter, etc.) and each person is assigned to a team.

Team Jones has 30 people on it. There are 5 tasks that a person on that team may or may not work on during Q1 of 2013. Here are those tasks:

1. Business Analysis
2. Project Planning
3. Testing
4. Test Planning and Preparation
5. Quality Inspection

Now, what I want to do is to find the unique instances for each task where 1 or all of the 30 people worked on that task for the quarter. To further illustrate, say for Business Analysis that Mary Smith did Business Analysis in January, February, and March. She performed that task 3 times (1x in each month) but I am only concerned about knowing that she did perform Business Analysis during the quarter (so I'd like to show a 1 instead of 3).

Ideally, if everyone on Team Jones did Business Analysis during the quarter at least one time, I am only concerned about the one instance where they did and the first occurrence of it, not the other occurrences. So if everyone did do Business Analysis for the quarter, my total count should not exceed the count of members on the team (in this case, the 30 people). So the distribution could look like this (I'm just making this up but this is what I'd like to see):

Task
Team
Jan
Feb
Mar
Total

Business Analysis
Jones
7
15
8
30

My fields in the table are:
1. Task
2. User
3. Date
4. Team (there are 5 different teams)

Is this doable in a query?

View 3 Replies View Related

Can A Query Sort For Unique Values?

Nov 29, 2005

I don't know if I'm going about this problem correctly. In a nutshell:

I have a table which contains a number of fields, only a two of which are important for this task, as indicated below.

| ID | Type |

Where ID is an alpha-numeric identifier (say a store number), and there are three potential types, plus Null values. For this, we'll assume the three types are Fruit, Vegetable, and Grain (plus Null).

An example dataset might look like:

0-4 Vegetable
1-2
0-1 Grain
1-2 Fruit
0-4 Vegetable
1-2 Vegetable
0-1
0-2 Fruit
0-4 Grain
1-2 Fruit
0-4
0-1 Vegetable
0-3 Fruit

If I want to know how many stores have Vegetable, I could put "Vegetable" as the criteria in the query and set UniqueValues to Yes. Then I would get three unique values, one for each Vegetable existing at a unique store.

But, what if I want to know at which stores Fruit exists and Vegetable DOES NOT. Is there a way within Access to find all the records with "Fruit" as the criteria and then eliminate those with ID numbers matching the dataset with "Vegetable" as the criteria?

In other words, if I do my "Vegetable" criteria I end up with the resulting dataset:

0-4 Vegetable
1-2 Vegetable
0-1 Vegetable

If I then use "Fruit" as my criteria, I get the following dataset:

1-2 Fruit
0-2 Fruit
0-3 Fruit

Since Store 1-2 has Vegetable, I want to eliminate it from the list so that my resultant list is:

0-2 Fruit
0-3 Fruit

Is that possible?
Sorry for the long bizarre example, but I thought it the easiest way to make the situation clear. I'd really appreciate any suggestions anyone has on dealing with this situation!!
Thanks

View 2 Replies View Related

Counting Unique Records In A Query

Jun 1, 2006

I'm having trouble getting a query to return a simple count of unique lot numbers for a given ProductID. The data is stored in a large table where each test result of a stability program is stored. Each result has an associated lot number, product id and several other data fields. I've managed to get a combination querries to return the count, but if the lot has both real time and accelerated data then the counts are added and reported as double for each type. The current SQL is as follows.

SELECT tblProducts.ProdName, Count(qryAccelerated.Lot) AS AccelCount, Count(qryRealTime.Lot) AS RTCount
FROM qryRealTime RIGHT JOIN (qryAccelerated RIGHT JOIN tblProducts ON qryAccelerated.ProductID=tblProducts.ProdID) ON qryRealTime.ProductID=tblProducts.ProdID
GROUP BY tblProducts.ProdName
ORDER BY Count(qryAccelerated.Lot) DESC , Count(qryRealTime.Lot) DESC;

qryAccelerated and qryRealTime are simple SELECT DISTINCT querries returning the product id and a list of unique lot numbers for that ID.

(e.g. SELECT DISTINCT tblResults.ProductID, tblResults.Lot FROM tblResults WHERE (((tblResults.TypeID)=3));)

Currently the top query returns 4 in the both the AccelCount and RTCount columns when there are only 2 unique lots for the product. Other products without both real time and accelerated lots count correctly.

Any help is greatly appreciated. Thanks.

View 4 Replies View Related

Unique Results Of Two Relational Query's

May 14, 2007

Hi, I have a query with approximately 100 criteria. That is a bit much, thus I have made a table of the criteria and in the query a relation laid to this table. The criteria are countries with an increased risk on tbc. I point these query on a column with parent 1 and afterwards on the column with parent 2. Then I want the results from both query's in a table, but without double rows. I have tried this with a merge query but these only give the results where the hits for parent 1 and parent 2 are the same. This is by far not always the case, so, I miss all hits where there would be a hit for one parent only. Cumbersome tale, but perhaps there is someone who understands what I mean?

View 4 Replies View Related

Query Not Delivering Unique Records

Mar 22, 2008

I am trying to set up a query to select PaymentDate, CustSurname, CustForename, AgentName showing only the last record for each customer if the last payment is over 7 days and also include customers with no payment.

the table structure is:

tblLoans
LoanID (PK)
CustID (FK)
AgentID(FK)

tblPayments
PaymentID (PK)
PaymentDate
LoanID (FK)

tblAgents
AgentID (PK)
AgentName

tblCustomers
CustID (PK)
CustSurname
Custforename

I have tried the code as below: but it now gives me every payment for the defaulters as opposed to just the last defaulted payment. Can you shed any light on what I have done wrong.

select b.PaymentDate, a.CustSurname, a.Custforename, c.AgentName, d.LoanID
from tblCustomers a, tblPayments b, tblAgents c, tblLoans d
where d.LoanID = b.LoanID and d.CustID = a.CustID and d.AgentID = c.AgentID
and (DATE()-(SELECT max(PaymentDate) from tblPayments f where f.LoanID=d.LoanID ))>7
UNION select '' as PaymentDate, a.CustSurname, a.Custforename, c.AgentName, d.LoanID
from
tblCustomers a, tblLoans d, tblAgents c
where a.CustID = d.LoanID
and c.AgentID = d.AgentID
and (select count(*) from tblPayments where tblPayments.LoanID = d.LoanID) = 0;

View 1 Replies View Related

General :: Query Last 10 Unique Records

Apr 9, 2014

I got a large transaction file with deplicated records!! For unit price checking, I need to copy the last 10 unique records to another file with the same criteria but price is not the same as the current checking record.

For instance, I got 5000 records with around 400 records unit price is not the same as history. For each checking, I needed to copy the same criteria historical records to a tempory file and then delete corresponding duplicated records first, following by another deleting action only to keep the last 10 records according to date field.

View 2 Replies View Related

Queries :: Find A Way For Access To Find Unique Dates And Unique Names?

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

Return One Unique Instant Of A Field From A Query

Oct 16, 2007

Hi,

I've been given a flat-file database to 'improve'. One field is simply titled 'Room' and refers to a room number in a school. I want to create a MakeTable query that will run through all the records and make a table containing one instance of each room number (for normalizing). Hope that makes sense!

Any help much appreciated,

Steve

View 1 Replies View Related

Unique Query Based On Single Field?

Apr 11, 2008

Hi Guys, I'm new here and fairly new to anything indepth with Access.

I'm using Access 2003 and have a table which has a number of customer records. The two relevant fields are the CustomerID which is a simple integer and OrderDate obviously this is a date field!

What I need (to link in with the other forms) is a query that will return one record for each CustomerID it should be the most recent OrderDate.

I've had a few attempts and done a couple of searches but can't seem to find exactly what I'm looking for. If someone could point me in the right direction I'm happy to play with sample code and read up on bits other people have done (I tend to learn better that way) rather than just being given some code!

Thanks for any help you could can provide.

View 1 Replies View Related

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 10 Replies View Related

Queries :: Unique Query Is Giving Duplicates

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

Queries :: Append Query With Unique Duplicates

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

Query To Return Only Most Recent Unique Values

Sep 22, 2015

I have a set of devices that are assigned to a truck, on occastion they are moved from truck to truck, I need to be able to track where they are AND where they have been. Here is what I have:

ID (autonumber)
GPS ID (number, this is the specific ID to each device)
Truck Number (this is the specific ID for each truck
Installation Date (Date installed / moved)

I have created a query to show the data I need, I have put them in order based on date.

- I must keep each event stored on the table

Goal:

To show a list of the current location of each device, nothing more.

View 5 Replies View Related

Queries :: Access 2007 / Query Count Of Unique IDs

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

Modules & VBA :: Total Count Of Unique Values In A Query

Jan 13, 2015

I have the following code:

Code:
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Set db = CurrentDb
strSQL = "SELECT DISTINCT tbl_contract_lev.ContractID FROM (tbl_leverancier_gegevens INNER JOIN

[Code] ....

The result of the query is a list fo unique values but by including the "count" function in SQL seems not te be working in combination with "DISTINCT"...

View 5 Replies View Related

Queries :: Manifest - Query A List And Then Show Only Unique Values

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

Queries :: Append Query - Unique Records To Create Relationship

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

Modules & VBA :: Export Query To Excel 2010 Per Unique Field

Jul 19, 2014

have a query which I would like to export to excel 2010 and would like separate files saved using a unique field called [Brokerage]. The code below exports the query however does not export separate worksheets as I am missing something perhaps the OutputTo function.

Private Sub Commission_Excel_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim MyFileName As String
Dim temp As String
Dim mypath As String

[code]....

View 14 Replies View Related

MAX Date Query - How To Have A 'unique' Date?

May 10, 2007

My database holds details of visits made to customers by support staff.
I am running a query which returns details of the last visit made to a customer as below with results displayed on a webpage.

SELECT Visits.Date1, customers.CustID AS customers_CustID, customers.Customer, Visits.CustID AS Visits_CustID, Visits.Visitee, Visits.VisitType, customers.RegionID, customers.NextVisit, customers.NextVisitBy, customers.Confirmed
FROM customers INNER JOIN Visits ON customers.CustID = Visits.CustID
WHERE (((Visits.Date1)=(SELECT MAX (Visits2.Date1) FROM Visits AS Visits2 WHERE Visits2.CustID = Visits.CustID)))
ORDER BY customers.RegionID, customers.CustID

SELECT MAX on Date1 is used to select the most recent (largest) date. The problem I have is that if 2 different staff members visit the same customer on the same day, the query logically returns 2 MAX date results for that customer, whereas I only want 1 result per customer. As the dates are the same I am not sure how to proceed. It doesn't matter which of the 2 results are returned. Does anyone have any ideas? Thanks in advance :)

ps - I can't perform MAX on the VisitID as the support staff don't always enter the visit data in the correct order meaning that an older visit date might have a higher VisitID than a more recent visit.

View 3 Replies View Related

Unique ID

Jul 14, 2005

I am just about to start a new DB, but there are a few things I need to sort before i start.

I am working with a quotation system etc which will require the use of unique IDs...

eg 000123 then 000124 etc, this bit is not a problem.
The thing I would like to know how to do is when someone makes an order, I would like the quotation number to be changed slightly to add more detail.
For eg, If the quotation number that is allocated is 00123 and they then order a Conservatory, I would need to have this 00123 become 00123CNS, but this can be in a seperate field, Which I would need anyway.

The question is, How Would I go about this.
I have a table that describrives the product, TBL_PRODUCTS; ID, Product_Type, Product_ABBR

Product_Type = for e.g Conservatory
Product_ABBR = for e.g CNS

I would need the ref number and product abbr combined to create an order number.
[note] There will be a check box to say of ordered ot not, so I assume there will be an If statement somewhere?

View 2 Replies View Related

Generating A Unique ID

Nov 2, 2004

So I've read a good 100 postings or so so far on the subject of how generating a unique identifier through any other means than an autonumber can be dangerous. But I can't resist the opportunity to simply ask if there is a way to make this possible, if only through a calculated field...

In my single user database, I have an employment table with an autonumber [EmploymentID] field as its primary key. The employment table exists in a one-to-many relationship with it's child, the income table where each form of income is recorded as it is recieved so that one form of employment (or employer) can produce many unique forms of income (like weekly paycheques).

I would like to be able to generate an identifier for the income table that is a combination of the parent [EmploymentID] field and an increasing number to produce something that looks like this:

1006789-001 -|
1006789-002 -|--> Same EmploymentID
1006789-003 -|
1006790-001 ----> New EmploymentID with a reset numeric

Any ideas?
~Chad

View 4 Replies View Related

Help With My Database - Unique Id

Nov 12, 2004

Hello Everyone,

I've data already entered (access/tables) on two different computers, however for the clientid on one computer has a hypen (-) and on another - because of something they couldn't get the hypen to work - so they used the slash (/) my Q is: 1) can I search and convert these to one, 2) is it wise the have / or - in the id?

View 6 Replies View Related

Creating An Unique ID

May 20, 2005

I am very new to db programing so please forgive for such a simple program.

Here is the deal. I have a db for about 500 students.

I would like the table [Students] to create a unique ID "number" for each student. I would like the number to be based on information from the same table. For example if the student's name is John Doe and his number is 555-1212 then I would like to the id to be JD1212. I plan on using more than name and phone number but this was just to show you, what i am trying to get done.

I can do this using a form and a calculated control, but I can't get the table and form to kiss and make up. I was also told you can't save data from a calculated control in a form to a table and it is gernerally not a good idea, so I was told.

Any help would be appreciated.

Sincerely,

Pastor Nick.

View 3 Replies View Related