Need help designing db

[Steve]

I am fairly new to designing db's and I did one db for my company for fun and they liked it so much they want another one. Problem is I am having trouble getting the idea down on paper so I can start. Here is the situation:

This department issues their product to our other plants. The product are not marked by any type of serial number so they are just bulk issued. They may issue 100 one day and then only return 50 the next day. (very sporadic) Some items may never return, maybe 1% of total usage. So....
First,
they have 21 diffferent products but they are tricky because they are resuable items so they can be issued as either "brand new/refurbished" or "used". I also have to note whether some of either of those use any "new item".
Second,
after the other plants use them, they must return them in the system. The returns are placed into 5 categories - "Good for reuse", "Need to be refurbished for reason 1", "Need to be refurbished for reason 2", "Lost due to Reason 3", Lost due to Reason 4"

I have played around with a couple of ideas but none of them really feel right to me. The employees are used to typing all the issue information into an excel spreadsheet that has the products down the side and the "Brand new" and "used" in the columns 2 and 3. So I was trying to keep it looking the same for them so it is not too confusing.

After I gather the data, I need to give them a daily qty at the plants as well as end-of-month information regarding usage and time spent working on each product.

Any help would be greatly appreciated
your truly,
"Staring at the computer"
Steve

 

[Theo]

Hi Steve,
You're basically gonna need 4 Tables.
Table 1: Product- with all the products captured here + Start Qty,Total Usage,End Qty fields (this fields will be used to determine your Monthly stock level.
Table 2: Status- with "New","Used","Refurbished" and "Lost" captured as rows.
This table should be used for both outgoing and incoming status so that the updating of your records can be more easy.
Table 3: Reason- with a list of common reasons already submitted in the past
Table 4: Usage- with fields "Product","Outgoing Status","Outgoing Qty""Return Status", "Reason", "Return Qty".
The fields in table4 should be linked to the respective tables (table1-3) with a Combo Box Lookup in the Field Properties.Also set the field properties "Limit to List" as True. Therefor the data will stay uniform and you'll know what you're working with.

Updating the daily qty's and product status can be done by a simple update query, where you update "Return Status" with Outgoing Status" and "Return Qty" with "Outgoing Qty" in the first query.Then create an update query that updates "Return Status" and "Return Qty" to Null so that the fields will be cleared for the next day.

I did not touch the end of month information as I do not know how you calculate time spend on each product.If you can elaborate a bit more on this,we can look in to it.
I hope I understood your question correctly,if not let me know and we can try something else.

Ta
Theo

 

[Roger Carlson]

DON'T try to model your database after the spreadsheet! That way lies
madness. Well, perhaps that's a touch dramatic, but if you do, you will be
"Committing Spreadsheet", which is one of the deadly sins of Relational
Database development.

Before you go any further, you really need to delve into Relational Design
theory. Get a copy of "Database Design for Mere Mortals" by Michael
Hernandez and read it as your Bible. Then go to my website in the tutorials
sections (http://www.rogersaccesslibrary.com/Tutorials.html) to see examples
of implementing his process.

There are other good sites out there. Google can help.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

I am fairly new to designing db's and I did one db for my company for fun

and they liked it so much they want another one. Problem is I am having
trouble getting the idea down on paper so I can start. Here is the
situation:

This department issues their product to our other plants. The product are

not marked by any type of serial number so they are just bulk issued. They
may issue 100 one day and then only return 50 the next day. (very sporadic)
Some items may never return, maybe 1% of total usage. So....

First,
they have 21 diffferent products but they are tricky because they are

resuable items so they can be issued as either "brand new/refurbished" or
"used". I also have to note whether some of either of those use any "new
item".

Second,
after the other plants use them, they must return them in the system. The

returns are placed into 5 categories - "Good for reuse", "Need to be
refurbished for reason 1", "Need to be refurbished for reason 2", "Lost due
to Reason 3", Lost due to Reason 4"

I have played around with a couple of ideas but none of them really feel

right to me. The employees are used to typing all the issue information
into an excel spreadsheet that has the products down the side and the "Brand
new" and "used" in the columns 2 and 3. So I was trying to keep it looking
the same for them so it is not too confusing.

After I gather the data, I need to give them a daily qty at the plants as

well as end-of-month information regarding usage and time spent working on
each product.

 

[Rick B]

Steve:

One post please.


I am fairly new to designing db's and I did one db for my company for fun
and they liked it so much they want another one. Problem is I am having
trouble getting the idea down on paper so I can start. Here is the
situation:

This department issues their product to our other plants. The product are
not marked by any type of serial number so they are just bulk issued. They
may issue 100 one day and then only return 50 the next day. (very sporadic)
Some items may never return, maybe 1% of total usage. So....
First,
they have 21 diffferent products but they are tricky because they are
resuable items so they can be issued as either "brand new/refurbished" or
"used". I also have to note whether some of either of those use any "new
item".
Second,
after the other plants use them, they must return them in the system. The
returns are placed into 5 categories - "Good for reuse", "Need to be
refurbished for reason 1", "Need to be refurbished for reason 2", "Lost due
to Reason 3", Lost due to Reason 4"

I have played around with a couple of ideas but none of them really feel
right to me. The employees are used to typing all the issue information
into an excel spreadsheet that has the products down the side and the "Brand
new" and "used" in the columns 2 and 3. So I was trying to keep it looking
the same for them so it is not too confusing.

After I gather the data, I need to give them a daily qty at the plants as
well as end-of-month information regarding usage and time spent working on
each product.

Any help would be greatly appreciated
your truly,
"Staring at the computer"
Steve

 

[Steve]

Sorry about that, I tried to submit it once and there was an error, so I tried a couple more times but every time it said there was an error. Little did I know that it went through every time it said there was an error.

Sorry again

 

[Steve]

Roger,
Thanks for the advice and I agree with you that I don't want to model it after the spreadsheet. I have three really good books on Access 2003 and I have read all of them. I was trying to say that I wanted the form to look like their spreadsheet so the users don't go into shock that they are changing the way they have always done it. I just needed some guidance/suggestions on how to create an inventory tracking database. I made one already for some different type parts that was rather simple but this one has got me in a bind.

 

[Steve P.]

<disclaimer>I am an Access rookie </disclaimer> but I thought I would offer
this as it is a structure that I have used before in real life (not my
design though). Some of the Pros might have some criticism of it and I would
honestly welcome it. One point that I think they might take exception to is
storing dollar values and the checked out quantities in a separate table
instead of calculating them but in my case it was necessary as we needed a
"snapshot" of the historical values as opposed to current ones which could
change daily.

tblProducts
pkProdID
ProdDesription
MinimumQty
MaximumQty
etc.
-----------------------------
tblInventory
pkProdInventoryID 'ProdID with suffix to indicate "new" or "used" product
fkProdID
QtyAvailableForIssue '** See below
IssuePrice 'price charged to dept
AvgCost 'cost that was paid to vendors for product
VendorID
CostFromVendor 'today's replacement cost
ReworkVendorID
ReworkCost
-------------------------------
tblTransactions
pkTransID
TransType
ProdInventoryID
Department
DateTime
Qty
IssueValue
---------------------------------
tblTransactionTypes
ID
TransType
'IssueNonReturn (doesn't write to Checkout table)
'IssueReturnable (written to Checkout table until returned)
'ReturnToNewInv
'ReturnToUsedInv
"ReturnScrapped
'ReturnToRework
'RecievedNew
'RecievedReworked
'AdjustmentToInv
ReturnReasonCode
-----------------------------------
tblCheckedOut
ID
ProdInventoryID
Qty
Department
TransDate
DueBackDate
IssuedPrice

** as opposed to QtyOnhand, which is calculated and may optionally include
QtyInRework and QtyCheckedOut depending on your model.

I'm not exactly sure what you mean by:

"so they can be issued as either "brand new/refurbished" or "used". I also

have to note whether some of either of those use any "new item".

but a reason for having a different Inventory item for each Product state
are that you can probably buy new tools from tool VendorX, you send tools to
be reworked to repair shop VendorY, and you can't really buy replacement
"used" tools at all. If you need to track new tools separately from
refurbished from used then you would need three inventory records for each
product.

"After I gather the data, I need to give them a daily qty at the plants as

well as end-of-month information regarding usage and time spent working on
each product."

The checkout table would store how many are at the plants, a sum of
Transactions by plant (positive value for IssueReturnable and negative for
ReturnsToInv, don't subtract for ReturnScrapped, you need to decide whether
ReturnToRework means the product was "consumed" or not) within a date range
would give you the usage. I'm afraid I don't understand what you are trying
to measure when you say "time spent working on each product".

Part of the package that isn't included above is how to track items that are
out at rework.

I hope this gives you some ideas.

Steve P.

I am fairly new to designing db's and I did one db for my company for fun

and they liked it so much they want another one. Problem is I am having
trouble getting the idea down on paper so I can start. Here is the
situation:

This department issues their product to our other plants. The product are

not marked by any type of serial number so they are just bulk issued. They
may issue 100 one day and then only return 50 the next day. (very sporadic)
Some items may never return, maybe 1% of total usage. So....

First,
they have 21 diffferent products but they are tricky because they are

resuable items so they can be issued as either "brand new/refurbished" or
"used". I also have to note whether some of either of those use any "new
item".

Second,
after the other plants use them, they must return them in the system. The

returns are placed into 5 categories - "Good for reuse", "Need to be
refurbished for reason 1", "Need to be refurbished for reason 2", "Lost due
to Reason 3", Lost due to Reason 4"

I have played around with a couple of ideas but none of them really feel

right to me. The employees are used to typing all the issue information
into an excel spreadsheet that has the products down the side and the "Brand
new" and "used" in the columns 2 and 3. So I was trying to keep it looking
the same for them so it is not too confusing.

After I gather the data, I need to give them a daily qty at the plants as

well as end-of-month information regarding usage and time spent working on
each product.

 

[Roger Carlson]

Take a look here:
http://www.databaseanswers.com/data_models/index.htm

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Roger,
Thanks for the advice and I agree with you that I don't want to model it

after the spreadsheet. I have three really good books on Access 2003 and I
have read all of them. I was trying to say that I wanted the form to look
like their spreadsheet so the users don't go into shock that they are
changing the way they have always done it. I just needed some
guidance/suggestions on how to create an inventory tracking database. I
made one already for some different type parts that was rather simple but
this one has got me in a bind.

 

[John Vinson]

The biggest problem I was having was each plant had different ways of doing the very thing I was given to make a db on. So we now have one way they are going to do it.

<wry laugh>

I've seen this happen many a time. Customers often don't KNOW how
they're doing things, until one of us comes along and insists on
building a data model of what they're doing; the big boss is sometimes
aghast to realize that the same job is being done six different ways
in the five different locations.