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