Posted on 2007-10-04
Last Modified: 2012-06-21

Hello All,

I am trying to import these fields into an Access DB.

The trick lies in the fact that I am attempting to insert one instance of 'FullName' and 'Email' Fields, while there will be multiple (up to 50) occurences of the 'Location' and 'Material' fields for that one row. Right now it is creating a seperate record for each Name, Email, Location, Material.

Does anyone know how I can group/combine all the Location, Material to force all into a single instance for each Name/Email??

One record should look like this:

FullName    Email              Location                   Material
-------- ---|--------------------- |----------------------|-------------------------------
John Doe |    |  Building A            |   Wood  
                |                            |  Building B             |   Steel
 <cfquery name="HR_import" datasource="Material">
    INSERT INTO HR_NoEmail (FullName, Email, Location, Material)
       <cfqueryparam value="#FullName#" cfsqltype="CF_SQL_VARCHAR">,
       <cfqueryparam value="#Email#" cfsqltype="CF_SQL_VARCHAR">,
       <cfqueryparam value="#Location#" cfsqltype="CF_SQL_VARCHAR">,
       <cfqueryparam value="#Material#" cfsqltype="CF_SQL_VARCHAR">,

Question by:zlinst28
    LVL 73

    Accepted Solution

    depends on your database, and how you want to see and work with the data.

    if your db supports it,  you could create a collection type column for location and material where each element is one member of your location list or material list.


    you could also concatenate the locations and materials into long strings prior to inserting


    go ahead and load the data as individual rows and use a view to concatenate the location and materials into long strings (look out for sql column length limits though)


    load data as individual rows and use a view to group the locations and materials into collections
    LVL 15

    Assisted Solution

    Looks to me to be a design issue. This should be two tables. one containing a primary key, FullName and Email. The second should contain a primary key, a foreign key back into the first, location and material. What you are showing above is NOT one record, it is two.

    To keep it as a single row, you'd need to do what sdstuber suggested and concatenate the locations and materials together, but then you run into the problem of breaking them apart again should you need to do so. COncatenating them would give you something like:

    FullName    Email              List
    -------- ---|-------------------|----------------------------------------------------------
    John Doe |    |  Building1  Material1, Building1  Material2, Building1  Material3, Building2  Material1, Building2  Material2, Building2  Material4, Building3  Material5, Building3  Material6...

    To do so, you'd still need to load the data into some kind of staging table. Besides, if you are doing this in Access, I don't know that it will give you the ability to write the functions required to concatenate the data. What you are wanting to do might be outside the scope of Access (I'm more of a SQL Server guy).
    LVL 1

    Expert Comment

    Forced accept.

    Community Support Moderator

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now