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