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 Replies


ADVERTISEMENT

Queries :: Excluding Duplicates - Show Only Unique Values

May 17, 2013

I have duplicate data in a cell, I want to hide duplicate data and display only non-duplicate data.. I changed the property sheet to only show unique values, but it keeps showing data I don't want to see...

View 3 Replies View Related

Queries :: Select Distinct For Combobox - Show Only Unique Values

Oct 20, 2014

I am trying to get a combo box to only show unique values, but I can't get it to work properly - it still shows duplicates in the list.

The Row Source is set to

SELECT DISTINCT Areas.ID, Areas.Area FROM Areas ORDER BY Areas.Area;

And the combo is bound to column 2

View 12 Replies View Related

Forms :: Can't Get Unique Values To Show In Combo Box

Jul 22, 2014

I've used this guide [URL] .... to only show unique values in a combo box. I can't get it to work, it just shows a load of blank values. If I run the expression builder part from the background, it works but it doesn't work in the combo box. Not sure what I can post to give more details?

View 13 Replies View Related

Queries :: Unique Values For A Field

Oct 31, 2013

I have a material usage table that tracks material going into products...Oftentimes the same material will be used and multiple records will contain that material.

I want my query to return only records with unique material used. I'm clicking the "Unique Values" and "Unique Records" property setting but its not working.

View 1 Replies View Related

Queries :: Merge Duplicate Values With Unique ID

Feb 28, 2014

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".

View 1 Replies View Related

Queries :: Count Unique Values Without GROUP BY

Sep 15, 2013

I am currently using the below code to query values that have a unique "OverrideDescription" and where by the "Upload Date" is always the latest.

SELECT t1.*
FROM [DaisyServiceRates-Amended] AS t1 LEFT JOIN [DaisyServiceRates-Amended] AS t2 ON (t1.OverrideDescription = t2.OverrideDescription) AND (t1.[Upload Date] < t2.[Upload Date])
WHERE t2.OverrideDescription IS NULL;

However I also need to be able to do a unique count of the [OverrideDescription] field.

I have found methods whereby I can do use, but they all use a GROUP BY function, the issue being that when I use this method it prevents be from updating the values saying "recordset not updateable".

Any method that does not use the GROUP function. I did also try preforming the COUNT on a separate table and doing as JOIN, but this also prevented updates to the date.

View 1 Replies View Related

Queries :: Comparing Text Within A String To Keep Only Unique Values

May 15, 2013

I have one field where string contains several words separated by semicolon and my goal is to be able to remove duplicates within the string and keep only unique values. Here is an example:

initial field:
xxx;yyy;ppp;yyy

targeted result:
xxx;yyy;ppp

How this could be achieved ?

View 5 Replies View Related

Queries :: Job Scheduling Database - Output Unique List Of Weeks

Sep 1, 2014

I'm writing a simple job scheduling database, so I've got resource availability (by week) and job list (by week). I've written a single-column query whose only output is a unique list of weeks with resources available, and another single-column query whose only output is a unique list of weeks with at least one job allocated.

How do I join these so that I can get a single-column list of weeks that have either (or both) of resources and jobs?

So Query1 returns:
25/08/2014
08/09/2014
15/09/2014

while Query2 returns:
01/09/2014
08/09/2014
15/09/2014
22/09/2014

I want query3 to return:
25/08/2014
01/09/2014
08/09/2014
15/09/2014
22/09/2014

View 5 Replies View Related

Queries :: How To Reduce Down Data Set To Get A List Of All Unique Combinations Of Column

Jul 8, 2014

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.

View 4 Replies View Related

Queries :: Loop To Create New Tables With Unique Field Values

Sep 20, 2013

I have a table - (Table A) that has 2 fields X and Y. I would like to write a query or script to make two new tables based on the unique values found in field X. In other words, all data where field X = 1 would be written to a new table called "1" and all data wehre field X =2 would be written to a new table called "2".I would like this done automatically.

Table A
Field X Field Y
1 a
1 b
1 c
1 d
2 a
2 b
2 c

View 5 Replies View Related

Queries :: How To Show Row Values As Column Values

Mar 8, 2015

I have a table with following two columns

TechName Version
SQLServer 2000
SQLServer 2003
SQLServer 2005
SQLServer 2008
SQLServer 2008R2
Oracle 11G
MSOffice 2000
MSOffice 2003
MSOffice 2007
MSOffice 2010

How can i show this data as components eqch version for a techname to be one component order need not follow

TechName Component1 Component2 Component3 Component4 Component5
SqlServer 2000 2003 2005 2008 2008R2
Oralce 11G NULL NULL NULL NULL
MSOffice 2000 2003 2007 2010 NULL

View 4 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

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

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

Modules & VBA :: Get Number Of Unique Values Without Separating Values That Belong To Same Block?

Dec 5, 2014

I have the following dataset in a table called NR_PVO_120. How do i pick out a number (which can change but let's say, 6) of UNIQUE OtherIDs without excluding any OtherIDs under any fax numbers?

So, if you pick OtherID from Row7 you then also must pick OtherIDs from rows 8 and 9 because they have the same fax number. Basically, once you pick an OtherID you're then obligated to pick all OtherIDs that have the same fax number as the one you picked.

If the number requested (6 for this example) isn't possible then "the closest number possible but not exceeding" would be the rule.

For example, if you take OtherIDs from rows 1-10 you will get 6 unique OtherIDs but row 10 shares a fax with rows 11 and 12. You either need to take all 3 (but that will raise the unique count to 8, which isn't acceptable) or skip this OtherID and find one with a fax that has no other OtherIDs and that isn't on the result set already. My result of 6 UNIQUE OtherIDs will need to contain ALL OtherIDs under any fax the existing OtherIDs are connected to.

So one solution is to take rows 1-6, 26. Another is to take rows 1-4,10-14.

There will be many possibilities (the real dataset has tens of thousands of rows and the number of people requested will be around 10K), as long all OtherIDs connected to all faxes on the result set are part of the requested number (6 in this case) any combination would do.

A few notes.

1.Getting as close as possible to the requested number is a requirement.

2.Some OtherIDs will have a blank fax, they should only be included as a last resort (not enough OtherIDs for the requested number).

my table (NR_PVO_120)
Row OtherID Fax
1 11098554 2063504752
2 56200936 2080906666
3 11098554 7182160901
4 25138850 7182160901
5 56148974 7182232046
6 56530104 7182234134

[code]....

A few sample outputs

one solution is taking rows 1-6 and 26.

OtherID
11098554
56200936
25138850
56148974
56530104
56148975

Another solution is taking rows 1-4 and 10-14.

OtherID
11098554
56200936
25138850
56024315
56115247
56148974

This is for a fax campaign, we need to make sure no fax number is faxed twice, that all people connected to that fax number are contacted under one fax sent.

View 12 Replies View Related

Queries :: How To Join One Value To List Of Values

Jan 2, 2014

An affiliate sent us a table of email addresses, one per record. We need to find which ones already exist in our master table. Our master table contains an email field but it may contain MULTIPLE email addresses separated by semicolons. How do we create a query (or queries) which tell us which email addresses already exist somewhere in our master table?

View 7 Replies View Related

Queries :: Return Values From A List

Feb 12, 2014

I have two tables, TBL_Students and TBL_Email. Each of these tables have a field called Category, which allows multiple values from a list.

Is there a way to return records from TBL_Students where at least one value in TBL_Students!Category = at least one value in TBL_Email!Category.

E.g. If Student A has categories Maths, Physics and Computing;Student B has categories Maths, English and History;Student C has categories Physics and Geography

I would expect:
when TBL_Email!Category = Maths,Physics : Students A,B,C to be returned
when TBL_Email!Category = Maths : Students A,B to be returned
when TBL_Email!Category = Physics,Geography : Students A,C to be returned

View 2 Replies View Related

Using List Box To Show Query

Dec 8, 2004

Please help, I have a form with 7 option buttions and a calendar. i would like to be able to run a query from the selection that is made. the way this would work is:
7 option buttions - listing different locations
Calendar - select date
the query would list any event and the start time and end time that fell on that date.
my problem is that I dont know were to start. i have been told to use SQl statements. i have tried this but only failed miserably. the events and times are in 2 different tables, i cant get a SQL stastment to work like that.
I have tried to use a List box, using the Row Source to display a query, but only 1 row showed. it still was unselectable.
Please help, dont know were to go with.

John

View 1 Replies View Related

Tables :: Count Unique Values With Different Values

Feb 7, 2014

i have one table in which ID is Primary ID with Different Values

Like

ID NAME PAN
1 A X
1 B Y
1 A X
2 C Z
2 C G
3 D U

it shows that ID 1 having 2 Name (A& B,with PAN, X & Y ,respectively).how can i get this that ID having More than 1 Value like 1 and how can i select only these records ID which having more than 1 value and how can i update values for 1 ID.

View 8 Replies View Related

Forms :: Query Does Not Show Selections From List Box

Jun 19, 2014

I have a list box (SUB CATEGORY) on my form where I can select multiple items. I have a query (Final) that has this field in it.

Problem - I cannot seem to get the list box selections to appear in the query results. What the "Criteria" should look like or maybe some code that I can try.

View 1 Replies View Related

Query Will Not Show Values In The Popup Form

Nov 12, 2005

Hi,
I have a popup form called Labor that is working perfectly fine, what i mean is that it is getting populated with data from the query I built using a single table called PrimaryBid_Master. However I have had to add many fields to this table before invoking a query on it, now I'm running close to the 255 field max limitation.
So I have created a new table called Labor_Primary with all the fields that I need and took them out of the PrimaryBid_Master table freeing up about 50 fields.

The query I made combines 2 tables: table1=PrimaryBid_Master table2=Labor_Primary, they are linked by a common field called invoice#.
But now when I’m in the PrimaryBid_Master form and click the command button to go to the popup form Labor non of the values that were populating the popup form from the primaryBid_Master form work, they were working fine the only thing I changed was the popup forms record source to the new query I built.
Here is a print screen of the new query I built using 2 tables, and I also show the old query that works fine using 1 table. http://www.roofmart.net/query1.asp

Maybe you can see some thing I did wrong; do I need some type of filter to fetch the fields? When I open the new query there is no data/values in it.

Thanks--I appreciate any help.

View 1 Replies View Related

Changing Query To Show Null Values

Jun 26, 2006

Hi,
This query will allow me to view payments that are made between 2 dates. I would like to know how to flip the query around so that it gives me the payments that have not been made. I think this would be described as returning the null values?

The SQL code i have at the momnet is:
SELECT download20060602.Date, download20060602.Description, download20060602.Amount, Members.FirstName, Members.[Mid Name], Members.Surname, Members.[Memb No]
FROM Members LEFT JOIN download20060602 ON Members.description = download20060602.Description
WHERE (((download20060602.Date) Between [Enter Start Date] And [Enter End Date]));


Any help or ideas would be fantastic.

Cheers
Phill

View 3 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







Copyrights 2005-15 www.BigResource.com, All rights reserved