FIND LATEST RECORD

[Janatwork]

Hi,
This seems to have been covered before, but none of the answers work for me...........

Bascially I have a query which looks at an inventory table.

I have 7 fields in the table:-

ID which is an autonumber
Product code
Opening balance
closing balance
Year
Period
Date timestamp

I can have the same product code for all periods within the year, so 2007, period 1 & 2 etc.

What I need to do is to get the latest / last record, pick up the closing balance and then use it as an opening balance for the next month.

I have tried using MAX, LAST, DMAX and nothing works, I get all records back whether they be period 1 or 12 !!

I have no concept of SQL and this is all done through MSACCESS.

Can anyone help PLEASE.

Thanks in advance

 

[genomon]

I have no concept of SQL and this is all done through MSACCESS

Yikes. Without SQL, how are you using MAX, LAST, DMAX ?
You must have some kind of code you could post here to look at?

All I ask is a chance to prove that money can't make me happy.

 

[JoeAtWork]

SQL isn't optional if you are using MSAccess. You simply can't create a program in Access without foundamental knowledge of SQL (the wizards will only get you so far). A good book I recommend is Access Developer's Handbook.

 

[jedraw]

What exactly do you mean by last/latest record?
Do you mean the record with the greatest Date timestamp?
If so, this sort of query should give you the filds in the record with greatest Date_Timestamp

SELECT * FROM InventoryTable
WHERE Date_Timestamp = (Select Max(Date_Timestamp)
FROM InventoryTable);

 

[Janatwork]

Hi Guys,

Many thanks for taking the time to help me and I know.......I really should get to grips with SQL, but I have managed to achieve my goal !!!

I've added an autonumber to my INVENTORY table which I append to on a monthly basis. I then created a little query in Access with just a unique ID and the product code so:-

Field - ID
Table - INVENTORY
Total - Last
Sort
Criteria
or

Field - product
Table - INVENTORY
Total - Group By
Sort
Criteria
or


and this set up seems to work fine, although I still have more testing to do...