ansaurus

Question

Queries in MS Access 2003, help using a NESTED SELECT

Answer 1

A:

It looks like the columns selected, the join and the order by are the same in all three queries. The only thing that changes is the WHERE clause. So perhaps you just need to OR the three clauses together...

SELECT ...list of columns...
    FROM BUDGET INNER JOIN ZBASED ON BUDGET.ACCOUNT=ZBASED.ACCOUNT
WHERE (((BUDGET.FISCAL_YEAR)=2010) And ((BUDGET.BUDGET_NBR)=6) And ((ZBASED.ACCT_UNIT)=BUDGET.ACCT_UNIT And (ZBASED.ACCT_UNIT)=[Cost Center]))
OR (((BUDGET.FISCAL_YEAR)=2011) AND ((BUDGET.BUDGET_NBR)=2) AND ((ZBASED.ACCT_UNIT)=[BUDGET].[ACCT_UNIT] And (ZBASED.ACCT_UNIT)=[Cost Center]))
OR (((BUDGET.FISCAL_YEAR)=2010) AND ((BUDGET.BUDGET_NBR)=1) AND ((ZBASED.ACCT_UNIT)=BUDGET.ACCT_UNIT) And ((ZBASED.ACCT_UNIT)=[Cost Center]))
ORDER BY ZBASED.ACCOUNT

(give or take a () or two).

Brian Hooper 2010-07-01 22:02:10

@ Brian. That worked to bring all the accounts together in my CCquery, but it omitted some of the other information I needed within the query. I have a question for you, can you convert the query to a nested select or select within a select? If so, how would you write it?

Edmond 2010-07-02 14:40:22

I'm not sure that a nested select is the solution to your problem; what information is missing in the query? When we have sorted out what is wanted, then we can worry about the means of getting it.

Brian Hooper 2010-07-02 19:16:24

I have 16 rows in qrybud11, 15 rows in qrybud10, and 16 rows in qryactvproj. When I use the CCquery to combine all the separate queries together I only get 13 rows of data, because if the queries dont have the same row information it omits the whole row. I need it to import all the rows from all the queries and just add a 0 if one query doesnt have the identical information.

Edmond 2010-07-02 19:56:29

I think I can see what you are driving at...you are joining the results of these three queries to form a sort-of-table, and in doing so, some of the rows appear to be vanishing. I think that is an effect of the inner join; you'll only get a result if the ACCOUNT appears in all three queries. I'd suggest doing a FULL OUTER JOIN. Inspect the results (16 * 15 * 16 rows so not too horrendous), and then see what join conditions are necessary to get the rows you need.

Brian Hooper 2010-07-03 08:42:35

Ive heard tha Access does not support Full Outer Joins. Is that inaccurate? I've also tried the full outer join and came up with a join error. Not sure exactly what it said but it would not let me run the query.

Edmond 2010-07-06 13:43:53

Oh. Perhaps you could create the three queries as views, and then trySELECT * FROM VIEW1, VIEW2, VIEW3; That's an outer join really. I hope I'm not putting you to a lot of trouble when I'm barking up the wrong tree.

Brian Hooper 2010-07-06 14:10:00

Not at all just trying to get this up and running properly. How do you create queries as views?? Without giving me the exact way to set the query up, I was told that I needed to try a nested select Select within a select....

Edmond 2010-07-06 14:54:15

ansaurus

tags:

views:

answers:

Queries in MS Access 2003, help using a NESTED SELECT

related questions