Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2010
    Location
    UK
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hi, does anyone know how datatype is set in union query of mixed datatype fields? (bonus question is how/why - presumably something in Jet database engine)
    ie. a date field is added to a text field which is added to a long integer field
    I found comment on http://www.softlookup.com/tutorial/data_vb/ch08.asp which seemed to imply that the dataype is set by the first SELECT clause in the UNION query:
    "The UNION query uses the column names of the first SQL query in the statement and creates a result set that displays the data even if data types must be altered to do so."

    However, I tested this in Access by creating several simple tables each with one field but of datatype text, date, memo etc. No matter which order I put the Tables in the Union Query, the output datatype always has been text in my experiments which contradicts what the quote above says.

    On further googling I found MS site which says that Access is more "lenient" on mixed datatypes in a field that other database programs ie other SQL systems are less likely to force conversion/override dataype

    While all data seems to have been converted to text, I am concerned that I may lose data since I have not found the answer to this question. I prefer having all my fields as text for stability reasons and as it avoids datatype mismatches.

    I would prefer not to cast / force datatype conversion to text or to limit it since given the large amount of data (I went upto 4 million records) I assume this could slow things down: If I do I assume that I'd need to use Cstr(Nz([Field],"")).

    I have constructed "Normalised Union Queries" which combines around 600 fields into one (using intermediate maketable since Union quesries seem to be only able to cope with around 50 unions in Access). I am comparing the current version of each field record with the previous and so this reduces the calculation from 600 expressions to one.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    You don't specify which database engine you are using - Jet 3, Jet 3.5, Jet 4 or the ACCDB version, depending of course on what version of Access you are using. The behavior of each of those may be slightly different - presuming that you are using Access tables, not ODBC linked tables. In general the data type will be whatever the first SELECT statement provides, and the DB engine will convert subsequent data types to that type if it can. This article based on Access 2007 may give you some further insight. One thing to note is that memo fields in Access can be more than 255 characters, so you may see field truncations if you mix the record types. In general the display of data that you see is always text so that can be a bit misleading. Doing a make-table may give you some insight as to the data type, but that process may also alter the data type.

    A couple of comments and observations. If you are working with 4 million records, you are probably pushing the limits of data storage in Access - so you may run into the 2GB limit on size. Also, I don't understand this comment:
    I have constructed "Normalised Union Queries" which combines around 600 fields into one (using intermediate maketable since Union quesries seem to be only able to cope with around 50 unions in Access). I am comparing the current version of each field record with the previous and so this reduces the calculation from 600 expressions to one.
    If you have 600 fields that exceeds the number of fields allowed in a query. Or do you have 600 different SELECT statements?
    Wendell

  3. #3
    New Lounger
    Join Date
    Jun 2010
    Location
    UK
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have worked in Access 2007 with over 4 million records and did not experience a problem. I think the database's normal size was under 1GB. My normalised union query converts records with lots of fields into one with just three. The ID, the field name and the value. I had to break down the 600 union queries because Access could only handle around 50 at a time. I then applied maketables and union the resulting 12 tables.

    I'm using Access 2007 which I understand uses the ACCDB version of the Jet engine. Per my earlier email, the unionised field are cast as text it seems based on the first SELECT query in each union. To be sure I now apply CSTR(NZ([Field],"")) to the first field in each union.

    The fields being unioned are text, memo, date, integer, decimal. I'm doing all this to look for differences. Seems to work well. But I'm concerned I may have missed a potential problem.

    Is the ACCDB method for forced data conversion explained anywhere. I'd certainly like to look under the hood and understand what Access is doing to the data. I understand that other database engines are less forgiving.

    I also have the issue that my process may be ported back to Access 2003 and so would also likel to know how that version of Jet deals with forced data conversion.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by rupert kenna View Post
    I have worked in Access 2007 with over 4 million records and did not experience a problem. I think the database's normal size was under 1GB. My normalised union query converts records with lots of fields into one with just three. The ID, the field name and the value. I had to break down the 600 union queries because Access could only handle around 50 at a time. I then applied maketables and union the resulting 12 tables.

    I'm using Access 2007 which I understand uses the ACCDB version of the Jet engine. Per my earlier email, the unionised field are cast as text it seems based on the first SELECT query in each union. To be sure I now apply CSTR(NZ([Field],"")) to the first field in each union.
    Access 2007 selects the appropriate engine based on the extension of the database file, so if you are using the .ACCDB format it will use that engine, while if you specify the .MDB format, it will use the Jet engine. The .ACCDB format is new (and not supported in 2003), so it may be a bit less tested in extreme or unusual situations, while the .MDB format has been around for over 15 years.

    The fields being unioned are text, memo, date, integer, decimal. I'm doing all this to look for differences. Seems to work well. But I'm concerned I may have missed a potential problem.

    Is the ACCDB method for forced data conversion explained anywhere. I'd certainly like to look under the hood and understand what Access is doing to the data. I understand that other database engines are less forgiving.

    I also have the issue that my process may be ported back to Access 2003 and so would also likel to know how that version of Jet deals with forced data conversion.
    Unfortunately I don't know of any resource that delves into the internals of the Jet or Ace database engines and how and when they do forced data conversion.
    Wendell

Posting Permissions

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