Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012

    Unanswered: Looking for help with a union query

    Newb here with Access but learning! I'm thinking I might have a few flaws with my design, we were under the gun to get a tracking tool up and running and my number was picked to build this mainly because I'm 20 years younger than anyone else in my dept.

    I have an Access 2007 database used to track project metrics. Consists of 2 main tables for our operational groups:


    Fields are ID, Project Name, Site, Org name, POC1, POC2, Sourced Date, Sourcing Method, Delivery dates, and several hyperlinks to word documents.

    I can't mingle the data from the two org's on the same table though they both use the same field structure.

    Each record for Org1 and Org2 is unique based on an ID (auto number) but displayed on my forms as fields project name and site. Project name is user typed (through a form), and a site pulls from a fixed list (seperate table) that the user selects (through a list box on the same form).

    Additionally, I have two more tables:

    Org1 Events (one to many linked by project name)
    Org2 Events (one to many linked by project name)

    Fields on these two tables are ID, Project Name, Site, Event Date, Event, Personnel (multi-value lookup to seperate personnel table), Org Name

    The Org Events tables also have a auto-number ID, and the project name and site autopopulates when a new event record is added based on which project record the user is viewing on the main form. (Main Org form with an Events subform and parent/child linking by ID to Project Name and Site)

    I'm now trying to create a report fed by a union query that pulls the data from both Org1 Events and Org2 Events and lets me select a date range based on picking a beginning date and ending date on a pop up form. The problem I'm having is that when I run the report, I get the ID values for project name and site versus the actual project name and site name. The event date and event type show as intended. The project name and site name display properly on my forms, but not my report.

    I wish I could attach the database but it's just not feasible. Hope this wasn't too confusing. Wondering if this is the result of a design flaw, or if I just goofed up some settings.

  2. #2
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 10
    No spaces in feildnames
    When doing a union query make shour the cols are in the same place
    hope this help

    See clear as mud

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

  3. #3
    Join Date
    Jun 2012
    when you say cols are the same place, you mean match the order in the table structure? or that the cols are in the same place in the query string?

    They are in the same place in the query string but they do not match the order of the fields in the table structure.

  4. #4
    Join Date
    Aug 2004
    Cary, NC
    This isn't a direct answer to your union query question but there are some issues with the design...

    The event tables should not include Project Name, Site, or Org Name as those are already defined in the Org tables.

    The Organizations should have been defined in their own table with the hyperlinks in another joined table.

    Why do you have two Org tables and two Event tables? It seems that it would have been easier to keep them as one (and add an OrgNum field if needed).

    But back to the union query question... can you post the sql?


Posting Permissions

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