Stock Transaction Table - Location A to Location B (1 Viewer)

[Lyconal]

I have a database and want to log transactions from location A to location B, so I require 2 records inserted into the transactions table (One to remove the quantity from Location A, and one to add the quantity to Locaion B)

What would be the easiest / best way of acheiving this?

Cheers

 

[DCrake]

Use an update query to reduce the value in A and increase the value in B

 

[Lyconal]

Will that show in a history table aswell though?
As I would like to be able to see the transactions historically.

 

[DCrake]

If you want to keep a history of what has happended then you will need an audit table to view previous transactions. Such as Item Number, Date, source Quantity Source, destination, etc and run an append query to the table.

 

[Lyconal]

Ok - That sounds all great.

Any starting point to do this?

Cheers

 

[DCrake]

Immediately after you have run the update query to move the stock from A to B run an append query to post the transaction details to the transaction historty table. As mentioned you will need the following fields at minimum

ID (PK)
Trans date/Time
Prod Id
Qty
Location A
Location B

Optional:
Date/Time Stamp
UserName
ComputerName


Note the Trans date may not be the same date as the append took place the optional fields are for internal traceability reasons only.

David

 

[Lyconal]

Use an update query to reduce the value in A and increase the value in B

Please do you have any help on what this would look like? (So I can see if it works for me)

Cheers

 

[DCrake]

From your comment sI take it your Access knowledge level is somewhere near the bottom of the ladder There are various ways of doing this, under the curcumstances the easiest way would be for you to create an update query using the query designer. The F1 key is a good place to start.

David

 

[Lyconal]

I am fairly new to access (Did a part of my college course several years ago around it).

Im having trouble getting the fields to 'filter' data properly.

Due to each item being able to have more than one location, I have an item, a location, and a locationitem table.

I need the form to filter only the locations where that item is available for the locations, once the item has been chosen, and then this update query will work fine.

Sorry if I explained myself wrongly.