Ad:

MS Access and FileMaker Pro Discussion Thread
Marked Solved
Views: 963

Apr 28th, 2009

inventory db

Expand Post »

I'm in the planning stages of an inventory db. The part I can't figure out is how to design the database as far as changing costs go. If I have a cost associated with an item, how do I handle when the cost changes? I know I need to have an effective date with each cost, but all items won't have the costs changing at the same time.

I appreciate any help or suggestions.

Thanks,
Mary

Similar Threads

Need help C++ inventory project in C++
Inventory Control Database Design (Costing Methods) in Database Design
MS Access DATABASE HELP!! composite keys and inventory in MS Access and FileMaker Pro
Recommendation for Network Inventory tool in Networking
PHP-MySQL: Registration, Leave Request, Inventory Information, etc... in PHP

marymi

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Apr 2009

Permalink

Apr 28th, 2009

Re: inventory db

You should have a seperate table which holds your costs and is referenced to the item table.

Items
--------
id
item_name
Desc
etc.

costs
----------
id
items_id
Costs
effective_date
expiry_date

When you need to update a cost you set the expiry_date for the current record and insert a new record in costs for the items_id. This way you will also have historical data for your changing costs. When referencing the costs you just have to select the active record for the item.

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Apr 28th, 2009

Re: inventory db

Thanks. That makes sense, but I'm a little fuzzy about the details. (Let me preface this by saying I'm reasonably experienced with access, but I'm trying to learn filemaker and would rather develop it in filemaker)

How would I call up the correct cost?

Appreciate your help.

Mary

marymi

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Apr 2009

Permalink

Apr 28th, 2009

Re: inventory db

I have never used filemaker but it is a relational db so their is most likely either a sql window or graphic sql designer like in acccess.

If there isn't you will have to find out how to join tables in filemaker, then join the items table to the costs table where the expiry date is null.

Simple sql

sql Syntax (Toggle Plain Text)
SELECT a.Item_name, b. Costs
FROM items a, costs b
WHERE a.id = b.Item_id
AND b.expiry_date IS NULL
Select a.Item_name, b. Costs
From items a, costs b
Where a.id = b.Item_id
and b.expiry_date is null

Sorry I couldn't help with the filemaker syntax, there might be someone on here that knows the product.

Last edited by peter_budo; May 6th, 2009 at 3:11 pm. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

cgyrob

Reputation Points: 91

Solved Threads: 18

Junior Poster

Offline

125 posts
since Sep 2008

Permalink

Apr 28th, 2009

Re: inventory db

Thanks! I didn't think of the is null part!

Regards,
Mary

marymi

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Apr 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS Access and FileMaker Pro Forum Timeline: Query Criteria issue and Emailing with Access 07

Next Thread in MS Access and FileMaker Pro Forum Timeline: BD remains open after SELECT

DaniWeb Message

Cancel Changes

User Name
Password