Locating specific Information

I just noticed that you already have the "AssetLocationUpdates" table except
that you just type in the location text rather then drawing from a list.

Thank you to all those who replied with advice. I really appreciate all the
help each and every one of you have given me. It worked!!! Yah!!!! At last,
I can finally get the information out that I have been after for ages. So
once again a big Thanks!, Mal.com




"Michael Gramelspacher" wrote:

On Mon, 5 Jan 2009 19:06:00 -0800, Mal.com wrote:

I am using Access 2003

The tables are structured as follows:

The first table is called Assets and has four fields,
1, Asset ID (Primary Key)
2, Asset Description (Text)
3, Make (Text)
4, Model (Text)
and a total of 519 ID records

The second table is called Weekly Register and has four fields
1, Auto Primary Key
2, Asset ID (Text)
3, Location (Text)
4, Weekending (Date/Time)

I enter into the second table each week the assets and their location and
that tells me where all the assets are that have been recorded but i would
like to create a Query on the assets that have not been recorded for that week

Thanks!

"John W. Vinson" wrote:

On Mon, 5 Jan 2009 16:35:00 -0800, Mal.com
wrote:

I am setting up an Asset Tracking database that will need to track the assets
weekly. The assets move around to different locations during the week. I am
having trouble with being able to Querie the assets and locations that have
not been entered for the week. Any help, suggestions or advise would be
greatfuly appreciated. Thanks.

We'll be glad to help if you give us some information about how your tables
are structured. I'm guessing that a NOT EXISTS clause can be used to find
things "that have not been entered" but without knowing anything about your
tables, it's hard to say.
--

John W. Vinson [MVP]


Probably like this, but the actual date here would be a parameter.

You should have a UNIQUE index on AssetID and WeekEnding in you WeeklyRegister table.

SELECT Assets.AssetID,
Assets.AssetDescription,
Assets.Make,
Assets.Model
FROM Assets
WHERE NOT EXISTS (SELECT *
FROM WeeklyRegister
WHERE Weekending =# 1 / 2 / 2009 #
AND WeeklyRegister.AssetID = Assets.AssetID);