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

    Answered: Linking Tables with different fields (Access 2010)

    Hello all
    Straight off the bat I'd like to throw it out there that I'm only now really learning the intricacies of Access so please have patience.

    My issue is as follows and I'd greatly appreciate any help.

    I have a database which I have been given control over, where the main data is held across 2 different tables (_Main) and (Completes)

    I basically want to have one query which pulls information from both tables (some fields match, others do not, but the tables have different amounts of data / columns in them), into one table so I can run further queries from one data source

    I've had a look at UNION queries but that doesn't work as the tables aren't identical in layout, and I tried messing around with linking tables but got completely confused.

    EDIT:
    Specifically this is what I have at the moment (it includes all the field names) but it just gives me the error: "The number of columns in the two selected tables or queries of a union query do not match"


    SELECT [MPAN], [Run Date], [Open Date], [Status], [Comments], [Date Complete], [Allocated To],
    * FROM [_Main]
    UNION
    SELECT [MPAN], [Run Date], [Open Date], [Comments], [Date Complete], [Allocated To], [Removed from Database],
    * FROM [Completes]


    Really just forcing it all into one table would be fine, but I can't seem to get anything to work.

    Thanks (and apologies if I'm just being daft)
    Last edited by Saikred; 03-08-16 at 09:07.

  2. Best Answer
    Posted by weejas

    "Welcome to the forum! Have a few pointers:
    • UNION queries require the same number of columns from all entities. If a column is missing from a table, you have to enter a constant to replace it in the relevant position within the others.
    • Using "*" in a SELECT statement will select all columns from the named table or query. It is heavily frowned upon because it can impede database performance and lead to sloppy programming. Also, if you use it after specifying a set of columns, you'll get them again.
    • If you use a constant in the first SELECT clause, you will also need to specify an alias to use as the column heading.
    • If you can, try to avoid using reserved words or characters in column names - it will make writing queries and code a lot easier and less prone to unexpected behaviour.

    Try:
    Code:
    SELECT 
       [MPAN]
    ,  [Run Date]
    ,  [Open Date]
    ,  [Status]
    ,  [Comments]
    ,  [Date Complete]
    ,  [Allocated To]
    ,  '[N/A]' AS 'Removed from Database'
    FROM 
       [_Main]
    UNION
    SELECT 
       [MPAN]
    ,  [Run Date]
    ,  [Open Date]
    ,  '[No Status]'
    ,  [Comments]
    ,  [Date Complete]
    ,  [Allocated To]
    ,  [Removed from Database],
    * FROM [Completes]
    "


  3. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Welcome to the forum! Have a few pointers:
    • UNION queries require the same number of columns from all entities. If a column is missing from a table, you have to enter a constant to replace it in the relevant position within the others.
    • Using "*" in a SELECT statement will select all columns from the named table or query. It is heavily frowned upon because it can impede database performance and lead to sloppy programming. Also, if you use it after specifying a set of columns, you'll get them again.
    • If you use a constant in the first SELECT clause, you will also need to specify an alias to use as the column heading.
    • If you can, try to avoid using reserved words or characters in column names - it will make writing queries and code a lot easier and less prone to unexpected behaviour.

    Try:
    Code:
    SELECT 
       [MPAN]
    ,  [Run Date]
    ,  [Open Date]
    ,  [Status]
    ,  [Comments]
    ,  [Date Complete]
    ,  [Allocated To]
    ,  '[N/A]' AS 'Removed from Database'
    FROM 
       [_Main]
    UNION
    SELECT 
       [MPAN]
    ,  [Run Date]
    ,  [Open Date]
    ,  '[No Status]'
    ,  [Comments]
    ,  [Date Complete]
    ,  [Allocated To]
    ,  [Removed from Database],
    * FROM [Completes]
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #3
    Join Date
    Mar 2016
    Posts
    3
    Oh wow, so I was way off, i'm sorry (like I said, still learning)

    Many thanks for your suggestion, when I tried it I'm getting the error

    "The number of columns in the two selected tables or queries of a union query do not match"

    I tried taking the * out at the bottom (as per your previous comments) but that didn't fix it either.

    Could it be that there are more columns that those I've mentioned in the tables, but those are the only ones I want to see?
    I can re-write it (following your rules) to include all columns across both tables (there are quite a lot on the first one) if that's what's causing it.
    Last edited by Saikred; 03-08-16 at 11:54.

  5. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You need to take both *s out of the query.

    To make it easier, start with just the columns that are common to both tables, and build it from there.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #5
    Join Date
    Mar 2016
    Posts
    3
    Quote Originally Posted by weejas View Post
    You need to take both *s out of the query.

    To make it easier, start with just the columns that are common to both tables, and build it from there.
    Thanks very much.
    Tinkered with it a bit this morning and it's working after your advice

    Thanks very much to the both of you

    A* =)

Posting Permissions

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