Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel to Access (2k)

    I'm considering switching from xl to access for a budget project.
    My current xl form is set up as in the following table.
    Requirements:
    Column 1 &2 are not user-defined (locked)
    All other colums are optional: numbers and text (Exp cats may or may not have numbers or may be blank or out of sequence numerically. There may or may not be sub items)
    All item amounts within Obj Code 30 are totaled for each year (same for 40, 50....)

    This is all quite simple in xl but I am new to Access so I need a bit of assistance with structuring this.
    Would it be preferable to have one table for Expenses or separate ones for each obj code and/or Exp Cat?
    What I would like is a cascading Entry form -- enter 30 in box1 and a sub form opens allowing/showing only items 31-39; when 32 is entered in a combo box another subform opens permitting entry of Deisel, Propane, Gasoline etc.

    Any suggestions in helping me get started?
    Thanks

    <table border=1><tr><td align=right>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel to Access (2k)

    If you're new to Access, run to the bookstore and get a copy of Access Database Programming & Design, from O'Reilly. It has a very good section on data normalization, which is what you need to do. I see at least 4 tables in your data: ObjCode, ExpCat, ExpItem, Budget. This assumes that ObjCode and ExpCat are lookup tables that are related. ExpItem is related to ExpCat, and Budget is the table where you store the actual numbers.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel to Access (2k)

    Charlotte
    thanks for the suggestions.
    Can you give me a quickie on data normalization?

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Excel to Access (2k)

    There really isn't such a thing as a quickie on data normalization - even the guru's have't completely agreed on what it right. Charlotte's suggestion on reading material is excellent, and most Access books at the intermediate level attempt to cover it. In a nutshell, it means breaking records apart into logical collections so that data isn't repeated in different places in your system - which is a pretty abstract idea. But it goes well beyond that as well. A simple example would be a person record which included multiple addresses. In a normalized design, the addresses would be placed in a separate table with a pointer to a person record. You might find this article in SQL Magazine of interest as a web resource, but it isn't terribly complete. So go get the book she suggests.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel to Access (2k)

    Here's a knowledgebase article that might help: ACC2000: Database Normalization Basics.
    Charlotte

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Excel to Access (2k)

    If interested, the O'Reilly web site has a sample chapter from the aforementioned Access Database Design & Programming, 3rd Edition by Steve Roman. Link:

    Chapter 4 Database Design Principles

    This chapter discusses some of the principles of database design & normalization. (I have the actual book & also recommend it.)

    HTH

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel to Access (2k)

    But in the second scenario, you can have two records in the Order Hdr table with the same OrderHdrID, but different CustID's. Is that acceptable?

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Excel to Access (2k)

    Thanks Hans

    I forgot to mention that OrderHdrID is an autonumber field. Also OrderLineID in the Order Line table is an autonumber field as well.

    So does this make it ok?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel to Access (2k)

    The idea of the primary key is to provide a unique identifier for the records. An AutoNumber field does that all by itself, I see no point in adding another field. And I didn't understand your remark "if you had put the order to a wrong customer you would merely change the CustID in the OrderHdr table". I'm sure I'm dense, but I fail to see what that has to do with CustID being part of the primary key or not.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Excel to Access (2k)

    <P ID="edit" class=small>(Edited by patt on 14-Jan-04 21:00. Changed 2nd to 1st)</P>Tell me Charlotte, which is the better design of the following 2 scenarios?
    3 tables are Customer, Order Header, Order Lines.
    Scenario 1.
    Table Fields Primary Key fields
    Customer CustID, Name, etc CustID
    Order Hdr OrderHdrID, CustID, etc OrderHdrID
    Order Lines OrderLineID, OrderHdrID, etc OrderLineID

    Scenario 2:
    Table Fields Primary Key fields
    Customer CustID, Name, etc CustID
    Order Hdr OrderHdrID, CustID, etc CustID, OrderHdrID
    Order Lines OrderLineID, OrderHdrID, etc CustID, OrderHdrID, OrderLineID

    I like the 1st scenario because it keeps the primary key in all cases to a minimum and if you had put the order to a wrong customer you would merely change the CustID in the OrderHdr table.

    What do you think?

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Excel to Access (2k)

    I made a mistake in my previous post, I meant that I preferred the 1st post not the 2nd. I have changed the previous post to reflect this.

    I used to always use the 2nd scenario as the preferred way of defining tables that were related.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel to Access (2k)

    OK, I prefer the 1st scenario too.

  13. #13
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel to Access (2k)

    This <!post=handout,185086>handout<!/post> I prepared for my Students might help with the basics as well.
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel to Access (2k)

    Scenario one is fully relational. You don't need CustID in the OrderLines table if you use the OrderHdrID, since CustID is already in the OrderHdr table. However, you don't change values in the tables, even to correct them. You use forms as an interface because forms are programmable and can keep you from making dumb mistakes like trying to assign an order to the wrong customer.
    Charlotte

Posting Permissions

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