How to set up table

[Amelia]

I have a problem I am not sure how to attack. We have 1 item that we need to
track how many we order and how many we sell. I am thinking of setting up a
"purchased" table and "sold" table. I am not sure if this is the way to go
though.

What I need to be able to do is record how many books we buy (for example we
will buy 300 books). As we sell them or distribute them I need it to subtract
from our begining number. I am just not sure how to do this. If anyone has a
suggestion I would appreciate it. I am trying to make it simple, but I have
found it is never as simple as I expect!

I am trying to get ideas from the current inventory template available
online, but that is too in depth for our purposes.

Thanks for any help!

 

[Steve]

If all you want to do is track total books and not individual books, one
table is sufficient:
TblBookTransaction
BookTransactionID
TransactionDate
Sold (Yes/No)
BookQuantity

For Sold use a checkbox. For purchases do not check. For sales, check.

Steve
(e-mail address removed),

 

[KARL DEWEY]

What other information do you need to record/track besides quanity on-hand?
Buy_Date
Rec_Date
Sell_Date
Buy_Price
Sell_Price
Sold_To
Buy_From
etc?

 

[Amelia]

Well I have been testing this. I have Transation Type table, Inventory
Transactions Table, Inventory Items Table.

Now I am trying to get a calcuated field so I can see how many books I have
actually in stock.

(I ended up having 4 different books in the inventory since I need to know
the location and gave them each a name that I know where they are located.)

So I created a query and then put in this:

Actual Quantity: IIf([Transaction Types].[Add/Remove]="Addition",[Inventory
Transactions]![Quantity],-([Inventory Transactions]![Quantity]))

Put it keeps asking me for Inventory Transactions!Quanity.

I want it to automatically put in the quanity that is there for each line
and then have a total row that will sum the actual quanity field. What am I
doing wrong?

 

[KARL DEWEY]

Actual Quantity: IIf([Transaction Types].[Add/Remove]="Addition",[Inventory
Transactions]![Quantity],-([Inventory Transactions]![Quantity]))
What kind of query did you put this in?

You did not answer any of my questions.
--
Build a little, test a little.

Well I have been testing this. I have Transation Type table, Inventory
Transactions Table, Inventory Items Table.

Now I am trying to get a calcuated field so I can see how many books I have
actually in stock.

(I ended up having 4 different books in the inventory since I need to know
the location and gave them each a name that I know where they are located.)

So I created a query and then put in this:

Actual Quantity: IIf([Transaction Types].[Add/Remove]="Addition",[Inventory
Transactions]![Quantity],-([Inventory Transactions]![Quantity]))

Put it keeps asking me for Inventory Transactions!Quanity.

I want it to automatically put in the quanity that is there for each line
and then have a total row that will sum the actual quanity field. What am I
doing wrong?

What other information do you need to record/track besides quanity on-hand?
Buy_Date
Rec_Date
Sell_Date
Buy_Price
Sell_Price
Sold_To
Buy_From
etc?

 

[Amelia]

Sorry, I sort of figured out what I need. I am going to finish testing these
tables I created and see if I can get them to do what I want. Thanks for your
help and if I have more questions related to this I will post them.

This site is so awesome and I love all the help! Thanks again!

Actual Quantity: IIf([Transaction Types].[Add/Remove]="Addition",[Inventory

Transactions]![Quantity],-([Inventory Transactions]![Quantity]))
What kind of query did you put this in?

You did not answer any of my questions.
--
Build a little, test a little.

Well I have been testing this. I have Transation Type table, Inventory
Transactions Table, Inventory Items Table.

Now I am trying to get a calcuated field so I can see how many books I have
actually in stock.

(I ended up having 4 different books in the inventory since I need to know
the location and gave them each a name that I know where they are located.)

So I created a query and then put in this:

Actual Quantity: IIf([Transaction Types].[Add/Remove]="Addition",[Inventory
Transactions]![Quantity],-([Inventory Transactions]![Quantity]))

Put it keeps asking me for Inventory Transactions!Quanity.

I want it to automatically put in the quanity that is there for each line
and then have a total row that will sum the actual quanity field. What am I
doing wrong?

What other information do you need to record/track besides quanity on-hand?
Buy_Date
Rec_Date
Sell_Date
Buy_Price
Sell_Price
Sold_To
Buy_From
etc?

--
Build a little, test a little.


:

I have a problem I am not sure how to attack. We have 1 item that we need to
track how many we order and how many we sell. I am thinking of setting up a
"purchased" table and "sold" table. I am not sure if this is the way to go
though.

What I need to be able to do is record how many books we buy (for example we
will buy 300 books). As we sell them or distribute them I need it to subtract
from our begining number. I am just not sure how to do this. If anyone has a
suggestion I would appreciate it. I am trying to make it simple, but I have
found it is never as simple as I expect!

I am trying to get ideas from the current inventory template available
online, but that is too in depth for our purposes.

Thanks for any help!