Combine multiple queries in one

[svjensen]

I have a database structured around a number of business cases (BC), each with a number of sheets (Faner) of various types. Each sheet is linked to a "department" (NIVII).
I now need to build a list of BCs which affect a given NIVII.
So far I have been using multiple queries (see below), which I then merge into an array (omitting dublicates) giving me the soughtafter list.

Query 1:

SELECT distinctrow qryBCListeSimpel.BCuid, qryBCListeSimpel.BCnr, qryBCListeSimpel.Titel
FROM (qryBCListeSimpel INNER JOIN qry1aFaner ON qryBCListeSimpel.BCuid = qry1aFaner.BCId) INNER JOIN tblBCStatus ON qryBCListeSimpel.BCuid = tblBCStatus.BCId
WHERE (((qry1aFaner.NIVIIId)=1) AND ((tblBCStatus.StatusId) In (1,2)) AND ((tblBCStatus.DatoSat)<=Now()) AND ((tblBCStatus.DatoUdloeb)>Now()));

Query 2:

SELECT distinctrow qryBCListeSimpel.BCuid, qryBCListeSimpel.BCnr, qryBCListeSimpel.Titel
FROM (qryBCListeSimpel INNER JOIN qry1bFaner ON qryBCListeSimpel.BCuid = qry1bFaner.BCId) INNER JOIN tblBCStatus ON qryBCListeSimpel.BCuid = tblBCStatus.BCId
WHERE (((qry1bFaner.NIVIIId)=1) AND ((tblBCStatus.StatusId) In (1,2)) AND ((tblBCStatus.DatoSat)<=Now()) AND ((tblBCStatus.DatoUdloeb)>Now()));

And actually I have to do this nine times to get the complete picture.

But surely there must be a better way!
I am thinking that ideally the queries can be combined into one single query, which gives me the list that I am after.

Is this possible?

/Soren

 

[Guus2005]

You are giving a part of the solution and not the data to start from. For instance what's in query qry1aFaner and qry1bFaner?

Can you give more insight into your db model?

HTH