If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Locating specific Information
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. |
#2
|
|||
|
|||
Locating specific Information
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] |
#3
|
|||
|
|||
Locating specific Information
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] |
#4
|
|||
|
|||
Locating specific Information
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" wrote in message ... 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] |
#5
|
|||
|
|||
Locating specific Information
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. "Steve" wrote: 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" wrote in message ... 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] |
#6
|
|||
|
|||
Locating specific Information
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. |
#7
|
|||
|
|||
Locating specific Information
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); |
#8
|
|||
|
|||
Locating specific Information
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" wrote: 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. |
#9
|
|||
|
|||
Locating specific Information
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 "Mal.com" wrote: 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" wrote: 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. |
#10
|
|||
|
|||
Locating specific Information
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 "Mal.com" wrote in message ... 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. "Steve" wrote: 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" wrote in message ... 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] |
|
Thread Tools | |
Display Modes | |
|
|