Select record with latest date Results

Hi...cant get my head round this at all ...I have a subform where info about the progress of debt collection on a clients fees is recorded.
The main form (ClientID, ClientName, ClientCode, Manager) has a subform which shows all current o/s debts for that client (this sf is populated from an excel sheet which is imported daily to tblAgedDebtors).
As the manager contacts the client the date and detail of the call are recorded and a combo is selected (cboFeeDue, options Yes, No, InPart,Deferred) this is done on the main form where there is also a combo which lists the current o/s fee note numbers for that client, this too is selected.
As the manager builds up the calls one FeeNote number can have many entries, at the time of wanting to produce the report a fee note can have been any and all of the options in cblFeeDue.......I am trying in vain to build a report that looks at the date of the calls, using the latest date only as the cboFeeDue selection against this date will be the only one relevant I then need to sum the fee note value by cboFeeDue for each and all managers eg. view all those by latest call date and that have been categorised as deferred ....if this make any sense at all and anyone can give me some help it would as always be higely appreciated ...cheers Fi

Query: qrySAP DED DUMP

Original query SQL:

[SAP DED DUMP].PersonnelIDNumber,
[SAP DED DUMP].FullName,
[SAP DED DUMP].[Start Date],
[SAP DED DUMP].[End Date],
[SAP DED DUMP].Amount,


WHERE ((([SAP DED DUMP].PersonnelIDNumber)=nnnnnn))

ORDER BY [SAP DED DUMP].FullName; Ok using a specific personnel number, I know there are two records for this query (using a particular person's id# in the place of nnnnnn as an example).

What happens is an employee may change pay grades at any point in time and thus their deduction amounts will change. When this happens a new record is created for them reflecting the amount change. Along with this, their old record's end date is changed to the date of the change in pay and the new record is given the end date of the fiscal, annual, or academic year depending on their new assignment.

After doing some research, I understand that in order to return only the record with latest date I would need to use either Max or Last in this form

(Select (Max([End Date])) from [SAP DED DUMP])

Barring me setting this up wrong and getting the aggregate error, I end up with no records at all. The end dates may be soemthing like 10/15/2009 and 06/15/2010. I would expect to get the 2010 date as a result, but instead I get nothing back.

query that returns no records:

[SAP DED DUMP].[Start Date],
Max([SAP DED DUMP].[End Date]) AS [MaxOfEnd Date],
[SAP DED DUMP].Amount,
[SAP DED DUMP].PersonnelIDNumber,


[SAP DED DUMP].Amount,
[SAP DED DUMP].PersonnelIDNumber,


(((Max([SAP DED DUMP].[End Date]))=(Select (Max([End Date])) from [SAP DED DUMP]))


(([SAP DED DUMP].PersonnelIDNumber)=nnnnnn))

So...what am I missing?


I need to pull only the records with the latest date into Excel from Access. As a simplified example, let's say the Access database has two tables:

Table T1 is price history with fields PartNum, PriceDate, Price. For each PartNum, there are multiple dates (PriceDate) and associated prices (Price). This table has hundreds of records because for each PartNum there are dozens of price changes (i.e., PriceDate & Price)
PartNum, PriceDate, Price
P1, 1/1/10, $1.00
P1, 2/1/10, $2.00
P1, 3/1/10, $3.00
P2, 1/1/10, $4.00
P2, 2/1/10, $3.00
P3, 1/1/10, $1.00

Table T2 has fields PartNum, Cost, and ProdStatus. This table has only dozens of records and almost always has one record per PartNum but could have more than one depending on ProdStatus (which is either 1 or 0).
PartNum, Cost, ProdStatus
P1, $0.50, 1
P2, $0.75, 1
P2, $0.75, 0

Using a query executed from within Excel, I need to pull into Excel from Access only records where PartNum is in both tables and ProdStatus = 1. For these records, I want only the latest PriceDate and Price, the Cost, and the ProdStatus.
results example:
PartNum, PriceDate, Price, Cost, ProdStatus
P1, 3/1/10, $3.00, $0.50, 1
P2, 2/1/10, $3.00, $0.75, 1

I've tried this query but it gives me all price dates and the price dates are not sorted.

SELECT T2.PartNum, T2.Cost, T2.ProdStatus, T1.PartNum, T1.PRICE, T1.Date
FROM T2 LEFT JOIN T1 ON T2.PartNum = T1.PartNum
WHERE (((T1.PartNum) In (SELECT TOP 1 PartNum FROM T1 WHERE T1.PartNum = T2.PartNum ORDER BY T1.PartNum,T1.Date Desc))) AND T2.ProdStatus = 1

With this query, I’m getting hundreds of (almost as many) records as in T1 when I should be getting fewer than T2 because of ProdStatus =1 criteria. I’m getting a record for each of the PriceDate’s (not just the latest) and the PriceDate is not sorted.

Any help would be appreciated.


I have a table that shows me 3 fields:

My issue is each process and operation may be there multiple times due to multiple Effective dates. I only need to see each Process and Operation one time based on the latest Effective date. Below is what I have NOW:

1/010/1-8TCOWLFEEDER 2005-11-01 10
1/010/1-8TCOWLFEEDER 2005-11-01 20
1/010/1-8TCOWLFEEDER 2005-11-01 30
1/010/1-8TCOWLFEEDER 2005-11-01 40
1/010/1-8TCOWLFEEDER 2005-11-03 10
1/010/1-8TCOWLFEEDER 2005-11-03 20
1/010/1-8TCOWLFEEDER 2005-11-03 30
1/010/1-8TCOWLFEEDER 2005-11-03 40
1/010/1-8TCOWLFEEDER 2005-11-09 10
1/010/1-8TCOWLFEEDER 2005-11-09 20
1/010/1-8TCOWLFEEDER 2005-11-09 30
1/010/1-8TCOWLFEEDER 2005-11-09 40

This is what I need:
1/010/1-8TCOWLFEEDER 2005-11-09 10
1/010/1-8TCOWLFEEDER 2005-11-09 20
1/010/1-8TCOWLFEEDER 2005-11-09 30
1/010/1-8TCOWLFEEDER 2005-11-09 40

Out of the records above I would only want to see the records with the 2005-11-09 date. The dates can vary based on processes so I really need something that selects the the latest date for each Process and Operation. I would like to do this in a query or multiple queries.

Thanks for any help.

Hi all, Im not sure if this is possible. Any gurus out there might be able to answer this one.

I have a table with, amongst other information, delivery dates for consignments. Each consignment consists of one or more containers. Where there are multiple containers. As each container arrives at its destination, the arrival date is recorded in the table. I need to update another table that has consignment arrival dates (it doesn't show container level detail). The consignment arrivals table needs to store the date of the last container to arrive for the specific consignment. To create a query that will accomplish this is easy (its just a max query essentially).

The problem comes where all containers in a consignment have not yet been delivered. The are some consignments where, for example, half of the containers have arrived so there are arrival dates for some containers, but for other containers there are Null values (since they haven't arrived yet). So my question is this: Is it possible to write a query that, where all containers have arrived for a consignment, select the date of the last container to arrive AND if all container have not yet been delivered for the consignment (ie there are null values still in the consignment's container records arrival date field), do not select the last container arrival date.

Essentially, the NULL value acts as a wild card. If a null is present, then that becomes the 'latest date' otherwise, if no nulls are found then the last container arrival date is used.

Below is a cut down example of data in each table:

Containers Table

consNum contNum arrivalDate
12345 0001 1/2/2012
12345 0002
23456 1111 17/5/2012
23456 1112 18/6/2012
23456 1113 19/5/2012
99901 1001 16/7/2011
99901 1002 18/7/2012
40345 2222 19/7/2012

The query should be able to return the following based on the data on the table above

consNum arrivalDate
23456 18/6/2012
99901 18/7/2012
40345 19/7/2012

Many thanks in advance

I need to able to quickly find which the last records that have been modified. I have an invisible field "txtModified" on the main form and selecting when changes where made to the record on the main form is easy using a qry on that field. The problem I face is that the form with all details of a particular item contains 2 subforms. Changes to data on each of these subforms doesn't effect the time/date on the main form. However, each of the subforms is also time stamped upon change.

The qry to select the last records changed therefore needs to look at 3 fields and select records with the latest date in any of the 3 "Modified" fields. How do I do that? I suppose I'll have to put an expression in the qry?

Thanks for your advice.

I am trying to run a Query that searches for a single Tool ID Number and returns the transaction record that has the latest date. My query currently is this:

SELECT LocationStatus.ToolID, LocationStatus.CurrentLocation, LocationStatus.DateofEvent
FROM LocationStatus
WHERE (((LocationStatus.DateofEvent)=(SELECT MAX(dateofevent) FROM [LocationStatus] AS t2 WHERE t2.[ToolID] = [Tool Number])));

But the problem I am having is that the query is returning MULTIPLE Tool ID numbers with matching dates. Before the query runs a parameter box pops up asking for a Tool ID number but there should only be one record returning. HELP!!!

Hi everyone,
I have a normalized table containing the dates and associated monthly performance of several investment funds. The date range is 07/01/1990 to 06/01/2010. Most funds have a performance track record that is less than the maximum 240 observations. Some investment funds also have gaps in their track record. I would like to create a query that selects funds with at least 60 continuous observations between 07/01/1995 and 06/01/2010 (i.e. cut off the first 60 obs + a minimum track record of 60). Asssuming the table is called Performance and the relevant fields are [ID] and [MM_DD_YYYY], I have come up with the following:

SELECT Performance.* FROM Performance WHERE (((Performance.MM_DD_YYYY)>=#7/1/1995# And (Performance.MM_DD_YYYY)= 60;

Sadly, this does not work because it would also include an investment fund, for example, with a history of 12/1/1990 to 12/1/1997. This fund should not be included since the track record of 60 must be between the specified earliest and latest date. Any help in this regard is greatly appreciated.

I am trying to retrieve data for a particular record.

When Project field matches a certain project number I want it to pick the record with the latest date in the date field field to select certain data fields(Owner & Rating) from that record.

Below is my attempt. However the problem is that displays all records with that project number and not just the record with the latest date.

Any ideas would be much appreciated?

	SELECT [Combined PRB Roadmap].ProjectNumber, Max([Combined PRB Roadmap].DateField) AS MaxOfFDateField, [Combined PRB
Roadmap].Owner, [Combined PRB Roadmap].Rating
FROM [Combined PRB Roadmap]
GROUP BY [Combined PRB Roadmap].ProjectNumber, [Combined PRB Roadmap].Owner, [Combined PRB Roadmap].Rating
HAVING ((([Combined PRB Roadmap].ProjectNumber)="NR-4237"));

I have a table - the key is made up of 2 fields (Client ID and Session Date). I want to create a query to select the latest record for each client. That is, if I have 3 records for client A123, with dates of Jan-1-2001; Feb-2-2001; and Mar-3-2001; I only want to select the record with the Mar-3-2001 date. Can anyone help me to do what I want? Thanks for any asistance.

I thought I had just about finished my DB but now Ive printed out and checked my reports I notice I have several records in different types of reports showing duplicate records. Iv'e gone back over the queries and there are one or two duplicates in several of my queries that I did not notice before because there are are only the odd one or two. No matter what I do I can't stop this happening. I think it must be something to do with the dates in my payment table. PaymentID is key field - foreign key is MemberID. One member can have several paymentID's a new one every year when membership renewed. The problem seems to be when the member has two payment dates in the same year. This shouldn't be anyway but the database has not been used properly with entry dates missing, written over of wrong dates etc. Ive tried to correct this but do not want to tamper with past payment date records. I already have 'select distinct' in my queries and have tried 'distinct row' which seems to return even more duplicate records. Im pulling my hair out over this there must be a way to return the records from members showing just 1 only of their very latest payment record/date. Im using this expression in the query.

LastPaid: (SELECT MAX(PaymentDate) FROM S_Payments_Table WHERE S_Payments_Table.MemberID =S_Members_Table.MemberID)

I have a database with PCs and installed hardware. An external program scans all PCs, the result of the scan can be saved as Access file.
Now I need a query to select from each PC the installed hardware (Monitor, Printer...) but only with the latest date. I have written a query MaxDatum to select the the lastest date. Here's my query code:

SELECT [Table1.needed fields], [Table2.neede fields]...
FROM (((Table1 INNER JOIN (Table2 INNER JOIN qryMaxDatum ON (Table2.Field1 = qryMaxDatum.Field1) AND (Table2.Field2 = qryMaxDatum.MDate)) ON Telle1.Field1 = Telle2.Field1)
INNER JOIN Table3 ON Table2.Field2 = Table3.Field2)
INNER JOIN Table4 ON Table2.Field2 = Table4.Field2)
INNER JOIN Table5 ON Table2.Field2 = Table5.Field2
ORDER BY Table1.Field1;

My problem: this query mulitply the number of data records so that I have 80 data records with exactly the same content per PC. How do I neesd to change the query to get only one data record per PC?

I'm running access 2k, WinXP Pro SP2 - both are current with latest updates. I'm having a problem getting the select query behind a report to give me the results I need. The query works with two tables (Client List and Atty_Pmt). Atty_Pmt is related to Client List on a one-many relationship, using ClientList_ID primary key.
The payments table structure is as follows:Atty_PmtIDDatePayAmtPayNotesClientList_IDThe Client List table has fields like 'lname', 'fname', 'dob', etc, etc.The ClientList table structure is as follows:IDLnameFnamedobstatusIm trying to structure a query that will list each client, and ONLY the last payment they made. I tried using a select query Top n, making 'n' value 1, and it only lists one client, one payment record, which isnt even the last payment they made.
This is the query that I currently have....

	SELECT TOP 1 [Client List].ID, [Client List].Lname, [Client List].Fname, Atty_Pmt.Atty_PmtID, Atty_Pmt.Date,
Atty_Pmt.PayAmt, Atty_Pmt.PayNotes FROM [Client List] INNER JOIN Atty_Pmt ON [Client List].ID = Atty_Pmt.ClientList_ID;

Can someone tell me how to single out the most recent payment (either by greatest date - maybe closest date to current date - or by the 'Atty_PmtID' field). It could possibly be based on the 'Atty_PmtID' field because the payments are entered in order as they come in, so the most recent payment would be the higher 'Atty_PmtID' autonumber, but I just dont know.
Thanks in advance for your suggestions and help

Hello. I thought I could handle this one, but I'm just not getting anywhere. My situation is pretty simple: I have two tables, with a one-to-many relationship. Both tables have primary keys, and the primary and foreign keys are set up correctly. Referential integrity is enforced. It's a pretty simple join/relationship. OK...

If I build a query and select a row from my "one" side table, obviously 0, 1, or more than 1 related records can be selected in the "many" side table. Very ordinary, and most of the time, that's what I need.

But now, instead of returning the "many" related records from the "many" side table, I want to select only ONE record, based on the most recent date value in one of the date fields. That is, if I build a query that selects a particular record on the "one" side, I want ONLY the most recent (by virtue of the date field) record from the "many" side table to return. I don't want the other records that are related. AND, I need to be able to return (in the same record set) the "many" side table's unique ID.

I'm writing on the fly SQL below (don't have Access on this machine), so it may not be exact, but here's an idea what I'm trying for:

SELECT Max(ManyTable.MyDate) as MaxDate, ManyTable.ManyTableID, OneTable.OneTableID FROM ManyTable INNER JOIN OneTable ON OneTable.OneTableID = ManyTable.OneTableID

This SQL returns all the related records from the "many" table, even though I thought my Max() function would only return the latest date value.

SUMMARY: I just want a "temporary" one-to-one recordset in my query, with the one (on the "many" side) simply the record that has the greatest date value in my specified date field. I've tried a dozen different totals queries, crosstabs, etc. I can't believe I'm having such a block about this. Any help is appreciated; thanks.


have a simple table with projects, some dates and a column "status" showing if the project has finished or not (yes/no type). The projects are identified with nummbers (Project-Nr). in the table it ist possible that one and the same project number has not been finished and is marked with no, but at a later time it has finished and is marked with yes in the column status.

for the report I need to extract projects that are finished and the ones that are not. but I cannot simply choose the ones that are marked with yes or no, because some projects with the same number are simply both status yes and no.

the parts of the table :
Project-Nr Status
400 No
401 No
402 Yes
403 No
403 No
403 Yes
Date-at a certain time a project was not finished at a later time point it is finished, that's why I have duplicate entries in Project-Nr

Project-Nr ist numerical, Status is Yes/No (True/False)

I need to extract Projects for the report, the user can choose if he wants to see the finished or unfinished projects. The problem is for example with the project number 403, because it was first set to status NOT FINISHED and later the same number is finished, otherwise I could simply just check the status column and extract the finished and the unfinished ones. I cannot do that, because then project number 403 would appear as unfinished and it is finished in the end.

this ist my code:

stSQL = "SELECT [Pr-Nr], Status, Max (Termin) FROM Termine GROUP BY [Pr-Nr], Status"
rsPSB.CursorLocation = adUseClient
rsPSB.Open stSQL, conCurrent, adOpenStatic, adLockOptimistic, adCmdText

and later I want to open a report with this data and several conditions

strConditionLatestDate= "Termine.Termin ORDER BY Termin DESC LIMIT 1"

DoCmd.OpenReport "internes Audit", acViewPreview, "query", strConditionDatumAb & " And " & strConditionStatus & " And " & strConditionLatestDate

but the condition for the latest date doesn't work, do you have any idea how to do this..

I'm still working on my first database project, and have learned so much during this process. Now I'm stuck at what seems to me, a simple query, but how to do this escapes me.

I have created a database (with SSnafu's help, that is!) that tracks assets (aircards, in this case), when they're checked out and back in, and to whom.

Here are the things I want the query to do:
1. Given an inventory number of an aircard (aircards.inventory_number),
2. Find the records for aircard actions (action_list.action_list) that begin with "check*" (this covers records that are "checked out to" and "checked in to"
3. AND returns the ONE record with the max date (aircard_actions.action_date) from #2.

Overall, what I want is the userid (employees.userid) that was the most recent person a card was checked in/out to, so I can display it in a read-only box on the form with the other aircard data. This way when going through the aircards, you can see instantly who it belongs to at the present time.

The query I've made will drill down somewhat, but I cannot seem to get it to show ONLY the latest date. And for this instance, I've hard-coded the criteria of "m100" as the inventory number so I'm only looking at 1 card. That would be removed later. Here's what it looks like:

Or, the code:
Code: SELECT Aircards.inventory_number, action_list.action_list, Aircard_Actions.action_date, Employees.userid FROM Employees INNER JOIN (action_list INNER JOIN (Aircards INNER JOIN Aircard_Actions ON Aircards.Aircard_PK = Aircard_Actions.AircardId_FK) ON action_list.ActionID_PK = Aircard_Actions.action_FK) ON Employees.EmployeeId_PK = Aircard_Actions.UserID_FK GROUP BY Aircards.inventory_number, action_list.action_list, Aircard_Actions.action_date, Employees.userid HAVING (((Aircards.inventory_number)="m100") AND ((action_list.action_list) Like ("che*"))); And this gives me exactly what I want it to, but I only want the MAX date, 1 record, and I'm getting all the records. I've tried using MAX(date) everywhere, but I always get an aggregate error or operand error. This is what I get presently with this code:

So, can anyone tell me how to get the ONE record from the above picture and where or what I would use "max(date)" or if there's something else I should know about. I would appreciate it greatly!

I have a Check Register I am working on and of course the need arises to schedule future transactions. Here is my dilemma: I want the user to be able to enter the data if date is today, easy! If not then I need a way to store data until it is time to enter this into the Check Register then append to the register.

This is as far as I have got! Am I approaching this right? 2 Screenshots attached to help you understand. Store data into temp table until then ???

Date Criteria for schedulleddte is;

In other words, if user selects a Register Transaction to be entered into the Check register and if fields, frequency,scheduleddte, frequencyamount and freqpayee are NOT Null then transaction can be saved. If Date is today!
If scheduleddte is date() then save record Else use scheduleddte as criteria to save record for future date.

I created a new table to store data in until date criteria is met, it is named Tsavedtrans
It has the same filed names as the above.

Here is my code such as it is when I got stumped as how to proceed!
Private Sub Form_Current()
If IsNull([Bank]) Then 'Bank Account is required
MsgBox "A Bank Account For Register Must be Selected"
End If

If Not IsNull(frequency) Or Not IsNull(scheduleddte) Or Not IsNull(frequencyamount) Or Not IsNull(freqpayee) Then
If MsgBox("prompt", vbYesNo, "Schedule a Future Transaction?") = vbYes Then
If (scheduleddte) = Date Then
If (scheduleddte) > Date Then

FYI I created a new table to store data in until date criteria is met, it is named Tsavedtrans
It has the same field names as the tables being used for the check register.
I also created a append query named Qappendfuturetrans that appends the data from the QRegB query where TReg table and TFrequency tables are used and that is what the form CheckRegister is based on, the query QRegB. NOW this is as far as I have got. Attached Thumbnails     Last edited by burrina; 12-25-2012 at 10:19 PM. Reason: More Explanation Reply With Quote 12-26-2012, 12:08 AM #2 burrina Expert Windows 7 64bit Access 2010 64bit Join Date Oct 2012 Location Freeport,Texas Posts 738 Ok, here is my latest attempt at entering a New Record every day when the user has selected "Daily" as the ([frequency]) criteria.
([scheduleddte]) is a date field.

Private Sub cmdCopyRecord_Click()
If Me.NewRecord = True Then 'Checks For New Record
End If
Exit Sub

If Not IsNull ([scheduleddte]) Then 'Checks For a Scheduled Date
End If
If ([scheduleddte]) < Date Then 'Date must be a New Date To Continue
End If

Dim v1 As Variant 'Variable 1
Dim v2 As Variant '""
Dim v3 As Variant '""
Dim v4 As Variant '""

v1 = Me!frequency.Value 'Sets Variable Values
v2 = Me!freqpayee.Value
v3 = Me!frequencyamount.Value
v4 = Me!scheduleddte.Value

RunCommand acCmdRecordsGoToNew 'Saves New Record

Me!frequency = v1 'Copies New Variables To New Record
Me!freqpayee = v2
Me!frequencyamount = v3
Me!scheduleddte = v4

End Sub

Hello all,
I'm a very new Access user, and this is my first database project. It's supposedly a simple database designed to track the checking in and out of Aircards (cellular cards for laptops). I'm not very good at explaining what I want to do, and I've searched for this solution, but I haven't come up with anything that clicks in my brain - but here goes. =)

1. On my main form (Add Aircard Form) and table (aircard_master) there is a value for Current Assigned User (aircard_master.userid)
2. On the main form, there is also a subform that shows actions associated with the currently selected aircard on the main form. (actions such as when it was issued, returned, activated, assigned a phone number, etc) and each action is required to have a date assigned to it. These actions are stored in the table (aircard_actions)

My proposed problem is that I want the Current assigned user to be auto-populated with the last user (aircard_actions.user) when data is entered into the subform (aircard_actions). All this can be seen/done on the main form. At this point, I'm unsure whether it needs to be done in a query, as code when a new action is recorded in that table, or what. But, the data needs to update the original table (aircard_master.userid) automatically based on the latest DATED entry for that associated card (aircard_master.inventory_number), from a non-null entry in the (aircard_actions.user) field.

This was confusing to me even as I wrote it, I know the people here are a lot smarter than I am on these matters, so maybe in makes sense to you. I'm going to try to find a way to link an attachment of the database with test data in it if anyone wants to take a look. It's 2.5MB so I can't attatch it here...
Oops, just figured out how to share it online. Here's the download link for it:

Thanks in advance if anyone has any ideas!

I am trying to retrieve unique records using select query. If I use Distinct or GroupBy funtion I am able to achieve the desired output, however I am not able to tick the check box (Reminded) when I run the query.
I have table with data as follows,
AuditID Date Reminded
1203 01/01/2010 No
1203 05/01/2010 No
1204 06/01/2010 No
1204 06/01/2010 No
1204 07/01/2010 No
1205 07/01/2010 No
1206 06/01/2010 No
1206 06/01/2010 No
I want to achieve the following outout
Reminded AuditID Date
No 1203 05/01/2010
No 1204 07/01/2010
No 1205 07/01/2010
No 1206 06/01/2010

I am looking to retieve unique AuditID with the latest date.
I tried the below queries
select b.reminded,b.auditid from RiskActionPlan b where date=(select max(date) from RiskActionPlan where auditid=b.auditid)
I am able to select the Reminded Checkbox when I run the above query however the it does NOT result with unique auditid (audit ids are repeted)
If i add distinct (Select distinct ....) I am able to get the unique records but i am not able to check the Reminded checkbox.

I have been trying to sort this out since last 4 hours, I will appreciate If someone could assist me with this.
Thanks in advance.

Hey Gang. Well, thanks to the Access World Forum's many helpful members, I'm venturing into coding some VBA, rather than using multiple hidden forms, queries, etc. to create convulted code-free solutions. Here's the latest item to be re-examined, but I haven't spotted the VBA solution yet...

Take a simple table that includes just dates and amounts:

TABLE: tblReadings

FIELD: timeReadingDate PRIMARY KEY
TYPE: Date/Time
INDEXED: Yes, no dupes

FIELD: longReadingAmount
TYPE: Long Integer

Now, it seems silly to me to record the difference/volume with every record, since that's a calculated value (today's reading, less yesterday's reading, equals yesterday's volume). However, my current implementation seems inelegant.

First, I have a query that repeats the two original fields, sorting the reading table in ascending order by the dateReadingDate field:

QUERY: qryReadingsByDates

FIELD: timeReadingDate
TABLE: tblReadings
SORT: Ascending

FIELD: longReadingAmount
TABLE: tblReadings
SORT: None

Then I have a second, separate query that takes the first one, repeats the fields again, and adds the longVolume field:

QUERY: qryReadingsWithVolumes

FIELD: timeReadingDate
TABLE: tblReadingsByDates
SORT: None

FIELD: longReadingAmount
TABLE: tblReadingsByDates
SORT: None

FIELD: longVolume:

The longVolume field contains the following expression

longVolume: IIf([timeDate]=DMin("[timeDate]","qryReadingsByDate"),[longAmount],[longAmount]-(DSum("[longAmount]","qryReadingsByDate","[timeDate] =#" & (DMax("[timeDate]","qryReadingsByDate","[timeDate] < #" & [timeDate] & "#")) & "#")))

Then, if I want to to take a further step (say, for example, examining readings and volumes that occur between selected dates), it means adding a THIRD query, making the chain even LONGER...

It occurs to me to wonder if there's not a shorter and faster way to do this in a VBA function. Surely there's something better than slogging through a series of 2 (MINIMUM!) nested queries every time I want to include the volume on a form or report?

All ideas welcome...

--Newbie-Once-Again Mac

Not finding an answer? Try a Google search.