Results 1 to 7 of 7

Thread: table dilemma

  1. #1
    Join Date
    Sep 2006
    Posts
    60

    Unanswered: table dilemma

    Hi - I'm new on dbforums with a dilemma. I'm on my first project in a new job, dumping data from an Excel sheet that makes up an A/R Billing db. However it has only ONE table right now, with all the historical A/R collections data.
    This is supposed to be a "small" (per my boss) db. Here are the fields:

    ID: autonumber
    ADJ-NUM: date-like but not a date, so text
    CUSTOMER: text
    PROJ. NUM: text
    INVOICE NUM: text
    INVOICE AMT: text - ( indexed )
    INVOICE AMT: currency
    BALANCE DUE ON AGING: currency
    REASON: text
    DATE GIVEN TO A/R: date
    ORGANIZTION: text
    RESOLVED: yes/no
    REASON CODE: text
    COUNT: text not what you think, just 1's
    COLLECTION REP: text
    RTND BY A/R: text
    A/R COMMENTS: memo-type

    Any suggestions on how I should normalize these? Or just leave it one big table to updated every period?

    The collector is assigned a particular Org (there are 4), and has so many customers. So, one collector, many customers? One Org, many collectors?

    Thanks for your input.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you're going to normalize it, think about like fields you would store the same data in the same Table and possibly ways to "group" them with a grouping field in that table (Sometimes duplication on a field in another table in unavoidable but that's the goal you want to achieve (i.e. DONT separate out into separate tables for City, State and Zip - makes your reports a nightmare to return 1 single address!). That's usually the way I look at. Also look at how you're going to report stuff and what they usually want reports on (do you always want to link in a certain table for all your reports? - links to other tables can become costly in time). It's difficult to say how you would specifically normalize it without knowing the business (it's rules) and it's needs. You may get a few suggestions on some posts though.
    Last edited by pkstormy; 09-18-06 at 14:16.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Sep 2006
    Posts
    60
    I think this db is going to be used as a reporting db - that is, for outputting the data back into Excel and Powerpoint. I'm trying to determine how the data is going to be updated; by user entry or importing. Guess it's time to put my Systems Analyst hat on and do some talking to the staff and the boss..also, I need to brush up on my accounting knowledge. Thank you...

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    There's nothing to normalize there... Not unless each record is a single item for the same invoice. It looks like all you have there is totals.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Sep 2006
    Posts
    60
    No, there are different invoices nums, orgs and adj num is a sort-of autonumber. I'm finding out that the data comes from an Excel dump of historical sheets by month as excel reports. They have VLOOKUPS on a particular column. I'm seeing this as just a reporting database.
    I did have Access go through the Table Analyzer, for what it's worth, it gave me a query that I use for the form in datasheet. Thanks, fellas.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You're right Teddy. There doesn't seem to be anything to normalize in the structure he gave us. Guess I didn't look at it close enough.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Sep 2006
    Posts
    60
    Nope, I decided not to normalize.

Posting Permissions

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