I have a table (table 1) containing property records, say 1 - 10, all with Names, Address Contact Details. I have another table (table 2) containing transactions for this property. Transactions include rent paid, arrears etc. Now what I want to do is make a report using a query to list each property (from Table 1) and the last amounts from the fields in Table 2. See below

table_1

PID (primary)
name
property_address
contact_number

table 2 (this appears as a subform in the table 1 form for each client)

TID (primary)
amount
arrears

So to clarify I need to get a query so it will display the last figure of amount and arrears for each client in table_1.

I have used DLast("[table_2]","Arrears"," [PID]<=" & [PID]) and this returns nothing.

What am I doing wrong? I need to find the correct expression for the query. Not VBA code.

I dont think I need a loop. I just need every record from table_1 to show the last field from arrears from table_2. Table_2 form appears as a subform in the Table 1 form.

Cheers Mike

You've reversed the first two arguments:

http://www.mvps.org/access/general/gen0018.htm

I wonder if this is what you're after:

http://www.baldyweb.com/LastValue.htm

Hi Paul,

Yes it looks like this is what Im after.... but this info in the link is code I think.... Im trying to avoid this and just use a query.... I am using Access 2007 and Im just setting up a simple query to read the records from Table_1 and combine the last record of a field (column) with Table_2. In fact Table_2 doesnt really exist, its a Query to find which clients are in arrears on their rent. I then combine the arrears of each client to produce a report that displays which properties (addresses) have arrears which has been calculaated independently for each property. i.e. As a customer pays their rent we add it in a transaction subform. Then we total up the months rent for each client and compare it to what should be paid in a query. The problem is I need to make a report so that all the properties that have arrears and the amount of arrears is produced.

Can you give me the expression argument I would need to type into the query field(column) so it will read all the data (last record amount) to produce it with all the properties?

Can I send u the program.... ? (you might laugh at the complexity of it all)

Neither link is code; the first is about the syntax of domain aggregate functions, the second is SQL (queries). You should be able to post your db here:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

I have 2 forms, 1 form tenants and another as a subform tenant transactions.

The problem is that it returns just 1 record from the subform and inserts the same field amount from this 1 record into all the records for the main form tenants.

I need a query control source to take the amount for each corresponding record of the subform and to display it in the tenant table/form.

e.g. dlast("amount","Tenant Transactions","ID=&"??????)

can you provide me an example what whould be written?

Cheers Mike

Mike

If it's supposed to get the value from the current record, something like:

dlast("amount","Tenant Transactions","ID=" & [ID])

I would caution you that DLast is usually unreliable:

http://support.microsoft.com/kb/208190

Thanx Paul,

Youre right, something as easy as that statement did it. I tried it in a new sample base to play with and when I convert it to the main program it still doesnt work and think its just a child master issue or relationship.

I will work on a version 2 of the database and migrate the information across to it.

Thanx for your help and I owe you a beer!

Mike :)

No problem Mike, and welcome to the site by the way!