Querying queries 2

[JeremyAnderson]

Ok I have been told that you can preform queries to pull data from multiple other queries in MS Access. I have tried this and keep getting an error stating that I am attmepting to pull info from both a query and the table it relates to. This is not the case however. I have 2 tables one has avtive account data and another has data on accounts we have sent to collections. I have 2 queries on the first table, one to pull all the accounts a given person is reponsible for and another query to pull a count of the accounts and a sum of balances for those accounts that are delinquent for the given person that is responsible. I am wanting to make a query to run both of the other queries and ut the information together. Is this possible? AM I going about this all wrong? Thanks in advance!

 

[lespaul]

select * from qry1
UNION
select * from qry2

will put the information from both queries together IF there are the same number of fields in each query.

If qry1 is:

SELECT Field1, Field3, Field5, Field7 From Table1

and qry2 is:

Select Field2, Field4, Field6, Field8, Field10 From Table2

the queries can't be unioned unless qry1 is modified to:

SELECT Field1, Field3, Field5, Field7, "" From Table1

what is the SQL of the two queries?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[JeremyAnderson]

Thank you very much Leslie! That worked, however is it possible to get them to display in the same record like:

Query 1 Data | Query 2 Data

rather than as 2 seperate records like:

Query 1 Data
Query 2 Data

The end result of this query will be a macro which copies the output of the query to a local table.

 

[lespaul]

in order to get them on the same record you would need a join between the two queries. What's the SQL of the two queries? Are there any common fields between the two that "match"?

 

[JeremyAnderson]

The first is:

SELECT Sum(dbo_LoanViewAll.currbalamt) AS SumOfcurrbalamt, Count(dbo_LoanViewAll.custnum) AS CountOfcustnum
FROM dbo_LoanViewAll
WHERE (((dbo_LoanViewAll.collectoruserid)=[Enter User ID]));

the second is:

SELECT Sum(dbo_LoanViewAll.currbalamt) AS SumOfcurrbalamt, Count(dbo_LoanViewAll.custnum) AS CountOfcustnum
FROM dbo_LoanViewAll
WHERE (((dbo_LoanViewAll.currdayslate)>0) AND ((dbo_LoanViewAll.collectoruserid)=[Enter User ID]));

Another question I had about this is: Will it be possible to add a third query to this which does have similar fields but not the sam number of fields?

 

[JeremyAnderson]

ALso each of the queries prompts for a userid to search for within the table. Is there a way to script this query so that it only prompts once but finds all values based on that user id?

 

[lespaul]

so the first one finds the sum and count of the userid and the second shows which of the above is late?

results look like:

SumOfcurrbalamt CountofCustnum SumofPastDueAmt CountofPastDueAmt

do you reall want to enter each id? You can group by the id and get the information for everyone all at once, but separated.

Is this for a report?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[JeremyAnderson]

Actually it will be used by a Macro to query from the linked table and place it in local table named the same as the userid. Reports will enevitably be ran from the local tables but right now I am focussing on getting the data where it needs to be.

 

[JeremyAnderson]

And no I really dont want to have to enter the userid each time. I would like to be able to only enter it once for the query and have it pull all the results for each of the 3 original queries.

 

[PHV]

A starting point:
SELECT Sum(currbalamt) AS SumOfcurrbalamt, Count(custnum) AS CountOfcustnum
, Sum(IIf(currdayslate>0,currbalamt,0)) AS SumofPastDueAmt
, Count(IIf(currdayslate>0,custnum,0)) AS CountofPastDueAmt
FROM dbo_LoanViewAll
WHERE collectoruserid=[Enter User ID]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JeremyAnderson]

Thank you all for your insight on this! PHV that SQL statement worked for the most part, however the last count if statement isnt working. I am looking into that now, but thank you very much for giving me a wonderful starting point!

 

[JeremyAnderson]

SCORE!! I found it... replaced the Count(IIF(currdayslate>0,custnum,o)) with a Sum(IIF(currdayslate>0,1,0)) and it worked! This project is really starting to come together. I am so greatful to all of you for your insight. I am in no way a database guy. Actually, my specialties are Networking & IS Forensics, so this is a big challange for me lol.

 

[PHV]

Sorry for the typo, glad you found the correct formula yourself.