Item Tracking Database

[BillyGreaves]

Hey guys, I'm fairly new to Access, we touched on it in school a bit but now I'm out in the real world and have to make up a database to track items. The company I work for loans out hire radios and to make life easy I wanna make up a database to track the radios as they come in and go out.

Is there anyway I can do this?



Cheers.

[ArviLaanemets]

Tables
tblCustomers: CustomerID, Forename, Surname, AddressRow1, AddressRow2, AddressRow3, ZIP, Email, Phone, ...,Status (The simplest way to keep info about devices not loaned out is define your company as a customer too, with Status = 1. To register devices out of use, define also a company with Status = 9. For for all other customers Status will be 0.);
tblDevices: DeviceID, SN, Producer, Mark, ..., CurrCustID, CurrStatus (CurrCustID and CurrStatus are added to make it easier to set filters on forms/reports, but they are calculated at form level, not at table level.);
tblMovements: MovementID, MovementDate, DeviceID, CustomerID.

Forms
fMain: unbound form with a tab control. Tab has pages pgDevices and pgCustomers;
fCustomers: a continious form with tblCustomers as source, placed on pgCustomers;
fDevices : a single form with tblDevices as source and with subform based on tblMovements. Add a combo to select filter condition for this form on pgDevices of fMain, to display only loaned out devices, devices not loaned out, devices out of use, or all devices except out of use.
fMovements: a continous form with tblMovements as source, placed as subform on fDevices and linked through CustomerID with it. In afterUpdate event(s) of form/form controls, CurrCustID and CurrStatus for current device are calculated.

[ssanfu]

I stole the following from a post by orange - it is better than anything I could write:
---

"I recommend you create a narrative in simple terms about your proposed database and application. You don't start building a database by identifying a Form with 3 buttons. Now, that may very well be your initial user interface, but that is not the database. It is not as easy as many think to clearly describe WHAT your business is/will be. Consider the "things" involved(entities), the particular characteristics of each(attributes) that are important for your business and how these things relate to one another (relationships). After you have the narrative, you can use pencil and paper (or whiteboard or cardboard or ...) to develop and refine a data model. Create some test data and vet your model.
<snip>

<snip>
I see too many people, who have the latest HW and Access, jumping in head first think the software will build the database. They have multiple issues and can't access the data required for X and/or Y, and typically it is an issue of basic table and relationship design. Their next "rationalization" is " I've got too much invested to go back and correct the design...". Don't get yourself in that predicament.

You have to do the analysis, build a plan, set priorities......Since you have the business, you know the processes better than anyone, so analysis should be second nature-- and providing you are familiar with database concepts -- designing the database should be an iterative process and a good learning experience for you. The data model and testing/vetting will result in a blueprint for your database. Review, test, adjust, and repeat until you get the results you expect. People are here and willing to help."
---


While ArviLaanemets has good suggestions, I suggest that you get pencil and paper, a whiteboard, chalkboard, etc and design your table structures/relationships before jumping on the computer.
Try working through the design adding data to see if it is functioning as you expect/want.


"Old Programmer's Rule" is this: If you can't do it on paper then you can't do it in Access. Until you can draw out your tables AND rules for how they change for each function you want to perform, you are going nowhere fast.



Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.

Do not use spaces, punctuation or special characters in object names.
Do not use look up FIELDS, multi-value fields or calculated fields in tables.
Do not begin object names with a number.


In addition, don't make the common mistakes when it comes to naming,
http://www.access-programmers.co.uk/...d.php?t=225837
reserved words, http://allenbrowne.com/AppIssueBadWord.html
lookup fields (don't),
calculated table fields (don't) http://allenbrowne.com/casu-14.html
and multi value fields (definitely don't) see "What's Mixed" here http://allenbrowne.com/Access2007.html
and definitely understand what autonumbers are for http://www.utteraccess.com/wiki/index.php/Autonumbers
Microsoft Access Tables: Primary Key Tips and Techniques http://www.fmsinc.com/free/newtips/primarykey.asp


AutoNumber
Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.



You might also check Barry Williams site to see if there is a model
===============================================