Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2011
    Posts
    3

    Unanswered: Multiple Records in one field

    Hello everyone,

    I have a bit of a problem that I have been unable to resolve. I am currently using Microsoft Access 2007. The database is an asset management database and it currently has two tables and one query; tbl_main, tbl_tamn, and qry_main.

    tbl_main holds information such as serial numbers, current status, location, and other assorted information.
    tbl_tamn holds identifying information for our assets.
    qry_main combines these two tables into one sheet.

    for instance:
    tbl_tamn is built as:
    stock number, part number, type of gear
    tbl_main is built as:
    serial number, location, status
    qry_main is built as:
    stock number, part number, type of gear, serial number, location, status, etc...

    Each piece of gear in the database is the end item. I need to add a field in the main table called "Components with Serial Numbers". This field will be used to track parts for that item; i.e Hard drive serial numbers, hard drive part numbers, and hard drive manufacturer, for the computers.
    End items have a different amount of components.
    I also need this to be mail-merge-able so that it can be pushed to word. (I tried a lookup-field and it only merged a single number instead of all items)

    I have tried lookup-fields and memo fields; lookup fields don't yield the proper results and memo fields are too cumbersome to use.
    What is the best course of action to set this up?

    Thanks in advance for any assistance.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This is a very bad idea. What you try to do is against the rules of databases normalization (among others: "A column must contain one and only one data item", "The same data item can never be duplicated in several columns "). See: The Relational Data Model, Normalisation and effective Database Design, Fundamentals of Relational Database Design -- r937.com, An SQL Introduction - Normal Form

    The normal way of handling such cases is through association tables (bridge tables) creating many-to-many relationships. See: Database Design - Many-to-many

    If you need to create compound data this can be done through SQL Statements or through functions in cases where SQL cannot provide the desired result.
    Have a nice day!

  3. #3
    Join Date
    Apr 2011
    Posts
    3

    hey

    Thanks for the reply,

    I am not really a database programmer, so I do not understand how to write sql statements, or fully understand relationships. Using access 2007, if I create a relationship, I create it using tables, correct? Or can it be done using queries? Also, once I create the relationships, will it auto-populate the information from one table into other tables? I am not exactly certain how this all works. If you could explain it to me, that would be appreciated.

    Respectfully,

    coxne

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by coxne View Post
    ... I am not exactly certain how this all works. If you could explain it to me, that would be appreciated.
    coxne
    This is precisely the topic of the pages for which I posted the links in my previous answer. Shortly:

    1. If you amalgamate several pieces of information into one field and that later on you need to access and use each piece distinctly, you'll have to write non-standard functions to split the compound data into its constitutive elements. Depending on how those elements where agregated, such functions can be complex and will always degrade more or less the performance of the application using them.

    2. If the same piece of information is stored in several places in the database (repetition or redundancy) nothing can garantee that all copies will remain synchronized (at least in Access). Sooner or later you'll end up with some contradictory information (e.g. address of a customer updated in one place but not in the other, etc.).
    Have a nice day!

  5. #5
    Join Date
    Apr 2011
    Posts
    3

    Shinndho

    Thank you so much for the reply.

    I was able to get the many-to-many relationships working, and I was able to create a form that will reflect the information the way I am planning it to look. My final questions though are; I need to print this on a company letterhead. Using Mail merge, it was easy until it required the many-to-many information. Is there a way to use the split-form on a company letterhead using mail-merge or something of the like?

    Thank you in advance.

    Coxne

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Yes it's possible, of course.

    Once you have the data properly stored in a normalized way: data tables + association tables (aka bridge tables or link tables) + Relationships (aka Schemas or Diagrams), you can create Queries that will associate pieces of data from several tables together, eventually filtering (select only a subset of data based on some criteria) and/or sorting-ordering the resulting data set. These queries will then be used as data sources for populating Forms and Reports objects. Forms and Reports can go further on processing and transforming data they receive from their data source.

    Generally speaking, a Form is used to process data: Change the contents of the rows in the tables (Edit function), add new rows into the tables (Insert function), delete rows from the tables (Delete function), etc. and often allow the user to perform actions such as creating new queries that will be used as data sources for other forms or reports, opening other forms or reports, performing complex computations, closing the application, etc. Your imagination is the limit

    A Report creates a document (or a set of documents) that presents the processed data from its data source on pages that can be printed, emailed, converted to pdf documents, etc. While designing a Report, you can select various polices, sizes, colours and other character attributes, dispose the different objects that will display the data (mainly text boxes) is such a way that they fit the blanks in a pre-printed document (letterhead...).

    The whole mailmerge process can be done in Access, though some information (addresses for instance) can be retrieved from other sources such as a database server (MS SQL Server, Oracle, MySQL...), an Outlook address book, the Exchange section of the Active Directory in a Domain and more generally from any ODBC data source that Access can use (they are many!).
    Have a nice day!

Posting Permissions

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