Problem with duplicate entries in reports

[vlad]

Hi All,

I have a database with three tables named, Assets, Tracking and
Items. The Assets table contains a record for each asset. The
Items table contains a record for items belonging to each asset.
The Tracking table contains tracking info for an asset (when it
was received, when it got issued, where it is currently located
etc).

What I'd like to do is to generate a report based on which asset
the user is asking for (or a range of assets). This I can do by
providing two select items that's not defined. What I'm having
difficulty is in selecting the latest tracking history info.
That is. If an asset was received on 01/01/05, got issued to
someone on 01/02/05 and then got returned on 01/03/05. I want the
report to just say DateReceived of 01/03/05 with the DateIssued
column blank and the location containing my office. Also, because
this asset has three entries in the tracking table, the report
seems to list each item in triplicate. Also note that each record
in the tracking table will either have the dateissued filled in
or the datereceived filled in but not both.

Below is the query I'm using currently:

SELECT Asset.Description AS Asset_Description, Tracking.DateReceived, Asset.AssetID, Tracking.DateIssued, Tracking.Location, Items.Description AS Items_Description, StartAssetIDNumber, EndAssetIDNumber
FROM (Asset INNER JOIN Items ON Asset.AssetID=Items.AssetID) INNER JOIN Tracking ON Asset.AssetID=Tracking.AssetID
WHERE (Asset.AssetID between StartAssetIDNumber And EndAssetIDNumber);

Below is what I'm getting (a laptop that was received on 01/01/05, sent to John
on the 01/02/05 and received back on the 01/03/05).

AssetID         Description         DateReceived      DateIssued     Location
-------------------------------------------------------------------------------------
        ItemDescription
=====================================================================================
 76             HP Laptop                             01/02/05       John's Office
        Laptop Mouse
        Laptop Mouse
        Laptop Mouse
        Power Supply
        Power Supply
        Power Supply
        MS Windows XP CD
        MS Windows XP CD
        MS Windows XP CD

What I want is

AssetID         Description         DateReceived      DateIssued     Location
-------------------------------------------------------------------------------------
        ItemDescription
=====================================================================================
 76             HP Laptop           01/03/05                         My Office
        Laptop Mouse
        Power Supply
        MS Windows XP CD

All help much appreciated and thanks in advance.

 

[AN60]

vlad
Have you checked your tbl relationships? When I have had similar results the relationships between tables has been the problem.

 

[vlad]

Hi All,

Any one else with a solution. The main problem is that I can't come up with a query
that'll return the correct row of the Tracking table (which lists an asset multiple times,
that is, once when we get it, again when we ship it to someone and again when we
receive it back) and each entry for an asset either has the Date Issued filled in or
the Date Received filled in. What I want is a query that'll return the row with the latest
date of Date Received and Date Issued.

TIA.