Query only showing last 10 entries

[Guest]

Thanks for any help in advance for this.

I've got 2 tables 'tbl Modified' and 'tbl Form Name' The modified table is
linked to several forms and date stamps with username whenever anyone opens a
form from the database. So the tbl Modified looks like this:
Username Form Name DateStamp
uksmiths FRMSTART 10/08/2006 10.18am
ukjones FRMFOOD 12/08/2006 12.13pm
ukjones FRMSTART 12/08/2006 12.20pm
etc

The second tbl Form Name simply gives each form a more friend name:
Form Name Description
FRMSTART First entry into the scorecard
FRMFOOD Entry of Food Activity
etc
A relationship exits between tbl Modified. Form Name and tbl Form Name.Form
name

I've created a query (from which to base a report for management) which
pulls the info together:
Username Description DateStamp
uksmiths First Entry Into Scorecard 10/08/2006 10.18am
uksmiths First Entry Into Scorecard 15/08/2006 2.23pm
etc
However, for each of the 'Usernames' and 'Descriptions' I only want to show
the last 10 entries. So for uksmiths, it would show the last 10 entries for
'First Entry into Scorecard' and the last 10 'Entry of Food Activity'. etc

I hope this makes sense. Have probably got where I am by fluke (and this
forum!!) so please take it slow with me!!
Thanks

 

[BruceM]

The short answer is that you can limit your query to the top 10 values.
Open the query in design view, click View > Properties, and make your
selection from the Top Values row on the property sheet (or enter your own
choice). The longer answer is that there may be something amiss with your
database's structure. Relationships are between tables, but that there is a
relationship between tables could mean a variety of things. The statement
that "the modified table is linked to several forms and date stamps with
username whenever anyone opens a form from the database" is unclear. It
would help if you were to describe just what real-world situation you are
attempting to address.

 

[BruceM]

I should have mentioned that in order to use the top 10 values you will need
to sort a field in query design view. In you case I expect you would sort
DateStamp in Ascending order.

 

[Guest]

Hey Bruce

Thanks for your speedy response. To provide a little more information to you:
With the help of some expertise on this forum, I have set up the tbl
Modified so that everytime anyone opens any of my 4 forms, it creates a new
record in this table and adds to the fields 'Username' 'Form Name' and 'Date
Stamp'. So, if a user opens the form 'FRM Q4 ACTIVITY' a new record would be
created in the tbl Modified which adds the name of user under 'Username', the
form they opened under 'Form' and the date and time they opened it under
'DateStamp'.

To give you some background as to the reason for this, I work for a company
with a large number of reps. They are supposed to fill in these Access Forms
at least twice a week which I then report on. However, I am constantly being
challenged that the reports are wrong, the figures are low etc. I believe
this to be because the reps are not doing their jobs and filling it in
regularly. This has been proven by my 'tbl modified' which shows me nobody
has entered any data this week. I would like to provide managers with a
report which they can view as and when which shows activity on these forms.

My understanding is that if I change the properties to limit Top Value to
10, this would mean the query would only show 10 values. I want under each
user name and each form to show the top 10 values. So, for example:
Username Description DateStamp
uksmiths Food entry 10/08/2006 10.18am
|
|
|
V
Sue would then have maybe 20-50 entries for Description 'Food entry'. I
want to limit this to 10

ukjonesp Bev Entry 12/08/2006 3.52pm
|
|
V
Peter would then have loads of entries under 'Bev Entry' but I need to limit
this to 10.

My query brings back all user - Is this a problem?

Sorry - hope this is coherrent?

 

[BruceM]

I doubt it's a problem, but I don't know how to do it. Your reply posting
contains description and details that were clearly stated in your original
posting. Had they been there I would have left the question for somebody
else to answer. Sorry I couldn't help.

 

[BruceM]

I can tell you that I did a quick Google groups search for "microsoft public
access" "Top 10" "per category" (note that there are three separate strings
enclosed in quoates) and came up with the following (watch for line
wrapping):
http://groups.google.com/group/micr...op+10"+"per+category"&rnum=1#482cd03ced287457

There was also this one, which includes randomizing, but I expect that part
could be skipped:
http://groups.google.com/group/micr...op+10"+"per+category"&rnum=1#482cd03ced287457

Also, this search string in Google groups returned 43 entries, some of which
may be helpful:
"microsoft public access" Top 10 per category

 

[BruceM]

I meant to say "that were not clearly stated...". I said the exact
opposite.