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

SQL query to select latest crime for each criminal

P: 7
Hi,

I have created an MS Access database containing the following tables:

Crime
CrimeCommitted
Criminal
CriminalOrganization
CriminalType
HideoutType

The Criminal table is the parent table that contains a one-to-many relationship with the CrimeCommitted Table (a criminal can commit many crimes). I've sorted the CrimeCommitted table in descending Date (that the crime was committed) so that the latest crime is at the top of the table.

What i need to do is to write a query to select only the lastest crime committed by a criminal. Then i will be retrieving this database from a Crystal Report to view the lastest crime for each criminal. I've tried using the SELECT DISTINCT statement

SELECT DISTINCT CriminalID FROM CrimeCommitted

and it returns each CriminalID once. But how can i write a query to retrieve only the lastest crime for each criminal?
Apr 9 '07 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,675
Hi,

I have created an MS Access database containing the following tables:

Crime
CrimeCommitted
Criminal
CriminalOrganization
CriminalType
HideoutType

The Criminal table is the parent table that contains a one-to-many relationship with the CrimeCommitted Table (a criminal can commit many crimes). I've sorted the CrimeCommitted table in descending Date (that the crime was committed) so that the latest crime is at the top of the table.

What i need to do is to write a query to select only the lastest crime committed by a criminal. Then i will be retrieving this database from a Crystal Report to view the lastest crime for each criminal. I've tried using the SELECT DISTINCT statement

SELECT DISTINCT CriminalID FROM CrimeCommitted

and it returns each CriminalID once. But how can i write a query to retrieve only the lastest crime for each criminal?
Expand|Select|Wrap|Line Numbers
  1. SELECT Criminal.CriminalID, Criminal.Name, Max(CrimeCommitted.Date) AS Last_Crime_Committed
  2. FROM Criminal INNER JOIN CrimeCommitted ON Criminal.CriminalID = CrimeCommitted.CriminalID
  3. GROUP BY Criminal.CriminalID, Criminal.Name;
Apr 9 '07 #2

P: 7
Expand|Select|Wrap|Line Numbers
  1. SELECT Criminal.CriminalID, Criminal.Name, Max(CrimeCommitted.Date) AS Last_Crime_Committed
  2. FROM Criminal INNER JOIN CrimeCommitted ON Criminal.CriminalID = CrimeCommitted.CriminalID
  3. GROUP BY Criminal.CriminalID, Criminal.Name;

Thanks for your reply ADezii!
Apr 9 '07 #3

ADezii
Expert 5K+
P: 8,675
Thanks for your reply ADezii!
Glad I could help you, Penny111.
Apr 9 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.