Trouble returning results from table based on criteria in another table

[digitalshepard]

I have a VBA query building function that works well for sorting information from TableA. However, I also have various link tables TableB and TableC that are associated with TableA. Data from TableB and TableC is entered and displayed in subforms within the main form that pulls data from TableA.

I am having trouble building a query that returns all results in TableA where [TableB].[Part] equals a selected value (lets say 1090), and each TableB record has a field [TableB].[Doc_ID] corresponding to [TableA].[Doc_ID]. Any ideas what the SQL (or VBA) would look like?

 

[YNWA]

Can you post an example of your DB or you might be better suited in the VBA section.

You might need 2 queries to get the end result.

1st - Query linking your two tables together and on field Part put "1090" in Criteria box, if you need more Part numbers use Or eg. "1090" Or "1111" Or "1083". Remember to include the fields you need in your 2nd query (Doc_ID)

2nd - Using your 1st query, use the Doc_ID field to pull through something like TableA.Doc_ID = TableB.Doc_ID.

Be easier to see your actual database.

Maybe you do only need one query, just try the 2nd example in the 1st query as well. The expression I gave is prob wrong but you need something like that.

 

[digitalshepard]

YNWA, I'm not sure how I would include two queries in a single VBA function. Anyway, a DB with the essential elements is attached for your review.

 

[jdraw]

I think you will get more useful responses if you tell us exactly what it is you are trying to do. Describe it in general terms, using sample data from your tables if necessary.

Once we have some understanding of WHAT you are trying to do, someone will offer advice as to HOW to achieve it.

 

[digitalshepard]

In laymans terms, I have a database (attached), where I have a VBA search function that takes input from combo boxes on the top level form and displays results of the search in a subform. Currently, I can search by components, but not by component and piece part. I would like to search by component and piece part. The challenge is that the results are are pulled from tbl_Document where components are listed, and piece parts that correspond to the components are listed in linktbl_Doc_IDPiecePart_ID.

I need to augment my VBA search function to support searching by piece parts, but don't know how.

 

[jdraw]

Can you describe it in terms of your business?

Documents represent..... and contain info about Components
A Component represent .... a product we manufacture..???
A Component can have 1 or more PieceParts...

You have a Component table with 16 records.
Why do you have tbl_Document with 96 records and only recs 1 - 5 have data? What do the empty records mean?
What does tbl_PiecePart represent? Why does it have a Component_ID? How did you populate that table? I think this table may have been intended to relate Components and PieceParts, but why would you have the PiecePart Name in that table?

It seems the you are trying to show a hierarchy between component and parts.
For a component to have multiple parts, and a part can be associated with several components, (M:M relationship) you need a junction table.

I think you are missing a tbl_PiecePart that contains info about PieceParts only, and has an Id and a PiecePartname.
A junction table such as tbl_ComponentParts would have an Id field(autonumber), and a field A (long Integer) representing the key of the Component, and a field B (long integer) representing the key of the PiecePart.
In addition you would have a unique composite index on A and B. This would be used to prevent duplicates.

But most of these comments are based on my guesses of your business.
Understanding the business is key to getting the table structures right.

Good luck with your project.