Results 1 to 7 of 7
  1. #1
    MissRyan is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    4

    entering new records

    Hello, I am very new to access and having a pretty hard time using it. Basically I need to develop a database for a bunch of test items. For each test item, there are (or will be) 6 alternate versions. Right now I am using the datasheet function and essentially using the sheet just as i would in excel or SPSS. However, I would like to insert a record (or row) in between other records (as new alternate test items get developed I need to enter them into this sheet) but I am finding that when I try to insert a new record (row) it jumps down to the bottom. I will definitely need to move rows around a lot so I need to know if this is possible, and how.



    Alternatively, if someone can recommend a more savvy approach to developing a database to track test items, please advise!! Thanks so much for any input!!

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Welcome to the forum!

    First and foremost, Access is not a glorified spreadsheet. Organizing data in a relational database such as Access requires a different way of thinking and of course, there are rules that must be followed: rules of normalization.

    You first have to analyze your data and determine how the data is related. For example, you say the following:

    For each test item, there are (or will be) 6 alternate versions.
    So, you need a table to hold the basic test information. Each test item will be a record in the table

    tblTest
    -pkTestID primary key, autonumber field (just a field that Access will use to give the record a unique identifier)
    -txtTestName
    (other fields that describe the test)

    You said that a test can have 6 alternate versions; this describes a one(test)-to-many(versions) relationship which is represented by a separate but related table

    tblTestVersions
    -pkTestVerID primary key, autonumber
    -fkTestID foreign key relating the version to the corresponding test in tblTest
    -txtVersionName
    other fields related to the version


    From what you have described, that is about all we can discern at this point.

    Could you provide more information on what business process you are trying to model and from there we should be able to help you flush out the table structure (tables, fields and the relationships between the tables)?

    You may also want to take a look at these links to help you get started:

    Database design Part 1
    Database design Part 2
    Access Basics

  3. #3
    MissRyan is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    4
    Thanks for the warm welcome and the helpful information! Just to clarify - for each original test item, there will be 6 alternate test items, not tests. So for example, a test that has 45 items will eventually have 6 alternate items per original item: 270 test items total. The purpose of the spreadsheet is to be able to track which organization uses which test item. It's pretty confusing..sorry!

    If I think about how my data is related, I would say that the original item: Analogies, for example, which I have recorded as "A1" will be related to each of it's alternate forms: A1.1, A1.2, A1.3, A1.4, A1.5, A1.6. The second set of Analogies is recorded as A2 and it's alternate versions will be A2.1, A2.2, ...A2.6. On a separate "object" or tab, I have my Multiplication items: M1 and it's alternate items will be M1.1, M1.2, ....M1.6. M2..etc. Does this make sense?

    When you say a "separate but related table" does this mean a new "object" or is it something different?

    Thanks again and I will check out the links you suggested!

  4. #4
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    So for example, a test that has 45 items will eventually have 6 alternate items per original item: 270 test items total. The purpose of the spreadsheet is to be able to track which organization uses which test item.
    So you have a test and each test has items and each item can have many alternate items.

    First a table to to hold the tests
    tblTests
    -pkTestID primary key, autonumber
    -txtTestName

    Since the items and the alternate items are all items, they belong in 1 table (rule:like data in 1 table), so a record for each item in tblItems

    tblItems
    -pkItemID primary key, autonumber
    -txtItemName

    An item can be related to other items (the alternates)

    tblItemAlternates
    -pkItemAltID primary key, autonumber
    -fkPItemID foreign key to tblItems (the main or primary item)
    -fkSItemID foreign key to tblItems (the alternate or secondary item)

    Since you cannot have two fields in a table with the same name, I added P and S to distinguish them.


    A test can have many test items

    tblTestItems
    -pkTestItemID primary key, autonumber
    -fkTestID foreign key to tblTest
    -fkItemID foreign key to tblItems

    Of course you will need a table to hold the organizations

    tblOrganizations
    -pkOrgID primary key, autonumber
    -txtOrgName

    Since an organization can use many test items and I assume that a test item can be used by more than one organization, you have a many-to-many relationship which is handled with a junction table

    tblOrganizationItems
    -pkOrgItemID primary key, autonumber
    -fkOrgID foreign key to tblOrganizations
    -fkItemID foreign key to tblItems

  5. #5
    MissRyan is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    4
    Thanks, this was helpful. As I am playing around, I found the subdatasheet option and i am wondering if I could have the main items in one table and the 6 alternates in a corresponding subdatasheet.

    Would that work? Thanks again! And apologies for my ignorance!

  6. #6
    MissRyan is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    4
    "Since the items and the alternate items are all items, they belong in 1 table (rule:like data in 1 table)"

    Does this mean that tblItems tblItemAlternates belong in one or two separate tables?

    Thanks again

  7. #7
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    All items would be in tblItems. The function of tblItemAlternates is to associate an item with its alternates.

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

Similar Threads

  1. entering one value but storing another
    By greatfallz in forum Forms
    Replies: 1
    Last Post: 06-09-2011, 07:23 AM
  2. Entering Criteria in a Query
    By wdrspens in forum Queries
    Replies: 4
    Last Post: 04-13-2011, 04:44 PM
  3. Entering more than one item when prompted?
    By 10 Gauge in forum Reports
    Replies: 10
    Last Post: 03-23-2011, 08:47 AM
  4. Entering Text in Combo Form
    By Bobt in forum Forms
    Replies: 2
    Last Post: 10-22-2010, 03:53 AM
  5. Replies: 2
    Last Post: 03-16-2009, 12:19 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