Supply Inventory, Musical Instrument, hand receipts and Budget, Purchasing database (1 Viewer)

[mgtuba]

Hello Everybody,

I am a relatively new self-taught access user and brand new to this message board with a very small amount of access knowledge but a relentless work ethic and passion to really understand and learn the program. As a matter of fact, everytime I learn something new- I get excited. I enjoy seeing what other people do and sample templates because it gives me ideas I can use for work. Anyway, my reason for writing is I have a project at work for our supply, budget, purchasing and inventory control work area. I need to develop a database that would be easy and fun tailored for inventory control, maintenance, purchasing and budgeting (integrating quarterly projections and actual spending amount on a quarterly basis). The problem is I know exactly what I want the database to do but I can't seem to get it to work. The existing database is not organized very well and people normally typed directly into the table, added unnecessary field that if deleted mess up the queries and reports. Do you know if there is a super-inventory/purchase and budget database perhaps even with bar coding options that already exists? I've been to Microsoft and downloaded some cool templates but just not quite what I am looking for. Anyway onto the database. I am in a professional band of about 70 members and an overall inventory of over 2 million. The members of our band work here anywhere from 2 to 8 years and are allowed to take the instruments with them or they have the option of turning them back into supply. The problem is when the member leaves, how do you keep a permanent history of every single piece of equipment that has ever been associated with the band? Also, when new band members arrive here they sometimes bring their own instrument with them that becomes the band's property until they move and then the instrument and last known information needs to become apart of a permanent history archive. Other things that also need to be in the permanent history is things we turn in to music stores (for store credit ) in this event we need to show how we expended this store credit but the instrument that is turned in needs definitely be in the history archive. Also equipment that is outdated, lost or turned in for junk needs to be reflected in the history. While still on the topic of equipment issue each of the 70 members can sign for more than one piece of equipment but require a signed hard copy of the report. (The report we have for the individuals to sign is pretty decent but the process of having to create a new report for each new musician is very odd-seems like I can simplify this process. (maybe creating a template?) The way it is set-up right now is 70 individual queries and 70 reports created by copying and pasting and changing the "musicians name" from the properties of the report. Currently we are working from a table; no forms or switchboards. I expect there will be about 1000 records once all equipment is inventoried and properly entered into the database. There are several items that we own more than one of the same type of instrument, like amplifiers or guitars, drums, keyboard,etc.... All in all, there is a table of about 70-120 different types of musical instruments we are accountable for with own unique serial numbers, make, date of purchase, amount of purchase, replacement cycle (each is unique for the type of instrument) and individual maintenance, warranty and condition history. What is the best way to develop this property book for ease of data entry and show all the critical elements to include how many of like items are owned by the band and the total cost of musical property.

On the purchasing aspect, a feature I would like to include in this project is when one of our purchasers purchase an instrument or something is ordered, the estimate amount and the equipment itself shows up in a different color in the property book as being in suspense until the item is received. I would also like the purchaser to use a template form with purchase information such as cost; total; shipping quantity..etc where the estimate information will automatically be entered into the chief of supply's budget and subtracted or committed from the quarterly budget-only on approval by the chief of supply. Once approved by the chief of supply, I would like the cardholder to receive an automated receipt authorizing the purchase.The purchaser would then receive notice of approval and simultaneously chief of supply would also send this automated approval to the official that oversees all the credit card purchases. Lastly, without going into too much detail -I need to consider the lifelong maintenance of equipment and life expectancy of equipment. For example a electric piano lasts only 8 yrs before it's time to replace. (This is what drives our budgeting and planning) Other things for me to consider and integrating budget planning and accountability is purchase and costumes. Also, I need to manage/inventory office products and replace as well as integrate with the budget.

In closing this email, I thank you very much for your advice and opinion. I just don't even know where to begin or a good way to map this thing out. Everytime I think about it, I get this pain in my chest and a headache of not knowing where to get started. From everything that I read, planning the database is the most important part of designing the database, this is why I am coming to you all. Do you know of an existing system that would fit my needs? Will I need more than one database and then link them together? Should I pursue this project in access or work in both excel and access? I am sorry about this message being so long but I really need help putting together a solid plan and have even sought a personal tutor in the local area without luck. Again, I am extremely and will do whatever I need to get this project done for work so we can have accountability in the workcenter. Right now there is minimal accountability, excessive data entry and endless errors-simply scary! I am a musician myself and don't have as much knowledge with access. I have been trying to read books to no end on access with every free moment I have between rehearsals, concerts, tours...etc (very slowly I might add) And I only forget what I learned...frustrating!I guess I am just overloaded. Anyway, could somebody please help me get started and get through this?

Thanks!!!!!!!
Access 2002

 

[Parker]

Cripes!

I'm relatively new to Access as well.
Like you I started with a mammoth project. (I am Still developing it) but I have since knocked up half a dozen smaller db's that took a mater of hours rather than years to develop.

You’re confused. Hell I've only just read it and I sympathise........So am I.

Like you I started with an existing system. Our old system was worked with Excel and was rapidly falling apart after years of bloat and compounding problems. But I had the advantage of knowing the developer......me.

You are right though. Planning is probably the most important thing to start with. Get that wrong on a project of this complexity and you will be constantly redesigning your db.

I think the first thing I would do is decide what is stored information, what is queries, and what is reports.

I.E. the physical information of an instrument needs to be stored information. So does information about who owns it. But who borrows it and the length of time owned or loaned is a query because it is constantly changing information. As is lifetime cost.
Reports are, basically, printed from information that is stored in the db.
Printed invoices and purchase orders are reports that are based on a query. As would be a loan agreement.

So first you need to decide what information you need to keep.

As I see it you have four different types of information.

1. People
2. Inventory
3. Financial
4. Maintenance

Lets take the first item.........People.

You will want to store name, contact details, position (staff or member, etc.) type of instrument(s) they play, Instruments they own or borrow, etc.
Once you have made your list then examine it carefully. Every item will fall into one of three categories

1. Information that is unique and can be found nowhere else in your db
2. Information that is directly related to another table
3. Information that is duplicated.

A person’s name and telephone number is, probably, unique, and, therefore needs to be stored in its own table.

The instruments that they are involved with is not unique to this table because you will store detailed information about instruments elsewhere so this information does not need to be stored in this table.

The town that the person lives in is, probably, duplicated as is staff member or band member because you will have many people living in the same town, many that are members of a band and many that are members of staff. You may also have people that are members of staff and members of a band. So this does not need to be stored in this table either.

Get the idea?

Sorry if this sounds rather complicated but by (a) listing this information and (b) breaking this list down you start to get organised and build a 'map'

It would be good to first examine the information that is already available. How is it stored? Is it already in Access? You may be able to just reorganise what already exists.

Hope this helps a little.

 

[Pat Hartman]

I see nothing in your description that would lead me to believe that anything needs to be done in Excel, so stick with Access.

Before you start development you'll need to determine how to store the data properly. This process is called normalization and if you search this forum and the web, you'll find lots of articles on the topic. One thing you can try is the table analyzer. It will analyze a spreadsheet like table and based on its analysis of the repeating values in various rows, come up with a proposed set of tables. Now, I don't promise that this will necesarilly be correct, but it will be better than a single monolithic table.

To handle the inventory part of the db, you'll need tables like the following:

tblPerson
PersonID
LastName
FirstName
Addr1
City
State
.....etc. - information about a band member that occurs only once

tblInstrumentType
InstrumentTypeID
InstrumentName - list of all types of instruments - piano, guitar, violin, etc.

tblInstruments
InstrumentID
InstrumentTypeID (foreign key to tblInstrumentType)
PurchaseDate
PurchaseCost
SerialNumber
Manufacturer
Status - codes such as lost, in inventory, assigned, damaged, on order, etc.
....etc. - information about each specific instrument

tblPersonInstrument
PersonID
InstrumentID
AssignedDt
ReturnedDt

With these tables, you should be able to answer any question regarding what instruments you currently have, who they are assigned to now, who had them in the past, how many you have of each type, and lots more. You can also produce documents for a band member to sign when he signs out an instrument.

You can use the Status of an instrument in a ConditionalFormatting condition to color certain lines on a form.

 

[mgtuba]

Thank you both for your helpful comments!

I only wish I could know a fraction of this knowledge. I think the biggest problem I am having, at this point, is with the initial setting up these tables, and the overall design process. I followed your advice with setting up the tables for starters but I ran into issues like: The instruments are divided into several categories: Brass;Woodwind;Percussion;Sound. Do I make different tables for these groups or is it better to keep everything in one table and have a field to sort? Also our Band is divided into 11 Smaller bands. We generally sign out all the sound equipment to the sound technician and he sub hand-receipts it out to the person in charge of the group. It would also be very nice to view equipment by group or monitor what the sound technician signs out. Also when foreign key was mentioned it kind of threw me for a loop. Will I still use autonumber?

Thanks again for your helpful comments and advice.

 

[Parker]

The instruments are divided into several categories: Brass;Woodwind;Percussion;Sound..

You should have a separate table for category to define the type of instrument. And a table for the instruments themselves. These tables are then joined.

Also our Band is divided into 11 Smaller bands. We generally sign out all the sound equipment to the sound technician and he sub hand-receipts it out to the person in charge of the group. It would also be very nice to view equipment by group or monitor what the sound technician signs out.

Then you need to have a separate table to hold the information on the different bands. The rest is achieved by queries

Also when foreign key was mentioned it kind of threw me for a loop. Will I still use autonumber?

Read up on joining tables. Basically if you use an autonumber as a primary key (PK) in table 'A' and you want to join it to table 'B' then table B needs a 'Key field' this key field is the foreign key (FK) and would usually, but not necessarily, have the same name as the PK in table A. PK's and FK's need to have the same field type so, Because you have used an autonumber in table A the FK in table B would need to be a numeric field. Then you can join the two tables and the information in each table will be related according to the type of join you have made.

Hope this helps.