Locating specific Information

[Mal.com]

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.

 

[John W. Vinson]

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.

 

[Mal.com]

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!

 

[Steve]

Create an unmatched query using the wizard where you look for AssetID in the
first table that is not in the second table. You need to set criteria in the
Weekending field so you can specify the week you want to check.

Steve

 

[Mal.com]

John & Steve
Thankyou for your reply but I am having no luck, I created an unmatched
query using the wizard as sugested but I am geting no recordes at all. I
just can't identify the records that have not been recorded for that week,
its ether I get all that was recorded or no recordes at all. If you any
other help, suggestions or advise would be
greatfuly appreciated. Thanks.

 

[Fred]

The key thing will be to clarify to yourself (or us) EXACTLY what you mean by
"Query the assets and locations that have
not been entered for the week."

Do you mean:

1. Check for assets that have been entered, but where no location has been
entered?
2. Check for assets that exist but which have not be en entered? (
- impossible until they add the mind-reading feature to Access)
3. Check for location updates only when they have actually occurred, but
have not been entered? Same answer as #2
4. Check for assets where a new location has not been entered for at least
a week?
5. Check for assets where the location has not been entered or RE-entered
for at least a week? This presumes that people need to keep re-entering
locations for assets that didn't move.
6. Check for locations which have no assets assigned to them?
7. Etc.


Chances are, once you clarify that, the solution might be obvious.

 

[Michael Gramelspacher]

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!

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);

 

[Mal.com]

I mean number 5.
Check for assets where the location has not been entered or RE-entered
for at least a week? This presumes that people need to keep re-entering
locations for assets that didn't move.
The people will be re-entering locations for assets even if they didn't move.

 

[Fred]

You can do the heavy-lifting part of your goal via. structure or via code.
(shorten my explanatory field names)

The structure method would be to have an "AssetLocationUpdates" table which
has a record for (each instance of) updating the location of an asset. It
would have "AssetIDNumber" and "LocationIDNumber" FK's linked to their
namesake PK fields in the Asset and Location tables respectively. Also put a
Date/Time field in the AssetLocationUpdates table with the default value set
for the current date [ "=date()" or = "Now()" ] Then link them in a find
unmatched query with a condition on the date field of a date newer than 1
week old. The advantage and disadvantage of this method is that you are
creating and saving more data,, i.e. a record for each recording / updating
of the location of an asset.

Regarding the code method, I can barely do basic code must less tell someone
how to do it so I'll just give a 30,000' view idea and leave this for someone
who knows coding better. But here you'd just have two main tables (Assets and
Locations) linked to each other, and and put a "DateofLastLocationUpdate"
field into the Assets table. The location would just be a FK
LocationIDNumber field in the Assets table. Arrange it so that updates are
done only through a form. Code the form so that when that when a new date is
put in or a "ReconfirmCurrentLocation" button is hit, the current date gets
loaded into that field. Then a simple query for records that don't have a
date newer than 1 week old in that field will show you the stale ones.

Hope that helps!



Since I can barely do code less explain it, I'll only

 

[Steve]

Malcom,

An unmatched query is what you need! Take a look at the desigb of Table2. If
you have assets A, B and C and A and B are entered for weekending 1/10/09,
you want a query to return Asset C since it has not been entered for
weekending 1/10/09. AssetID for Asset C will be in Table 1 but AssetID for
Asset C will not be in Table 2 for weekending 1/10/09. So you need an
unmatched query that looks for AssetIDs in Tbale 1 that are not in Table 2
for weekending 1/10/09. Use the wizard to construct this query paying close
attention to the instructions on the screen.

Steve

John & Steve
Thankyou for your reply but I am having no luck, I created an unmatched
query using the wizard as sugested but I am geting no recordes at all. I
just can't identify the records that have not been recorded for that week,
its ether I get all that was recorded or no recordes at all. If you any
other help, suggestions or advise would be
greatfuly appreciated. Thanks.

 

[Fred]

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.

 

[Mal.com]

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