1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Creating Access database with multiple tables for query, forms

Discussion in 'Business Applications' started by NAWCC_Mart, Dec 1, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. NAWCC_Mart

    NAWCC_Mart Thread Starter

    Joined:
    Nov 24, 2010
    Messages:
    3
    Good morning!

    Here are my computer specifications:
    OS Version: Microsoft Windows XP Professional, Service Pack 3, 32 bit
    Processor: AMD Athlon(tm) II X2 250 Processor, x86 Family 16 Model 6 Stepping 2
    Processor Count: 2
    RAM: 3327 Mb
    Graphics Card: NVIDIA GeForce 6150SE nForce 430, 512 Mb
    Hard Drives: C: Total - 472278 MB, Free - 447618 MB; D: Total - 4649 MB, Free - 421 MB;
    Motherboard: PEGATRON CORPORATION, 2A99h, 6.01, X312345678
    Antivirus: eTrust ITM, Updated: Yes, On-Demand Scanner: Enabled

    Here are my questions:
    1) I am trying to set up an Access database that takes information from 3 tables (Customer Info, Ad Data, Issue Dates) and makes one form with all information included.
    (I have a "Complete" table with the Customer Info, Ad Data, and Issue dates all included that works well with the form. The problem with this is that this will easily become a huge, unmanageable database.)
    2) I want to be able to update the form (which also updates the tables) as well as being able to pull up past records. (I don't want to use a subform, because the Ad Data are all calculated fields.)
    3) I also want to be able to group each ad by issue date.
    4) The Customer Info table should be the master table. When I try to run a query using Customer Info, Ad Data, and Issue Dates as a record source, I get a blank query (not populated with data).
    5) I am not able to create relationships between the Customer Info, Ad Data, and Issue Date tables for the query. (I want to have a one-to-many relationship; 1 customer, many ads). Access won't let me create a Primary Key for the Ad Data table. When I try to use "Member Number" as my Primary Key I get the following error message: "The changes you requested to the table were not successful as they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again". When I change the "Indexed" field to read "Yes (duplicates okay)" or "No" it saves the change, but inserting the Primary Key makes it default back to "Yes (no duplicates).

    I apologize if this is not clear, I tried to make it as concise as possible. I have been working on this piecemeal (between issue deadlines) since July. . .I have read the other threads on Access databases, tables, relationships, etc. on this forum, but am completely confused when I try to apply it to my own database, because it still doesn't work. :(

    My boss would like this ready to run by December 15th, so I'm under the wire here. Any help would be greatly appreciated!

    Thank you!
     
  2. NAWCC_Mart

    NAWCC_Mart Thread Starter

    Joined:
    Nov 24, 2010
    Messages:
    3
    sorry, forgot to add I'm working in Access 2003
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    NAWCC_Mart, welcome to the Forum.
    It sounds like you are making a common mistake where Master and Subtables are concerned.
    The Linking field in the Subtable should NOT be an Autonumber or even a Key field. It should be a Field Type Number -Long and should store the Autonumber Key field Value from the Master Table.
    Take a quick look at some of the Databases that I have worked with on the forum to see what I mean.
    The Master Table can't have any Duplicates in the Key Field, so does your Member Number field in the Customer Table have any Duplicates?
     
  4. NAWCC_Mart

    NAWCC_Mart Thread Starter

    Joined:
    Nov 24, 2010
    Messages:
    3
    OBP, thank you so much for your quick reply! I am sorry it has taken me so long to answer you - other projects came up at work, and now we're heading into our deadline period. I hope to be able to get back to this in January 2011. Thank you again, and have a Merry Christmas and a Happy New Year! :)
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/965881