Limited List Rows In Distinct Row Query
Nov 21, 2006
Hello,
On a form I have a way to search for a record by using a listbox that has a Distinct Row Query from the table that the main table that the form is bound to. It looks for the Sample ID's that are associated with the samples that we test. The list ends at record 87877. We are WAY past that number in our numbering scheme but the list box does not display all the records. When the users type anything over 87877 the auto complete doesn't work and the last record shown in the list is 87877. Does anyone have any idea how to make all records show? The users use this to navigate quickly between samples but now it is broken. Is there some sort of limit?
Thanks
Greg
View Replies
ADVERTISEMENT
Jun 7, 2013
I've got two nested queries. One finds the oldest inspections from an import table, the second compares that query to the main table again and pulls all 'expired' inspections for each Service Order in the first.
These queries are trimmed down for the essential elements of my question. I can post the full SQL if necessary.
queryDoTheseFirst:
Code:
SELECT TOP 18 ImportTemp.[SO ID], ImportTemp.[Inspection Activity],
[Activity Created]+[AddDays] AS [Due Date]
FROM [Priority List] INNER JOIN (ImportTemp INNER JOIN
queryNeededFirst ON (ImportTemp.[SO ID] = queryNeededFirst.[SO ID])
ON [Priority List].Activity = ImportTemp.[Inspection Activity]
[Code] ....
The refinement I would like to make is, rather than having to pull TOP 18 activities in the final query, just pull TOP 12 [SO ID]s and however many activities come along with them (usually 1 or 2, averages out to about 1.5 so 18 is my compromise). In theory an inspector could have two inspections due on every single property, and would only get 9 unique addresses/[SO ID]s. But I can't figure out how to do that when [SO ID] is no longer unique in the second query.
I suppose I could 'number' the rows in the subquery and add a <=12 criteria on that calculated field, but I'm leery of the processing required (that table contains ~14,000 rows, and most methods of numbering seem to want to use DCount).
View 2 Replies
View Related
Oct 10, 2005
I have data which consists of:
xxxxx123 A.Nother 123456
xxxxx123 B.Jones 123457
xxxxx456 D.Smith 123458
xxxxx456 Z.Zephir 123489
How would I ensure that the query returned only unique rows (where column 1 is unique) based on the first alphabetical record of column 2?
Any ideas?
View 1 Replies
View Related
Oct 13, 2005
I have a table containing items and their prices. I need to run a SELECT Query which can return the last 5 prices for each item.
My Query is:
SELECT item, podate, price
FROM PurchaseOrders
GROUP BY item, podate, price
ORDER BY item, podate DESC, price
Let's say my Item A001 returned 12 records. I need only the 1st 5 records in my Report.
Can anybody help pls???????????
View 2 Replies
View Related
Feb 26, 2013
The column in my list-box did not show all the text i save in the table field. is there any limited size for list-box's column to show the data ?
View 2 Replies
View Related
Nov 27, 2012
I have a table of data which includes a date field and also various other fields which may or may not be filled with data.I'm trying to populate a listbox with the months for which this data is missing (a separate macro will then loop through these months to fill the missing data) But I only want each distinct month to populate the listbox - not each individual date.
Code:
strSQL = "SELECT DISTINCT month([EntryDate]), year([EntryDate]) FROM [SampleTable] WHERE [ValueField] Is Null"
Me.lstSampleListBox.RowSource = strSQL
I want to return the month in <mmm yyyy> format.
View 2 Replies
View Related
Jan 24, 2007
Hello,
I have created a query using the query builder by concatenationg several fields using the expression builder. Once concatenated, the total number of characters is greater than 255, and is therefore truncated. Since this is a query field and not a table field, there must be a way around this limitation. I can't even create the query and dump the results into a memo field because the dump will still only contain 255 char.
I could probably create the query using VBA, which creates a table containg a memo field, which is then populated by a variable containing the concatenated fields, but I would like a simpler solution.
Any ideas on how I can generate a query field that contains more than 255 char? The query is used to populate a report.
Thanks in advance.
Ken
View 6 Replies
View Related
Aug 10, 2005
Hi,
I am in need of help to sort out some records. I have tow existing queries I would like to combine and get one final set of records out of. They go like this:
Query 1. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 x x (check
Y ACD 2 x x boxes)
Query 2. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05 10/8/05 (query
Y ACD 2 8/8/05 9/8/05 10/8/05 performs calculations)
What I am looking to retrive through the third query is this:
Query 3. (unique #)
Lot Protocol Sample # 1 mth 2 mth 3 mth
X ABC 1 8/8/05 9/8/05
Y ACD 2 8/8/05 10/8/05
Where the third query only shows the calculated dates when the check box is true. So for month 1 & 2 I get dates for Lot X and for Lot Y I get dates for month 1 & 3. I have tried to go through the expression builder, but to no avail. I either get all records, like query 2 or I get nothing reported. I am not sure how to limit the records based on the check boxes.
Thanks,
CB
View 1 Replies
View Related
Sep 15, 2006
Hi, Wish if some one could help me ASAP.
I have a table which contains name, tel, email
i need to import only records which have distinct email.
I do need need to import data of all three fields but only which has distinct email.
As there are number of record which are duplicate.
They have different names but same email.
So i need to condition only for distinct email but dump the data in a new table with all three records.
so same names can have different email.
but same email can't have duplicate email.
So need only records which have distinct email.
Please help .......
View 1 Replies
View Related
Apr 15, 2013
I'd like to run an Update Query on a limited number (or percentage) of records from a Button on a Form.
[URL] .....
But instead of opening a Form, I want to Run an Update Query instead.
View 1 Replies
View Related
Feb 25, 2006
Is there a way to specifiy the background color of a row in a List Box based on the value of a field withing the table? For example, if a table has First_Name, Last Name, and Status (either a 1 or 0), I would like to set the background color based on the status.
View 2 Replies
View Related
May 20, 2014
In a query I'm trying to return a list of rows sorted by Service Type Ascending and then the last item in the list should be a row called "Add Edit Value".If I 'ORDER BY 2' then the "Add/Edit" row appears at the top which is not what i want.
My SQL:
SELECT '' As ServiceTypeID, 'ADD/EDIT VALUES' As ServiceType FROM ServiceTypes UNION SELECT ServiceTypes.ServiceTypeID, ServiceTypes.ServiceType FROM ServiceTypes
ORDER BY 1 DESC;
View 5 Replies
View Related
Feb 15, 2008
Hey all
I have data in the table.
(various IDs - in example below just 1)
Each ID has date and status.
I need to pull each ID only ones
for MAX date
If there are 2 like that (with different statuses) i need the one with lowest importance. Any advise ?
StatusLogIDDateID DateImportance
ssss02/04/200802/04/2008ghj6
ssss02/04/200802/04/2008ghj6
tttt02/04/200802/04/2008ghj5
tttt02/04/200802/04/2008ghj5
rrrrrr02/07/200802/07/2008ghj4
rrrrrr02/07/200802/07/2008ghj4
ssss02/08/200802/08/2008ghj6
ssss02/08/200802/08/2008ghj6
ssss02/11/200802/11/2008ghj6
ssss02/11/200802/11/2008ghj6
tttt02/11/200802/11/2008ghj5
tttt02/11/200802/11/2008ghj5
View 3 Replies
View Related
Apr 16, 2007
Hey all
Hope everyone is well.
Im having some probs with a DISTINCT query and hope you can help me out.
In the simplest terms I have a table that has 3 fields. Firstly the BikeManufacturer, then the BikeModel and finally the EngineCC.
Now from this table I am dynamically creating a drop down list via an AJAX request.
See it here .
When the user clicks on a manufacturer the select should be created showing DISTINCT BikeModel.
This could simply be achieved with a DISTINCT Statement but what I also want to do is order the list by the EngineCC.
Now if i put both fields into the DISTINCT statement i get duplications of the models since they dont all have the same EngineCC. But if i leave out the EngineCC from the select then I cant order by it.
Any ideas?
Thanks
View 1 Replies
View Related
Oct 2, 2007
I am working in Access 2003. I have a combo box based on the query below that works perfectly except that it shows duplicate Department Names in the combo box. I have not been able to find any way to show the Department Name only once. I have tried putting in SELECT DISTINCT in the string, but to no avail I recieve an error :
ORDER By clause(DepartmentMembers.[DepartmentMembers]) conflicts with DISTINCT.
Any light you can shed on this subject would be greatly appriciated. I have been researching forums for over two days and have not been able to generate a solution to this. Thank you in advance!
SELECT Departments.[Department Name], DATA.[BUILDING LOCATIONS], DATA.[REPORTS TO], DATA.[DIRECTOR NAME], DATA.[NUMBER OF PERSONNEL], DATA.[PC NAMING CONVENTION], DATA.[CISCO SWITCH(ES) ATTACHED], DATA.[SERVER-BASED APPLICATIONS USED], DATA.[DEPARTMENT SHARE (Z:DRIVE)], DATA.[EXTERNAL SERVERS], DATA.[Department ID]
FROM (DATA INNER JOIN DepartmentMembers ON DATA.[Department ID]=DepartmentMembers.[Department ID]) INNER JOIN Departments ON (Departments.[Department ID]=DepartmentMembers.[Department ID]) AND (DATA.[Department ID]=Departments.[Department ID])
ORDER BY Departments.[Department Name], DepartmentMembers.[Department Members];
View 14 Replies
View Related
Jan 20, 2007
I'm going nuts here...can you privide some help.
I'm trying to QUERY an ACCESS database called ARQUIVO and i would like to have from the column EMPRESAS all the DISTINCT records that have the same 'aviacao' in the INDUSTRIA column.
and it goes like this:
Code:<%Set rse = Server.CreateObject("ADODB.Recordset")sSQL = "SELECT DISTINCT empresa FROM arquivo WHERE industria='Aviacao'" rse.open sSQL,con, adOpenStatic, adLockPessimistic, adCmdText%>
But all I get in a 500 error.
The fact is that if I use "*" instead of "empresa" the query runs but i list all the records with 'aviacao' in INDUSTRIA
I have this SQL query made in ACCESS - that does what I want - but it doesn't rune in mine ASP page.
SELECT DISTINCT arquivo.EMPRESA, arquivo.INDUSTRIA
FROM arquivo
WHERE (((arquivo.INDUSTRIA)="Cāmaras Municipais"));
Would anyone help on this?
View 1 Replies
View Related
Sep 13, 2011
I have a query I have 4 fields:
E.G.
Field1 Field2 Field3 Field4
C 20 25 145541
C 40 20 145541
C 10 10 145540
D 20 20 145540
D 10 10 145538
I need to obtain:
C 70 55 2
D 30 30 2
I need to group Field1 and sum Field 3 and field3 and count the UNIQUE ID in field 4.
View 5 Replies
View Related
Sep 7, 2011
I'm trying to set up a simple query that links four tables. However, the tables are extremely large, all in excess of 1.5GB each so I had to split the tables up into four separate DBs. I've tried the following with no success:
1) Link the 4 tables in the DB which contains my primary key. This quickly inflates increases the file size above 2GB and won't let me go any further.
2) Build a remote query to connect the four tables. This looked promising until I tried to run the query and it became evident that it only knows to point to the last database source that you specified.
I'm running everything locally on my C drive. The data source are simple text files (1.6 million rows) from the FDA website.
View 3 Replies
View Related
Jul 20, 2005
Is this possible, if using the following:
SELECT DISTINCT (Format([Month],"mmm yyyy")) FROM Table;
Adding a ORDER BY [Month]; gives a "distinct and order by" conflict...
Is there any way to alleviate this?
I want to display a "mmm yyyy" formatted combobox in order (i.e. Jan 2005, Feb 2005, Mar 2005, etc...)
TIA!
View 7 Replies
View Related
Oct 26, 2004
Seems easy but I can't get it to work
Table Services
Pk_Service
Service_Name
MyDate
Fk_License
Table Licenses
Pk_License
License_Name
I need to get all the services for one license, but only once, you'll see what I mean
Pk Service Service_Name MyDate Fk_Licence
1 Base 10/5/2004 5
2 Base 12/6/2004 5
3 Super Base 11/4/2004 5
4 Base 20/10/2004 5
5 Super Base 17/7/2004 5
there's about 7000 records with the same 4 services recurring all the time
All I want for one license is the all distinct services for a chosen date
If I select License 5, and today's date, 26/10/2004, I would need the latest service (closest date but has to be inferior or equal), in this case
Pk Service Service_Name MyDate Fk_Licence
4 Base 20/10/2004 5
5 Super Base 17/7/2004 5
Here's the query I have:
select distinct Service_Name from Services where Fk_License=5 where MyDate <= SelectDate order by DateDiff('d',SelectDate,MyDate) DESC
where Fk_License is to get only the services for a particular license
distinct if to only get each service once
MyDate <= SelectDate if to get only the services older than the selected date
order by is to get the latest service cause there will be a lot of services older than the selected date
they say distinct conflicts with order by
Anyone knows how to do this query right?
View 6 Replies
View Related
Feb 21, 2014
I'm trying to create a report for how many "nasty grams" (rejection notices) my company has sent to people who keep sending in paper forms when they are supposed to file electronically.Every letter that goes out has information recorded based on whatever they sent to us - so the only remotely reliable way to count how many each person received is by the address on the envelope (people use different names, different business names, use different telephone numbers on the forms, etc).
I just built several queries that feed into a report that gets sent to my boss on a monthly basis to show the people who've sent in more than one paper form and have received our rejection notices more than once.I'm not the greatest at SQL, but I've been trying to find a way to use DISTINCT Addresses, leave all other fields the same (not DISTINCT), to:
1. Only return people who have received 2 or more letters
2. If at least one of the letters was sent more than 90 days ago
AND If at least one of the letters was within the last 90 days
-If at least one was within the last 90 days, only display the most recent send date of the letter (lots of people get back-to-back letters).
3. Display their names, addresses, telephone numbers, the date of the most recent letter sent, count of the total letters ever sent to that person. (the report will already do this, just need Max date)
My first query counts the number of times each address appears in the main table and simply only has [Address] and [CountofAddress]
My second query has the [Name], the [LetterDated] >=Now()-90, and the qryCountofAddress is linked to the main table by [Address], using [Countof Address] >=2...I have tried Selecting Unique Values in the Properties tab. Yes, I have tried INNER JOIN (but can't get the rest of my fields to display once I make addresses distinct).
View 1 Replies
View Related
Jan 23, 2015
I am trying to get a count of the unique customers in an access 2010 database
After some research it seems i should be using
SELECT Count(Distinct [Customer]) FROM [tblMain];
But when i use this i get
Syntax error (missing operator) in query expression 'Count(Distinct Customer)'.
I have tried leaving out the square brackets but this does not work....
View 3 Replies
View Related
Nov 13, 2014
i removed the DIsTINCT in my query to move some field to be updateable on the form. Once I did that my detail section of my form was empty ..why and how do I fix this problem.
View 1 Replies
View Related
Mar 30, 2006
Hi I am a noob and I'm having problems performing a particuler form.
I need a form that will auto-increment records as they are ented from 1 to 20 but once I have receached 20 I need it to start over at 1. So no record will have a higher number than 20 in this particular field.
Any help would be greatly apprciated.
:)
View 1 Replies
View Related
May 2, 2007
Hi there!
I`ve been using Access DB for a forum for about two years now, but from time to time I have to download the db and delete about a 1000 posts (+/-).
The reason for this is that the db want accept more posts unless I do so.
My question is how do I "extend" the db to accept more posts?
Thx in advance,
Sarre
View 5 Replies
View Related
Jan 3, 2007
I have a query that returns a set of records which details stock items that are older than a date given in a form. However i need to limit the results to the number of items held in stock
i.e say i want to look at stock over 1 year old, i get a list of all the stockids, and the date added.
Say there is a stock level of 3 for a particular stockid the results should be limited to the first 3 records that are over 1 year old. Rather than the whole list of dates I get now.
Any help or direction with searching terms would be appreciated
thanks:confused:
View 2 Replies
View Related
