Running a query that only shows the latest entry

[maxinemdk]

I'm basically new to creating a query/database. We currently have a query of
which information is taken from 2 separate tables. Each table has hundreds of
entries and they are linked according to their Order No.

One of the table (Forecast table) contains several entries such as Forecast
Date, Status, CAP Forecast, EXP Cost, among others. These entries have
different values. I'd like to update our current query and narrow down the
result taken from the Forecast table wherein it will only pull the last entry
or line (most likely containing the latest date). This result should not sum
up the dollar amounts entered in the other 10 columns but would only take the
info from the last line/entry or last row. I would greatly appreciate any
help you can give me. Thanks.

 

[Lord Kelvan]

do that on the latest row is not the best method as the latest row is
not always the latest record and it would also only work if you are
using an auto number for the primary key

working on the latest date though is better but that may also have the
problem if there are two dates on the same day

regardless the code you would use to get the latest record between two
tables is

SELECT *
FROM table1, table2
where table1.table1id= table2.table1
and table2id in (select max(table2id) from table2 group by table1id);

but if you are just trying to get the latest row in one table you can
do something like

select top 1 *
from table
order by fieldidentifinglatestrecord desc;

 

[maxinemdk]

Thank you for replying. I made sure that the Forecast date is not replicated
so I guess this is better. The info I need will be at the bottom of the
Forecast table but I don't want duplicate entries nor the amounts on every
column of this table be totaled. I apologize for not clearly describing
earlier how the table looks. The Forecast table contains about 10 columns and
1 to several line entries. Each line entry is sorted (ascending) by Forecast
Date which is manually entered (the latest row is the latest entry). In the
form this table is linked to the other 2 tables (Main & Cable tables) by its
hidden Order No. All three tables are linked by the product's unique Order
No.

I'm new to this and I appreciate the coding but where do I put this in the
Design? Should I type in "Select bottom" or something instead? Pease clarify.