Amalgamating Different 'Many' Tables

Posted on 2007-07-23
Last Modified: 2013-11-28
I have a stores table that has 'many' relationship with projects table. Recently, I had to introduce an entirely diiferent projects category, so I created a another projects table with 1-M relationship with the stores table. So now I have got two projects table with one parent.  The system works for me as data entry can be carried out seperately, however, there is a need for the business to report on projects in both tables using a single report.  I tried to create a query but the record set is not representative of both project tables.  Is there any other approach I may take to resolve this?  
Question by:khwajaa
    LVL 92

    Accepted Solution

    Hello khwajaa,

    Without knowing what your queries are all about, or what your table structures are, it is
    hard to be too specific, but perhaps a UNION query will do it:

    SELECT ...
    FROM ...
    SELECT ...
    FROM ...



    Author Comment

    Thanks. Queries are pretty standard showing a few fields from the parent and the rest from 'many' side. Union queries are limited in the sense that you have to have same fields. Some of my fields are different. Don't know how to manage these.

    LVL 9

    Assisted Solution

    You can set dummy values for those tables which are not common.

    e.g. Customers [custID, custName] and Customers2 [custID, custAddress]

    A union query to join these would be

    SELECT custID, custName, "" As custAddress
    FROM Customers
    SELECT custID, "" As custName, custAddress
    FROM Customers2

    LVL 1

    Expert Comment

    Forced accept.

    EE Admin

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now