Related Topics

Table Design For Software Asset Management

Table Design
I am creating an assets database. Each asset has approx 100 fields, all 1:1. Should I make one table, or try to break it down into several tables linked by Asset ID? There are no 1:M relationships, so there is no real reason to make more than 1 table. Is the massive 100 field table poor design


Table Lookup or Query?
I have a main Asset Table recording IT Equipment. Two of the fields in the Asset Table are Equipment_Type and Equipment_Type_Specific I.e. PC > Desktop

There is an Equipment_Type table with a child table Equipment_Type_Specific .

The Asset Table needs to look up to these tables for the Equipment Type values. The Equipment_Type_Specific field will be a cascading combo so that the user can only select Laptop,Desktop,Tower etc. if it is a "PC".

Would it be best to have these two Fields in the Asset Table looking at a query so that the names are saved in the Asset table rather than the Key reference of the various Equipment Types?


Help asset tracking database
I'm developing a simple asset tracking database. The assets I want to include are Laptops, workstations, and printers. I want to be able to know the exact location of the asset, be able to change the status of an asset and any other important information. I think it's also important to keep track of the asset location changes.
It is also important to be able to make queries by department, by type of asset.

Here is a screenshot of the tables I tought I can create. I have knowledge in Database development but this is my first real project. I don't know what other table I should consider !


Delete query using criteria from another table
I have two tables (Master Asset List & Asset Versions Lists) with one common field (Asset_ID). I am trying to create a delete query that will delete a record in the Master Asset List of there is no corresponding record in the Asset Versions List.

I used the Query Wizard to create a 'Find Unmatched' query. This correctly returns the results I want to delete. I opened this query in design view, and changed it to a delete query by clicking Delete in Query Type on the Design Ribbon. I thened add the table I wanted to delete the record from (Master Asset List) with the field as "*" and changed the Delete setting to 'From' (see attached image).

When I run this query, I am warned that "You are about to run a delete query that will modify data in your table" and I am propted to confirm if I wish to continue (Yes/No/Help). When I confirm, I get the error message "Specify the table containing the records you want to delete".

Any suggestions on what I should do to fix this


Business-Based Functions
An asset is an object of value. It could be a person, a car, a piece of jewelry, a refrigerator. Anything that has a value is an asset. In the accounting world, an asset is a piece of/or property whose life span can be projected, estimated, or evaluated. As days, months or years go by, the value of such an asset degrade.


Form Auto population
I guys,
Im completly new to Access and have been asked to build a database which includes, three main tables customers, buildings and asset register for each building. As you can see from Customer Form, I created a button called Asset Register, which brings up Asset Form based on Macro <OpenForm = Asset, Datasheet, , , , Normal; ApplyFilter = [Asset]![Building ID]=[Forms]![Customer]![Building].[Form]![txt_BuildingID].[Value], >. (Not too sure if its right way, but it works).
The problem is when I open up Asset Register Form - I can input all the information and it will be saved in Asset Register Table, but I have to input Building ID manuly for the same building, and I have about 300 items ;(. How do I automate this proccess, so building ID populates automaitcly for all items when I enter information (and saves it in Asset Register Table)?
Please inform me if I made very stupid mistakes in my database, any critisism will be appreciated, I am trying to learn Access


Form Auto population
I guys,
Im completly new to Access and have been asked to build a database which includes, three main tables customers, buildings and asset register for each building. As you can see from Customer Form, I created a button called Asset Register, which brings up Asset Form based on Macro <OpenForm = Asset, Datasheet, , , , Normal; ApplyFilter = [Asset]![Building ID]=[Forms]![Customer]![Building].[Form]![txt_BuildingID].[Value], >. (Not too sure if its right way, but it works).
The problem is when I open up Asset Register Form - I can input all the information and it will be saved in Asset Register Table, but I have to input Building ID manuly for the same building, and I have about 300 items ;(. How do I automate this proccess, so building ID populates automaitcly for all items when I enter information (and saves it in Asset Register Table)?
Please inform me if I made very stupid mistakes in my database, any critisism will be appreciated, I am trying to learn Access


why isn't this insert into working?
trying to get matching data from my query to insert into an existing table, nothing happens it just sits there, any ideas?

INSERT INTO Transactions ( [Asset Number], [Location Name] )
SELECT Books.[Asset Number], Books.[Location Name]
FROM Books INNER JOIN Tabletest ON Books.[Asset Number] = Tabletest.[Asset Number]
WHERE (((Books.[Asset Number]) Is Not Null


Table Design for Assets Inventory with Multiple Asset Type/Asset Attribute Combos
I need to set up a database for tracking fishing supplies inventory. The problem is that for each type of asset (rods, reels, jigs, hooks, etc.) there are different combinations ofattributes that need to be entered.

Eventually I would like to be able to create a form for adding new assets to the database, but am having trouble structuring my tables in a way that allows me to create the form that I want.

The form that I envision would pop-up and give me the option to type the new assets name in a text box, and select the asset type from a combo box. After these are filled out, there will bea "next" button that moves me to a new pop-up form.

This new pop-up form will allow me to enter specific data about the asset I have just entered. The dilemma is that when I select "Rods" for asset type, I should be able to enter specificdata that would not apply to a selection for "Reels." I am having a lot of trouble designing my tables to give me this kind of freedom in my forms.


Opening a new blank form rather than first record in the table
I have created an asset database but have a problem with one of the forms.
I have a form with the button 'New Asset' and when I click it opens a New Asset form so I can enter new asset details. The problem is that it opens the first record in the table rather than a brand new (empty) record which I require. Can anyone help.

The code I have on the 'New Asset button' is below

Private Sub Openad_Click()
On Error GoTo Openad_Click_Err
DoCmd.OpenForm "Asset Details", acNormal, acFormAdd
On Error Resume Next
DoCmd.Requery ""
DoCmd.GoToRecord , , acNewRec
Openad_Click_Exit:
Exit Sub
Openad_Click_Err:
MsgBox Error$
Resume Openad_Click_Exit


Last Record from a table
I've created a database which currently has two tables. One called asset and the other movement. A relationship exists between these tables so that I can find out which movements have been submitted to an asset. (Movement ID is related to the Asset ID in the asset table.)

My question comes when I try to find the last movement of an asset.

I have created a query which has a movement id field and a location field. I have set the query totals to group by the movement id field and the location to last.

This query seems to pull out random records, right now it will only pull the first record or 2nd record if I set the query to first.


Last Record from a table
im quite the access noob and I have what is hopefully a simple question to answer for the veterans .

I've created a database which currently has two tables. One called asset and the other movement. A relationship exists between these tables so that I can find out which movements have been submitted to an asset. (Movement ID is related to the Asset ID in the asset table.)

My question comes when I try to find the last movement of an asset.

I have created a query which has a movement id field and a location field. I have set the query totals to group by the movement id field and the location to last.

This query seems to pull out random records, right now it will only pull the first record or 2nd record if I set the query to first.


Bar-Coding
I need to set up an Inventory Tracking System in an Access Database using bar-coding technology. I need to have each asset tracked via a bar-code. This will require a bar-code sticker to be generated and placed on each asset. We will use a scanner to check bar-coded items in and out.

Has anyone ever done this? Which 3rd party bar-coding software is the best and the most user-friendly that works with Access


Quick Filter Field - MS Access "Asset Tracking Database"
I am working from the MS Access "Asset Tracking" database template. Using the "Asset List" form (I renamed it Equipment List). I have populated the table with my data. I have also switched out, renamed and added some new fields to the equipment (formerly asset) table.

The filter quick search macro no longer works. It says:

"The ApplyFilter action requires either the Filter Name or Where Condition Argument is set"


TempVars Access 2003 equivalent for the Lending Library template
I am trying to recreate the Access 2007 MS Lending Library template in Access 2003.

The 2007 version uses the expression "TempVars" in the Asset field (Default Value =[TempVars]![ItemID]) of the Check out form to temporarily store the value of the asset I believe so that the asset is not available to check out in the Asset List form once the Check Out form data is completed.

I have read that TempVars is a new expression in Access 2007 and was wondering what the equivalent expression would be in Access 2003? I'm thinking I should declare the asset number as a variable in VB so that it can update the Asset List form to show the asset as not available


assigning a transaction to a record
so I'm trying to work out the best way to tag a record if something has changed on it.
The main table has asset number and serial number, which both need to be checked cos one of the other can duplicate but not both at the same time. If they move locations then I need to keep a record that they've done so.

I've got the main records in the table now, and I'm looking at importing the redistribution spreadsheet. I was thinking something like this:

if Master.asset number and Master.serial no = import.asset and serial (Probably use an SQL join) then
for i=1
master.location = import.location i+1
next I
else
(copy the location and asset number to a transactions table and copy the new location name to current record)

would that work or is there a better way to do it?


Create inventory management for tyre shop
I want to create a retail management software for my tyre shop I request your help to do it. first thing I dont have much knowledge in access
I created product list table , I am trying to create a customer database table I want to create a field in which I have to enter customer (who owns more than 1 vechile) vechicle numbers and their model in a list view.pls hlp me to do it


Copy One Field Data To Another Table Field
I'm super new at this and this question probably seems extremely simple to most but I've tried the Append Query and it wants to append all records.
I have and Assets Table which has an AssetID field and I want to Copy the current record field data to my TasksHistory Table and put it into the current Asset ID field. Seems simple but it's stumping me right now. Example: Asset Table: AssetID=1 and I want that AssetID=1 to be placed in the TaskHistory Table: Asset ID field. I hope this example is understandable. Help is really needed


compare two fields in two different tables
I need a database that compares two tables and if the data entered in the form is on one of the tables for it to just say yes or no.

I have two tables

table field1 field2 field3
Table1 (with no data) serial# asset# yesno
Table2 (with data) serial# asset#

Now the form has all three fields from table1 serial#,asset#,yesno
what I want is that when the user enters the serial# or the asset# the field checks table2 to see if it exist . if so it places a yes or a no if it does not exit , into the yesno field.


Introduction to Microsoft Access 2003
Our goal is to assist you to learn the software, understand some basic concepts and show you some tips and techniques so you can develop your database management skills over time. The five workshops are: Introduction to Access; Access Queries; Access Reports; Access Database Design; Access Form Design. (pdf file, install to Acrobat Reader to read this tutorial).