Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

Help with ms access query

Hi, experts.
I have a simple database.
I have two queries:
qryRec_Last---------------shows how many projects received
qryPro_Last---------------shows how many projects completed

select Count(qryRec_Last_1.projectId) AS Count FROM qryRec_Last_1
------->60
select Count(qryPro_Last_1.projectId) AS FROM qryPro_Last_1;
------->30

i want to write a query showing:
Received             Completed           %
60                        30                        50%

Thanks
0
rfedorov
Asked:
rfedorov
3 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are they stored queries?

If so, then create a third, and include those two in the query design, and join them on the appropriate field if needed.

Now create another field that calculates them:

CompletePct: qryRec_Last_1.Count / qryPro_Last_1.Count

Note that you have NOT used an alias in qryPro_Last_1, so you'd need to do that:

select Count(qryPro_Last_1.projectId) AS Count FROM qryPro_Last_1;
0
 
rfedorovAuthor Commented:
Thank you for the fast respond, but i did not get ... I do not have a proble with calculating the %, i can not figure out how to put them on the same line:
Received             Completed           %
60                        30                        50%
0
 
Rey Obrero (Capricorn1)Commented:
what is the name of the table where based your two queries?

post the sql of the two queries.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rfedorovAuthor Commented:
select Count(qryRec_Last_1.projectId) AS Count FROM qryRec_Last_1
select Count(qryPro_Last_1.projectId) AS FROM qryPro_Last_1;
0
 
Rey Obrero (Capricorn1)Commented:
what is the name of the table where based your two queries?

post the sql of the two queries.  
"qryRec_Last_1"

"qryPro_Last_1"
0
 
rfedorovAuthor Commented:
' qryPro_Last_1
SELECT
DISTINCT DataExtr.StatusCreatedByUser, DataExtr.StartStatus, DataExtr.EndStatus, DataExtr.StartDate, DataExtr.projectId, Format([StartDate],"mm/dd/yyyy") AS DatePortion
FROM DataExtr
WHERE (((DataExtr.StatusCreatedByUser) In ("GR","Cs","Jr")) AND ((DataExtr.StartStatus)="15 - Project Completion") AND ((DataExtr.EndStatus)="Invoice Approved") AND ((Format([StartDate],"mm/dd/yyyy")) Between "01/06/2014" And "01/10/2014") AND ((Format([StartDate],"yyyy"))="2014"))
ORDER BY DataExtr.StartDate, DataExtr.projectId;



'qryRec_Last_1
SELECT DataExtr.projectId, DataExtr.EndStatus, TimeValue([StartDate]) AS [Time], DataExtr.StartDate, IIf(TimeValue([StartDate])<=#12/30/1899 15:0:0#,[StartDate],DateAdd("h",12,[StartDate])) AS New, Format([StartDate],"mm/dd/yyyy") AS JustDate
FROM DataExtr
WHERE (((DataExtr.EndStatus)="06a - Implementer Verified Measures") AND ((Format([StartDate],"mm/dd/yyyy")) Between "01/02/2014" And "01/10/2014"))
ORDER BY DataExtr.projectId, DataExtr.StartDate DESC;
0
 
skullnobrainsCommented:
select
  (select Count(qryRec_Last_1.projectId) AS Count FROM qryRec_Last_1) as received
  ,(select Count(qryPro_Last_1.projectId) AS FROM qryPro_Last_1) as completed
  completed * 100 / received & ' %' as percentage
0
 
rfedorovAuthor Commented:
If i copy exactly what you put, i am getting the error...
0
 
Rey Obrero (Capricorn1)Commented:
try this query



Select sum(iif(A.[Source]="Rec",1,0)) as Received, sum(iif(A.[Source]="Pro",1,0)) as Completed,
([Received]/[Completed]) as CompletePct
From
(
select [projectId], 'Pro' as Source from qryPro_Last_1
Union all
select [projectId], 'Rec' as Source from qryRec_Last_1
) as A
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now