Prev: Queries to reduce a set of data pairs
Next: what's your favorite windows
From: SD707 on 19 May 2010 18:56
Hi,
I am a new user of Access 2007. I am creating a database to track inventory
quantities by locations. I have a Products table and a Locations table, and I
am wondering what is the best way to set up a Transactions table. We have
three types of transactions:
1. Move products from one location to another.
2. Introduce new products to a location.
3. Deplete products from locations.
We would like to keep a record of transactions, by transaction ID and date.
But the more important function will be to report on current Product
quantities by Location.

What fields should I include in the Transactions table, and with what
relationship types to the other two tables? Do I need more than one
Transaction table? Should I put Add and Remove quantities in separate columns?

Thanks for your help!
From: KARL DEWEY on 19 May 2010 19:27
>>What fields should I include in the Transactions table,
You can find templates on the web and Northwind should be adaptable for you.
I would have the following fields --
TransActID - autonumber - primary key
ProductID - foreign key
LocationID - foreign key
TransDate - DateTime
Action - (Move, Intro, Delete, and Inventory)
QTY - number
ALTLocationID - foreign key
ALTUpDated - Yes/No

Using Inventory to correct and bookkeeping and losses. Current balance from
last inventory.

>>and with what relationship types to the other two tables?
One-to-many with referential integerty and cascade update.

Do I need more than one Transaction table?
No, but will need an append query to create a record for ALTLocation on moves.

>>Should I put Add and Remove quantities in separate columns?
Yes, without sign, the action determines plus or minus.

--
Build a little, test a little.


"SD707" wrote:

> Hi,
> I am a new user of Access 2007. I am creating a database to track inventory
> quantities by locations. I have a Products table and a Locations table, and I
> am wondering what is the best way to set up a Transactions table. We have
> three types of transactions:
> 1. Move products from one location to another.
> 2. Introduce new products to a location.
> 3. Deplete products from locations.
> We would like to keep a record of transactions, by transaction ID and date.
> But the more important function will be to report on current Product
> quantities by Location.
>
> What fields should I include in the Transactions table, and with what
> relationship types to the other two tables? Do I need more than one
> Transaction table? Should I put Add and Remove quantities in separate columns?
>
> Thanks for your help!
From: Jeanette Cunningham on 20 May 2010 04:52
With this part of the answer

>>Should I put Add and Remove quantities in separate columns?
Yes, without sign, the action determines plus or minus.

That should read

>>Should I put Add and Remove quantities in separate columns?
No, the quantities all go in the same column (quantity) without sign, the
action determines plus or minus.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

"KARL DEWEY" <KARLDEWEY(a)discussions.microsoft.com> wrote in message
news:0B7FEBDC-9675-487E-A970-3AA7D2957FAE(a)microsoft.com...
>>>What fields should I include in the Transactions table,
> You can find templates on the web and Northwind should be adaptable for
> you.
> I would have the following fields --
> TransActID - autonumber - primary key
> ProductID - foreign key
> LocationID - foreign key
> TransDate - DateTime
> Action - (Move, Intro, Delete, and Inventory)
> QTY - number
> ALTLocationID - foreign key
> ALTUpDated - Yes/No
>
> Using Inventory to correct and bookkeeping and losses. Current balance
> from
> last inventory.
>
>>>and with what relationship types to the other two tables?
> One-to-many with referential integerty and cascade update.
>
> Do I need more than one Transaction table?
> No, but will need an append query to create a record for ALTLocation on
> moves.
>
>>>Should I put Add and Remove quantities in separate columns?
> Yes, without sign, the action determines plus or minus.
>
> --
> Build a little, test a little.
>
>
> "SD707" wrote:
>
>> Hi,
>> I am a new user of Access 2007. I am creating a database to track
>> inventory
>> quantities by locations. I have a Products table and a Locations table,
>> and I
>> am wondering what is the best way to set up a Transactions table. We have
>> three types of transactions:
>> 1. Move products from one location to another.
>> 2. Introduce new products to a location.
>> 3. Deplete products from locations.
>> We would like to keep a record of transactions, by transaction ID and
>> date.
>> But the more important function will be to report on current Product
>> quantities by Location.
>>
>> What fields should I include in the Transactions table, and with what
>> relationship types to the other two tables? Do I need more than one
>> Transaction table? Should I put Add and Remove quantities in separate
>> columns?
>>
>> Thanks for your help!


From: NoellaG via AccessMonster.com on 20 May 2010 05:46
HI,

I have been a warehouse engineer for several years and found that the
classical approach still works best:
having the tables:

tblProducts
tblPackings (usually 3 levels are enough: pallets (several types)/large
packing like large boxes, bags, drums,.../small packing like small boxes
placed in the large ones, sacs)
tblProductsPackings : which product can come in which bags
tblPurchaseOrders (movements in)
tblPurchaseOrderLines
tblDeliveryOrders (movements out)
tblDeliveryOrderLines
tblLocations
tblStock: what is stored where, this is a table that's filled automatically
with each movement. You need this to now what is stored where and to avoid
tedious calculations using in-out movements.
tblStockMovements (in: when the received goods are physically given a place,
out: when order picking, internal movements like repackings, damaged goods, ..
.)
tblStockMovementLines

Might have forgotten some tables, but this will set you on your way.

greetings
NG



SD707 wrote:
>Hi,
>I am a new user of Access 2007. I am creating a database to track inventory
>quantities by locations. I have a Products table and a Locations table, and I
>am wondering what is the best way to set up a Transactions table. We have
>three types of transactions:
>1. Move products from one location to another.
>2. Introduce new products to a location.
>3. Deplete products from locations.
>We would like to keep a record of transactions, by transaction ID and date.
>But the more important function will be to report on current Product
>quantities by Location.
>
>What fields should I include in the Transactions table, and with what
>relationship types to the other two tables? Do I need more than one
>Transaction table? Should I put Add and Remove quantities in separate columns?
>
>Thanks for your help!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1

From: KARL DEWEY on 20 May 2010 11:16
Thanks, I was in a hurry.
--
Build a little, test a little.


"Jeanette Cunningham" wrote:

> With this part of the answer
>
> >>Should I put Add and Remove quantities in separate columns?
> Yes, without sign, the action determines plus or minus.
>
> That should read
>
> >>Should I put Add and Remove quantities in separate columns?
> No, the quantities all go in the same column (quantity) without sign, the
> action determines plus or minus.
>
>
> Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
>
> "KARL DEWEY" <KARLDEWEY(a)discussions.microsoft.com> wrote in message
> news:0B7FEBDC-9675-487E-A970-3AA7D2957FAE(a)microsoft.com...
> >>>What fields should I include in the Transactions table,
> > You can find templates on the web and Northwind should be adaptable for
> > you.
> > I would have the following fields --
> > TransActID - autonumber - primary key
> > ProductID - foreign key
> > LocationID - foreign key
> > TransDate - DateTime
> > Action - (Move, Intro, Delete, and Inventory)
> > QTY - number
> > ALTLocationID - foreign key
> > ALTUpDated - Yes/No
> >
> > Using Inventory to correct and bookkeeping and losses. Current balance
> > from
> > last inventory.
> >
> >>>and with what relationship types to the other two tables?
> > One-to-many with referential integerty and cascade update.
> >
> > Do I need more than one Transaction table?
> > No, but will need an append query to create a record for ALTLocation on
> > moves.
> >
> >>>Should I put Add and Remove quantities in separate columns?
> > Yes, without sign, the action determines plus or minus.
> >
> > --
> > Build a little, test a little.
> >
> >
> > "SD707" wrote:
> >
> >> Hi,
> >> I am a new user of Access 2007. I am creating a database to track
> >> inventory
> >> quantities by locations. I have a Products table and a Locations table,
> >> and I
> >> am wondering what is the best way to set up a Transactions table. We have
> >> three types of transactions:
> >> 1. Move products from one location to another.
> >> 2. Introduce new products to a location.
> >> 3. Deplete products from locations.
> >> We would like to keep a record of transactions, by transaction ID and
> >> date.
> >> But the more important function will be to report on current Product
> >> quantities by Location.
> >>
> >> What fields should I include in the Transactions table, and with what
> >> relationship types to the other two tables? Do I need more than one
> >> Transaction table? Should I put Add and Remove quantities in separate
> >> columns?
> >>
> >> Thanks for your help!
>
>
> .
>
 | 
Pages: 1
Prev: Queries to reduce a set of data pairs
Next: what's your favorite windows