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
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).
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 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.
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.
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 .......
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.
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;
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.
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];
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"));
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.
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
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).
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.
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.
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
