Pick Latest Record From Query?
Dec 16, 2005
I am trying to pick the latest record from "tblEmpVac.id" field but I keep getting syntax errors. I am thinking if the "tblEmpVac.id" = Count('tblEmpVac.id') then that will show the most current record as it will count and match the last record correct?
Another problem is if I put it into the WHERE section, it will give a conflict. So I don't know where to put it. Below is the SQL, if anyone can help it would be great.
SELECT DISTINCTROW tblEmp.position, tblEmp.fname, tblEmp.lname, tblEmpWorkHistory.region, tblEmpWorkHistory.[current store], tblEmpVac.current_year, tblEmpVac.id, tblEmpVac.entitlement, tblEmpVac.days_taken, tblEmpVac.days_carryover, [days_carryover]+[entitlement]-[days_taken] AS total
FROM ((tblEmp INNER JOIN tblEmpVac ON tblEmp.ssn = tblEmpVac.ssn) INNER JOIN tblEmpWorkHistory ON tblEmp.ssn = tblEmpWorkHistory.ssn) INNER JOIN tblEmpWorkHistoryData ON tblEmpWorkHistory.ewh_id = tblEmpWorkHistoryData.ewh_id
WHERE (((tblEmp.position)<>"Terminated") AND ((tblEmpVac.current_year)="2005"))
ORDER BY tblEmp.position, tblEmpWorkHistory.region, tblEmpVac.id;
View Replies
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
Dec 11, 2014
I have a query with the following structure;
aDate(pkey) cost1 cost2 cost3 calc1 calc2 calc3
01/012014 ,,,,,,,,,1,,,,,, 2,,,,,,, 3,,,,,, 1,,,,,,, 2,,,,, 3
01/02/2014 ,,,,,,,,1,,,,,, 2,,,,,,,, 3,,,,,, 2,,,,,, 4,,,,,, 6
01/03/2014,,,,,,, 1 ,,,,, 2,,,,,,, 3,,,,,,, 3,,,,,, 6 ,,,,, 9
01/04/2014 ,,,,,, 1,,,,,,,, 2,,,,,,, 3 ,,,,, 4,,,,,, 8,,,,,, 12
fields calc1,2,3 are running totals of cost1,2,3
I expect/hope to first calculate the sum of a cost field and then minus the value of its corresponding calc field from a specific record.
result = sum(cost1) - calc1 selected record value
I want to select the calc1 record from a drop down list of the primary key. Which cost field is in the equation will static/defined as I intend to make a textbox for each field.i need to know the code to pick a field(and retrieve it's value) from a record selected via dropdown list.
View 4 Replies
View Related
Jun 21, 2005
Hi All,
I am looking for some help with a project I am working on where I need to automatically print a report from my database every time a new record is added to the table. The table contains 13 fields and the report needs to display 12 of them, the other being the index which is set to Autonumber.
The table is being updated solely by ODBC, this is working OK.
There is the potential for records to be added to the table very quickly via the ODBC link, so I need to safeguard that the report is being populated with correct information from the record that triggered the print event. Also, should multiple records be added in close succession, a report needs to be correctly generated/printed for each of one. The DB is to have no user intervention, and will just run on the PC at startup with all access menus/controls locked out.
Any information on how you think I should structure this, or any examples of helpful code that you might have would be very much appreciated.
Thanks in advance, :)
Jon.
View 7 Replies
View Related
Mar 12, 2014
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:
Timestamp EquipmentNumber Status
Today ------Machine1 ----------Running
Today ------Machine2 ----------Running
Yesterday -Machine1 ----------Down
Yesterday -Machine2 ----------Running
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.
View 6 Replies
View Related
Jun 17, 2014
My computer has been updated to 2010 whilst I've been off sick (was 2003 before my accident).
I've created a main table, for devises across the company, and a combo box/selection box based on another table which holds a list of all the "Responsible" employee's aswell as another combo box/selection box for the device location.
So the person entering the information, can enter all the information for a device (torq wrench, socket set etc), who is responsible for it and which department they belong to (where to find the device).
Which all works fine
However, I'd like to create 2 queries, one to enable the user to run a report of all device's allocated with an employee or to be able to run a query for all device's stored in a particular department.
But I have been unable to set the correct query criteria, to enable to query user to be able to selection from a drop down list, which responsible person or location to pull back the correct list.
I was getting an error asking me to set the parenthesis, I have now deleted criteria for both queries, as even if I put [Enter] and type a Responsible person's name exactly as its held on the table, the report comes back blank.
View 1 Replies
View Related
Feb 26, 2007
I am having difficulty formulating this query.
I have 2 tables... 1 is customer info (name, address, etc...), and the second is shipments (ShipToCustomerName, ShipmentDate, etc...).
Now, I wish to query the shipments table to get ONLY the LATEST shipment information for EACH customer.
If customer A has a shipment in Jan 1, Feb 2, and March 3... while customer B has a shipment in April 4 and May 5. then the result of the query should be
customer A - March 3
customer B - May 5
Is there a simple way to construct such a query?!?
Thank you for your help,
- arm
View 1 Replies
View Related