Solved Stock Database Help (1 Viewer)

[andatabase]

Hi All,
I need some assistance building a stock database.

Premise: To monitor movements in stock on a period by period basis. The movements are then used to create journals to ‘revalue the stock’ in our accounting system.
• Users use the previous valuations to review and update the stock
• Once reviewed, they are submitted to management for approval
• Once approved, the accountants will process the journals.

Problem areas
Monitoring previous and current data side by side ( Site A, Stock B, Date, Unit Price, Total Value)
Unit Prices (if I update the prices in a table, all records will be retrospectively updated). I would like for prices to remain fixed for previous periods.

Thanks

 

[Ranman256]

you want a timestamp field:
TICKER, PRICE, TIME

(dateTime "7/22/15 11:25 am")
This way you have an historic account of the prices.

 

[andatabase]

Ok, that's great.
So what would i need to do on the form side, so that only the latest prices are displayed?

thanks in advance.

 

[JLCantara]

@AnDataBase: numerous thinks need to be clarified.

Premise: To monitor movements in stock on a period by period basis.

How are the periods defined?

• Users use the previous valuations to review and update the stock

How does valuation influences 'stock'?

Monitoring previous and current data side by side ( Site A, Stock B, Date, Unit Price, Total Value)

If the periods are defined, where is the problem?

Unit Prices (if I update the prices in a table, all records will be retrospectively updated).

Is valuation depending on unit prices? How?

I would like for prices to remain fixed for previous periods.

If periods are defined, that's no problem.

 

[Uncle Gizmo]

I think you would also benefit from reading Allen Browns inventory example here:-

http://allenbrowne.com/appinventory.html

 

[andatabase]

JLCantara, in response to your queries;

How are the periods defined?

The database would be reviewed every six months, so March & September

How does valuation influences 'stock'?
Previous valuation prompts the use to review stock at a particular site, and then provides an update.

If the periods are defined, where is the problem?
I need to be able to display the data in a form, in such a way that March's data can be compared to September's data in one row, for example.

So, for instance for my London Site, i had 2 items in stock in March. In September the stock was 6, so there is a movement of 4. I need this to be displayed as a row. Could this be a crosstab query?

Is valuation depending on unit prices? How?
The valuation of stock is the quantity ( of stock)* Unit prices.

This should be a relatively simple inventory database, which measures movement. Perhaps I'm over complicating it!

 

[AccessBlaster]

you want a timestamp field:
TICKER, PRICE, TIME

(dateTime "7/22/15 11:25 am")
This way you have an historic account of the prices.

@Ranman256, how would you keep this field from being over written? Every time the record is updated this value changes wiping out the historic record.

 

[andatabase]

Hi,
I still need help on this. Your advice would be much appreciated!

 

[Ranman256]

You can have it overwrite , but you can have it add continuous new records.
thats what the DATE/TIME field is for.
Every hour you download the ticker, it gets timestamp of NOW()

 

[jdraw]

andatabase,

What exactly is the issue(s) at this time?
Did you read and understand the Allen Browne article that Tony/Uncle Gizmo suggested?

 

[andatabase]

andatabase,

What exactly is the issue(s) at this time?
Did you read and understand the Allen Browne article that Tony/Uncle Gizmo suggested?

Thanks, was able to come up with a solution many years ago. Forgot to reply!

 

[Gasman]

Thanks, was able to come up with a solution many years ago. Forgot to reply!

This has to be a record?