Query to exclude returned items? (1 Viewer)

[compiler]

I am working on a rental store database. I have MANAGE table like the following:

ItemNo Direction ClientName DateOfProcess

45------ OUT----John-------- 27/12/2010
34 ------IN------Green------- 5/1/2011
9 -------OUT----Howard----- 6/1/2011
45------ IN------John-------- 8/2/2011
45 ------OUT----Jim--------- 10/2/2011

and made a query to search for items that aren't returned to store for more than 1 month.
Criteria for Direction: "OUT"
Criteria for DateofProcess: Date()-[DateofProcess] > 30
'Assuming Date() returns 26/2/2011'

It's obvious from above table that item 9 is the target, but the query returns
45 Out John 27/12/2010
9 Out Howard 6/1/2011

without recognizing that item 45 has been rented again.

How can I make the query exclude this case? I can restructure the database if a

solution won't fit my structure

 

[Brianwarnock]

I've never designed a rental DB but I would have had

Item Client Dateout Datein then what you are asking for is easy.

With split recording you have to match the In amd Out records.

Brian

 

[compiler]

thanx , but is there any solution without adding two dates in the record, i wonder if there is a VBA code or something that can do the job

 

[philben]

Hello,

Could you please try this query :

SELECT 
  T1.ItemId, 
  T1.Direction, 
  T1.ClientName, 
  T1.DateOfProcess
FROM 
  MyTable AS T1 INNER JOIN 
  (SELECT ItemId, Max(DateOfProcess) AS MaxDate FROM MyTable GROUP BY ItemId) AS tMax 
   ON (T1.DateOfProcess = tMax.MaxDate) AND (T1.ItemId = tMax.ItemId)
WHERE T1.Direction="Out" AND T1.DateOfProcess<#2/26/2011#-30;

Best Regards,

Philippe

 

[jdraw]

thanx , but is there any solution without adding two dates in the record, i wonder if there is a VBA code or something that can do the job

There may be some models here that will help
http://www.databaseanswers.org/data_models/index.htm

 

[compiler]

nice idea philben. it works perfectly. although the new structure made querying more easy but in some cases rebuilding the database can be a huge pain

Hello,

Could you please try this query :

SELECT 
  T1.ItemId, 
  T1.Direction, 
  T1.ClientName, 
  T1.DateOfProcess
FROM 
  MyTable AS T1 INNER JOIN 
  (SELECT ItemId, Max(DateOfProcess) AS MaxDate FROM MyTable GROUP BY ItemId) AS tMax 
   ON (T1.DateOfProcess = tMax.MaxDate) AND (T1.ItemId = tMax.ItemId)
WHERE T1.Direction="Out" AND T1.DateOfProcess<#2/26/2011#-30;

Best Regards,

Philippe

 

[compiler]

thanx jdraw, it's really a cool source for almost everything