Asset ID Table / Tables

Mar 13, 2007

Hi guys. I have been attempting to add an asset tracking function to my database. I have a table 'tblAssetIdList' which holds all the available asset ID's. Asset ID's can be allocated to either Computers from tblComputers, Software from tblSoftware and finally Hardware from tblHardware.

I initially created additional tables to hold details of the allocations: tblComputerAssetIdAllocation, tblHardwareAssetIdAllocation & tblSoftwareAssetIdAllocation. I then created queries from the above tables and then forms from the queries to allow for data input.

The one flaw (quite obvious really) is that asset ID's should be unique. Although unique Asset ID's are required in the individual tables I am able to allocated the same Asset ID to the different tables. (i.e. A20222 can be allocated to only one computer in tblComputers but can also be allocated to both Hardware & Software once).

I then tried creating a single table tblAssetIdAllocation with fields:

ID
AssetID
ComputerID
SofwareID
HardwareID

These linked to the appropiate tables. However if I allocated an asset ID for computers then the same asset id was then allocated to hardware & software.

I must be close (only taken 2 days to get it wrong!! :) )
Help Appreciated,
Phil.

View Replies


ADVERTISEMENT

Asset ID Table

Oct 18, 2007

I have decided to futher develop / normalize a db I have been working on for well over a year now (keep promising to post the updated version when complete!!).

I currently have a number of tables that may or may not require an Asset ID.

i.e.
tblComputers
tblSoftware
tblHardware

I have seperate tables:
tblComputerAssetID
tblSoftwareAssetID
tblHarwareAssetID

Can any one advise a better approach? If I have only 1 table tblAssetID, then I am unable to enforce referential integrity as values must exist in all related tables.
Thanks,
Phil.

PS: There is more but I will post back with further questions when I resolve the above!!

View 4 Replies View Related

Assigning Asset To Employees

Jul 26, 2005

Hi, I've lost touch of Access few years back & now I'm back again using Access... kinda rusty & yet rushing for a proj... so I'll very much appreciate if someone can help me on this..... :)

I've a database storing inventory details & after adding these details, i'm supposed to have another form where i'll start assigning these assets to different employees..& of cos, i'll need to edit this form over time if the employee left the company or asset is being transferred to another person. However, this asset list is quite comprehensive with thousands of records, so it isn't feasible for me to list all assets at one go & start assigning different asset to different employee... any idea how I shld go abt assigning? shld i minimise the search by invoice key & track no? (each invoice contains a few grp of items & each grp will contain several items, therefore, i've assigned an invoice key for each invoice & track no for each item.)

any help is very much appreciated.... thanks :)

View 1 Replies View Related

Queries :: Generate Amortization Schedule For Each Asset

Jul 19, 2013

I'm working on a database where I enter a series of assets and their costs, and I'm trying to build a query that will generate an amortization schedule for each asset.I'm using an amort methodology that amortizes a certain amount each period based on a calculation. I've got a query that generates the amount that would be amortized based on that calculation.

My problem is in the last period of amortization before the asset is fully amortized. In that last period, the amort methodology may generate an amount to be amortized that would be greater than the total value of the asset. In such cases, I want the amort for that period to be the total value of the asset less the cumulative amort up to this period.

So, for example, I may have an asset that is worth $225. My amort calculation generates an amort amount of $50 per period. For the first four periods, the amort would be $50. But in the final period, only $25 would remain unamortized, and in that instance I want the final amort amount to be $25 instead of $50.

[AssetName], [MonthStartDate] (i.e. the first day of the month for the period of amortization), [BusUnit], [RawMonthAmort]. [RawMonthAmort] has an expression that performs the calculation that determines how much to amortize. I've tested this part and it all works (except for during the final month of amortization, as noted above).

I tried including another layer of conditionals in the [RawMonthAmort] expression that would calculate a cumulative total amort and check that against the total asset value (by bus. unit) and give the difference if the cumulative total amort exceeded the asset value, but I couldn't figure out how to make that work.

View 7 Replies View Related

Auto Populate Asset Number Without Having To Enter It?

Apr 14, 2014

my asset numbers look like this MIT000001DT, MIT000002DT, etc is there a way to auto populate my asset number without having o enter it. I was thinking it would look like this $$$000001$$ + 1= (new asset number)?

View 10 Replies View Related

Modules & VBA :: Asset Database - Special Characters In DLOOKUP Expression

Jul 29, 2013

I have an asset database I am designing to manage our computer inventory and assets.

I am trying to get a DLookup to work with one of my forms that will auto-populate some of the fields depending on what is entered in to the ProductID field. For instance, Make, Model, Asset type...

My problem is that the string that returns contains special characters, specifically "#" and gives me the error message -

Run-time error '3075':
Syntax error in date in query expression 'productID=EN371UA#ABA'.

My expression is definitely working, it just looks like it things it has something to do with date/time which it does not. Unfortunately, most HP equipment contains a # in the Product ID number.

Here is my expression -

Private Sub ProductIDCombo_AfterUpdate()
Make = DLookup("Make", "productlist", "productID=" & [ProductIDCombo])
End Sub

Make is the field I am looking up from the ProductList table. The Product ID is the ID I'm looking up from the ProductList table to find the make. My problem is actually getting it to return the correct value of "HP or Dell or Lenovo". etc.

View 12 Replies View Related

Forms :: Asset Tracking When Items With Unique ID Transferred From One City To Another

Aug 6, 2014

I have a task of managing hardware assets like desktop, monitor, HDD, routers etc. Each item is marked with a unique ID.

These items could be transferred from one city to another city; so need to track that too.

1. There will be two tables

One (say AssetDetail) will have the following fields
RecordID
City
AssetID
Description of the asset (desktop/monitor etc)
ProductSerialNumber of the asset

Second (say AssetTransfer) will have the additional fields
NewCity
AssetTransferredDate

2. I only want to open a single form :

a) where on the basis of either AssetID or the ProductSerialNumber, I could open a particular record and edit it.

i) if the asset is transferred from city A to city B; then the "City" field value in AssetDetail table should change to "city B"(New City field value).

ii) At the same time, each asset transfer record should get appended to the AssetTransfer table even if the table has an existing record with the same AssetID or ProductSerialNumber.

3. Also need an option to make a new entry to the AssetDetail table from the above mentioned single form.

View 4 Replies View Related

Tables :: Consolidating Multiple Tables In One Single Table (Sorted Ascending By Time)

May 30, 2014

What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.

I have tried Union coding but always get Syntax Error etc.

View 8 Replies View Related

Tables :: Calculated Fields From Two Tables / Based On Relationship In Third Table

May 29, 2014

I have two tables of data, each relating to three business branches (branches A, B and C).

Table 1 shows the expenditure of each branch (by fuel, premises and wages).

Table 2 shows a number of units for each branch (mileage, floorspace and sales).

What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.

View 6 Replies View Related

Tables :: Joined Two Tables - Unable To Filter / Lookup On Second Table?

Feb 18, 2013

I have an Access Table with about 28,000 Automobile dealerships across the country shown. I've joined a new/small phone contact table to this to keep up with our phone contacts with the dealership and followup efforts. When I search/filter on the dealership table all is fine. However when I search/filter on the phone contact table with a few test entries, I get nothing at all. I supposed that after joining the tables, I'd be able to do a search on the field named follow up date and find/filter today's date or other dates and locate which dealerships to contact when the correct date arrives. But nothing.

View 14 Replies View Related

Tables :: Populating Several New Tables With The Contents Of Original Table

Mar 29, 2013

I have a feed from ODBC to Access DB. I need to populate several new tables with the contents of the original table. I know I'm being stupid but it's been a long week.

View 1 Replies View Related

Tables :: Combine Two Tables That Have Identical Structure To One Table?

Jul 8, 2013

I need to combine two tables that have identical structure to one table. Different people have used them to insert data from different parts of our project. I have tried to find information about how to do this but so far with little luck.

View 13 Replies View Related

Tables :: How To Combine Records Of Three Tables Into One Table

Jul 30, 2013

I have three tables. I want to combine all the records in these tables into one table. I need VBA code to do this. The first table is called down1, the second table is called down2 and the third table is called down3. All these tables contain the same fields so I don't think combining them will be a problem.

View 3 Replies View Related

Tables :: Populating Tables On Entry To Another Table

Apr 12, 2013

I've got a working database with pretty good structure etc. How I would go about creating 3 prepopulated records on a many side of a relationship on entering a new record in the one side of the relationship.

View 6 Replies View Related

Tables :: Splitting One Table Into 3 Related Tables

Jul 16, 2014

I'm trying to split a table up because I now realize it won't be able to do what I need in the future. It wasn't designed properly at the outset, and I'm trying to correct it now.

This is a database of pregnancies and deliveries.

The single table does not cater well for multiple pregnancies (twins, triplets, etc), and also I foresee problems when mothers come back in future for another delivery.

Therefore I am trying to separate data into 3 tables: Mother, Delivery and Baby.

This is because each mother can deliver more than once, and each delivery can have more than one baby.

I have set up a trial database, with just a few fields in each table to see if this works. An screenshot of the table relationships is attached.

The primary key of the Mother table is linked to the Delivery table, and ditto the primary key of the Delivery table and the Baby table.

I'm not sure how to migrate the data over, in terms of the primary keys of each table, because in the new tables, these should be an AutoNumber field, so that they are unique numbers.

View 14 Replies View Related

Tables :: Joining Two Tables In 3rd Table With All Fields From Both

Oct 27, 2014

I have a db with 2 tables one with 10 fields and the other with 11 fields. In these tables there are 4 and 5 non common fields in tables respectively.

How can I join these tables in a 3rd table with all the fields from both tables.?

View 1 Replies View Related

Tables :: Calling Sub Tables In Parent Table

Oct 26, 2012

I am working on a complex database, as i have to call sub tables in the parent table. but i don't know how i can create it. My database format is as below

Village name, UC Name, District, village basic information, village disasters history, village school safety plan, village DMC members, Hazard Map of the village,

After the field of District i have to call sub tables against each field with its details. How I can create it. in Hazard Map field i have to attach the map img files.

View 1 Replies View Related

Tables :: Pull Field From Main Table And Create A New Table With Date Stamp

Nov 4, 2014

I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).

Currently, I setup the query to pull info from the form field like this:

DateField: [Forms]![frmmain]![DateField]

However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).

View 6 Replies View Related

Tables :: Multiple Parents Table Linked To Child Table - Primary Keys

May 28, 2015

I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...

*Child_ID
Parent_First_Name
*Primary/Secondary/Other

View 8 Replies View Related

Create Table Of Different Columns In Three Tables Join To Form A Master Table?

Jun 4, 2015

I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.

If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.

Note: except the Name column, none of these tables have any other columns in common How do I go about this?

View 7 Replies View Related

Tables :: Make Calculated Field In One Table That Calculates Values From Another Table Linked By Order ID

May 13, 2015

I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?

View 3 Replies View Related

Tables :: Update A Field Of Table A By Entering Data On Table B

Sep 14, 2012

Some days ago I made it by using "query", but now I forget it how I make the relation on this situation. Here is my problem.I have 3 Table on mdb file, named Table: A, B, Status.Table Status have One Filed with 1 Data: Dishonor

Table A have three fields

Sl Number: (Auto Number)
Status: Lookup wizard-data of (Table-Status)-Default Value is "Honor"
Amount:Number

Table B have two Fileds
Sl Number: Number
Status: Lookup wizard-data of (Table-Status)

After Entering Some data on Table A it's Look like as:

Asl numberstatusamount
1Honor5222
2Honor855
3Honor988
4Honor7777
5Honor777
6Honor9999

[code]...

Now I want to change the Status of SL Number Honor to Dishonor so I fillup data on Table B is as like following

sl numberstatus
5Dishonor

Now how I can get the result as following by using query:sl numberstatusamount
1Honor5222
2Honor855
3Honor988
4Honor7777
5Dishonor777
6Honor9999
7Honor6666
8Honor7777
9Honor666

View 1 Replies View Related

Tables :: Multiple Table Data To Save To A Different Table From A Form

Mar 6, 2014

I have three tables: Vehicles; Vehicle Reallocated; and Vehicles Retired. I have a form that runs a query to find all the info in the Vehicles tbl that is not "Retired", not visible in the form. I then have the option to toggle to a Reallocated or Retired form. When i toggle to the reallocated form, i have the like fields in that table (ie Van #, Vin, Make etc) pulling the info from the hidden subform with the vehicle query, so i do not need to fill in repeat data. However, when i add a reallocated date and the new clinic that vehicle is for, i get the record ID for the vehicle reallocated table as expected, but when i save none of the data moved over from the query saves in the record?

How to get all the data on the reallocated form to save?

View 6 Replies View Related

Tables :: Disable A Record From A Table Based On Attribute Of Another Table

Jan 12, 2013

I have a keys table and a keysctivity table. I need keys to not be available if they are currently signed out (return_date is null) or if they have been marked as lost (lost_key = true)

This is my activity table. URL....If for example signin_id 1 was not returned or was lost, key_id 1 should no longer be available. Is this something that can be done?This is the access file I am working on: URL....

create two new tables "lostKeys" "unreturned_keys" and have records moved to their respective tables based on whether they are indicated to be lost or currently not returned.

View 4 Replies View Related

Tables :: Normalize Existing Table Containing Data And Copying PK Value To FK In New Table

Nov 21, 2014

I am working from an existing database which is just two table. The main table has a massive amount of redundancy and duplication and needs splitting into, at first glance, 5 tables.

After I have run my various make table queries and added a Primary Key and FK field to the new tables how do I populate the FK with the Parent PK.

I thought I could simply add all the fields from the new table and then create an adhoc join in an update query to populate the PK to the FK. When I do this however I get "You are about to update 0 records"

I have tried the table analyzer but it doesn't give the correct options to split the table the way I need.

View 6 Replies View Related

Tables :: Combining Several Tables Into One Table

Aug 23, 2013

I have 10 tables. None of them have any entries in them. Just field names. Is there a way to combine them all into one table.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved