By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,466 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Select top on 2 tables based on date?

P: n/a
ET
Please help with the query:

There are two tables, A and B.
A table has information about cell phones, like cell number, sim
number, model, manufacturer etc...
B table has user related information, because one cell phone can be
used by more then one user, one after another, that information is
UserID, Name, Location, Date Received, Date Returned, etc.

Tables A and B are related one to many with column ID which is present
in both tables.
ID is one in A (cell phone related information) and is many in B table,
because again, one cell phone can be used my more then one users.

Here is the dummy data from both tables:
A table:
ID-CellPhoneNumber-Model
---------------------------
1 3214556322245556 Casio
2 5439595843909393 Nokia
3 2103940392020430 Ericson

B table (user history table):
HistoryID-ID-UserID-NameFirstLast-DateReceived-DateReturned
------------------------------------------------------------
1.........1. 654667 James Short 01/01/2005 01/15/2005
2.........1. 439605 Felmar Dunn 01/15/2005 03/15/2005
3.........1. 854323 Dejan Bogic 03/20/2005
4.........2. 543245 Danial Crek 02/10/2005 02/20/2005
5.........2. 565694 Marek Leach 03/10/2005
6.........3. 870932 Brian Korte 01/15/2005 03/10/2005
7.........3. 327655 Vincent Ken 03/20/2005

Column HistoryID in table B is auto number, records
are not sorted like shown but rather as new record for any phone
gets entered, which means ID in table B is not in ascending order
as shown in the above example.

What is needed is report based on query, and query will extract
only the information about the current users (with phone
information from table A ) which means top records based on
Date Received... I tried to search by rows Date Returned
where they are empty (which means phone has not been returned)
but for whatever reason it does not work properly.

Thank you!

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
ET wrote:
Please help with the query:

There are two tables, A and B.
A table has information about cell phones, like cell number, sim
number, model, manufacturer etc...
B table has user related information, because one cell phone can be
used by more then one user, one after another, that information is
UserID, Name, Location, Date Received, Date Returned, etc.

Tables A and B are related one to many with column ID which is present
in both tables.
ID is one in A (cell phone related information) and is many in B table,
because again, one cell phone can be used my more then one users.

Here is the dummy data from both tables:
A table:
ID-CellPhoneNumber-Model
---------------------------
1 3214556322245556 Casio
2 5439595843909393 Nokia
3 2103940392020430 Ericson

B table (user history table):
HistoryID-ID-UserID-NameFirstLast-DateReceived-DateReturned
------------------------------------------------------------
1.........1. 654667 James Short 01/01/2005 01/15/2005
2.........1. 439605 Felmar Dunn 01/15/2005 03/15/2005
3.........1. 854323 Dejan Bogic 03/20/2005
4.........2. 543245 Danial Crek 02/10/2005 02/20/2005
5.........2. 565694 Marek Leach 03/10/2005
6.........3. 870932 Brian Korte 01/15/2005 03/10/2005
7.........3. 327655 Vincent Ken 03/20/2005

Column HistoryID in table B is auto number, records
are not sorted like shown but rather as new record for any phone
gets entered, which means ID in table B is not in ascending order
as shown in the above example.

What is needed is report based on query, and query will extract
only the information about the current users (with phone
information from table A ) which means top records based on
Date Received... I tried to search by rows Date Returned
where they are empty (which means phone has not been returned)
but for whatever reason it does not work properly.


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It'd be helpful to see the SQL of your query.

I don't understand what you mean by "top records based on Date
Received." If you mean you want to see the latest date at the top of a
list of phones where the Date Returned is empty (indicating the phone
hasn't been returned) then something like this:

SELECT A.CellPhoneNumber, B.NameFirstLast, B.DateReceived
FROM A INNER JOIN B ON A.ID = B.ID
WHERE B.DateReturned IS NULL
ORDER BY B.DateReceived

If you use this query in a report omit the ORDER BY clause and set the
report's Sort/Group by for the DateReceived to whichever order you want
(Ascending/Descending - oldest records first would be Descending).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQzCG44echKqOuFEgEQJTVACeLdTX5m5mNgq5N+y+C7MxGr 6uq/QAn39Y
2vyoZWE7r2ntwVBX3f5w62kB
=JMsk
-----END PGP SIGNATURE-----
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.