How to update source queries before running a query based on the source queries.

[Jerry Maiapu]

Hi all,
Can someone help me on this? I ‘m using Access 2003

I have 2 select Queries: qryA and qryB.

Now I need to create a summary query; qryAB: based on qryA and qryB.

qryA and qryB are related/joined as both contained a unique key from the same table.

My problem is that when I am running qryAB it does not give me updated results as
qryA and qryB need to be running in the background or something so that qryAB when run picks up the updated results from the two source queries .

How do I make the two queries run somehow first and run the third query so that I can get updated values for may report?
My Report is based on the third query: qryAB

Thanks..

Jerry M

[Jim Doherty]

Hi all,
Can someone help me on this? I ‘m using Access 2003

I have 2 select Queries: qryA and qryB.

Now I need to create a summary query; qryAB: based on qryA and qryB.

qryA and qryB are related/joined as both contained a unique key from the same table.

My problem is that when I am running qryAB it does not give me updated results as
qryA and qryB need to be running in the background or something so that qryAB when run picks up the updated results from the two source queries .

How do I make the two queries run somehow first and run the third query so that I can get updated values for may report?
My Report is based on the third query: qryAB

Thanks..

Jerry M

Hi Jerry

I am afraid your explanation is not particularly clear to me certainly. It bases itself on an experience of the environment that only you are seeing. Stating that two queries are joined on the same key essentially means nothing because there is no explanation as to the scope/complexity of those queries to whoever reads the post.

My instinct tells me (but I may well be wrong) that we are talking sub queries or nested or maybe a taste somewhere of a union queries here if you are familiar with that. What I mean by subqueries in laymans terms essentially is that you can put a query, in a query, in a query (if I am making myself clear that is lol ;-) )

[Jerry Maiapu]

Thanks Jim for the quick reply.

What I am saying is this.
Ok I’ll break the question down into the root for clarity purposes.

Ok I have two tables tbl1 and tbl2. (They have 1-many relationship .tbl1 having one side and tbl2 having the many side)

Now my first query qryA is as follows (a nested/sub query):

SELECT DISTINCTROW sqrt.[column name 1], sqrt.[column name 2], sqrt.[column name n]


FROM (SELECT tbl1.[column name 1], tbl1.[column name 2], [B]tbl2[/B].[column name1,[B]tbl2[/B].[column name 2] etc..) 

FROM tbl1 INNER JOIN [B]tbl2 [/B]ON
 tbl1.empId = tbl2.emID;) AS [B]sqrt[/B]
GROUP BY ……so on……..;

My second query qryB is another nested query type similar to qryA.

Now I want to create a third query qryAB that will perform some INNER JOIN operation on qryA and qryB and return the results.

qrtyAB will look something like this:

SELECT qryA.[Column 1], qryA.[Column2], [B]qryB[/B].[Column 1], [B]qryB[/B].[Column 2] …..
FROM qryA INNER JOIN qryB ON

qryA.empID=qryAB.empID;

To put all this in one Sql statement I though it was messy and quite complicated so I decided to simply run a query wizard to create a summary query qryAB. Which is fine but the problem is that; to run sqryAB I have to first run qryA and qryB everytime so that qryAB returns the updated records.

Is there any simplier way to do this..or may I just go ahead and squeeze every thing into one sql stamen like you said where you put a query, in a query, in a query and son on…?

I am confused here…hope someone passing by helps..

Jerry

[Jim Doherty]

Thanks Jim for the quick reply.

What I am saying is this.
Ok I’ll break the question down into the root for clarity purposes.

Ok I have two tables tbl1 and tbl2. (They have 1-many relationship .tbl1 having one side and tbl2 having the many side)

Now my first query qryA is as follows (a nested/sub query):

SELECT DISTINCTROW sqrt.[column name 1], sqrt.[column name 2], sqrt.[column name n]


FROM (SELECT tbl1.[column name 1], tbl1.[column name 2], [B]tbl2[/B].[column name1,[B]tbl2[/B].[column name 2] etc..) 

FROM tbl1 INNER JOIN [B]tbl2 [/B]ON
 tbl1.empId = tbl2.emID;) AS [B]sqrt[/B]
GROUP BY ……so on……..;

My second query qryB is another nested query type similar to qryA.

Now I want to create a third query qryAB that will perform some INNER JOIN operation on qryA and qryB and return the results.

qrtyAB will look something like this:

SELECT qryA.[Column 1], qryA.[Column2], [B]qryB[/B].[Column 1], [B]qryB[/B].[Column 2] …..
FROM qryA INNER JOIN qryB ON

qryA.empID=qryAB.empID;

To put all this in one Sql statement I though it was messy and quite complicated so I decided to simply run a query wizard to create a summary query qryAB. Which is fine but the problem is that; to run sqryAB I have to first run qryA and qryB everytime so that qryAB returns the updated records.

Is there any simplier way to do this..or may I just go ahead and squeeze every thing into one sql stamen like you said where you put a query, in a query, in a query and son on…?

I am confused here…hope someone passing by helps..

Jerry

Hi

Looking at your syntax with the described qrys A and B you are returning two separate datasets each identical in terms of column layout (I presume each has different where clause criteria and that this is primarily the reason for creating them independantly from each other).

My understanding is that you want to work with those two datasets joined together as though there were ONE dataset with the results for for both qryA and qryB 'piled' one on top of the other (so to speak) as though they were one dataset from which you can then create your summary query based on that single dataset

To combine those datasets qryA and qryB together as one dataset look at the functionality of UNION queries you can then query on the 'Union' query itself.

When you examine the functionality you will see that you can manipulate the return dataset according to the command you give it either UNION or UNION ALL

The subtle difference between the two is that UNION will remove any duplicates from the return set whereas UNION ALL will provide all records from both datasets ignoring duplicates that will exist when both datasets are combined and viewed on screen.

You can then create your third query (qryAB) based on the union query itself grouping by, counting, or whatever else it is you require.

[Jerry Maiapu]

Thanks Jim,
Before I follow your help hints using UNION ALL or UNION operation, let me just give you an insight of my problem.

QryA has a where clause criteria to filter the records by a date and
qryB returns a total calculation of a value for all the dates that needs to be carried forward without any where clause criteria .

qryAB needs to basically collect the results of QryA and then get the progressive total from qryB where the IDs are same.

That is the main idea behind this problematic scenario. So I’ll try your path first.

Thanks

[Jim Doherty]

Thanks Jim,
Before I follow your help hints using UNION ALL or UNION operation, let me just give you an insight of my problem.

QryA has a where clause criteria to filter the records by a date and
qryB returns a total calculation of a value for all the dates that needs to be carried forward without any where clause criteria .

qryAB needs to basically collect the results of QryA and then get the progressive total from qryB where the IDs are same.

That is the main idea behind this problematic scenario. So I’ll try your path first.

Thanks

Ok Jerry.... but I am still not at all convinced that I am with you on this (I must be having a 'dense' moment or something) mainly because I am not seeing what you are seeing. I give you the option of 'Union' to look at but am not at all convinced it is what you are looking for.

There are two solutions to every scenario as you know either SQL based or code based recordsets (which give you movement logic and editing control over your data at the same time as the data is being processed) it may resort to that if the SQL side is less than friendly

[Jerry Maiapu]

Thanks Jim..

I'll try union or recordset for now..

Cheers