Queries :: Max Date Selection - Subquery?
May 23, 2013
I need to select a record with the latest (max) date along with associated values that go along with that record. However, some records have the same date. Initial query sorts by Asset_ID and Date (Desc)...A second query against the initial query then groups by Asset_ID, taking the Max Date and First Switch and Port...Results show the latest date but with a different Switch and Port
Asset_ID | Date | Switch | Port
123 23/05/2013 WAR01 GI01
123 23/05/2013 SAM01 GI02
123 20/05/2013 ROC01 GI03
123 21/05/2013 CHR01 GI04
e.g I get 23/05/2013 with ROC01 | GI03...I have tried doing this as a nested query but get similar results.
View Replies
ADVERTISEMENT
Dec 3, 2013
We are developing a complaints tool. Each completed complaint needs to be signed off by 3 leads and I'm hoping to display the progress of this in a form. Obtaining the first is simple and I was able to do that relatively quickly. The subsequence ones are now giving me a headache as it doesn't seem I can reference the initial subquery field in the others.Here's what I have so far:
Code:
SELECT COMPLAINT_TBL.COMPLAINTID, COMPLAINT_TBL.CASENUMBER, COMPLAINT_TBL.COMPLAINTANTFORENAME, COMPLAINT_TBL.COMPLAINTANTSURNAME, BUSINESSUNIT_TBL.BUSINESSUNIT, COMPLAINT_TBL.FINALRESPONSEDATE,
(SELECT TOP 1 [SIGNOFF_TBL].[SIGNOFFDATE] FROM [SIGNOFF_TBL]
WHERE [SIGNOFF_TBL].[COMPLAINTID] = COMPLAINT_TBL.COMPLAINTID
ORDER BY [SIGNOFF_TBL].[SIGNOFFDATE] DESC) AS FIRSTSIGNOFF,
[code]....
View 6 Replies
View Related
Sep 11, 2014
Below is the SQL for a query I have, which returns events that are scheduled for today, I need to change this so that The user can specify events to be displayed in a date range and If possible only show those events for the users windows ID
Code:
SELECT tblEvent.EventStart, tblEvent.EventOwner, qryCompany.Company, ltDescriptionType.Description
FROM (tblEvent INNER JOIN qryCompany ON tblEvent.Company = qryCompany.ContactID) INNER JOIN ltDescriptionType ON tblEvent.EventDescrip = ltDescriptionType.[DescriptionType ID]
WHERE (((tblEvent.EventStart)=Date()))
ORDER BY tblEvent.EventStart, tblEvent.EventOwner;
View 2 Replies
View Related
Jul 31, 2013
I'm trying to create an query that has two separate expression in it and a date selection criteria. The first expression is,
Expr1: DateDiff("s", [Arrival Time 1],[Departure Time 1])/3600
and the second is,
Expr2: IIf([Expr1]>[Hours Per Day],[Expr1]-[Hours Per Day]
When I run this query it asks me for the date (which is fine) but then it also pops up a box asking for "Expr1". How can I get "Expr2" to use the value returned from "Expr1"?
View 3 Replies
View Related
Jun 3, 2015
Extended Cost] and salestran.[Extended Price] for each salestran.[SKU Code/Number], Count the number of unique customers, salestran.[Customer Code/Number], per SKU for any transactions equal to or after salestran.[Transaction Date] 11/1/2014.
Problem is, doing a regular count on the Customer field returns an incorrect value. It counts the number of times the sku appears within the given date range, ie 6 transactions with 2 customers, my count says 6. I need a distinct count on the customer, for the above example I want to see 2. Here is the code I have so far which yields an error of "At most one record can be returned.." distinct count in my Select statement along with the other fields I want to see, ie Summary data and SKU.
SELECT Salestran.[SKU Code/Number], (SELECT COUNT(cd.[Customer Code/Number]) AS Count FROM (SELECT Distinct [SKU Code/Number], [Customer Code/Number] FROM Salestran) as cd GROUP BY cd.[SKU Code/Number]) AS [Number of Customers]
FROM Salestran
WHERE (((Salestran.[Transaction Date])>=#11/1/2014#))
GROUP BY Salestran.[SKU Code/Number];
View 4 Replies
View Related
Jun 2, 2013
I'm doing a subquery to select the top 5 of products for each supplier. The selection needs to be done randomly on the products for each supplier. For this I have made the following query (based on Allen Browne's example):
SELECT tblProducts_temp.SupplierID, tblProducts_temp.GTIN
FROM tblProducts_temp
GROUP BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN
HAVING (((tblProducts_temp.GTIN) In (SELECT TOP 5 Dupe.GTIN
FROM tblProducts_temp AS Dupe
WHERE Dupe.SupplierID = tblProducts_temp.SupplierID
ORDER BY RND(Dupe.GTIN) DESC)))
ORDER BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN;
The query returns a random number of products, but not a top 5. So for supplier X one time 3 products and the next time for supplier X 7 products.The query without the RND function, so just the top 5 works fine:
SELECT tblProducts_temp.SupplierID, tblProducts_temp.GTIN
FROM tblProducts_temp
GROUP BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN
HAVING (((tblProducts_temp.GTIN) In (SELECT TOP 5 Dupe.GTIN
FROM tblProducts_temp AS Dupe
WHERE Dupe.SupplierID = tblProducts_temp.SupplierID
ORDER BY Dupe.GTIN DESC)))
ORDER BY tblProducts_temp.SupplierID, tblProducts_temp.GTIN;
View 2 Replies
View Related
Feb 27, 2014
How to DELETE rows in one table that have a field value that matches a field value from another table? Tried these but sytax errors:
DELETE * FROM tmpBankDebitsMinusJE
WHERE tmpBankDebitsMinusJE.ConcatenateBankDebits IN
(SELECT tmpJournalEntryChangeOrders.Concatenate);
DELETE FROM tmpBankDebitsMinusJE
WHERE ConcatenateBankDebits IN
(SELECT Concatenate IN tmpJournalEntryChangeOrders);
View 2 Replies
View Related
Jun 24, 2015
I have a one-to-many query which I would like to add a subquery to eliminate all but the TOP 1 of a field.
Here is the SQL:
Code:
SELECT QrySitesBatteries.SiteKey, QrySitesBatteries.SITEID, QrySitesBatteries.Battery
FROM QrySitesBatteries
WHERE (((QrySitesBatteries.Battery)=[Forms]![FMHome]![Battery])) OR ((([Forms]![FMHome]![Battery]) Is Null))
ORDER BY QrySitesBatteries.SITEID;
And I would like to only show the TOP 1 of the SiteKey field.
So, I think I have to add the subquery before the ORDER BY, but how to do it?
View 3 Replies
View Related
Sep 1, 2013
At most one record can be returned by this subquery. (Error 3354)
This is my SQL:
SELECT Students.[Student ID], Students.[Student Name]
FROM Students
WHERE (((Students.[Student ID])=(SELECT [Student ID]
FROM [Student_Sports]
WHERE [Sporting Team] = [Forms]![SV_Teams]![txtTeam])));
View 3 Replies
View Related
Jan 5, 2014
I have a race league, I want to select the top 10 point scores for each member.
I have read the Allen Browne article (and many others) and tried many variations on his code but cannot get this working.
I face two issues
- The ORDER by clause has no effect, points are not sorted with largest first
- Access being unable to differentiate between scores with the same value and returning additional records. I have added an "Event" field to make the record unique, but this does not seem to work.
Query code is
SELECT qLeague.Member, qLeague.Event, qLeague.Points
FROM qLeague
WHERE qLeague.Points IN
(SELECT TOP 10 Points
FROM qLeague AS Dupe
WHERE Dupe.Points= qLeague.Points
ORDER BY Dupe.Member, Dupe.Points DESC
)
ORDER BY qLeague.Member ASC, qLeague.Points;
This returns more than 10 results per member:
Member Event Points
Alex Peters SDMC North Weald Sprint 3
Alex Peters HCAAC Debden May 3
Alex Peters GB/Harrow TAMS NW Sprint 4
Alex Peters HCAAC Debden Sprint 5
Alex Peters Llys y Fran Hillclimb 6
etc ....
View 10 Replies
View Related
May 7, 2014
I have a table of accounts and a table of rates. There is a one-to-many relationship between them (i.e. each account can have multiple rates, each with their own - unique - effective date)
I'm trying to build a query which will show me all of the accounts in the accounts table and the most recent rate (based on the effective date) for each of those accounts.
This is as far as I've gotten with the SQL :
Code:
SELECT [tblAccounts].[AccountID], [tblAccounts].[AccountNumber], [tblAccounts].[AccountName], [LatestRate].[IntRate], [LatestRate].[EffectiveDate]
FROM [tblAccounts]
LEFT JOIN
(SELECT TOP 1 [tblRates].[AccountID], [tblRates].[IntRate], [tblRates].[EffectiveDate]
FROM [tblRates]
ORDER BY [tblRates].[EffectiveDate] DESC) AS LatestRate
ON [tblAccounts].[AccountID] = [LatestRate].[AccountID]
But this can't work because the [LatestRate] subquery can only ever return one record (i.e. the most recent rate across all of the accounts)
I need the most recent rate for each of the accounts in the main query
(FYI - I use an outer join as it is possible for no rate to be available in the rates table for a given account, in which case I want to return the null value rather than omit the account from the resulting dataset...)
View 2 Replies
View Related
May 27, 2014
I've created the following but it keeps coming up with the error message You tried to execute a query that does not include the specified expression 'ICE Team' as part of an aggregate function.
SELECT ztSub.[Master Sheet].[ICE Team], ztSub.[date], Count(ztSub.[Count])
FROM (SELECT [Master Sheet].[ICE Team],[Master Sheet].[Visit Date (planned for)] AS [date],Count([Master Sheet]![Visit Date (planned for)]) AS [Count]
FROM [Master Sheet]
UNION
SELECT [Master Sheet].[ICE Team],[Master Sheet].[Date retasked to?] AS [date], Count ([Master Sheet]![Date retasked to?]) AS [Count]
FROM [Master Sheet] ) AS ztSub
GROUP BY ztSub.[Master Sheet].[ICE Team];
View 5 Replies
View Related
Jan 9, 2007
I currently have a query that takes info from an asset support table.
I have a Support Contract Start Date in the table (Date time)
I have a Support Contract Length In months field in the tabel (numeric)
i am calculating the end date of the query using the following expression.
Support End Date: IIf([Support Start Date] Is Null Or [Support Contract Length] Is Null,"N/A",DateAdd("m",[Support Contract Length],[Support Start Date])
this Works fine and I get a Date however many months in the future that the contract length in months field dictates.
however i am now trying to add criteria to this in order to select certain date spans of when a contract ends.
So i only get records in a query that show records with end dates in 30 days time from now or another query that shows contracts ending in 365 days from now.
Can someone point me in the direction of how to do this effectivly , as i need to generate a report from this query that will show me support contracts expiring in 1 year and in 30 days time.
Thankyou for your time and help
View 10 Replies
View Related
Mar 15, 2007
Hi
I have this expression in a query on which a report is based and would like to use Between And to select the date period, but cant get it to work, the table has Feb and March dates in it but the query brings out March dates if I select 01/02/07 and 28/02/07 (UK date format) ..it works if I put for example "01/02/07" in the criteria of the query ie it shows all records dated 01/02/07
MyDate: Format([LogInTime],"dd/mm/yy")
As always any help, advice, pointers would be gratefully appreciated
cheers
Fi
View 1 Replies
View Related
Jan 14, 2008
Hi, I have a database which contains deliveries made for the last 24 months i need to select only those made within a selected time period i.e. last month, Does anyone have any idea how this can be done?
View 1 Replies
View Related
Nov 19, 2004
I have a table to store the new append records from a SQL statement. In this table I have defined a "Status Date" in field type date/time with format dd/mm/yyyy. However, when execute the SQL append statement, the system has appended the record with format of dd/mm/yyyy hh:mm:ss.
The problem is when I run the report with a date range input, the report cannot select any records with such date range. I have examined the table format is correct, but I found the data actually included the time in it. Do anyone know how to overcome this problem?
View 3 Replies
View Related
Apr 16, 2015
I have a big list of data, with a row of data for every day for each client.
I need a selection criteria that will provide show the last day of the every month (historic data) for each client.
I've had a go but I'm not making much progress.
View 4 Replies
View Related
Sep 18, 2014
I have a report based on a query that returns all info from the query which is fine, I now need to amend this so that individual users can specify the date range to be queried and the person for whom the results are required (one of the query strings)...
View 1 Replies
View Related
Jan 5, 2014
I am using this code to filter a List Box based on a Date range and a Combo Box selection:
Code:
Private Sub Combo139_AfterUpdate()
Dim StrgSQL As String
StrgSQL = "SELECT [User Name], [Date Of Request], [Description of Problem], Status, Sub_Job FROM QRY_SearchAll " & _
"WHERE [Date of request] BETWEEN #" & CDate(Me.txtStartDate) & _
"# AND #" & CDate(Me.txtEndDate) & "#;"
StrgSQL = StrgSQL & " WHERE Sub_Job = Combo139"
Me.SearchResults.RowSource = StrgSQL
Me.SearchResults.Requery
End Sub
However, It is not working. when I click the Combo box the List Box comes up blank.
View 13 Replies
View Related
Feb 15, 2015
I have been building a database for use in a charity shop and am struggling with an issue regarding one of the forms.
I currently have a form which displays all expenses from the shop. I have added a combo box to the top of this form which allows users to filter records based on an expense ID Code. It all works fine but I would like to be able to add a start date and end date box to the form also so that records can be shown between two dates.
I have tried adding parameter boxes to the query which runs the form (which works) but the issue I am having is that when a new id is selected from the combo box the parameter boxes pop up again asking me for start date and end date again. This happens every time a new combo box id is selected.
I think the way resolve this issue may be to add a start date and end date box to the form but I don't know how to implement this.
Please see the attached files for images of what I currently have. The forum won't let me post images directly here until I have 10 posts so I have had to attach the files instead.:
Query running the form - Attachment 1
The Form itself - Attachment 2
Combo Box - Attachment 3
Bound Column on combo box - Attachment 4
Code in Combo Boxes after update event - Attachment 5
View 3 Replies
View Related
Aug 17, 2014
In my main form, there is this date entry selection and a subform with data on it.
How can I make the subform filter its data from date entry selection?
I have already created a relationships for my tables
I created a query for this subform (should I need to?)
It does not refresh my subform or I do not know what to do?
View 12 Replies
View Related
Mar 11, 2015
I'm creating a form that when the user selects the following categories in the same combo box (Date Received, Date Reviewed, Date kitted, In Work, Complete) it auto populates dates in the respective fields. As I mentioned, it's only one combo box. The dates will be spread out, so the user will change the combo box selection based on when these events occur. I already have a field for each category both on the table and form. Also, I do have multiple tables for other parts of data, but these categories all fall into the same table.
View 8 Replies
View Related
Aug 19, 2013
I have a combo box that filters records on a subform by user name. I'd like to add an "all" option to the combo box list so that all records regardless of user name may be displayed at once. I have attempted a few approaches to this using a "union query" but am failing to achieve the correct syntax. The query I'm using is this:
Code:
select top 1 0,"**ALL**" from tblauditstaff
union All
SELECT tblAuditStaff.AuditStaffID, [tblauditstaff].[auditstafflastname] & ", " & [tblauditstaff].[auditstafffirstname] AS [Auditor Name], tblAuditStaff.AuditStafffirstName, tblAuditStaff.AuditStaffLastName, tblAuditStaff.AuditStaffResponsible, tblDiscrepancy.DiscrepancyAssignedTo, tblDiscrepancy.DiscrepancyActualCompDate,
[Code] ....
This is the error message I'm getting:
The number of columns in the two selected tables or queries of a union query do not match.
View 2 Replies
View Related
Dec 21, 2011
I have several queries (crosstabs) which are eventually combined together to form the data for a single report. If I want to date select for this report I need to add a WHERE clause to every query via VBA. This isn't difficult but it set me wondering if there was any way to have one dummy query containing the necessary WHERE statement, and this somehow gets appended to all the other queries, i.e. to filter on date I only need a change in one place.
View 7 Replies
View Related
Aug 19, 2015
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code:
Between [StartDate:] And [EndDate:]
And
Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Neither of which work ....
View 13 Replies
View Related
Feb 12, 2007
Can anyone tell me what is wrong with this complex query? It works fine without the highlighted subquery. The subquery works fine on its own. But they don't work together.
I think that I might not be able to call multiple results from a subquery but I'm not sure about that. Does any one know???
Any help will be much appreciated!
Thanks!
select k.site_id, s.name, t.date_opened,
(select concat(c.name_first,' ', c.name_last) as User from sitepack_approval sa
left join sitepack_approval_users spa on sa.approval_id=spa.approval_id
left join contacts c on spa.user_id=c.contact_id where sa.site_id=k.site_id and spa.timestamp is null
order by spa.level limit 1) as pending_approver,
(select sat.name from sitepack_approval sa left join sitepack_approval_types sat
on sa.approval_type_id=sat.approval_type_id where sa.site_id=k.site_id order by sa.approval_id desc limit 1)
as sitepack_type,
(select t.site_id, D.name AS Division, R.name AS Region, M.name AS Market,
concat(C1.name_first, " ",C1.name_last) AS RD,
concat(C2.name_first," ",C2.name_last) AS DD
FROM stores as t LEFT JOIN regions as M ON t.region_id = M.region_id
LEFT JOIN regions AS R ON M.parent_region_id = R.region_id
LEFT JOIN regions AS D ON R.parent_region_id = D.region_id
LEFT JOIN contacts AS C1 ON R.contact_id = C1.contact_id
LEFT JOIN contacts AS C2 ON D.contact_id = C2.contact_id) as site, Divsn, Reg, Mkt, RDs, DDs,
concat(cd.name_first,' ',cd.name_last) as 'DRE'
FROM sitepacks k LEFT JOIN sites s on k.site_id=s.site_id
LEFT JOIN stores t on k.site_id=t.site_id
LEFT JOIN contacts cd on k.dre_contact_id=cd.contact_id
WHERE status_id=(select status_id from sitepack_status where description='Approving' and sitepack_type <> 'Renewal')
and t.status_code <> 'DEAD'
and t.date_opened > now();
View 1 Replies
View Related
