Queries :: JOIN Query To Return Most Recent Record?
Aug 18, 2014
I have two tables with a one to many relationship. The tables are linked by the INDEX column.
EXAMPLE:
Code:
TABLE_1
INDEX NAME
1 Name_A
2 Name_B
3 Name_C
TABLE 2
INDEX NUM_INDEX STATUS
1 1 REJECTED
1 2 REJECTED
1 3 OPEN
2 1 CLOSED
3 1 REJECTED
3 2 OPEN
I need the NAME field from TABLE_1 and the Last STATUS field from TABLE_2 (MAX of NUM_INDEX).
Example:
Name_A, OPEN
Name_B, CLOSED
Name_C, OPEN
SQL that I have now.
Code:
SELECT A.FIN_Finding_Number, B.Max_Index
FROM TBL_Findings AS A INNER JOIN (SELECT RES_Finding_Index, Max(RES_Response_Index) As Max_Index
FROM TBL_Response GROUP BY RES_Finding_Index ) AS B ON A.FIN_Finding_Index = B.RES_Finding_Index
WHERE (((A.FIN_Finding_Index)=34));
This SQL statement will return me the Finding_Number and Max_Index. I don't need the Max_Index. I need the Status. If I put the Status in the Sub-Query and GROUP BY it, it will return both REJECTED and OPEN. I just need it to return OPEN.
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 have a set of devices that are assigned to a truck, on occastion they are moved from truck to truck, I need to be able to track where they are AND where they have been. Here is what I have:
ID (autonumber) GPS ID (number, this is the specific ID to each device) Truck Number (this is the specific ID for each truck Installation Date (Date installed / moved)
I have created a query to show the data I need, I have put them in order based on date.
- I must keep each event stored on the table
Goal:
To show a list of the current location of each device, nothing more.
I have a database that is used to allocate appointments to our staff. It has 2 tables, one that lists the clients we need to call in that day, and another that stores details of each contact attempt. I'd like to design a query that find all clients who we have not dealt with so we can easily get their details in a list. I know what the criteria for the query would be, but I'm stuck for how to actually execute it. Here are the details.
Table tClients stores the current clients - primary key is named "clientRef" Table tContactEvents stores each contact attempt and the date/time is stored in a field named "dateTime".
When an entry has been dealt with successfully a yes/no field named "completed" will be set to "Yes".
There may be many attempts to contact a specific client on a given day, unsuccessful attempts will not have the completed flag set.
Once the completed flag is set that client will be ignored so no further entries will appear.
So I need a query that searches tContactEvents for the most recent match to each number in tClients.clientRef and checks if the completed flag is set. If the completed flag is false, or if the number has no match (i.e. no contact attempts made yet) then the clientRef should be displayed. I also need this to be restricted to the current date, as the same client could have rebooked their appointment to a different day.
I have 2 tables. One has employee info and the second has time data based on thresholds reached in the employee info. Obviously, a basic join will bring in all of the threshold records. How do I get it to only pull in the one that satisfies the threshold condition?
Using a single-step Access query, I need to retrieve, for each PatientID, the most recent LabDate and Result *of a given LabID*. Thus, from the example dataset above, the desired output for LabID 55 is:
I have searched this forum and others, but have not found an answer that I can directly tanslate to my situation. I have successfully written queries (with included subqueries) that retrieve the most recent of all the Labs, but have failed at obtaining a result dataset that contains only the records within a specified LabID.
For example, the query below fails because whenever the most recent of *all* the LabDates is not the same as the most recent of *the LabDates with a LabID=55*, the correct record is not included in the results. In the example dataset above, 0 records are returned.
SELECT a.PatientID, a.LabID, a.LabDate, a.Result FROM Labs AS a INNER JOIN (SELECT PatientID, MAX(LabDate) AS MaxLabDate FROM Labs GROUP BY PatientID) AS b ON (a.PatientID = b.PatientID) AND (a.LabDate = b.MaxLabDate) WHERE (((a.LabID)=55));
Here's the scenario. I have 3 fields [AgentID], [QA Date], [Points Issued]. What I want to do is run a query that will give me the last date [QA Date] a point [Points Issued] was issued for each agent [AgentID]. And I want the query to display all 3 fields according. Is this possible?
My database includes a Project table and a Status table. They are linked by the ProjectID. The status table contains records sorted by date pertaining to work accomplished on each project. I have created a report based upon a query to give me an update of the status of each project. I only want to see the most recent record for each project from the Status table in my report.
So an employee in this database can have many different positions. The tables are linked using an EmployeeID field.
I want to construct a query that will list each employee and the last job entered for them in the database. Right now my query simply returns all the positions held (where there are more than one)?
(1) a query that returns the latest record (newest) in a table called 'Invoices' and then
(2) places this value in a form called 'FrmInputInvoices' as the default value when the form opens. Newest record is by Autonumber and the table defaults this to top of table as views newest down to oldest.
Re (1) Query is called 'QInvoices'; the values I want to return in my query is ID (my autonumber) and Invoice_No . Must be a simple answer to put in the criteria, but I can't find this.
Re(2) What code do I use in my Form field named 'Invoice_No when the curser defaults there on opening?
I have a query that returns records in datasheet view. I need a field to accept the user selection from a drop down, then save that to a table. The query is based on a couple queries and one of those queries is based on the table i need to save the user input into. When i make a selection from the drop down list it puts the selection in that field for all the records and then doesn't save.
I have a table that contains readings from several pieces of equipment as well as the status of each one. Each record has a timestamp, equipment number, status, etc. What I want is to create a query that will return the latest record for each equipment number. Simplified example table:
There are more than 20 different Equipment numbers and they are read several times per day and sometimes some of them get missed. What I'm looking for is a way to get a list of all the machines with their latest reading so they can tell which machines are running and which are down based on the last time they were read (instead of specifying a date). I can get this for one machine with no problem. I'm having trouble getting it for more than one machine. I tried a union query (with just 2 of the machines included for testing) but it only returns the results from one machine:
Code:
SELECT TOP 1 TestCompressorRoundQuery.LoggedAt, TestCompressorRoundQuery.AssetNumber, TestCompressorRoundQuery.CompressorID, TestCompressorRoundQuery.Status, TestCompressorRoundQuery.CompressorIntegrity, TestCompressorRoundQuery.Notes FROM TestCompressorRoundQuery WHERE (((TestCompressorRoundQuery.AssetNumber)="104399")) UNION ALL
[Code] ....
I'd rather not have to create a seperate query for each machine and then join all of those together! I think perhaps a Union query might not be the correct approach. All the data is coming from only one table.
If I filter the date, e.g., from April 1st to 30th, I want to get as a result 4 records, one of each client and only the latest record from CLIENT 3 (04/22).
If I filter the date, e.g., from April 1st to 20th, I want to get as a result only the records of clients 4 and 3 from 04/16 (as every other contact is from beyond 04/20).
If I filter the date, e.g., from April 25th to 30th, the return must be only the top 2 records (the only ones after April 25th).
I tried using a query grouping the totals on "process key" and the other cells set as "first", but if I filter from April 1st to 20th, for example, no records from CLIENT 3 gets shown, since Access processed the "first" record request before the date filter, and therefore only considered the contact from 04/22, and as it is later than 04/20, wasn't shown on the query.
For each record in my database, there are observation periods which are recorded in the format dd/mm/yyyy hh:mm:ss, titles as follows
1st Obs Start 1st Obs End 2nd Obs Start 2nd Obs End 3rd Obs Start 3rd Obs End.
I have been asked to create a query that will quickly show how many obervation periods commenced in a particular month. What I am trying to do is create a column that will be named Obs Start, and another, Obs End. For each record ID, this would then show as follows:
Each record in my table has six possible sale dates as a result of cancellations. My problem results when a record is sold twice during the queried time frame (SaleDate1 and SaleDate2). Currently my query uses an if statement and if they are in the same time period, it will only return SaleDate1. I would like to see the record returned twice (once for SaleDate1 and once for SaleDate2). Any suggestions?
and what i need to is something that will link all sku 1 category names in a new field with a | divider and then all the sku 2 category names together with | as a divider and so on. the amount of categories is different for every sku.
And if this is easy enough, its not that important though but to delete duplicate category names when it transfers them across to a new field joined together.
I have my main linked table VI, and 4 of VI's fields are to be filtered in a query.
To do so, I have 4 very very tiny local tables (less than 20 records) called respectively "soff", "skus", "warr" and "typ", in which the user will only put what he wants in the result, and by doing Inner Joins, I filter it easily.
Problem is, doing more than 1 Inner Join slow the query so slow it's unbelievable.
If I run the query:
Code: SELECT VI.* FROM VI INNER JOIN SKUS ON VI.Sku= SKUS.Sku;
It runs in 0.7 seconds. It's about the same if I replace SKU by SOFF, WARR, or TYP, the query takes about 0.5 to 1.5 sec.
Now if I run:
SELECT VI.* FROM (VI INNER JOIN SKUS ON VI.Sku= SKUS.Sku) INNER JOIN SOFF ON VI.SOff = SOFF.Soff;
It takes either 15 seconds, or up to 100 seconds.
I tried by doing 4 successive queries, it's the same. The 1st query runs well, and then it grinds to a near-halt.
Running either the 4 queries or a query with the 4 inner joins takes me about 200 seconds, sometimes 400-500.
It's not even a hard query, VI has only almost 1mil rows, and the fields are indexed. The result is 800 rows.
If each join on VI takes 1 second, it should do 1sec+1sec+1sec+1sec, taking in account the fact that the left side of the join grows smaller at each step, it should even do something like 1+0.8+0.5+0.2, or something. Why does having these joins together, or follow each other, make things so damn slow?
I am looking to create a query using a main table and 3 related tables to produce a count of evaluations completed and evaluations passed by month (using the 3 related tables) and site location (using main table) which is joined by agent name. I have the following so far but:
Code: SELECT [MainTbl].Location, Format$([Tbl1].[EvaluationDate],'mmmm yyyy') AS [EvaluationPeriod], Count([Tbl1].[Agent Name]) AS [NoCompleted], Count(IIF([EvaluationScore]>=0.9,0)) AS [NoPassed]
Creating a query in Design View, how does one create a query in the "Condition" field that tells the Access to return the record set where the contents of one column is greater than the other?
There are obviously many more columns than those listed above in the table, but these are the two I want to compare. Also the two columns contain numbers.
In SQL I would write it like this:
Select * FROM SURVEY_RAW_DATA WHERE TOTAL_EXP > TOTAL NX;
I was working on an update query while joined to another table - and the error I was receiving was the query was not updatable. Er... The table that was being updated sure seemed able to be updated...
Then I wondered if the reason this didn't work was because the other table I was updating from was a query whose records were sum'd and group'd by..I ended up testing the idea by inserting the query's records into a temp table and then did the update to the target table from the temp table... which worked fine.
I have two tables with name of accone and the second with the name of acctwo. These two tables are same according to number of columns and also same according to data types and also same according to the column names just the data are difference and also one column (attribute) with the name of ID is same in both tables. Their is a primary key relationship (one-to-one) between these two IDs. I need a query that can combine the data of both of them and can be updated using query. I mean that data of first table and second table must become under one same column not two columns one for first table and second one for second tabel.
I have a table called MiscORders where all the orders are tracked. There is an ID, order number , other fields and ParentorderID fields in it. Whenever an order is modified they create a new order with ordernumber and have the previous order number as the parent. The next time it is amended or closed another order is created with the parent order attached. Here is some sample data
Order ID OrderNum Parent order id 1 MISC 2013-10 2 MISC 2013-10A MISC 2013-10 3 MISC 2013-10B MISC 2013-10
Now I am creating a query that shows the lifecycle of the order
MISC 2013-10 Issue and deadline date MISC 2013-10A issue and deadline date MISC 2013-10B issue and deadline date.I tried to create a query using self joins to the same table. SQL is attached
SELECT MISCORDER.OrderNum, MISCORDER.[Date Issued], MISCORDER.[Deadline Date], MISCORDER_1.OrderNum FROM MISCORDER LEFT JOIN MISCORDER AS MISCORDER_1 ON MISCORDER.OrderNum = MISCORDER_1.RescindedOrderID WHERE (((MISCORDER.OrderNum) Not Like '*A' And (MISCORDER.OrderNum) Not Like '*B' And (MISCORDER.OrderNum) Not Like '*C' And (MISCORDER.OrderNum) Not Like '*D') AND ((MISCORDER_1.OrderNum) Not Like '*B'));
How can I put a condition on the table to show only records with order num ending with A. It is not working if i use it in the where condition..
