Help a novice

[RossCSmfc]

Hi,

I have been tasked with coming up with a database to manage assets in my work. I am not proficient in Microsoft Access and would appreciate some simple steps to help me get the hang of it.

Basically I'm looking at a basic database with fields "Unique asset number, Description, Store Location, Condition, PAT test date, Next PAT Test Due." and I'm not sure where to start.

Could someone please advise me from basics how to create the database. I am aware that there are templates on the access system that involve asset management but would like to know how to start one from scratch & ideally know how to add a combo box for the condition field (working, not working, needs repaired).



It would be massively appreciated if anyone could help.

[orange]

You need to get some familiarity with Database concepts and there are several free youtube videos available.

With Access, do not use names with embedded spaces. It will save you from considerable syntax frustration.

Here is a link to information that may help you.

Good luck.

[davegri]

See attached. Very simple DB with one table and one form to update/view the table. This can get you started.
Ross v001.accdb

[ohmydatabase]

These videos really helped me. It's terrible in the beginning, but once you start getting the basic idea of how to actually make Access do what you want it to do, it get's better.
https://www.youtube.com/playlist?lis...03jQ_t9nFV737s

I, personally, use VBA to do all my query building / event procedures / output to other MS Office Applications. I suggest looking into using VBA, SQL, and DAO

I second what @orange has said. Good Luck.

[RossCSmfc]

Thanks to everyone for replying. I've been working with this database and am just getting to grips with queries etc. There is going to be a lot of entries into the database and wondered if anyone could help me out with another couple of questions;

I've managed to create things like drop-down/combo boxes for typing in, is there any other shortcuts for me putting in new entries? (there is going to be something in the region of 1000 entries on my main table) I've managed to begin creating queries to test my system (such as PAT test dates overdue etc), is queries the best way to go about this?

Is there anything I can do re creating a relationship between certain characteristics beyond the primary key? i.e. the ID will be my primary key, as some assets don't have a Unique Asset Number so I have to leave them blank yet they are individually numbered?

[orange]

How do you this now?

[RossCSmfc]

Thanks, I've been looking at the links you had provided and they have been very helpful

Essentially I'm creating a database system (from complete scratch), recording equipment my company owns, where it is, the condition of it and the date of it's electrical test. I'm going out to collect the information of each item, putting it on paper and into an electronic database system. Each item SHOULD also have a unique asset number on a sticker but some are missing so I'm going to have blank fields, I'd assume.
Each piece of equipment (asset) is to be recorded into a database and I've decided on my fields from information I'd like to query.

[orange]

Oops I think I deleted my previous post (mistake on my part)..

[Access_Blaster]

...as some assets don't have a Unique Asset Number so I have to leave them blank yet they are individually numbered?

Who assigns the unique asset number? I would think this would be you.

[RossCSmfc]

Who assigns the unique asset number? I would think this would be you.

The asset number is linked to the last time they were electrically tested by law, and the stickers have faded/gone away from some, so I don't have the facility of making my own (I think I'd need to use the generic ID no as my primary key in this case?)

[orange]

How do you manage these assets now?
Please describe the overall process/business in simple English.
Here is a free data model for IT Asset inventory from Barry Williams' site.

Here is an old post with a database that may offer some insight
.
Start with a clear description of the business processes involved.
Also, you might want to list some constraints that don't have any obvious/best approach and show these to the people who assigned the task.
--overdue tests
--equip with no identifier

[ohmydatabase]

Inputting data will most likely have to be done by you. That is unless you create a form where other users can input data.

As far as primary keys and unique IDs you can probably get around using them entirely. I don't use relationships. **cringe** I just try to be cute with my VBA. If you trust that people are recording these unique IDs correctly, you shouldn't run into a problem; however, this is a big if. You could also write some subroutine that compares the ID that you input to the ones existing in your tables. If it doesn't match anything continue, if it does have it spit out an error and at you so you can verify what is going wrong. If you're able to quickly pull these IDs into another subroutine that checks the ID for the 'code' that signifies the last electronic test, you could have the VBA insert that date into a new field.

These aren't the end all be all solutions to this problem.. There are many ways to accomplish the same thing. These were just what I came up with off the top of my head.

[RossCSmfc]

Thanks to everyone for the replies, thought I might try to be a little clearer with my scenario as it might not make much sense! These are the fields that I have to include now;

ID (autonumber, primary key)
Asset Number (each item has an asset number sticker, some are missing so I can't have this as unique as some fields are null)
Location (the area of the asset i.e. kitchen, computing room)
Condition (Working, not working, condemned)
PAT Test date (date the asset is due to be tested for safety (again, some stickers are missing so some fields are null)
Manufacturer (Henry, Bose etc)
Type (Hoover, speaker etc)
Unit (the first name of the building I have to put in i.e. Glasgow, Edinburgh)
Classification of building (Community Centre, Police station)
UPRN (a unique code for each building)

I'd love to be able to autofill/force the unit and UPRN to be linked but I am not sure at all how to do this. My skills in access are limited so would appreciate any advice being broken down as if I've never used it before

I basically have a main 'asset list' table that I've input over 100 records into, I've successfully run a couple of queries too. I've tried to run forms to input data but am struggling a little with this too.

Thanks again.