Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2014
    Posts
    6

    Unanswered: Combining Tables in Access

    Hi guys,

    I am trying to do a really weird combination of an Append/Union query and I'm not sure if it's possible or not, and if it is - is there a way to do it? I'm pretty new to SQL and VBA so I'm doing most of my commands through macros and queries.

    I know how to do a normal Append/Union/Create queries but I am trying to do something kind of weird.

    If table X has rows A, B, C, and D, and table Y has a column with rows 1, 2, 3, and 4... I need the values in table Y to be assigned to each individual row in table X. Is there a way to create a new table where the columns merge such that values can be assigned like:

    X Y
    A 1
    B 1
    C 1
    D 1
    A 2
    B 2
    C 2
    D 2
    A 3
    B 3
    etc..

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You shouldnt need to create a new table, but instead use a UNION join
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @x TABLE (
       x		CHAR(1)
       )
    
    DECLARE @y TABLE (
       y        INT
       )
    
    INSERT INTO @x VALUES ('A'), ('B'), ('C'), ('D')
    
    INSERT INTO @y VALUES (1), (2), (4), (4)
    
    SELECT * FROM @x
    
    SELECT * FROM @y
    
    SELECT *
       FROM @x
       CROSS JOIN @y
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jul 2014
    Posts
    6
    Hmm, I think I'm getting close but when I type in the following I get a pop-up saying "Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

    Do I have to do anything differently if I am doing this in Access 2013? And I'm not sure if I declared all of my variables in the right format - does this look right to you?

    DECLARE [SSOrgChart] TABLE (
    Budget CHAR(1)
    WorkCenter CHAR(1)
    WorkProduct CHAR(1)
    JobTitle CHAR(1)
    Shift INT
    Employee CHAR(1)
    FSTatus CHAR(1)
    FRI INT
    SaStatus CHAR(1)
    SAT INT
    SuStatus CHAR(1)
    SUN INT
    MStatus CHAR(1)
    MON INT
    TuStatus CHAR(1)
    TUE INT
    WStatus CHAR(1)
    WED INT
    ThStatus CHAR(1)
    THU INT
    Comments CHAR(1)
    )

    DECLARE [DateRange] TABLE (
    WeekStart CHAR(1)
    WeekEnd CHAR(1)
    DateRange CHAR(1)
    )

    INSERT INTO SSOrgChart VALUES ('Budget'), ('WorkCenter'), ('WorkProduct'), ('JobTitle'), ('Shift'), ('Employee'), ('FStatus'), ('FRI'), ('SaStatus'), ('SAT'), ('SuStatus'), ('SUN'), ('MStatus'), ('MON'), ('TuStatus'), ('TUE'), ('WStatus'), ('WED'), ('ThStatus'), ('THU'), ('Comments')

    INSERT INTO DateRange VALUES ('WeekStart'), ('WeekEnd'), ('DateRange')

    SELECT * FROM SSOrgChart

    SELECT * FROM DateRange

    SELECT *
    FROM SSOrgChart
    CROSS JOIN DateRange

    Thanks so much for all of your help so far!

Posting Permissions

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