Results 1 to 8 of 8
  1. #1
    Gapco1 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    4

    Beginner needs a point in the right direction!


    Hi,

    I'm a beginner using access, having gotten down the theory and best practices to some extent.

    I have 6 tables: Customers, Orders, Products, Order Details, Shipping, Invoices. I have created all the fields and related the tables to one another. Now, if I have one item on an invoice everything works fine. But I didn't allow for multiple items on an invoice which is the case most of the time.

    What is the best way to handle this situation in designing the database? Looking for the simplest solution. Any help would be greatly appreciated!

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The Northwind Traders example that ships with Access has a ton of excellent examples of how to do things. It includes exactly what you are having problems with and uses a Form/SubForm arrangement to handle it.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In addition to RuralGuy's excellent point re the Northwind Traders example:
    Here are a number of free video tutorials related to database design concepts.

    These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation. These videos follow a Customer, Order, and Items theme.

    http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

    http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

    http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

    http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

    http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

    http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

    http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

    http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

    http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

    The first few topics at this site are well worth the read.

    http://www.rogersaccesslibrary.com/forum/topic238.html

    Good luck with your project.

  4. #4
    jameslarsen is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2012
    Location
    Los Angeles, CA
    Posts
    20
    I can try to help you with your specific question regarding multiple items on a single invoice. Can you post the structure of your invoice table? Also, I need to know the difference/purpose of your invoice, order, and order details tables.

    What you will probably want is attributes related to the invoice itself in the invoice table (such as the invoice ID, the associated customer, the date, possibly tax rate, etc.). More than likely you will want some kind of "inventory" or "product" table, which you store identifying information about each item being sold, such as it's name, weight, price, etc. You would then just create a correlative table, using a reference to an item in your inventory, and a specific invoice, and create one record in that table for every item sold on a given invoice. This might be accomplished through the order and order details tables, but I'd need to know their purpose before I could say for sure. Let me know if this helps, or if you have any questions.

    James A. Larsen
    Database Architect, Data Analyst, and Business Intelligence Specialist
    james.larsen42@gmail.com

  5. #5
    Gapco1 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    4
    Thanks a form/subform I think is just what I'm looking for.

  6. #6
    Gapco1 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    4
    Thanks Orange, I usually find those video's very helpful

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Have fun. Post back if you get stuck.

  8. #8
    Gapco1 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jul 2012
    Posts
    4
    Thanks James it definitely helps. I am going to take a few days and figure it out but I also narrowed it down to order and order details table. I'll definitely get back to you if I get stuck. Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need direction and help
    By em815 in forum Access
    Replies: 9
    Last Post: 07-17-2012, 04:13 PM
  2. New to Access, looking for direction!
    By jkenworthy in forum Access
    Replies: 1
    Last Post: 01-20-2012, 01:36 PM
  3. New and need some help and Direction!
    By OlneyFD in forum Access
    Replies: 0
    Last Post: 12-02-2011, 07:08 PM
  4. Replies: 86
    Last Post: 10-07-2011, 06:06 AM
  5. Need Direction
    By sabrish72 in forum Programming
    Replies: 5
    Last Post: 06-08-2011, 09:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums