By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,640 Members | 2,355 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,640 IT Pros & Developers. It's quick & easy.

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

Jerry Maiapu
100+
P: 259
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
May 12 '10 #1
Share this Question
Share on Google+
6 Replies


Jim Doherty
Expert 100+
P: 897
@Jerry Maiapu
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 ;-) )
May 12 '10 #2

Jerry Maiapu
100+
P: 259
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):

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW sqrt.[column name 1], sqrt.[column name 2], sqrt.[column name n]
  2.  
  3.  
  4. FROM (SELECT tbl1.[column name 1], tbl1.[column name 2], tbl2.[column name1,tbl2.[column name 2] etc..) 
  5.  
  6. FROM tbl1 INNER JOIN tbl2 ON
  7.  tbl1.empId = tbl2.emID;) AS sqrt
  8. 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:

Expand|Select|Wrap|Line Numbers
  1. SELECT qryA.[Column 1], qryA.[Column2], qryB.[Column 1], qryB.[Column 2] …..
  2. FROM qryA INNER JOIN qryB ON
  3.  
  4. 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
May 12 '10 #3

Jim Doherty
Expert 100+
P: 897
@Jerry Maiapu
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.
May 12 '10 #4

Jerry Maiapu
100+
P: 259
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
May 13 '10 #5

Jim Doherty
Expert 100+
P: 897
@Jerry Maiapu
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
May 13 '10 #6

Jerry Maiapu
100+
P: 259
Thanks Jim..

I'll try union or recordset for now..

Cheers
May 13 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.