Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2010

    Unanswered: transaction tables

    i am trying to develop a db for a suit hire base. what i would like to know is how it is best to keep a track of stock levels. i think i need to have a transactions table, but how do i update it for goods returned/delivered and set up an alert for when there are low stock levels? i am using access 2007. also how would it relate to the delivery note table and the returns table?

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    MSAccess 2007 ships with some fairly good basic wizard templates to do just about anything (in general.) I'm pretty sure one of these templates is close to what you described. If not, I might recommend looking at the Northwinds database that ships with MSAccess (not sure about 2007 though). It has 'transaction' type tables of products sold/bought (and I believe goods returned/delivered.)

    Your question is like asking "How do I build a car?". There are many different ways to design something to meet your requirements.

    My questions to you would be:
    What level of vba coding/MSAcces experience do you have?
    How good are you at designing relational table structures?

    What you described is not a beginners level program. It's also too general of a question without any details on the specific data your going to track to give you a detailed answer. If you have MSAccess vba coding and good relational structure knowledge, then you should think about a basic relational structure with the fields you want to track and how you can utilize vba coding with that structure (since you'll most likely need to write some functions). Then post the structure you've thought about and ask for opinions/recommendations.

    But in general, think about your 'main' data table and the key pieces of information it's going to have. You don't want duplicates in this table when there shouldn't be so you'll need to think about how you're going to establish your primary key and what info should go into relational tables.

    Then think about other relational tables (where if they were in the main data table, would've duplicated records.) You'll want to join these tables to your main table (or even other relational tables) via some key field (I usually just use the autonumber field in the main data table.) Your main table can be a company type table or a transaction type table depending on your needs and how the program is utilized. If it's focused around transactions which are constantly updated, this table may only hold returned/distributed data along with ID fields to relational tables.

    To get more specific with transactions sold/bought and goods returned/delivered, you can either put these types of fields in the same table as the one that holds company data (but won't track history unless you design a routine to write to a history type table), or you can just set it up relationally linking to the Company table via an ID or primary key field (which is usually best). If whatever your tables are which holds your sold/bought or returned/delivered data is going to be 'heavily' used (ie. constantly updated/tapped into for alerts/reminders), you want to think about keeping this table only limited to the necessary data needed to track it's objective and put non-constantly-updating type data in another table.

    Alerts are usually designed via functions (or dlookups). Functions would 'tap' into the transaction table and display an alert when the appropriate conditions in the transaction table are met (ie. you may have a 'flag' type Yes/No field which is updated to true if a value such as selling goods versus in stock goods for a record is > 0.) Functions give you more flexibility than dlookups but you need to know vba coding on how to open and walk through recordsets. A Date type of field will be a big factor since you don't always want to cycle through the entire recordset. You need to think again about how the data is constantly updated if it's going to be. If not and you just want an alert when the user opens the program, you just call the function in your startup form. Functions are extremely powerful and you should have a good level of knowledge utilizing them especially for constant data updates. If there will be constant updates, how you utilize them with your transactions table will be key. Otherwise, a simple alert (ie. call to the function) on the startup form suffices.
    Last edited by pkstormy; 03-23-10 at 22:07.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Mar 2010
    to answer the basic questions here, my knowledge of vba is little and my knowledge of access is medium. i have built a db before it was a less complex scale, so i am familiar with normalisation, relations, fk's and pk's.

    i am interested how in a least complex manner i can keep a record of the stock in hand at a particular date and in the ability to give a low level stock warning. this is why i was attempting a transactions table but i am unsure how to relate it to the rest of the db.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts