Different field control sources for unbound form

[gdaniels]

Hi. I'm using Access 97. The short description of my problem is:
I have an unbound form named Stats1. On the form I have 3 text boxes
(AvgofR1, AvgofR2, and AvgofR3).

There are 3 "total queries" (Q1, Q2, and Q3) that when run return an
average in a field called AvgofR1 for query 1, AvgofR2 for query 2,
and AvgofR3 for query 3.

How do I get the value of AvgofR1 from query Q1 to appear in the text
box AvgofR1, the value of AvgofR2 from query Q2 to appear in the text
box AvgofR2, and the value of AvgofR3 from query Q3 to appear in the
text box AvgofR3 on the unbound form Stats1?

I've searched forums up and down and have not found any previous posts
that would help.

Here's the long description which may help in suggesting other
approaches to this problem:

My database named "Apartments" consists of apartment properties with
fields that describe each property (address, city, state, bedrooms,
rent....). As each record (apartment building) may have 1 to 3
individual apartment units, there are fields called U1, U2, U3, and
R1, R2, and R3. The data in the fields for U1, U2, and U3 indicate
the number of bedrooms in that unit (1, 2, 3). R1, R2, and R3
indicate the rent in dollars for that unit. For example, for the 1st
property or record, U1 could = 1, U2 could =1, U3 could = 2. For the
2nd property or record, U1 could = 2, U2 could =3, U3 could = 3. What
I want to do is to be able to find the average rent for all 1 bedroom
units, the average rent for all 2 bedroom units, and the average rent
for all 3 bedroom units. Once these averages are calculated I want
them to appear on a form, or on a report.

I have individual queries that will do this for each number of
bedrooms. Q1 calculates the average rent for 1 bedroom apartment
units, Q2 calculates the average rent for 2 bedroom apartment units,
etc. But from here I am stuck on how to get these averages from all
the individual queries onto a form or report. I've gone to the
control source of the properties for each text box on the unbound
forms (Stats1) and tried to enter code like "[Q1]![AvgofR1]", but it
does not work.

Thanks in advance.

Gary

 

[Baz]

The simple answer is to put a formula such as this in the control source of
each text box:

=Dlookup("AvgofR1","Q1")

The long answer is that your database design is flawed. I'm afraid it looks
like a classic case of believing that a database is just Excel with knobs
on.

I suggest that you really need two tables, roughly a follows:

Buildings
======
building_id (PK)
address
city
state

Apartments
========
building_id PK
apartment_no PK
bedrooms
rent

You then need a query (called, say, qryAverage) which calculates the average
rent for each number of bedrooms, something like this:

SELECT bedrooms, AVG(rent) AS average_rent FROM Apartments GROUP BY bedrooms

You can then create a form (to display, I would suggest, in either datasheet
view or continuous forms view) which uses qryAverage as it's RecordSource.
Now you can add that form as a subform to your original form.

Hi. I'm using Access 97. The short description of my problem is:
I have an unbound form named Stats1. On the form I have 3 text boxes
(AvgofR1, AvgofR2, and AvgofR3).

There are 3 "total queries" (Q1, Q2, and Q3) that when run return an
average in a field called AvgofR1 for query 1, AvgofR2 for query 2,
and AvgofR3 for query 3.

How do I get the value of AvgofR1 from query Q1 to appear in the text
box AvgofR1, the value of AvgofR2 from query Q2 to appear in the text
box AvgofR2, and the value of AvgofR3 from query Q3 to appear in the
text box AvgofR3 on the unbound form Stats1?

I've searched forums up and down and have not found any previous posts
that would help.

Here's the long description which may help in suggesting other
approaches to this problem:

My database named "Apartments" consists of apartment properties with
fields that describe each property (address, city, state, bedrooms,
rent....). As each record (apartment building) may have 1 to 3
individual apartment units, there are fields called U1, U2, U3, and
R1, R2, and R3. The data in the fields for U1, U2, and U3 indicate
the number of bedrooms in that unit (1, 2, 3). R1, R2, and R3
indicate the rent in dollars for that unit. For example, for the 1st
property or record, U1 could = 1, U2 could =1, U3 could = 2. For the
2nd property or record, U1 could = 2, U2 could =3, U3 could = 3. What
I want to do is to be able to find the average rent for all 1 bedroom
units, the average rent for all 2 bedroom units, and the average rent
for all 3 bedroom units. Once these averages are calculated I want
them to appear on a form, or on a report.

I have individual queries that will do this for each number of
bedrooms. Q1 calculates the average rent for 1 bedroom apartment
units, Q2 calculates the average rent for 2 bedroom apartment units,
etc. But from here I am stuck on how to get these averages from all
the individual queries onto a form or report. I've gone to the
control source of the properties for each text box on the unbound
forms (Stats1) and tried to enter code like "[Q1]![AvgofR1]", but it
does not work.

Thanks in advance.

Gary

 

[gdaniels]

Thanks!!! the "=Dlookup("AvgofR1","Q1") " works perfectly. Just what
I needed.

Gary

The simple answer is to put a formula such as this in the control source of
each text box:

=Dlookup("AvgofR1","Q1")

The long answer is that your database design is flawed. I'm afraid it looks
like a classic case of believing that a database is just Excel with knobs
on.

I suggest that you really need two tables, roughly a follows:

Buildings
======
building_id (PK)
address
city
state

Apartments
========
building_id PK
apartment_no PK
bedrooms
rent

You then need a query (called, say, qryAverage) which calculates the average
rent for each number of bedrooms, something like this:

SELECT bedrooms, AVG(rent) AS average_rent FROM Apartments GROUP BY bedrooms

You can then create a form (to display, I would suggest, in either datasheet
view or continuous forms view) which uses qryAverage as it's RecordSource.
Now you can add that form as a subform to your original form.

Hi. I'm using Access 97. The short description of my problem is:
I have an unbound form named Stats1. On the form I have 3 text boxes
(AvgofR1, AvgofR2, and AvgofR3).

There are 3 "total queries" (Q1, Q2, and Q3) that when run return an
average in a field called AvgofR1 for query 1, AvgofR2 for query 2,
and AvgofR3 for query 3.

How do I get the value of AvgofR1 from query Q1 to appear in the text
box AvgofR1, the value of AvgofR2 from query Q2 to appear in the text
box AvgofR2, and the value of AvgofR3 from query Q3 to appear in the
text box AvgofR3 on the unbound form Stats1?

I've searched forums up and down and have not found any previous posts
that would help.

Here's the long description which may help in suggesting other
approaches to this problem:

My database named "Apartments" consists of apartment properties with
fields that describe each property (address, city, state, bedrooms,
rent....). As each record (apartment building) may have 1 to 3
individual apartment units, there are fields called U1, U2, U3, and
R1, R2, and R3. The data in the fields for U1, U2, and U3 indicate
the number of bedrooms in that unit (1, 2, 3). R1, R2, and R3
indicate the rent in dollars for that unit. For example, for the 1st
property or record, U1 could = 1, U2 could =1, U3 could = 2. For the
2nd property or record, U1 could = 2, U2 could =3, U3 could = 3. What
I want to do is to be able to find the average rent for all 1 bedroom
units, the average rent for all 2 bedroom units, and the average rent
for all 3 bedroom units. Once these averages are calculated I want
them to appear on a form, or on a report.

I have individual queries that will do this for each number of
bedrooms. Q1 calculates the average rent for 1 bedroom apartment
units, Q2 calculates the average rent for 2 bedroom apartment units,
etc. But from here I am stuck on how to get these averages from all
the individual queries onto a form or report. I've gone to the
control source of the properties for each text box on the unbound
forms (Stats1) and tried to enter code like "[Q1]![AvgofR1]", but it
does not work.

Thanks in advance.

Gary