Creating total from different tables & problem with Limit to List

[RAYDLEVEL5]

How do I create a formula, so that 'Quantity' from "Requisition Details" is subtracted from 'Starting Quantity' from "Equipment", and the resulting value is entered in 'UnitsInStock' under "Equipment"?

Also, consider this statement:

SELECT DISTINCTROW [EquipmentID], [EquipmentName] FROM Equipment ORDER BY [EquipmentName];

Though the statement helps by automatically filling in the EquipmentID field in the Requisition Details table, it doesn't allow me to enter a different name than what's in the list. How can I change it so it'll allow me to enter new equipment names?

Requisitions
RequisitionID
RequestorID
DateFilled
LocationID

Requisition Details
RequisitionID
EquipmentID
Quantity
Unit
Comment

Equipment
EquipmentID
EquipmentName
CategoryID
QuantityPerUnit
Starting Quantity
Starting Quantity Date
Starting Quantity unit
UnitsInStock
ReoroderLevel
equip_Location
Discontinued

 

[lespaul]

storing a calculated value (like UnitsInStock) breaks third normal form, is there a particular reason for storing this information when you can get it from a query when you need it?

Have you read 'The Fundamentals of Relational Databse Design'? I'm confused with the Starting Quantity information (particularly why you're storing it and why in the Equipment table - what happens if you order more of something, where are you adding it back in? or do you just change the Starting quantity information?).



Leslie

 

[RAYDLEVEL5]

I have no objections to using a query, but I'd like to keep the data in the Equipment table.

I've not read "The Fundamentals of Relational Database Design", but I'll check out the link.

The idea behind the Starting quantity is that it's like the Starting balance in your checkbook. It is the amount of items in stock on the date of first inventory. 'Units in Stock' declare what is available after it's been taken out of stock.

I have no option to add new items except to change the starting quantity -- which might mess up the caluclations.

Do you have any suggestions on how to modify my database? What direction might I take this?

 

[lespaul]

depends on what you are doing and what kind of equipment you are talking about.

As far as the starting balance in your checkbook, in an accounting program the starting balance is not a part of the account identifier, it's the first entry in a journal of the account identifier:

tblAccounts
AcctID Description
10200 Regular Checking Account


tblTransactions
AcctID TransType Amount
10200 1 $5000.00
10200 2 $200.00

tblTransactionTypes
TypeID TypeDescription
1 Beginning Balance
2 Deposit
3 Withdrawl

(this is just to get the basic idea - not a "real" solution!)

for you, you might want to add tblEquipTransactions:

EquipmentID
TransType (Purchase/Distribution)
Quanity

to get the starting balances you would just need to add a purchase record for each equipment type.

HTH

Leslie

 

[BSman]

Using your setup, but avoiding the starting balance you are currently storing, you could have "requisitions" include a column for purchased quantities (or just set up a purchase order set of tables like you did for requisitions). The important thing is that you should have a requisition or purchase order that is identified as starting quantities (use that as a special vendor, for example). Then your current in stock quantities are a simple grouping query that totals (by item inventory #) the quantity purchased and the quantity issued and subtracts issued from purchased. The grouping query could include the equipment table so you would have the equipment description, etc. The quantities would be available at any time through the query (and you could use the query to populate those fields on an equipment form to show the quantities, since it would just be a matter of looking up the values for the inventory number of the equipment being displayed on the form).

Your equipment table should definitely not include columns for quantities and the like.