Combine query's

[cos]

Hi fellas,

how do i combine two queries in to one? As you may have noticed, i seem to run in to problems frequently, due to trying to accomplish too much at once.

As per usual.. i have my contracts table (with 707 records) and my sub-contracts table (with 317 records) with ContractID as foreign key. I made a query which lists all contracts, and a second one showing all sub-contracts. The object is: show ALL contracts with relevant sub-contracts next to the contracts [without duplicates].

has anyone got any ideas?

 

[John Big Booty]

You can use a query within a query in the same way you would use a table. Simply click on the Queries Tab in the Show Table dialogue box when you are in the Query builder. Highlight the query you wish to use and Click the Add Button.

 

[cos]

I tried that, but then it only shows 317 records, and doesn't display the other contracts without sub-contracts. Any ideas how i might be able to make it show all 707? (i had also tryed creating a blank sub-contract to every contract, but that results no structure and looks messy as hell)

 

[John Big Booty]

Without seeing exactly what you are doing it's going to be rather hard to diagnose.

 

[Anakardian]

While I don't want to step on John's toes I had the same problem once and solved it this way.

Make sure there is a relationship between the two queries. You can simply drag a common field from one to the other.
Double click on the relationship line and you will see 3 options at the bottom of the box that pops up. I used the 2nd option which makes it include all of the records in one of the queries even if there are none in the other.
You may need to play around with it a bit to make it do what you want but it is easy to do.

Perhaps John can enlighten us more on what to call this or offer a different solution.

 

[cos]

i have attached parts of the whole system, to try and show the issue, as in ListContractsWithSub's, i need the whole list of contracts as i tryed to explain before.

 

[John Big Booty]

The Problem is that your Table Sub-Contracts contains eight Orphan records, that is to say eight records that have now connection to your Table Contracts and that is why you are not getting all records you expect in your Query result.

 

[cos]

okay.. im not quite sure i understand the problem, but how may i fix this?

 

[John Big Booty]

Go to your Query Builder Wizard and choose Find Unmatched Query when it asks you to choose the table that contains the records you want in the results Choose your Sub-Contracts Table and the other table will be your Contracts Table link them ContractID. When you run the query it should select all the records in Sub-Contracts that are not linked to Contracts, this should be the difference you are seeing. You then need to workout why these Orphans exists and either rectify the linking problem or simply delete them.

Have a look at the query I have added to your DB.

As an aside consider implementing a naming protocol. Something along the lines of TBL_TableName, FRM_FromName, QRY_QueryName, this will make it quiet clear the nature of the object you are referring to once you start getting heavily into writing code. Also avoid space and other special characters in your names limit yourself to alpha and numeric characters and the underscore.

 

[cos]

after i deleted those recrs which your query identifyed, the result of the ListContractsWithSub's query remains the same.. it still doesn't project the 707 contracts but instead just the 316 contracts with their sub's.. =/ what am i doing wrong? i followed your instructions and my db had let me down..

 

[John Big Booty]

Sorry, my bad I think I may have got the wrong end of the stick

If you are trying to display your Contracts with their associated Sub contracts. There are two ways you could do this first up. Set up a forms sub-form set with the main form showing your contract and the sub-form show the associated sub-contract. Alternately you could show the subcontract in List box filtered using the criteria;

Forms!YourFormName!ContractID

 

[cos]

but i need to dispay it in a query

so that i can see all 707 of them.. and maybe able to create a callendar similar to ListContracts Query =(

i take it its unposible to do it in access, as it only shows subcontracts as last resort..

 

[John Big Booty]

Your tables store your data, and your user should have no direct interaction with the tables. Forms allow your user to interact with the data stored in the tables. Reports allow your user to create hard copy representations of the data. Whilst Queries allow you to manipulate the data for display in either forms or Reports.

So there is no reason for you display the data in a query as you wish, think of a query as a special instance of your table and the same rule applies to it and user interaction.