Different field control sources for unbound 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












0
gdaniels
1/2/2008 5:08:06 AM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
650 Views

Similar Articles

[PageSpeed] 38

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.


"gdaniels" <gdaniels@askdata.net> wrote in message 
news:d62d2d2d-ee4a-4fce-95eb-bc6af78c3e46@i29g2000prf.googlegroups.com...
> 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
>
>
>
>
>
>
>
>
>
>
>
> 


0
Baz
1/2/2008 5:40:05 AM
Thanks!!!  the "=Dlookup("AvgofR1","Q1") " works perfectly.  Just what
I needed.

Gary




On Jan 1, 11:40 pm, "Baz" <b...@nospam.nowhere> wrote:
> 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.
>
> "gdaniels" <gdani...@askdata.net> wrote in message
>
> news:d62d2d2d-ee4a-4fce-95eb-bc6af78c3e46@i29g2000prf.googlegroups.com...
>
> > 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

0
gdaniels
1/2/2008 3:00:35 PM
Reply:

Similar Artilces:

Additional lookup for contacts on opportunity form
Hi, I try to customise CRM 3.0 without any training - therefore a question for which I need some help. On the opportunity form exists a look-up (potentional customer) for contacts/accounts. I'd like to add an aditional one to the form (to replace the decision maker). I tryed to create a new field on the opportunity entity but there's no field-type "look-up". It's also not possible to create a new relationship to the contact entity (both system entity's). Does anybody of you know how I can do this? (if yes - please explain it more detailed...). Thanks Joerg Look...

Forms 07-27-07
Hi, A VB Code is used that has the following line: Me.Visible = False (Me refers to the forms names frmBirthsearch) After this line a second form (form2) is opned based on a query that reads two dates from the frmBirthsearch using the line code: DoCmd.OpenForm "frmBirthdates", acViewNormal at this point frmbearthsearh is hidden. What command shold I add after this in order to close the frmbirthsearch? ...

How to create Windows forms like in VB using VC++6?
Hi I'm newbie to VC++6 environment. I already know VB6,VB.net, C,C++ and JAVA. Now I am doing project in Directx sdk using MS-VC++ 6. I learned MFC enough to program. But I couldn't make Windows forms like VB. I have tried it using Appwizard but it so Clumsy. The Appwizard is focusing in Doc/View architecture. Now Anyone tell me how to create Windows Form Application without Doc/view Support in MS-VC++ 6............................ Any Help, Lot of thanks in advance................... itekchandru wrote: > Hi > > I'm newbie to VC++6 environment. I already know VB6,VB.n...

Displaying MS Excel Chart control in ASP.NET Application
Hi, We are working on an ASP.NET (VB.NET) application. In one of th screens of our application, we need to show a graph and for this we ar using MS Excel Graph control. We have the graph prepared in Excel. Th application would feed in certain values into certain cells of th excel file and the graph would be drawn by the graph control based o these cells. We need some help on how exactly to display the excel graph contro directly on the screen of ASP.NET application. Thanks for the help oursm -- oursmp ...

1 Chart
I presently have an XY line chart showing asset price over time. Pretty simple. X Axis - Time Scale Y Axis - Asset Price I would now like to add an additoinal series showing the volume of assets traded, ideally this would be as a bar chart sitting "underneath" the asset price on the chart. They would share the same X Axis. I have added another series, but this simply displays the volume traded as another line, and even when this is set to a secondary axis the scaling makes this unworkable. i have adjusted the scales of both, still this does not make it workable, i want the series...

Query fields
Is it possible to write a criteria where the value of an empty field is "0.00"? Background: I have three queries with different customer account groups. Not every salesperson has customer accounts in every accountgroup - so, he will not shown up in that query. But he has accounts included in another query. Now, I would like to get a sum of commission earned by each salesman calculated from all three queries together. Since the salesman has no record in one query the total sum of that specific salesman is not shown. Any idea how to solve that problem? Thanks Klaus On Wed, 29 A...

Report Writer Text Spacing Problems While Modifying a PO form
Due to Report Writer static text field size limitations, I am forced to try to use two adjacent text fields to complete a sentence. I am having an inordinate amout of problems trying to get words to align in sentences with the correct amount of character spacing between words that span the text field boundaries. And, the process of toggling between Report Writer and Dyamics to get the Purchase Order form to reprint is time consuming. What am I missing (besides patience)? -- Jay Jay, 1) Setup a calculated field for your static text. Set the result type to String, and use the Constant...

Changing Text Box Size and Location on a form, at run time with Access Xp
Hello All, Is it possible to change the size of the text box (or any other control) on a form at run time, by Drag and drop or resizing borders using the mouse events. (Just like the design mode but the user will use it at run time on form view). Thanks in advance... You should be able to resize in your code if necessary. Me.control.Visible = True Me.control.Height = 1000 Me.control.Width = 1000 Don't forget to set them back when you are done. "Developer" wrote: > Hello All, > Is it possible to change the size of the text box (or any other control) on > a ...

Percentage difference calc that knows the largest figure
I have a calculation in cell A3 which looks at the content of Cell A1 and Cell A2 and then works out the difference between both as a percentage For instance Cell A1 = 100 Cell A2 = 10 Cell A3 returns the difference as being 90% My simple calculation in Cell A3 is as follows =(A1-A2)/A1 Cell A3 is formatted to give the answer as a percentage to 2 decimal places The above works fine as long as the number in Cell A1 is greater than the number in Cell A2 If the number in A2 is greater than the number in A1 then my calculated answer is incorrect For instance Cell A1 = 10...

Keeping a table in a form editable and checking that fields are filled in before allowing a save
Firstly apologies if this is the incorrect forum but I was looking for a general word forum and could not find one. Please point me to one if one exists. I am trying to create a form where I want to specify what items need to be filled in. (Review minutes from design reviews). I want to make certain fields mandatory like the date, attendees and check list used and want to block saving of the document with a warning until they are filled in. Is there a way of doing this? Also as a part of the review actions are filled in to a table. depending on how many actions there are the table...

Error 2455 Closing Access 2007 database with form open
I have a form with a subform that is requeried when you select a new key for the main form from a combo box. Everything works fine - usually. But sometimes if you have the form open when you close the database down you get the following error message (twice) in a pop up. You say OK (twice) and the database closes OK "2455 you entered an expression that has an invalid reference to the property form/report" If I close the form before the database I never get the error. If I do not touch the form before you close the database I don't get the error. If I update a field by t...

Column Reference to External Source As a Variable
Can anyone help me convert the column referenced in the formula below into a variable that the user can define? More specifically, I have several columns that I need to read from an external workbook (Short_Billy.xls). Each column to the right of column C represents an additional day out in a 14 day projection from today (whose data is held in column C). In cell I5 of my active workbook (Inventory.xls), I would like the user to be able to enter a value representing the number of days out they would like to see the projection for (0=today=Column C, 1=Tomorrow=Column D, etc.). In cell I6, I...

Too many different cell formats #6
I am running into the error message: Too many different cell formats Is there a solution to lowering the number of formats I am using? Just trying to change them to make some consistent gives me the same error message. I tried running the search on the forums on my topic but they have been disabled for a Microsoft upgrade. Thanks! One idea - Rob Bovey's excellent Utilities add-in will list all the formats in use in your workbook, allowing you to manually delete what isn't being used. http://www.appspro.com/Utilities/ExcelUtilities.htm You can also see the source code for ...

Change of field separator
When I open CSV files in Excel all data is put in one column. Can anyone tell me, where I change my set up, so I get another field separator? Please be specific, because my Excel is a Danish version, and sometimes I have a hard time following the English instructions. Thanks! Jane Hi you specify this in the Windows regional settings. Another idea: - rename your *.csv file to a *.txt file - now open it with excel. The Import wizard should appear and should allow you to specify a different delimiter "Janepige" wrote: > When I open CSV files in Excel all data is put in one co...

WdfUsbTargetDeviceCreate creates NULL Control Pipe Handle
Hi, We have a usb composite device which has one mass storage interface and another as a network interface. We are developing a WDF driver (NDIS-USB) for the network interface. Immediately after WdfUsbTargetDeviceCreate if I break into the debugger and examine the newly created device, then I see that the Control Pipe Handle is NULL! Here is the actual output: -------- kd> !WDFUSBDEVICE 0x0000057f`fe5905f8 WDFUSBDEVICE 0000057ffe5905f8 ============================= Config descriptor fffffa80037216b0, device descriptor fffffa8001a6fb58 Control USBD_PIPE_HANDLE 000000000000...

Zero filling a number field
I have an auto number field that I want to zero fill to six digits but can't figure out how. Any ideas? You don''t say where you're trying to do this, but basically Format(YourAutoNumber, "000000") pdlginternet@aol.com wrote: >I have an auto number field that I want to zero fill to six digits but >can't figure out how. Any ideas? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200711/1 You can't do that wi...

Problem with View Source / Outlook 2000
I searched some more, found that the problem is caused when the Temporary Internet files cache is too big. Deleted the cache and the problem is solved. Changed the settings to a more reasonable 100 MB and it shouldn't happen again Steven =================================== I'm using Outlook 2000 and Win 2000, no problems now (had to reinstall Windows last week for unrelated reasons). I also use ZoneAlarm and Outlook has permission only to access the Trusted zone, not the Internet in general. Something has happened in the past couple of days. I got an email that looked blank ...

Advanced Financial Reports--Correct Type & Source
My Advanced Financial Reports have been modified and are incorrect. My Balance Sheet doesn't balance. It is subtracting Net Profit instead of adding it. Could someone please look at their Advanced Financial Report Definition Screen and tell me what the Type and Net Income/Loss Source is to be for Balance Sheet and Income Statement? I greatly appreciate the help. For my Balance Sheet, the Type is "Balance Sheet" and the Net Income/Loss Source is "P&L 2005", which is the name of my primary income statement. For my Income Statement, which is "P&...

building a custom calender control
I'm trying to build a calender control that will allow the user to select a month and display any appoitments / jobs due for that month. I want it to look like the calender in Outlook. i'm having trouble creating this in ACCESS 2007. Could anyone help please? This is a gigantic task. Is there some particular area where you are having a problem? Most people would opt for automation of outlook rather than trying to accomplish this... -- Jack Leach www.tristatemachine.com "I haven''t failed, I''ve found ten thousand ways that don''t wor...

Annoying problem with lookup field
On a client, I have a MOSS 2007 portal with two lists which are not wroking as expected. One contains people and the other one contains a lookup field based on data from the first list. When someone which hasn't got administrative rights logs in and tries to see the properties of any element in the second list (on DispForm.aspx), they can't see the lookup field value, and if they try to edit the element, the dropdown control used to choose a person from the first list appears empty. Administrator don't have this problem. The users having the problem have collaboration rig...

MemDC & Scroll Control with Mouse Wheel
Ever since I changed to use MemDC, using the mouse wheel made my CSrollView a complete mess. The wheel mouse control was working fine with my flickering version. Then I used MemDC to get rid of flickering issue, now I am having this scrolling issue for using the middle wheel mouse button. What could be wrong? Thanks. On 12 Aug 2005 06:53:36 -0700, guxu@hotmail.com wrote: >Ever since I changed to use MemDC, using the mouse wheel made my >CSrollView a complete mess. The wheel mouse control was working fine >with my flickering version. Then I used MemDC to get rid of flickering &g...

obtaining data in text form from a table
Hi all, I like to be able to obtain the dates in a text format from the table below. ie Test2 8-Feb Test5 4-Feb,8-Feb Test6 4-Feb,5-Feb, 9-Feb Do I need to do this by macros and if so, any help would be appreciated. Table Care Recipient Surname 4-Feb 5-Feb 8-Feb 9-Feb Test1 Test2 8-Feb Test3 Test4 Test5 4-Feb 8-Feb Test6 4-Feb 5-Feb 9-Feb Vlookup should do what you want, as in: =vlookup(a2,Table,2,false) Adjust the ranges t...

forms and column lengths
Is there a way to have excel do an auto "carriage return" to the next row when you have reached the specified maximum number of characters in the row above?? Hi there's n o bulit-in feature for this -- Regards Frank Kabel Frankfurt, Germany "Blair" <Blair@discussions.microsoft.com> schrieb im Newsbeitrag news:C1D2CAAD-C4E9-492A-ADF4-CBDB659514A3@microsoft.com... > Is there a way to have excel do an auto "carriage return" to the next row > when you have reached the specified maximum number of characters in the > row > above?? ...

Case statement for unbound textbox
I am creating an invoice where each product is totaled in an unbound textbox call [LineTotal]. Here is the complicate part. The total is based on certain conditions. I was able to do it just fine on the form, but am having a hard time getting it to do it in the report. First, if there is a discount I have to take that into effect. Second, if there are multiple copies I give a 50% discount unless the hours are 15 min (always $2.50) or 1 hour (always $10). Anyone know what I should do?? Thanks, KS Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) If PrintCount = 0 Then ...

Enable/Disable a Form Control Based on Security Group Permissions
How do I enable or disable a control in a form based on a user’s security group membership? For example: If I have a checkbox on a form (call it box1), I want box1 to be enabled if the user who opened Access is a member of a security group called “Breaker Test Admin.” For members of any other group (except of course “Admins”), box1 should be disabled. Thank you, for your help! On Mon, 02 Jul 2007 18:57:13 GMT, "BenS" <u35527@uwe> wrote: >How do I enable or disable a control in a form based on a user�s security >group membership? For example: If I have a checkbox on a ...