How to choose the 10 latests entries in a table (from a form)

[anb001]

I'll start by saying that I'm not sure which sub-forum to choose, as my question(s) covers several areas, so I have tried with this one first.

I'm currently making a database, which among other things contains a 'Port of Call list' table. I have a form, where I can add data to this table, meaning, that whenever a vessel has left its port, user can update following: Port name, security level, arrival date and departure date.

What I need is two things (it might be more or less the same):

1. When user click a "Generate" button on a form, the 10 latest port calls from the table, should be shown. I'm not sure what kind of control is the best to should the list on the form. Code is also needed.

2. I would also need to be able to print the list. It should be part of a report. Again which controls/which code to be used is needed.

I hope that someone are able to assist, as I have no clue what to do.

Thanks.

 

[Fear Naught]

The SQL in the query behind the form needs to start

SELECT TOP 10.......

Then you will only get the top ten records based on the sort order of the query

Hope this helps.

 

[Brianwarnock]

The way I would do this is for the button on the form to run a report which would be based on a query using Top values

eg
SELECT TOP 10 tablename.id, tablename.[arrival date], etc
FROM tablename
ORDER BY tablename.[arrival date] DESC;
The way to do this in design grid is described in help, lookup Top in Answer wizard.

Brian

 

[Fear Naught]

Nice to see an expert like Brian providing the same concept as an answer as I did. It will make my weekend. Thanks Brian!! lol

 

[Moniker]

For arrival dates (most recent one and the previous nine, in order):

SELECT TOP 10 * FROM YourTableNameHere ORDER BY ArrivalDate DESC;

For departure dates (most recent one and the previous nine, in order):

SELECT TOP 10 * FROM YourTableNameHere ORDER BY DepartureDate DESC;

 

[anb001]

Thanks. Worked like a charm (more or less). A couple of additional questions:

1. The 10th and the 11th entry in the table have same arrival dates, and because of that they are both included in the 'search' result. How do I make sure that it is only the 10 newest entries which are included?

2. I can e.g. show result in a list. Is there a way I can get result shown like a grid (table with borders) as e.g. seen in Excel?'

Thanks again.

 

[Brianwarnock]

If they both have the same arrival date how do you decide which should be included?

Brian

 

[Moniker]

In the arrival/departure date field, use Now() instead of Date(). Now() will include a timestamp. Unless one arrives and another leaves at the exact same second, you won't have the issue of arrivals and departures matching. They can be the same date, but it will pick the more recent time.

The "grid" is called Datasheet view in Access. Usually this is a subform in a form on Access, but not always.

 

[anb001]

The way it works is that I enter the data for the table in another form. Here I, among things, enter the arrival date and the departure date. The time is not used.

Sometimes port calls are very close to each other, meaning you could arrive port number 1 at 0200 hours in the morning, and leave again at 1400 hours in the afternoon, and after only a few hours of sailing, you could be arriving at port number 2 at, maybe, 2000 hours in the evening, and then leave some time the next day. Would it be possible to modify the SQL, so only 10 is included, and if e.g. number 10 and 11 have the same arrival day, then it should include the one with the most recent departure day (you won't find two port calls - arrival & departure - on the same day; only two arrivals and one departure or one arrival and two departures).

I'll try out the datasheet function.

 

[anb001]

I found a solution myself. I just make it sort after both arrival date and then departure date. Then it works as I want.

 

[Brianwarnock]

The best solutions are usually the one you find yourself, in addition it shows those of us trying to help that you are not just sitting there waiting to have your hand held.

well done , keep experimenting its the way to learn.

brian