Queries :: Pulling TOP DISTINCT Rows In Final Query
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).
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 have an Access db needing populated via a worksheet with unknown # rows and known # of columns. Before pulling this data in I must do a row count to determine how many records will be added to the db. Otherwise, it would be a very simple thing to just copy the contents of the worksheet over.
Known: - Only using DAO. - The access table is already created matching the column count in the worksheet. - The spreadsheet will have an unknown row count. - Some rows in the spreadsheet will have various cells empty. - A completely empty row will mean EOF.
Need: - I need to determine how many rows are in the spreadsheet before copying into db.
Problem that occurs from attempt: - Overflow issue. I seem to be counting every single row on the worksheet, even though only 2 contain data.
Any ideas out there? Pasting below my latest attempt.
Dim strStorage as integer strStorage = CLng(xlsWST.Rows.Count)
I am currently trying to pull a query off from a table that needs to have 3 columns side by side, linking to the same ID number. I seem to have managed to do this by making three seperate queries for each column I need, however, when I put it into one query, it is only pulling through the data form the second column (a total of 273 rows), when it should be pulling through the data from the first column (800+ rows).
user_change primary key = user_change_id (AutoNumber) user_id (Number) which relates to the user_id from "users" table change_type (Text) action_date (Date/Time) ...
In user_change I record any changes made to the users table.
What I am wanting to do, within a query, is pull basic details from the "users" table (forename, surname, etc.) which is working fine, but also add in SPECIFIC data from the user_change table if it exists.
I want to pull ALL rows from the users table, not just specific rows, and not just rows where my criteria for the user_change table match.
This is the data I want to pull from user_change...
The MOST RECENT action_date WHERE change_type is LIKE "*issued*".
However there won't be a change_type LIKE "*issued*" for everyone - I want it to be included only if it exists.
At the moment my query is ignoring any users who don't have a user_change record with "*issued*" in the change_type value. I'm also getting duplicate user rows where people have more than one value for "*issued*" - I only want the most recent one...
Unable to use Between query without pulling dates out of range.I
f I set the Criteria to Between [Enter the start date:] And [Enter the end date:] and input the dates 1/2/2014 & 1/15/2014 i'm pulling data for those dates but for year 2011, 2012, 2013 and 2014 as well.
if I enter in Between #1/2/2014# And #1/15/2014# it gives me just the dates I requested.I need to be able to have a user use the macro and just enter in the dates they need data for.
Also, I am using the criteria on a date ime field that I have set up an expression on - CreateDate: DateValue([TABLE]![date])the date in the table is stored with time and I just need to show the date.
I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.
Tables: Days with 7 records Hours with 24 records Workers with as many people that sign up to work the different hours Schedule signifying the worker, day, and hour which are signed up.
As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:
What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:
I am trying to get a total count of rows from a query on my tickets, Each ticket has a date and an ID number
Code: SELECT tbl_ticket.ticket, tbl_ticket.entrydate, Count(tbl_ticket.[entrydate]) AS [Row Count] FROM tbl_ticket GROUP BY tbl_ticket.ticket, tbl_ticket.entrydate HAVING (((tbl_ticket.entrydate) Between #1/1/2011# And #1/31/2011#));
I am trying to get this query built so I can attach it to a form.
I am trying to get three bits of data from this Query
1 a total count of all the Tickets in a given month
The ticket #'s and their date of entry.
So far this gives me a great list of Tickets and the date they got put in the system, but then for the total count it gives me 1 for each line. Even if some have the same date.
I have a table where and account could have multiple rows with different data like Applied_Date and Trans_Code, and AMOUNT. The AMOUNT in two of the rows will be a positive and a negative and will be zero each other out. I am trying to create a query that will only return the the rows that do not offset each other. Here is an example of my table:
ACCOUNT CODE APPLIED_DATE AMOUNT 292020 M 5/11/2012 ($33.95) 292020 11 5/14/2012 $33.95 292020 A 5/30/2012 ($33.95)
I need query to display ALL the rows from Table3 and show columns of Owner_LastName and Tenant_Lastname. However, I want the rows in Table3, that do not have the value for TenantID to still appear, just with Tenant_LastName being left blank.
From a Access database that I inherited. Users used to be able (from a form) run a query and then add data (i.e, new rows). But now that the the database is split and the backend is on MS-SQL, they no longer can do this. I do not think this will be possible. I think they will have to add data directly to the table or have another form for adding data.
I want an append query to create a new record in a table and populate that record with fields from a form. Some of these fields are free text, others are from combo boxes.The append query adds vastly too many records (albeit all populated with precisely the same information) - and the number it adds seems to depend on which combo box options I choose.
I have a similar append query which pulls information from earlier records in the table, and combines that with free text and combo boxes on a near-identical form, which works fine and always just adds one row.
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 .......
I have the following query which returns 2 columns, where 2 fields are summed :-
Code:
SELECT Sum(Stats.[No of Invoices Checked]) AS [SumOfNo of Invoices Checked], Sum(Stats.[No of Incorrect invoices]) AS [SumOfNo of Incorrect invoices] FROM Stats WHERE (((Stats.Period)=[Forms]![frmMain]![cboSingleMonth].[Value]));
I have a result for a query made from four different tables for which I would like to refine the result of this query grouping multiple rows into columns.
Attached is a pdf file showing the results being obtained by my query and underneath is how the result would like it be after running the query.
I am having a problem with a Dlookup query. I want to achieve the following - I have 2 different tables
Demography Table - consisting of 2 fields, City and Region
For example City Region NYC NAM
The other Table totalflow consists of several field, one of the is flow from.
For example Flow from NYC WAS SEA
I want to do the following. Lookup the Flow from filed in table totalflow, and compare it to City in demography Table. If City = Flow from, return the value in Region. All are text fields.
I have tried following Query, but it only returns the same value for all rows. (from SQL view)
SELECT DLookUp("[Region]","Demography Table","[City] = '" &[Flow from]& "'") AS test FROM [Demography Table], flowsize;
I have the attached code in a query. It should be bringing back just one row for each record, however, if I have anything in any column other than Call_NUmber_int it brings back multiples if that record.
Create a query to show rows with timestamps in between two timestamps? my variables are cutoff time and the current time where cutoff time is saved on a table (tblcontroltable.cutoff ) a pseudo code of what i want to achieve is
select date, product, timestamps from tblsample where date = date and timestamps > cutoff and timestamps < time
There are about 75 distinct sets of ID's and Names not shown here
The rows were sorted by the Mtr_Readings and we have to take the best four readings out of each group and average them. If a group has more than four rows then the top 4 are taken out and the balance is discarded. If the group has four rows or less all of the rows are taken.
I did try nested select statements and kept getting "invalid argument to function" exceptions.
Is it possible to change this query to search for either a Name or by an employee ID number? The query runs a report on after update. Currently I can only enter the name not the employee ID
Code: SELECT DISTINCTROW First(Inventory.Employee) AS FirstOfEmployee, Inventory.[Employee ID] FROM Inventory GROUP BY Inventory.[Employee ID] ORDER BY First(Inventory.Employee);
I have datasets in tables for some price data, which is monthly. Each month there is a list of unique ID codes for the products ("CAP Codes") and then a price for that product ("LM Retail").The table names are simple dates (2010 01, 2010 02 etc) I want to have a query which would create a table which follows the price of each product through the months. All of that would be simple except not all of the products appear every month, so when I set up a simple link between them all it only outputs products which appear every month. Is there a way that it'll keep the products with just some prices - leaving a blank in the table created when there isn't a price that month?
I have three tables the tblStudentInfo (holding the persons main data), the tblEducation (holding the students education records) and the refInstution (holding all university's and college information).I am pulling PersonID, name, etc from the tblStudentInfo, education records from tblEducation.
the problem is that in the query it displays the institution ID instead of the Institution name. How do i get the query/rpt to show the name instead of the ID?
On the form Institution this is pulled from the refInstitution table and the ID InstName etc is a combo box 0; 1.0; etc so the display on the form shows the name of the university instead of the ID number.
