473,287 Members | 1,629 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Access 97 query last value

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.

Feb 10 '07 #1
2 4097
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.

<da*********@telenet.bewrote in message
news:11**********************@q2g2000cwa.googlegro ups.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.
Feb 10 '07 #2
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.>

tblMediaMovement
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
<da*********@telenet.bewrote in message
news:11**********************@q2g2000cwa.googlegro ups.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.

Feb 10 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
6
by: Jarrod | last post by:
I have multiple reports in one database, on one form. I need to know what reports were run when, and by Network User ID. How do I do that?
1
by: Thats Me | last post by:
TMALSS: Task With maintenance of Access Database I did not develop, don't ask about non-commented code problems or where data dictionary is (LOL). Have table with Inventory data for all...
5
by: m_t_hill | last post by:
Running MS Access 2000 MS Windows XP Pro This has caused me a lot of hair loss in the last few days so would appreciate any help. I am running code to append/update a local access database...
4
by: ShastriX | last post by:
Getting a weird error while trying out a query from Access 2003 on a SQL Server 2005 table. Want to compute the amount of leave taken by an emp during the year. Since an emp might be off for...
3
by: stuart.medlin | last post by:
I have an Access 97 application that has a basic form (Transcript) and subform. The subform has a query as a recordsource that returns records from a table: SELECT DISTINCTROW Transcript.NCID,...
3
by: fperri | last post by:
Hello, I have a calculated field in my query that uses a function to come up with the value. The function has various fields from the table used in the query passed into it as parameters. For some...
1
MMcCarthy
by: MMcCarthy | last post by:
Access has a number of built-in functions which can be generally used in queries or VBA code. Some of the more common ones are: Note: anything in square brackets is optional Date Functions ...
13
by: Mike1961 | last post by:
Hi everyone. I have this query ASP and Db Access: strSql = "SELECT Last(ID) AS ID, " &_ "A, " &_ "B, " &_ "C, " &_ "COUNT(D) AS TotD, " &_ "Last(D) AS D,...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.