Skip to Content

doing a qry based on infor from a sub component

I am trying to pull a report based on where the parts have to be manufactured at. Each of the parts in question will have components that I need to pull info from as well. How can I pull the needed info (Say parts are made in Georgia) and then use that to see the components (which may be made in New York)?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Mar 19 at 05:37 PM

    Not knowing the structure of your database, I'm going to assume that components and parts are in the same table in the database (which I'll call "Parts") and that there is another table (which I'll call PartComponents) that links a part to its components. Here's what I would do:

    1. Add the Parts table to the report.

    2. Add the Parts table again. You'll get a warning message where Crystal will ask if you want to "alias" the table. Accept the alias and the table will be added as Parts_1.

    3. Select the Parts_2 table in the list of tables that have been added. Press F2 and rename this to "Components" (this will prevent confusion as to the purpose of the aliased table.)

    4. If you need to know the location of the components, you'll do something similar to add the Location table and a ComponentLocation alias. If there's an intermediate table that links locations to parts, you'll have to do the same with those as well. The point is that you cannot have both Parts and Components linked to the same table for any reason. This will form a "loop" and you will not have any data returned from the query.

    5. Add any other required tables to your report, including PartComponents.

    6. Link from the Part ID Parts to the Part ID in Parts to PartComponents. Then link from the Component ID field in PartComponents to the Part ID field in Components.

    7. Create whatever links you need to get from Location to Parts.

    8. Create whatever links you need to get from Components to ComponentLocation.

    9. Group or filter your report by the Location of the Part to get the data you're looking for, including the Components from other locations.

    -Dell

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.