Need help designing a stock/asset tracking database (1 Viewer)

[fat controller]

Before I begin, I will openly admit that my knowledge of Access is pretty limited, and that I am learning as I go.

I have built a few databases, although each has been a development of the the one before it so more developing, refining and expanding what I have done before; the latest version is proving to be highly successful now that it is in operation - I should say that it is fairly simple (I would imagine that most of the experts on here would build it standing on their head), in that it has a form which users simply enter information into the relevant fields which populates the table, and then reports can be taken within given parameters (main one being date from and to).

However that success has led to me being presented with new challenges, as more and more people are seeing the benefit of being able to get access to the information they want quite easily.

The latest one has me totally lost, hence me turning to you all for help, simply because it involves moving or displaying information in different locations.

In short, I have a fleet of vehicles (up to 150), and each vehicle is fitted with four items of equipment; each item of equipment has a serial number. I need to be able to track those items of equipment coming into stock from the supplier, then onto each individual vehicle, as well as being able to go back into stock and even back to the supplier.

There is only one supplier, and each vehicle has a unique ID number. The four items of equipment are entirely separate to each other, and it is common for only one of them to be changed out at any point in time.

I need the database to be able to show:

- what four items are on which vehicle at the time of the report, be that an individual vehicle report or a full fleet listing;

- what items are in stock

- the history of an item (which vehicles it has been on, and if it has been in and out to and from the supplier a number of times)

This may sound really silly to most on here, but I cannot even work out how many tables I need for this - and I would openly admit that relationships has me baffled.

Any, and all help gratefully received

 

[PaulO]

To get you started before the really wise owls give you better advice, it sounds like you need two tables to start

Vehicles (Vehicle Reg No: Make; Model; etc; etc)
Equipments (Part No; Part Description; Part Group; Serial Number; Vehicle Reg No; etc; etc)

The link/relationship between the tables is, clearly, Vehicle Reg No

The neat part is to then set up a Form and Subform wherein you assign Equipments (up to 4) to each Vehicle with the Vehicle Reg No stored in the Equipments table against each Serialised equipment. You'll need some validation around this, clearly, to prevent an Equipment being allocated to more than one vehicle but in its simplest form this can be handled by a Select query which feeds a drop-down list only with unassigned equipments.

Hopefully I haven't completely lost you.

Thereafter, I think, where Equipments need to be moved/transferred from one Vehicle to another this would perhaps best be handled by a separate form that lists the current assignments but allows you to select a new Vehicle and then a command button that actions the change to the Vehicle Reg No field in the Equipments table.

This is all very do-able ... the main thing for me is that you at least seem to know what you WANT and so, with a little perserverance, you'll get there.

 

[fat controller]

Thanks PaulO - I must confess I understand most, but not all, however am not completely lost.

All our vehicles have a unique fleet number, allocated as soon as soon as we purchase the vehicle and remains forever after as the primary identification that we use to refer to the vehicle throughout our operation, so I will use that as opposed to registration number.

So far then, I have created a table which has ID(autonumber, primary key) Fleet Number and Registration Number; and a further table that has Equipment 1 Serial, Equipment 2 Serial, Equipment 3 Serial, Equipment 4 Serial, Fleet Number & Registration Number.

I have set a relationship between the two, although it is currently indeterminate as this is where I begin to lose myself. I have three options (which I assume are the standard Access options?) as to the type of relationship, but I cannot work out which I need to use?

Should I also create a third table containing the four Equipement Serial fields to be a 'stock' table?

And should I have a date field on one or more of these tables to show the dates when the equipment move in and out of stock?

Sorry to be really thick.........

 

[DCrake]

You also need a transaction table which will hold all historic movements of equipment from stores to vehicles and back to stores. Including movement dates as well.

 

[fat controller]

Thanks

I've now added a Movements History Table which has the following fields -

- Date of movement
- From (Vehicle/Stock/Supplier)
- To (Vehicle Stock Supplier)
and four fields for the Equipment Serial numbers

I have also added a Supplier Table, which currently has only the four Equipment Serial number fields - does that sound sensible?

 

[HiTechCoach]

Thanks

I've now added a Movements History Table which has the following fields -

- Date of movement
- From (Vehicle/Stock/Supplier)
- To (Vehicle Stock Supplier)
and four fields for the Equipment Serial numbers

I have also added a Supplier Table, which currently has only the four Equipment Serial number fields - does that sound sensible?

Close. You have repeating fields. Each piece of equipment (not serial number) should be in a separated record. Why? For one, you said: The four items of equipment are entirely separate to each other, and it is common for only one of them to be changed out at any point in time.
There should be a separate transaction record for each piece if equipment. Repeating field also violate the rules of data normalization. They make your database limited in design and a lot more difficult for reporting, etc. Repeating fields may often done in a spreadsheet but not a relational database.

The link/relationship between the tables is, clearly, Vehicle Reg No

I have to disagree. The Vehicle Reg No is information about the vehicle. It should not be the primary key or use in any relationships. I would only use a system assigned ids for primary keys. The auto number data type is great for this.

This is also true for equipment. You will need an equipment table. The equipment's serial number should not be used as the primary key. I would only use a system assigned ids for primary keys. The auto number data type is great for this.


I have done multiple applications like this. I apply basic multiple warehouse inventory control to this. I have done tracking of equipment issued to employees and installed on vehicles, computer assets, inventory on repair trucks, multiple warehouse items for sale, etc.

For you I would think in terms of warehouses as being each vehicle, supplier(s), and main equipment room.

You will transfer each item between warehouses. The will be a separate record for each piece of equipment. This allow you to track the history of each item.


Your current needs are for only one supplier and four items per vehicle. The one sure thing you can count on is that this will change. You could have additional suppliers or switch supplier. In either way you need to handle multiple suppliers. Same for equipment. In the future you might have a different number than four per vehicle. If your database is properly designed then it will not matter how many suppliers you have or how many pieces of equipment each vehicle is issued.

 

[HiTechCoach]

Here is a screen shot from an app that I started back 1997 with Access 97. This is what is looked like in Access 97/2000.

Hope this helps

 

[fat controller]

Thanks HiTechCoach

The more I think this through, the more I think that the Vehicle Reg will never be needed.

I work in public transport, and as such my company is on fixed contracts which mean that we must use the ticketing equipment as supplied by our client - this has been the same for the last fifteen years at least, and is not likely to change at any point in the next five to ten years at least. However, if you advise that I should build in the ability to expand at a later date, then I will of course accept your recommendation, as my knowledge is really very limited.

Currently, we control the movements or equipment on a spreadsheet which has conditional formatting that shows up red if there is a duplicate serial number somewhere, however this does not allow us to track which vehicles an item has been on, or when it came into our possession or indeed when it leaves our possession.

In short, I need to end up with a reasonably simple form that one of our clerks can operate, where we can record items coming into our possession (stock), then be able to allocate them to a vehicle, back to stock, on to another vehicle or even back out of our possession to the supplier. I will need the user to be able to add or remove vehicles (fleet numbers) as they come and go, although don't necessarily need to be able to record the dates when the vehicles come and go (although it wouldn't hurt to have a record of it).

I need to be able to report where an individual item currently is; what items are currently on a specific vehicle; what items are currently in stock; a full listing of vehicles and their current equipment, and if possible the history of either an individual item serial number (what vehicles it has been on and when); the cherry on the cake would be if I could also show what items had been on a specific vehicle and when (but I appreciate that I may well asking too much with this)

I am certain that I am batting well above my level of knowledge with this, but I am willing to follow instructions (preferably written as though I am a complete idiot) and try and learn to make this work?

I have the luxury of being able to work with Access 2007 at home, however the final database will need to run in 2003 if that makes any difference at all.

If you could clarify how many tables I need, and what fields I should put in each, I would be most grateful.

 

[HiTechCoach]

In short, I need to end up with a reasonably simple form ...

I don't see how you can keep is simple and use a single form. That would be just another spreadsheet not a relational database.

You basically need to forget about how you would do anything in a spreadsheet. Almost nothing will apply to a good relational database.

I am certain that I am batting well above my level of knowledge with this, but I am willing to follow instructions (preferably written as though I am a complete idiot) and try and learn to make this work?

This is an inventory control issue. Inventory control is difficult. Inventory control with software is one of the most difficult task to do correctly. Id does not matter if you are using Access or VB.net with an SQL Server.

I have the luxury of being able to work with Access 2007 at home, however the final database will need to run in 2003 if that makes any difference at all.

Not sure that is a luxury. It is probably more of a curse. Access 2007 works a lot different. Make sure that you only use a .mdb with Access 2007. The new ACE (.accdb) format has many features that are no9t backwardly compatible. There are many thing you can do in the GUI in Aces 2007 that 2003 does not support.

Be very carefue using Access 2007 to edit your Access 2003 database.

If you could clarify how many tables I need, and what fields I should put in each, I would be most grateful.

Um ... that is really asking a lot from people that volunteer to any your questions for free.

TIP: Support forums like this one that are free are here to answer your questions. If you start asking people to do your work for you (especially if you are doing it for work) people will not help you. If you appear to not putting out the effort to try to learn to do it yourself you again you will not get my help.


What will get you better response is on any support forum that is free is to create your tables in a database and post a sample. We will be glad to take a look and give you some advice. You will benefit the most form this process. It will also help you be able to support this database later on once it goes into production. Welcome to the world of being a database developer and having to support your work.

Also see:
Splitting your Access database into application and data
** This is very important to do. **

 

[fat controller]

Thanks HiTechCoach - Apologies if I came across as though asking others to do the donkey work, it was not my intention.

Thanks for the link too - I must admit that I hadn't even heard of splitting a database until reading that, and I currently have one running on a network that can have various users dipping in and out of it in the course of a day, so I have apparently been very lucky that its not had any issues to date. I will now need to rethink that database too, however, so that it doesn't fall over in the future.

It must also confess that I didn't realise that Access could do quite so much (why does it seem to be overlooked by many businesses when it is such a powerful tool? Is it purely because people don't have the desire to learn how to use it?)

I will also have to be careful what I am doing when it comes to 2007 versus 2003, and make sure I save everything at home in 2003 format.

Right, I am off to play with databases for a while and try some trial and error learning to try and get my head round this - no doubt I will be back in due course for more help.

Thank you

 

[Rabbie]

In addition to the excellent advice you have received from HiTechCoach and other I would reccommend that you write down on a heet of paper everything you want your database to do. If you can't do it with a pencil you won't be able to do it properly in Access.

 

[fat controller]

Thanks Rabbie - Funny enough, I have just started typing stuff into a Word Document as I am plodding away (too lazy to get up to get a pen just now )

PS - would I be right in assuming from your username that you are a fellow Scotsman?

 

[fat controller]

Right, I come bouncing back feeling much more positive again (although I still have the feeling inside that I am only just 'making do' and not really understanding things - yet!).

I found a 'Lending Library' template online, which I decided to have a play with, and with a few fairly minimal tweaks it seems to work pretty well - quite a lot of cosmetic bits to change, but I feel that I am leagues ahead of where I was.

My current hurdle is that I have added a text box onto one of the forms, which I want to show which vehicle(s) that a particular item has been on; There is a table called 'Transactions' and the information that I want to display is contained within that table. All the other text boxes are also linked to the 'Transactions' table to show date in, date out etc, but try as I might I cannot work out where I am going wrong with the control source for the text box.

I have a screen grab of my best attempt, however I cannot post a link yet. So far, the best shot I have managed for the control source was =[Transactions]![Checked Out To], however this simply showed the number 5 instead of the information that is contained in that particular field.

Am I a hopeless case?

Thanks for your patience.

 

[HiTechCoach]

I find it a lot better to attach your stuff (screen shots, databases, etc) not link to them.

... table to show date in, date out etc, ...

Um ... in and out ion the same record does not should like a well
designed/normalized design.

Will you attach a copy?
TIP: Compact the database then ZIP it before attaching to a post.

I am thinking an asset management system would be a lot closer to what you need. Have you look at the Microsoft Asset template?

Adding a field and having it appear on a form:
1) edit the table design and add the new field
2) may need to run an update query to set the default value in the field
3) Edit the form's record source to have the new field.

Option 1 (let Access help):
4) view the available fields list.
5) drag the field onto the form from the field list an drop on the form.
Note: The new control is automatically bound

or

Option 2 (manually):
4) add a control to the form. It will be unbound
5) view the control's property sheet
6) select the control's property for control source.
7) click the drop down arrow to the right of the control source
8) select the field from the drop down list.
9) Now it is bound.

 

[fat controller]

Thanks HiTechCoach, I let Access help and it worked a treat

It is so close to being spot on for my needs it is remarkable, however there is a minor problem - essentially I have set it up as each vehicle is a 'borrower' and as such have created each vehicle as a contact. I can book items out from the assets form, and when running the reports the assets appear as allocated to the correct vehicle (borrower), and are not on the available assets report (so not in stock) - so far, so good.

However, when I then go to the contacts form and view the individual vehicle, and then click the current lending tab, it show all assets that are allocated to all vehicles/borrowers, not just those that are loaned to that particular vehicle.

In case I had done something whilst tweaking (a distinct possibility given that I appear to have the finesse of a baby elephant in a crystal shop when it comes to this), I created another database from the template, put a few contacts in, a few assets, booked them out as loaned to the various contacts and found that the same problem existed.

Is there anyway to get the current lending subform to show only the items that are currently allocated to the individual contact?

 

[HiTechCoach]

Thanks HiTechCoach, I let Access help and it worked a treat

It is so close to being spot on for my needs it is remarkable, however there is a minor problem - essentially I have set it up as each vehicle is a 'borrower' and as such have created each vehicle as a contact. I can book items out from the assets form, and when running the reports the assets appear as allocated to the correct vehicle (borrower), and are not on the available assets report (so not in stock) - so far, so good.

However, when I then go to the contacts form and view the individual vehicle, and then click the current lending tab, it show all assets that are allocated to all vehicles/borrowers, not just those that are loaned to that particular vehicle.

In case I had done something whilst tweaking (a distinct possibility given that I appear to have the finesse of a baby elephant in a crystal shop when it comes to this), I created another database from the template, put a few contacts in, a few assets, booked them out as loaned to the various contacts and found that the same problem existed.

Is there anyway to get the current lending subform to show only the items that are currently allocated to the individual contact?

Without seeing the your database or the template it is very difficult to spot the issue.

Would you please attach a copy of your database.

Curious, does the database you are using for lending show a history of who has had the book? Where did you get the template?

 

[fat controller]

I got the database from Microsoft's own site - I should add that it is a 2007 template, and that I've given up on the idea of doing this to work with 2003 as it looks pretty hopeful that we will have PC's running 2007 as a minimum very shortly.

It does show history of who has had the book/item. I have simply renamed stuff on the forms so far, leaving the background of it pretty much alone, and it is as near as anything to being bang on for my needs.

I've never attached anything to a forum before, so apologies if this goes wrong.

Thanks

 

[HiTechCoach]

Yeah .... your attachment worked.

I will take a look and let your know what I find.

 

[fat controller]

Thank you

 

[HiTechCoach]

However, when I then go to the contacts form and view the individual vehicle, and then click the current lending tab, it show all assets that are allocated to all vehicles/borrowers, not just those that are loaned to that particular vehicle.

I could not figure out how to do what you described.

Can you give more detailed instruction on how exactly to duplicate this issue?