Add Control on Form from unrelated query

  • Thread starter Thread starter Linda \(RQ\)
  • Start date Start date
L

Linda \(RQ\)

Access 2003. I have only had experience making forms based on a table or
query so when the recordset is set to query 1, I can only add controls from
that query. Is it possible to add a control from another unrelated query?
If so, how do I do that. Here is the sql from the query I want to show.
It's probably more complicated because I'll want the query to update
everytime a new record is added to the form. Since we want the count of
protocol treatments to equal the count of all treatments, this will give the
user a constant status.

SELECT qryCountOfProtocolTreatments.CountOfPtThpyID,
qryCountOfAllTreatments.CountOfPtThpyID
FROM qryCountOfProtocolTreatments, qryCountOfAllTreatments;

Thanks,
Linda
 
Hi Linda,

You could have an unbound field with the datasource set something like this:

=dlookup("CountOfPtThpyID", "qryCountOfProtocolTreatments")

dlookup or dcount or dsum are all useful functions that you should consider
for things like this. Check them out further!!

Hope this helps.

Damian.
 
Cool. That shows me the one number. The query I made combines the count of
2 other queries. Is there a way to show both? One number is the number of
protocol treatments, the other is the total of all treatments. My final
wish is to turn this into a percent. What percent of treatments are
protocol treatments. Now I wonder if that can be done? Do I need to have a
query made to use these functions?

Thanks,
Linda
 
Ok...after about a bazillion attempts, I got another control on my form
=DLookUp("CountOfPtThpyID","qryCountOfAllTreatments")

What do the parentheses mean, what does the " mean what does the comma mean?
In the sql view there are periods between the query name and the field name.
What is the difference in that vs the comma in the FROM part of the
statement?

Thanks,
Linda
 
Whoops, one more thing. I usually copy and paste the control source name
into the Name of the text box. What should I name this control?

Thanks,
Linda
 
Ok..I figured out how to get a percent on my query now this is my sql in the
query. I keep trying all kinds of combinations but I am on a gazillion now
and I don't think I can get it. How can I show my Protocol Treatment
Percent on my control on my form? I am hoping I can express as a percent on
the form. I don't seem to be able to get a percent on the query, only a
decimal...0.533333 is my answer on my query. I want it to show as 53% on my
form....


SELECT qryCountOfProtocolTreatments.CountOfPtThpyID,
qryCountOfAllTreatments.CountOfPtThpyID,
[qryCountOfProtocolTreatments.CountOfPtThpyID]/[qryCountOfAllTreatments.CountOfPtThpyID]
AS [Protocol Treatment Percent]
FROM qryCountOfProtocolTreatments, qryCountOfAllTreatments;

This is getting fun now!
 
I posted this question on the forms group.

Linda

Linda (RQ) said:
Ok..I figured out how to get a percent on my query now this is my sql in
the query. I keep trying all kinds of combinations but I am on a
gazillion now and I don't think I can get it. How can I show my Protocol
Treatment Percent on my control on my form? I am hoping I can express as
a percent on the form. I don't seem to be able to get a percent on the
query, only a decimal...0.533333 is my answer on my query. I want it to
show as 53% on my form....


SELECT qryCountOfProtocolTreatments.CountOfPtThpyID,
qryCountOfAllTreatments.CountOfPtThpyID,
[qryCountOfProtocolTreatments.CountOfPtThpyID]/[qryCountOfAllTreatments.CountOfPtThpyID]
AS [Protocol Treatment Percent]
FROM qryCountOfProtocolTreatments, qryCountOfAllTreatments;

This is getting fun now!


Damian S said:
Hi Linda,

You could have an unbound field with the datasource set something like
this:

=dlookup("CountOfPtThpyID", "qryCountOfProtocolTreatments")

dlookup or dcount or dsum are all useful functions that you should
consider
for things like this. Check them out further!!

Hope this helps.

Damian.
Click to expand...
Click to expand...
 
Back
Top