Queries :: List Latest In Each Group

Mar 23, 2013

I have a table of SOPs/WIs. Each, with time, gets a new version - this I keep in the child table.

tblVersions
------------
VersionID (PK)
SOPID(FK)
Version (integer)
IssueDate (date)

I want to make a list of the currently valid versions, i.e. for each SOPID pick the VersionID with the latest date.

One can do many things with subqueries and Max - functions, but after some experimentation I have stumbled upon this:

Code:
SELECT Last(tblTest.VersionID) AS LastOfVersionID
FROM tblTest
WHERE (((tblTest.IssueDate)<Date()-10))
GROUP BY tblTest.SOPID;

The function Last has a bit tricky reputation and description on the web. It does yields the right result in my testcase though , see the attached db - run query3.

Query3

Code:
LastOfVersionID
100
90
20
40
666
888
55
8

Update:
Well, it isn't. Just changing the VersionID numbers to something other than an ascending sequence renders an incorrect result with the above query. So what is the best way to get the desired result? (Access 2007)

View Replies


ADVERTISEMENT

Queries :: Create Query To List Hires By Customer Age Group

Jan 24, 2014

I am doing my project to create data base for Video Hire shop. Was allright so far; however hit the wall now.
I need to create query to list Hires(rentals) by customer age group. I have created query with the following fields: Customer ID, date (Date()), DOB field and calculating field: AgeGroup:Now()-[DOB]. When click to display data in AgeGroup field is displayed in days. How to set it up to display decades not number of days days?

View 1 Replies View Related

Queries :: Selecting Latest Row For Each Year

Dec 18, 2014

I'm having trouble with a simple query. They have an access database with a table like this (simplified for this example):

--------------------------------
Name Result Date
--------------------------------
Bob 5 1/1/2014
Bob 6 2/2/2014*
Bob 7 3/3/2015
Bob 8 5/4/2015*
Len 5 1/1/2014
Len 6 2/2/2014*
Len 7 3/3/2015
Len 5 7/4/2015*
---------------------------------

how I would write a query that gives me the latest result that each person earned in each year (I put a * symbol against the rows above that I am talking about)?

Bob 6 2/2/2014
Bob 8 5/4/2015
Len 6 2/2/2014
Len 5 7/4/2015

(The database is normalised and this table has an ID field).

View 5 Replies View Related

Queries :: Latest Record If Status Is AA?

Dec 22, 2014

I have a query with a record id, report date and status.

How do I pull the latest record if the status is AA?

For latest record in report date I used Max in Totals. With just this max it is pulling the latest date for each set of records with the same record id.

This is a start now how do i pull the latest record that has a AA status?

View 14 Replies View Related

Queries :: Find Latest Date

Jan 18, 2014

I have a list of project, each of which have dates which work were carried out on them. Each project can have more than one date. I want to be able to find the last date that any work was carried out, and then calculate how many days have passed since that happened.

View 6 Replies View Related

Queries :: Query To Show Latest Date

May 29, 2014

I have two tables. The first is called Drawing Register and contains the fields Drawing No (primary field) and Drawing Title.The second table is the Drawing Register Details table which contains the fields Drawing No (joined field - Drawing Register table), Revision, Revision Notes, Date Issued.

I have a query and report which will list all the revisions for each drawing. This is very important and useful. However I want a summary report which will only show the last revision for each drawing.

I copied the original query, turned on the Totals option and under the Date Issued field changed Group by to Max. However it is still returning all results for each drawing instead of only the last issue date for each drawing.

View 14 Replies View Related

Queries :: Finding Latest Number Using A Query

Jun 19, 2013

I need to find the latest number using a query.

All of the numbers follow this format:

[Doc No]
ABCD123

but the tricky part is the number could be suffixed with a revision. It is the latest revision I would like to find. For example, If I have a number followed by 3 revisions I would have:

[Doc No]
ABCD123
ABCD123+200
ABCD123+300
ABCD123+400

I need a query to give me the latest revision, in this case

[Doc No]
ABCD123+400

There is a possibility there are no revisions in which case the latest will be

[Doc No]
ABCD123.

How is this possible in a query???

View 5 Replies View Related

Queries :: Return Latest Record In Table

Feb 23, 2014

I'm trying to create in access2010

(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?

View 4 Replies View Related

Queries :: How To Get Latest Dates And 3 Other Data For Records From A Table

May 29, 2015

Giving up after a zillion tries. I have a table (tblLOADS) containing: BROKER, PUDATE, MATERIAL & DRIVER. I am able to create this query:

SELECT tblLOADS.L_ID, tblLOADS.BROKER, Max(tblLOADS.Pudate) AS MaxOfPudate, tblLOADS.Material, tblLOADS.Driver
FROM tblLOADS
GROUP BY tblLOADS.L_ID, tblLOADS.BROKER, tblLOADS.Material, tblLOADS.Driver
HAVING (((Max(tblLOADS.Pudate))>0));

Problem: It's datasheet view displays all of the records for BROKERS, PUDATE, MATERIAL & DRIVER, instead of ONLY the records for the last PUDATE of each of the BROKERS, with their corresponding MATERIAL & DRIVER fields.

View 12 Replies View Related

Queries :: Getting Latest Record For A Particular Customer Number Using MS SQL Query

Jul 27, 2013

My table appears like this with the following columns:

Name| CUSTNo| ORderNo.| AcctDate| OrderDate| OrderDescription

I would like to write a ms-sql query to return the records of each customer number with the latest OrderDate. One more thing, if the customer has two separate order numbers on the same date(should be the latest date), the query should be able to pull up both the records.

I tried with the code below but its taking a long time to execute and finally hanging up MS ACCESS.

SELECT * FROM TableName AS a WHERE Not Exists (SELECT *
FROM TableName b WHERE b.CUSTNo = a.CUSTNo AND b.OrderDate >= a.OrderDate);

View 2 Replies View Related

Queries :: Return Latest Record For Multiple Criteria

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

Queries :: Latest Date Per FName - Query From Table

Nov 23, 2014

How do I query from this table:

ORIGINAL_table

FName | END_Date|
--------|---------------|
John| Oct 09, 2014|
John| Oct 15, 2014|
John| Oct 25, 2014|
Mike| Dec 10, 2014|
Mike| Dec 15, 2014|
Mike| Dec 20, 2014|
Mike| Dec 25, 2014|
Jimm| Dec 10, 2014|
Jimm| Dec 15, 2014|
Jimm| Dec 20, 2014|
Alex| Dec 01, 2014|
Alex| Dec 05, 2014|
Alex| Dec 10, 2014|
Thom| Nov 10, 2014|
Thom| Oct 10, 2014|
Thom| Aug 10, 2014|

The output will be:

FName | END_Date|
----------|---------------|

John| Oct 25, 2014|
Mike| Dec 25, 2014|
Jimm| Dec 20, 2014|
Alex| Dec 10, 2014|
Thom| Nov 10, 2014|

The query table(output) will just have to be latest date("END_Date") per "FName"

View 1 Replies View Related

Queries :: Query Result For Latest And Max Date From Table

Apr 26, 2013

I try to make a query that can show me data from the lastest and max date from the table i have ( see attchment file )

I try using max but still the result is not what i am looking for...

View 6 Replies View Related

Queries :: Calculated Field To Find Latest Date For Each Record

Jul 11, 2015

If I have four date Fields in a query, Astart, Bstart, Cstart, and Dstart and want to have a calculated field to find the latest date for each record how would I do that? I have tried things like:

LatestDate: MAX(Astart, Bstart, Cstart, Dstart).

View 2 Replies View Related

Queries :: Subquery To Return Latest / Most Recent Value For Each Record In Main Query

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

Queries :: Append Remove History And Just Keep The Latest Archive Version Set Of Files

Feb 17, 2015

I have a list of archive folders with unquie IDs and images files which have a version history.

I need to remove the history and just keep the latest archive version set of files. It seems an append query would be best but just can't think the best way todo it.

Table Example:

Archive: Folder: User: SubFolder: Filename:

01 TA05420010125-01 8769091 WAG 20010125-01.tif
01 TA05420010125-01 8769091 TIM 20010125-02.tif
02 TA05420110324-01 8769091 WAG 20110324-01.tif
02 TA05420110324-01 8769091 TIM 2011032402.tif
02 TA05420110324-01 8769091 PD 20110324-02.tif
03 TA05420150114-01 8769091 VYE 20010125-01.tif
03 TA05420150114-01 8769091 DFU 20010125-02.tif

In the above example I only want to keep user 8769091 latest filename info from archive 3 and exclude the rest. I need todo this for over 3000 users who could have multiple versions.

View 5 Replies View Related

Group By Customer And List A Field

Apr 28, 2008

Hi all,

I have a data set such as:

CustomerIDCompanyNameSiteNameStrataIDRUID
4002Severn Trent Water LimitedFRANKLEY3~00074832
4003Severn Trent Water LimitedFRANKLEY3~00074831

I would like to only have 1 record of the Company name/ID with the RUIDs listed almost in one record something like...

CustomerIDCompanyNameSiteNameStrataIDRUID
4002Severn Trent Water LimitedFRANKLEY3~00074832, ~00074831

Can we do this in SQL? (Specifically and Append query)

Thanks for any suggestions.

View 3 Replies View Related

Queries :: Find Latest Date In A Table Where Dates Are In 2 Separate Columns And Multiple Rows

May 19, 2015

I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)

I have a table "Assessment tracker" with the following structure

Name Type
Candidate short text
Unit short text
EV1 Date Date
EV2 Date Date

My Data:

Candidate Unit EV1Date EV2 Date
TH1 10 07/05/2015 25/05/15
TH1 10 07/05/2015 07/06/15

I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.

Query:
PARAMETERS [Candidate Name] Value;
SELECT [Assessment Tracker].Candidate, [Assessment Tracker].Unit, [Assessment Tracker].[EV1 Date], [Assessment Tracker].[EV2 Date], Max(MaxDate([Assessment Tracker]![EV1 Date],[Assessment Tracker]![EV2 Date])) AS Achdate
FROM UnitData INNER JOIN [Assessment Tracker] ON UnitData.Unit = [Assessment Tracker].Unit

[Code]....

Output:

CandidateUnitEV1 DateEV2 DateAchdate
TH11007/05/2015 25/05/201525/05/2015
TH11007/05/2015 07/06/201507/06/2015

It does this by using a function shamelessly copied from the web somewhere...

Function Maxdate(ParamArray FieldArray() As Variant)
' Declare the two local variables.
Dim I As Integer
Dim currentVal As Date' Set the variable currentVal equal to the array of values.
currentVal = FieldArray(0)
' Cycle through each value from the row to find the largest.

[Code]....

This is working well (I think)

I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.

Query:
SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate
FROM [Candidate AC Dates]
GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit
ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;

But this is returning

Candidate Unit MaxOfAchdate
TH1 1025/05/2015

I expect it to return

Candidate UnitMaxOfAchdate
TH1 10 07/06/2015

It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)

View 14 Replies View Related

Cascade Combo Box Previous CD Group List Box

Apr 29, 2005

I need directions on Cascade Combo Box, So when I select:

CD Group

Displays only the CD's in that Group that are related to that Name
So what fields would I have to set up for CD Group and CD NAme with that Table.
Someone emailed me an article on Cascade combo boxes what fields so I need for the coding.......

View 2 Replies View Related

Can Change Group Of Checkboxes To Drop Down List

May 22, 2015

I have a spreadsheet that has been downloaded from a website. On the website people have chosen from a group of 28 checkboxes their answer. I have imported this spreadsheet into access. I now need to be able to run a report that shows only the one they chose instead of listing all 28..

View 1 Replies View Related

Reports :: Training Database - Group Report By Field List Row Source

Oct 30, 2014

I am in the process of creating a training database that includes levels of proficiency with certain tasks for employees.

In one of my reports I would like to appropriately display with tasks the employee "Cannot Perform";"Can Perform with Assistance";"Can Perform Alone";"Trainer" (straight from the field list of the task). But I can't seem to get the hierarchy correct. Tried it in a PivotTable too as I thought similar to PTs in Excel you could get some kind of "count" of values. Couldn't make that happen either.

Each employee has a proficiency rating on about 20 different tasks. Proficiency input is controlled by a field list. I would like to structure this part of the report like so:

------------------------------------------------------------
EMPLOYEE PROFICIENCIES
--Cannot Perform Task
----Cutting
----Trimming
----Grinding
--Can Perform With Assistance
----Painting
----Fixing
----Drilling

etc. etc. So in this case the Field itself would become the value being grouped. I know there has to be some logic either in a query or SQL.

View 2 Replies View Related

Queries :: Using Variable In A List Field Query - Getting Complete List On Initial View

Mar 28, 2014

In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.

So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.

I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.

VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);

VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery

The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.

Any hints, how I have to use the * for getting the complete list on the initial view ?

View 5 Replies View Related

Queries :: Return List Of Records From Original List

Apr 23, 2013

I'm looking to move an excel sheet to access because the row counts are too much.The main thing it does is compare the supplied data against a list I hold in the sheet.There are not duplicate records, however..Some data is a direct lookup for a full match, but much of it is a count to see how many records contain a certain string.

I have 500 keywords which have a countif function in using wildcards.I need to create a query/report which will return a list of records from the original list which contains each keyword featured and how many times it features.I was going to do it in PHPmysql but the time it took to parse a million records for every keyword made it pointless.

eg:
keywords:
look
billy
magic

list:
"have a look and see"
"spanish dave"
"who is billy brag"
"looky looky I go hooky"
"who's the man from argentina"
"could it be magic now"

my spreadsheet would return a 1 next to ""billy" and "magic" and would put a 2 next to "look".

the sheet has the keyword in each row and next to the column:
=COUNTIF(list,CONCATENATE("*@",B13))
where "list" is the external data.

View 3 Replies View Related

Queries :: Changing Dates In A Group Of Queries

Jul 21, 2013

I have written a large number of queries to gather data for a quarterly monitoring form. How do I replace the dates to update them for next quarter.For example I have written a query which counts the number of new members who started in the period 01/04/2013 to 31/07/2013. How can I change this (and the other 200 queries) without opening them all individually and manually altering it. Is there an Access equivalent of Word's Find and Replace?By the way I am using Access 2000.

View 11 Replies View Related

Queries :: Top N Per Group

Jun 19, 2014

I have been trying endlessly to get this working but to no avail. I have looked and used sample codes posted on the net.

I am trying to get top 1 [route_list_code] per [works_order_number]

I have attached three images:

- Query as isQL code
- Query with top n in criteria (pulling wrong information and its just by coincidence that the two records shown are split by group)

The code appears to just pull top # record (not per group)

as is.JPG
SQL code.JPG
query with top n in criteria.JPG

View 2 Replies View Related

View Queries By Group

Mar 8, 2012

I have moved from Access 2003 to 2010. In Access 2003 I was able to create query groups that contained shortcuts to queries in the main database. This worked great for us because we did not want to create user interfaces that required support- but we could have multiple queries in a database and have users look only in their query group.

The database I am using was created in 2003 and had those groups. Is there a way to view, edit and create a similar view in 2010?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved