Access 97 query last value

[dauwe.peter@telenet.be]

A table : Nameperson, Book nr, Bookdatein, Bookdateout, CD nr,
cddatein, cddateout, dvd nr, dvddatein, dvddateout.


I would like a query where a see the personsname en de last book with
the datein and out , the last cd with datein and out , and the same
for the dvd. Because the last time the person came he toke only a
book but dit not return his dvd (for example). But with one click i
would like to see the last of every item with the right dates.

Tx anyone, anywhere, anytime.

[Allen Browne]

Re: Access 97 query last value

See:
Getting a related field from a GroupBy (total) query
at:

http://www.mvps.org/access/queries/qry0020.htm

for 4 ways to approach this.

The example is very similiar.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<dauwe.peter@te lenet.bewrote in message
news:1171098139 .880138.137650@ q2g2000cwa.goog legroups.com...

>A table : Nameperson, Book nr, Bookdatein, Bookdateout, CD nr,
cddatein, cddateout, dvd nr, dvddatein, dvddateout.
>
>
I would like a query where a see the personsname en de last book with
the datein and out , the last cd with datein and out , and the same
for the dvd. Because the last time the person came he toke only a
book but dit not return his dvd (for example). But with one click i
would like to see the last of every item with the right dates.
>
Tx anyone, anywhere, anytime.

[tina]

Re: Access 97 query last value

suggest you take another look at your table structure. you've embedded data
in fieldnames (Book, CD, DVD), and have repeating groups (item, date in ,
date out). both are strong indications that your table is not normalized.
(for more information on normalization, see
http://home.att.net/~california.db/tips.html#aTip1.) this single table
should be broken into at least two, and i'd probably have a minimum of
three, as

tblPersons
PersonID (primary key, autonumber)
FirstName
LastName
<other fields that describe a person>

tblMediaTypes
MediaID (primary key, autonumber)
MediaName
<this table will have three records: Book, CD, DVD. if you have other media
as well, each type will be a single record in the table.>

tblMediaMovemen t
MoveID (primary key, autonumber)
PersonID (long integer, foreign key from tblPersons)
MediaID (long integer, fk from tblMediaTypes)
NR (i'm guessing this is a unique identifier for each item in
your...library? store? whatever)
DateIn
DateOut

with the correct table setup, a single Totals query will give you the "last"
item of each type, for each person.

actually, if this is a store or lending library of some kind, i'd probably
use a somewhat more elaborate setup, with the following tables, as

tblPersons
<each person who borrows items is listed as one record, with whatever
information you collect about your borrowers.>

tblMediaTypes
<a simple list of the types of media you loan, same as detailed above.>

tblItems
<a detailed list of each item that is loaned, including its' identifying
"nr", what media type it is, and all other details you want to track, such
as title, date purchased, cost.>

tblLoans
<each record is one instance of a loan of a specific item to a specific
person. to identify the person, use only the PersonID from tblPersons; to
identify the item, use only the ItemID from tblItems. include other data you
want to track, such as date out, due date, date in.>

hth


<dauwe.peter@te lenet.bewrote in message
news:1171098139 .880138.137650@ q2g2000cwa.goog legroups.com...

A table : Nameperson, Book nr, Bookdatein, Bookdateout, CD nr,
cddatein, cddateout, dvd nr, dvddatein, dvddateout.
>
>
I would like a query where a see the personsname en de last book with
the datein and out , the last cd with datein and out , and the same
for the dvd. Because the last time the person came he toke only a
book but dit not return his dvd (for example). But with one click i
would like to see the last of every item with the right dates.
>
Tx anyone, anywhere, anytime.
>