Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2016
    Posts
    3

    Question Unanswered: How to Export Empty Multivalue Fields?

    I'm trying to export data from Access 2010 but I'm a newbie -- learning as I go.

    The problem is that it won't pull records that don't have keywords (multiple values) or email addresses (infinite values).

    Click image for larger version. 

Name:	keyword.png 
Views:	11 
Size:	27.6 KB 
ID:	16829 Click image for larger version. 

Name:	email.png 
Views:	6 
Size:	22.1 KB 
ID:	16828

    So how can I make Access stop being so snobby and pull up records with missing keywords/emails?

    Or is there a better solution?

    Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Is there a better solution?
    Yes, design the schema yourself.
    Multivalue fields are an abomination in a relational db.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Also, you're storing multiple atomic values into a Memo Field! One of the Cardinal rules to having a well designed, normalized database is that no single field will contain more than one individual piece of data. Data stored in Memo fields should be restricted to "notes" or narrative type information, such as a salesman's notes on a customer visit or a doctor's progress notes on a patient.

    Threads abound, here and elsewhere, about the horrors of using Memo Fields, but working primarily in a Heath Care environment, I've used Memo fields extensively for a number of years, without ever having a problem. The secret is to follow one single rule:

    Never, never, never place data in a Memo field if there any possibility that you will ever need to search, sort, parse or in any other way manipulate the data...and this includes using it to retrieve Records, such as you are attempting!

    Because many Queries perform some of these functions, you also have to take care in using them in Queries or they will be truncated to 255 characters.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Mar 2016
    Posts
    3

    Question

    Quote Originally Posted by healdem View Post
    Is there a better solution?
    Yes, design the schema yourself.
    Multivalue fields are an abomination in a relational db.
    Is it too late to change the design once the data's already in there, though? It seems dangerous.

  5. #5
    Join Date
    Mar 2016
    Posts
    3

    Question

    Quote Originally Posted by Missinglinq View Post
    Also, you're storing multiple atomic values into a Memo Field! One of the Cardinal rules to having a well designed, normalized database is that no single field will contain more than one individual piece of data. Data stored in Memo fields should be restricted to "notes" or narrative type information, such as a salesman's notes on a customer visit or a doctor's progress notes on a patient.

    Threads abound, here and elsewhere, about the horrors of using Memo Fields, but working primarily in a Heath Care environment, I've used Memo fields extensively for a number of years, without ever having a problem. The secret is to follow one single rule:

    Never, never, never place data in a Memo field if there any possibility that you will ever need to search, sort, parse or in any other way manipulate the data...and this includes using it to retrieve Records, such as you are attempting!

    Because many Queries perform some of these functions, you also have to take care in using them in Queries or they will be truncated to 255 characters.

    Linq ;0)>
    Thanks for the info! So at this point, having been handed a horribly designed database and being told "export this!", is there anything I can do? Is it possible to change the Memo field to another kind of field, or is it too late? The only other solution I can think of is to go through every single record one by one and manually add missing keywords and blank email lines... but we have over 50k records, so I don't see that happening.

    While I'm here, another question: When I run the query, I get some 90k records, but when I actually export it to Excel, it only pulls up 65,535 every time -- it stops halfway through the Ns (sorted by last name). Does Excel have a limit?

Tags for this Thread

Posting Permissions

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