Table or tables

[Chillendw]

Hello everyone,

I had a question I was hoping to get some help with. I want to make a db to keep track of items. I was unsure if it would be best to have 1 table with a field which states if an item is received or dispensed, or if I should have 2 separate tables, 1 for received and another for dispensed.

I'm trying to find the best way to create a running balance for each item. From what I've read it's not suggested to put a calculation in the table field so I'd only have a qty (received or dispensed) field in the table(s). I'll include the calculation in the query afterwards.

Thanks in advance.

 

[Ranman256]

i would think 1 table if the items is only in 1 of 2 states.
[State] is either dispensed or received.

if you need to know if both have happend, then 1 table, 2 fields
item, [dispensed] , [received]
343 , true, false

if you are tracking events over time and need to know, then 2 tables
the child table would track each event
item, State, Date
343 , dispensed, 1/1/17
343 , Received, 2/1/17
343 , dispensed, 3/1/17

 

[Chillendw]

if you are tracking events over time and need to know, then 2 tables
the child table would track each event
item, State, Date
343 , dispensed, 1/1/17
343 , Received, 2/1/17
343 , dispensed, 3/1/17

Thanks for the response Ranman.

I will need to track each event, so it's similar to the last situation you mentioned.
I'm not fully understanding you when you mentioned that the child table would track each event. Wouldn't I track the events via a Union query (for each item)?

Thanks!

 

[HiTechCoach]

Thanks for the response Ranman.

I will need to track each event, so it's similar to the last situation you mentioned.
I'm not fully understanding you when you mentioned that the child table would track each event. Wouldn't I track the events via a Union query (for each item)?

Thanks!

I agree with Ranman256.


Iif you are tracking item then you will have a master table for all items. This would only have the detail that describe the item. Like Name, color, serial number, etc. This is data that should not normally change. Similar to a person's demographics where the DOB does not change.

Next you will need a transactions table. This is was Ranman256 referred to as a child table. Think of this table as a journal of the detail the description the activity for the item.

Using Ranman256's example with more data:

item#, Activity,    Date,    quantity
1 ,    Received,  1/1/17,  +1
2 ,    Received,  1/2/17,  +1
1 ,   dispensed, 2/1/17,   -1
2 ,   dispensed, 2/2/17,   -1
1 ,   Received,  2/15/17,  +1

To find out what you have use run a query to total the quantity. by item.

On 1/31/2017 you have

Item#  quantity
1           +1
2           +1

On 2/5/2017 you have

Item#  quantity
1            0
2            0

On 2/16/2017 you have

Item#  quantity
1           +1
2            0

 

[Chillendw]

I agree with Ranman256.


Iif you are tracking item then you will have a master table for all items. This would only have the detail that describe the item. Like Name, color, serial number, etc. This is data that should not normally change. Similar to a person's demographics where the DOB does not change.

Next you will need a transactions table. This is was Ranman256 referred to as a child table. Think of this table as a journal of the detail the description the activity for the item.

Using Ranman256's example with more data:

item#, Activity,    Date,    quantity
1 ,    Received,  1/1/17,  +1
2 ,    Received,  1/2/17,  +1
1 ,   dispensed, 2/1/17,   -1
2 ,   dispensed, 2/2/17,   -1
1 ,   Received,  2/15/17,  +1

Thanks for your response Coach.

I did end up making more than 1 table. I ended up separating the dispensed from the received. I figured that it would make it easier to have forms feed into them. I also have a table for the items and another one for the customers. I've attached my relationships. Am I making it harder on myself by creating more tables instead of just the 1 extra?

Thanks again

 

[The_Doc_Man]

The question that makes the biggest difference about how many tables is one I didn't see asked or answered.

Is the item # more like a serial number or more like a part or SKU number? If this is like a pharmacy or a car parts shop or a grocery, then the Item# field is like a description of any number of similar items. If it is like a gun shop, you can only sell each item once because the item is a registered serial number. I'm going to proceed assuming it is NOT like a gun shop.

In theory, if you are doing a sales inventory with multiple item numbers but each item with the same item number is indistinguishable form other items with that same number, then you should probably consider the idea of transaction tables.

You might have tables that resemble:

Items:
ItemID: identifier, primary key. This could be autonumber, a non-automatic number, or a text string depending on what your business uses.
ItemDescr: text description of the item
...other fields describing the items in general

ItemXAct:
ItemID: identifier, foreign key to Items table.
XActDate: date, date (& time if you need it) of transaction
XActQty: integer, items added or removed
XActType: integer or code letter, type of transaction (sale, receipt of new stock, etc.)
XActShipRcpt: integer, foreign key to shipments received (can be 0)
XActSaleRcpt: integer, foreign key to sales receipt

Then inventory on hand is the sum of all transactions grouped by the Item ID perhaps using a UNION query as the starting point and running the sum off of the UNION query as a second layer.

SELECT ItemID, XActQty AS XQty, XActDate FROM ItemXAct 
   WHERE XActType = 'NewStock' OR XActType = 'ReStock' OR XActType = 'InvAdjUp'
          OR XActType = 'ArchiveRef'
UNION
SELECT ItemID, [B]-XActQty[/B] AS XQty FROM ItemXAct
   WHERE XActType = 'Sale' OR XActTyp = 'InvAdjDwn' OR XActType = 'Shrinkage' ;

As many transaction types as you need (and I made up a few), you use to put the transaction on the plus side or minus side of the ledger. Then you can do a summation query on the union query grouped by ItemID and end up with a "SumOfXQty" that is the stock on-hand.

You would use those two link fields that I referred to as Foreign Keys so you could link each transaction to one or more types of paperwork for shipments received, sales, shrinkages reports, etc. You would have one table for each type of link and a JOIN query that shows you the details of each transaction. That's one way to approach the problem. Nowhere near the only way.

Search this forum for "Inventory" and "Sales" databases. You will find tons of articles on same.

 

[Chillendw]

Is the item # more like a serial number or more like a part or SKU number? If this is like a pharmacy or a car parts shop or a grocery, then the Item# field is like a description of any number of similar items. If it is like a gun shop, you can only sell each item once because the item is a registered serial number. I'm going to proceed assuming it is NOT like a gun shop.

Correct, it's not like the gun shop scenario. In this case, the Item# will differ depending on the weight (ItemAmt) of the Product. So 1 product can have several different item#'s. An Item# can only have 1 ItemAmt associated with it.

Items:
ItemID: identifier, primary key. This could be autonumber, a non-automatic number, or a text string depending on what your business uses.
ItemDescr: text description of the item
...other fields describing the items in general

ItemXAct:
ItemID: identifier, foreign key to Items table.
XActDate: date, date (& time if you need it) of transaction
XActQty: integer, items added or removed
XActType: integer or code letter, type of transaction (sale, receipt of new stock, etc.)
XActShipRcpt: integer, foreign key to shipments received (can be 0)
XActSaleRcpt: integer, foreign key to sales receipt.

I didn't think to do that with the quantity. Now I'm thinking of reverting back to a combined table instead

Then inventory on hand is the sum of all transactions grouped by the Item ID perhaps using a UNION query as the starting point and running the sum off of the UNION query as a second layer.

This is what I was thinking of doing. Thank you for confirming. And then I started thinking about using dsum..... rabbit hole.

Search this forum for "Inventory" and "Sales" databases. You will find tons of articles on same.

I'll be sure to do this. Thank you so much.

 

[The_Doc_Man]

And then I started thinking about using dsum..... rabbit hole.

No. Use another query that is a summation, don't do a DSum. The reason is that a pre-defined (i.e. stored) SQL-based query is analyzed by Access when you store it to optimize the operation. A DSum creates a query "on-the-fly" that does not get optimized. There is no reason you could not do a summation query of the UNION query.

Or did I misunderstand your intent there?

 

[Chillendw]

No. Use another query that is a summation, don't do a DSum. The reason is that a pre-defined (i.e. stored) SQL-based query is analyzed by Access when you store it to optimize the operation. A DSum creates a query "on-the-fly" that does not get optimized. There is no reason you could not do a summation query of the UNION query.

Or did I misunderstand your intent there?

For the tables, I'm going to try going back to 3 tables and trying it out in a similar fashion to how you mentioned.

I'm probably thinking too far ahead. For reports or just to show on a form, I would want a running balance based on the ItemName (which includes all of the Item#s). I thought DSum might be the answer. Or should I DLookup to the summation query that you're suggesting?

I could be totally off. Either way, thanks.

 

[Chillendw]

ItemXAct:
ItemID: identifier, foreign key to Items table.
XActDate: date, date (& time if you need it) of transaction
XActQty: integer, items added or removed
XActType: integer or code letter, type of transaction (sale, receipt of new stock, etc.)
XActShipRcpt: integer, foreign key to shipments received (can be 0)
XActSaleRcpt: integer, foreign key to sales receipt

I was wondering what these fields were for. Are you saying I should have 2 other tables for received and sales? That is what I had changed it to, but without this table. I also didn't have these as primary keys in their corresponding tables. Can you help me understand?

Thanks!