Initial Design Contemplation

[EisBlade]

My job includes taking incoming calls for Cellular and Landline issues as
well as managing and issuing cell phone equipment. I'm building a database to
track these two functions to break away from my current tracker, MS Excell.

The "Call Ticket" portion is done with all related tables finished. My
customers are soldiers so I have a table appropriately named "Soldiers".
Incoming call tickets are collected in a Call Tickets table. Nuff said. The
second portion of function has me stumped.

When I receive a cell phone via shipment from a vender (VerizonWireless,
AllTel, SunCom, etc) I need to split it apart and enter separate components.
Individual components of a mobile package/unit are: mobile#, the hardware it
is currently assigned to, and any accessories that might be received with it
or issued out to a soldier. Mobile#'s can be moved to different hardware, and
vice-versa. My question lies in which is the best way to design these tables.
My ideas are:
1) to design one large table and put all items of inventory into it, then
create a "join" table and join a mobile# to its assigned hardware device
(devices could be AirCards, cell phones, or blackberry). And then issue to
each soldier items selected from the join table since it now tracks what that
"unit/package" includes - minus accessories with are considered expendable.

2) Design 3 different tables (Mobile#'s, Accessories, Hardware) and relate
them to a "join" table - and issue out mobile packages/units from there.

Any ideas? Either would work, maybe, if Reports and Queries are done right -
but I'd like to design this smartly upfront in a way that would allow best
growth, and the management of each category of items overall.

Mobile#'s can be Active (incurring monthly charges) or Deactivated (no longer
incurring charges) and also be Issued (in use) or Available (for issue if
needed).
Hardware can be Issued (in use) or Spare (available for mating to mobile# if
needed), or Offline (removed from future use either due to damage, loss,
repair, etc.)



I have most of the database completed except for this final step of equipment
tables. I hope there is someone that can help. My project is basically at a
standstill til I figure this part out. Many thanks!

 

[mnature]

If you look at Microsoft's template for an Inventory Management database:

http://office.microsoft.com/en-us/templates/TC010184581033.aspx?CategoryID=CT011366681033

you see that each product can be assigned a category. It can also be
designated as "units on hand" or "units on order." You seem to have a
similar setup, in that you have particular hardware that belong to particular
categories (accessories, hardware), and needs further descriptions, such as
"on hand," "needs repair," or "FUBAR."

Seems like you do need to keep track of Mobile#'s separately, since they
seem to be less generic than hardware or accessories.

 

[EisBlade]

MNature - thanks for responding.

Yes, I have that example DB downloaded. I'm trying to create the database in
a way that is easiest to track who uses what. I'm very green at Access and
all self-taught. Sometimes, even with the plethora of example db's that I
have downloaded, it's difficult how best to use those examples to fit their
design to your specific needs.

Please correct if I've read your reply wrong, but you suggest that I have one
"products" table to which all items are entered/edited, and then track
issuing, accountability and all from that rather than creating (in this case,
two separate containing Mobile#'s and Hardware) separate tables and joining
them together as a unit before issuing as an item?


EisBlade
_______________________________

 

[mnature]

I suppose the first consideration is how well you are tracking items. That
is, I suppose you have a cell phone body, the mobile# card that goes in it,
and then various pieces that can be attached to the body (earphones, antenna,
carrying case). But are these items that are generic, in that one set of
earphones is the same as any other set of earphones, or do you somehow track
a particular set of earphones? In the case of the mobile#, do you track the
number or do you just keep count of how many you have? If the item is
generic, then you can have a simple accounting system that tracks who you
give it to, but just tracks it as another set of earphones being given out.
It the items are specific, then you need to check when that set of earphones
comes back from the person, to make sure it is the same set you checked out
to them.

If everything is generic, then I would suggest one table for all items
(would be a long list of everything you keep in stock), but lumping them
together at the time they are checked out to the soldier. You would have a
separate table for checking them in and out, which would be used by a
query/form to show you how many have been checked out, and how many you
currently have in stock.

That is why I mentioned the Inventory Management database, because it has a
ledger view that shows what has come in, and what has gone out. If you need
to keep track of the soldiers that are issued items, there is also a Customer
Order database where you can put in various products and customers.

If you are new to Access, and probably under pressure to get this database
up and running, then it can be very handy to use a template. There is also
an Order Entry template when you create a new file in Access. Sometime it is
better to have something that works mostly the way you want, and learn Access
by modifying it.

However, if you have the time and energy to create this, we can certainly
give you advice as you work through it.

 

[kikilein]

Hi EisBlade,

Now please don't take this question as respectless or "I am cheap and try to
get things for nothing", but I would be very interested in how you build the
database. I am in a similar position (have to track 700 Blackberries) and I
also want to get my data in a database that has very similar functions than
the one you described.

If you want I would pay for a sample of your work.

I am not an MS Access user and are trying to find an adequate application
(with spare units, disconnected etc. entries).

I would appreciate if someone else can point me to a software program or has
any other suggestions.

Thanks much for your help.