Query logic

Posted on 2010-01-08
Last Modified: 2012-05-08
Hi, Experts -
I have a query that is appending rows to an empty table - and uses as input a file containing one row for each employee in our company.  
Following each employee row is could be:
-  One-or-more rows of dependents
-  Another employee row.
The query is currently reading each row of input, and creating one row of (reformatted) output.  However, for each dependent row of output - I need to include a value that only resides on the associated employee row.  In the sample below, the value is either "Union" or "Mgmt."  I need to transfer this onto the associated Dependent rows.  How can I do this in my query?

Sample input:  EE=employee row  DE = dependent row
EE   Union   Joe Smith
EE   Mgmt    John Davies
DE               Sally Davies
DE               John Davies, Jr.
EE   Mgmt    Billy Bob Jefferson
(...and so on.)

Resulting output (desired):
EE   Union   Joe Smith
EE   Mgmt    John Davies
DE   Mgmt    Sally Davies
DE   Mgmt    John Davies, Jr.
EE   Mgmt    Billy Bob Jefferson
(...and so on)
Question by:OGSan
    LVL 5

    Expert Comment

    Your query should be a link between the main and the dependant tables, yes? if so, in your query designer, drag the field that says Union or Management from the main table and you'll get the results you're looking for.

    Let me know if you need more.

    LVL 5

    Accepted Solution

    If you're working with a single table that has both the Employees and the dependants, create two queries one for ONLY EE records, and one for ONLY DE employees. Make sure that there is an employee ID in both queries. Then create a third query to link the employees to the dependents. And drag the UnionManagement field from the Employees query.

    LVL 1

    Author Comment

    Thanks, eantar!  Sometimes I get caught up in heads-down activity that I don't see the forest for the trees.  Building an intermediate set of results will work for me.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    754 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

    17 Experts available now in Live!

    Get 1:1 Help Now