Question about how to take 2 values from 2 tables on the same day and * them

I have 2 tables set up. The first has 4 fields ID, Date, Quantity1, Quantity2, Quantity3. The Second has ID, Date, Price1, Price2, Price3.

I'm looking for a way to have quantity multiple price on the same day.


Table 1
Quantity 1:300

Table 2:
Price 1: $3.50

How I'd like this to work is for a query or something to take from table 1 on 3/14/2011 300 and multiply it be table 2's 3/14/2011 price of $3.50.

I've only been using access for several days so I'm green. If anyone can help and keep it simple that would be appreciated.

Post your answer or comment

comments powered by Disqus
Dear all,

How to auto fill value from table3 to table1 and 2 ? for exmple, ShareID and Contract#, move from table3 to table1 and 2.

Table1 ShareID ShareName Table2 ShareID Contract# Table3 Date 2/2/2013 Contract# 123 ShareID AAPL ShareName Apple Cost Quantity

Please help, thank in advance!

Ok. this is a stupid question, but i am in a hurry and new to vba programming. I need to collect two parameters into my vba code-wanted to do it with dialog box, but thought that getting value from a form would be better - and this is the question, how do i access the value of combobox in any form?

How can I achieve this? I've got a subform with a specific combo box in it. Depending on a selection in the parent/main form, I would like to update the combo box according to that value.

I populate the combo box's row source is as follow in the OnCurrent event:

	Me.cboReturnLineType.RowSource = 
"SELECT ODS.Return_Line_Type.Return_Line_Type_ID, ODS.Return_Line_Type.Return_Line_Type_Name 
FROM ODS.Return_Line_Type 
WHERE Return_Type_ID = " & ReturnType & " 
ORDER BY Return_Line_Type_Name"

Not sure using a global variable is the way to go but I've declared a ReturnType variable as "Public ReturnType as Integer" on the parent form and I've assigned it a value.

How can I force my code to pick up the global variable's value? And is it at all possible to pass a value between forms and subforms?

I've also tried using [Forms]![frmReturn_Header]![cboReturnType] to point to the specific field:

	Me.cboReturnLineType.RowSource = 
"SELECT ODS.Return_Line_Type.Return_Line_Type_ID, ODS.Return_Line_Type.Return_Line_Type_Name 
FROM ODS.Return_Line_Type 
WHERE Return_Type_ID = 
" & [Forms]![frmReturn_Header]![cboReturnType] & " 
ORDER BY Return_Line_Type_Name"

without any luck.

Hello friends,

How are you, here we're again

I'm asking about how to create 2 timers on the same form

I've a timer to play label with this code :

	Static C
C = C + 1
Me.Label1.fontsize = Abs(C) + 8
If C = 10 Then C = -10
End Sub

and set the timer interval on the form properties to 200

Then I need to put another timer to close with save the current form and open another form.
I need this timer to be every 3 hours.

Any Idea about that ???

Thanks in advance

I need a control which will update one set of fields from another on the same form - It's a valuation page with fields for the current value and previous. Obviously after a year current will become previous and a new value added to current. So I need to be able to hit a button which will copy value a into value b field, leaving me able to then update a.

I've tried an update query - which worked in isolation but I can't then use the result in calculations (for growth etc) as I need to combine it with the query it was based on. Ideally I'd like the two fields a & b in the same table with a query that will update b - only when the button is pressed.

Any suggestions?


I have a query with several fields.
The important fields are Student, Counselor and date.
I need to know how many time Student A (or Student B, C, D, etc.) visited Counselor 1 and Counselor 2 (or Counselor 3, 4 etc) on the same date.
I am not an experienced ACCESS user.
Help please?

Help me!!??

I have a form "frmCommercialTrials" with a field called "cboProjectRef", (the control source of which is [ProjectReference].

I have a button on this form and when I click on it I want it to open a form called "frmFundingDistribution" on the current record of the original form, (this form also has a field called "cboProjectRef", - the control source of which is [ProjectReference]).

I can get the form the open easy enough but cannot get it to do the above. I can pull this off when opening a report by using the code below. Do I need to use something similar?


Quote: Private Sub cmdCheckCost_Click()

DoCmd.OpenReport "rptEventPaymentsCrossCheck", acPreview, , "[ProjectReference] like '" & Me![cboProjectRef] & "'"

End Sub

Hello all,

This is my first time to post on and apologize if this is not the best forum to post in, but from my beginner's knowledge of Access, it seemed logical. I understand basic one-to-one and one-to-many relationships, but that's about it for me.

Background of problem:

I'm a hydrologist trying to set well measurements to a continuous list of dates, as the well measurements are not continuous.

Specific problem:

I've attached a sample of what I'm working with to add context to the problem. The measurements are stored as they appear in the table 'Well_Measurements'.

What I have no clue of doing is creating a relationship and query that could possibly create the table 'Desired_Data_Output'.

More info:

I setup a function in Excel that would search for two criteria and pull the measurement into correct cell, but it only works when applied to a small portion of the worksheet. When applied to larger portions of the worksheet, Excel will freeze as it drains resources.

Instead of the 6 wells and 25 associated measurements in my example, I have 7,000+ wells and 1,000,000+ measurements.

Any help is appreciated. It would be great if you repost the database file "corrected" to help me out.

A million thanks,

jmblackstock Attached Files Access_forums_question.mdb (248.0 KB, 8 views) Reply With Quote 10-02-2010,†10:36 PM #2 ConneXionLost Simulacrum Windows XP Access 2003 Join Date Jan 2010 Location Victoria, Canada Posts 291 Hi,

You could likely get something similar to your results with a crosstab query, but to be sure, I'll need to know the answers to a couple questions first:

a. Do you take measurements every day? If not, is it important to see those (empty) days in your results?

b. Do you ever record two or more measurements on the same well on the same day?

If a crosstab doesn't work for you, there are other ways.


P.S. - I'd be interested to see your attempt using Excel.

Hi all,

It seems like this should be easy but my sleep deprived brain isn't operating at full capacity.

See the image for a representation of query results isolating 2 records that make up transaction #174.

I want to be able to use the 2 parameters for the same transaction in a calculation and use that expression as the control source for a report text field.

I want to show the percentage that Time parameter B is of Time Parameter A.

Am I able to accomplish this with the expression builder? If not, does anyone have any clever ideas on how to get that value from this data?



Issue: Running the PT Query in SQL Enterprise manager takes about 2 seconds.
Double Clicking on the same new linked PT Query takes about 2 seconds to return the 100 values.

When Running a Select statement that uses the same PT, in a snapshot, it takes 1 full minute to return values.

Since we run every month for 2 years ahead (24 Worksheets of reports) it take 24 minutes to run.

Any ideas on how to speed up this process? My thought is that perhaps the Query is calling on a QueryDef that may be re-running, or maybe is runing out of cache?

I have an absolutely huge query: Multiple Selects on SQL Server Views - with Unions, transformations, left joins and more selects with user interface filters. A full page of SQL.
300 Set qdefPT = CurrentDb.QueryDefs("qryPT_WorkingCapital_Position")
310 qdefPT.SQL = strPT ' the sql string is a page full - not shown
320 qdefPT.Connect = strODBCPTConnect ' a global ODBC connect string
330 qdefPT.Close

A short time later, I run a SQL Select statement into a dbOpenSnapshot against the qryPT_WrokingCapital_Position to populate an Excel report.
strSQL = Select (field lists) FROM qryPTWorkingCapital_Position.....
370 Set rsExport = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
This query against the fast PT query takes a full minute.
-- yes this query has some left outer joins

For example: would QueryDefs.refresh help - or is there a way to open up more memory for queries to function?

Two questions that are probably not too difficult, but I canít seem to figure them out. Iíll admit I donít spend a great deal of time working on things like this, and was asked to do this just because I was the only one at the company who seemed to understand Access at all!


I have a table that lists companies we do business with, and then yes/no checkboxes to indicate whether we have PDF files for each of several different forms or policies, plus one to indicate whether we have their current catalog as a PDF file.

I have a form that shows one company at a time, and for each piece of paperwork it shows the checkbox. Next to each checkbox is a button.

I need to make this work so that IF the checkbox is checked, when the button is pushed, it will open the PDF file in Acrobat Reader.

All the PDF files reside in a single folder on a network drive, accessible to everyone who needs to open the PDF files. I would like the manufacturer name to be used to direct the expressin to the right PDF file, rather than having to do a separate expression for each file.

Anyone have any ideas how to do this easily? I am working in both Access 2000 and Access 2003. If this can be done with macros, that is not just okay, fine.


In the form where people will enter data about manufacturers, I would like the empty (Null) fields to have one background color, and then to switch to another color when they have had a value entered. I thought that would be very easy, but canít seem to figure it out. Any easy way to do that?

Thanks to whoever offers a solution!!

Just 2 quick questions.

I have been asked by my employer to make the default value of a text box on a new record the value from a previous record.
In this case, I use continuous forms as the display, and the records record an employee's leave.
So the form runs like this; on the left is the total days of leave left, then some details are in the middle (not important for this) and then on the right is the total days used and the remaining days of leave.
For example:

10 days of leave left ; he takes 2 days, then 8 days remain.

Now at this point, I need it that when a new record is added, the default value for days of leave left will be equal to the previous records days remaining. I know how to do this by creating a recordset in VB code, but Im looking to see if there are any more efficent ways to do this (my database is quite large and so I have to keep it optimised for speed). Any ideas would be great.

And Secondly;
This a simple but annoying thing that happened to me. I recently upgraded from Access 97 to 2k (no XP for me, we are trying to keep everyone on the same version ) and now some of my help files are missing.
Ordinary help files are there, but any DAO help (for example, OpenRecordSet) simply either says "Error in help file" or brings up a blank help screen.

I checked the install and it seems okay, (I even reinstalled to see if it would fix it), but nothing seems to help.
Could I have missed something? Any help (no pun intended) would be appreciated.


Hi All

I am using webElements in my Crystal Report and I have a drop down box for which I am using WESelect. Now the question is how to get this drop down box populated from a database column.

Syntax of WESelect is
WESelect (ElementName, ElementValues, ElementDisplays,ElementDefault, ElementFont)

Ex. WESelect("Value", "1|2|3|4|5|6", "One|Two|Three|Four|Five|Six","One", "")

I tried using {?city} in place of ElementValues and ElementDisplays but no luck.

Please help me out...

Thanks in Advance


I'm having a bit of an issue (confusion) about passing variables from one from to another. I'm worked out the code but something isn't quite right.

The user will select the Issues form, fill out the information requested and click an activity that is associated with the issue. I am grabbing the PK from the issue form (that field is hidden) and passing it to the FK field in the activity form (FK also hidden). That works fine. When the user clicks the appropriate activity button, the code below is run:

Option Compare Database

Private Sub CanvassingCommand_Click()
DoCmd.OpenForm "frmCanvassing Activities", acNormal, , , , acWindowNormal, [ProgramID]
End Sub

When the form loads, the On Load event runs the code below:

Option Compare Database

Private Sub Form_Load()
Dim i As Integer
i = CInt(Me.OpenArgs)
Me.ProgramID.Value = i
End Sub

This works just fine. The problem is when they return to the Issues form, fill out a new issue and then click the activity, let's say the same one for a new issue, the form returns the last issue and not a new data entry. So, how would I reset the variable to get the new PK, etc. (Providing I'm on the right track)


Dear all,

First off let me admit that I am a novice at Access and am well aware that my question could be stupidly simple, so my apologies if this is the case! That said, I'm also at a complete loss for how to proceed..

1st, I would like to add a field to a form where I can input data to records from a second table, and then have values from the first table automatically included in the new record in the second table. I can do this manually with a subform but want to avoid extra data entry where possible.

2nd, if to replicate the information in this way is redundant, then how do I ensure that any reports I do with the records from the second table will pull the values from the first table?

A bit of background: Table1 contains info for lectures recorded. Table2 will share some of Table1's info (unique values like who gave the lecture, the language it's in, etc), but will be used to house info on specific moments in those lectures that we will want to return to later. I would just include these moments as a field in Table1 except that there may be more than one per lecture and I understand that reporting would therefore be more difficult.

Any assistance to this noob would be hugely appreciated!

Hey all. I'm rather new to Access. I do have a good understanding of relational databases and such (Took a few college courses), but I need some help with this.

- It's a database for tracking the performance of Soldiers in our unit (US Army, active duty Soldier).
- Includes the main form "SM_Form" with several tabs on it that each contain subforms for data related to the Soldier (Physical fitness scores, weapons qualifications, etc.)
- One of those tabs is the "OML_Subform" which calculates the raw scores from each of the other tabs, and assigns a numeric value. This value is used to "rank" Soldiers' merit. Higher score = higher on the OML (Order of Merit List). Higher on the OML = first to be picked for special schools, training, etc.

I've more-or-less got the db working properly. It's at about 80% where I want it.
I would like to be able to generate a report that simply lists a Soldier's basic info (Rank, Name, and calculated total of the OML scores).
Since I'm told it's not a good idea to store calculated values in a db (all of the OML scores are calculated from the raw scores), I'd assume the report would have to make the calculations each time the report is generated.
I just don't know enough about reports.

Issue I'm having:
These are relatively simple calculations to convert raw scores to OML scores (as listed in the "OML_Subform" on the DB), but how do I make a report do it?

Please, view the attached DB to get a better understanding of what I'm talking about. (Converted to 2003 format)


can anyone tell me, if there's a way to put a value that a user selects from a combo box in a form into a report?

EG. The report has a field called Names which it needs to get from a Combo box on a form that stores various values for Names (eg. Bella, Bob, Jane, etc)

So, if the user selects Bella from a combo box on the form, Bella should turn up in the Names field on the report.

how do i do this? -

and more specifically - how do i do this if the report is based on a query that is independent of that particular combo box?)

any ideas?


I have created a table (data entered/selected via a form) which includes a drop down box, for 'Ethnicity' to pick a value from another table (ie used Lookup wizard to for options from another table).

The row source looks like this:
SELECT [Lookup_Tbl_ethnicity].
, [Lookup_Tbl_ethnicity].[Category], [Lookup_Tbl_ethnicity].[Sub-category] FROM
Lookup_Tbl_ethnicity ORDER BY 

Setting the ORDER BY
 displays the options in the drop down box in the correct sensible order.

The 'Bound Column' is column 3 - Sub-category. I assumed that this would be the 'value' stored, searched and displayed... but the table (and related form) display the Code.

Why and please can someone advise me on how to chage this so that the Sub-category is displayed?!



I have a simple question. Is there a way to take 2 or more table field values to create the value for another field?

I.e., I have a table named Procs with 3 ComboBox fields: CodA, CodB, and CodC. Each field is text of 2 characters. I have one other field called Concatenation that is text of 6 characters that I want to store the concatenation of the 3 ComboBox fields that are selected. I cannot figure out how to do this simple thing. I have tried unsuccessfully to use the Default value property of Concatenation (=[CodA] & [CodB] & [CodC] ).

This doesn't work. I want to store this result in a table--not on a Form. I can do this easily using a Form.

Thanks for any insights!

My issue is that the form i need to get my value from will be closed before I am able to take the value and pass it onto my new form.

I wanted to use the following code to move the value but with the form closed thats not possible.

	Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Forms!frmAuditQualification!cboCurrentSite) Then
Me!cboCurrentSite = Forms!frmDefaultStart!cboSiteSelection
End If
End Sub

I am getting the idea that I need to use SQL to do this but I am new to SQL inside VBA.

So in the end what I want to do is take the value from field 'cboSiteSelection
' inside 'frmDefaultStart' and pass it to Field 'cboCurrentSite' in form 'frmAuditQualification'.

Any tips to get me on the right path would be helpfull

Hi everyone,

I looked everywhere online and finally found out how to transfer a value from one form into another when the 2nd form is opened. And here's where I ran into my second issue: The transferred value on the 2nd form is located in an auto-update combo box (not sure what they're called), where the rest of the fields on this form needs to be updated depending on the combox value... but I can't get the other fields to update!

I select "Site" in Form 1
--> Click "Edit" button and Form 2 opens
--> Value in "Site" in Form 1 is now in "Site" in Form 2
--> Other fields in Form 2 have not updated even though the value in the top combo box has changed. I have to manually change the combo box in order for the fields to update.

I tried putting Form!frm_FORM2.Refresh on the button in Form 1, tried putting Me.Refresh on Form 2's various before/after update events with no luck. Could anyone please help me?

Thanks in advance for reading!

Hi There,

I like to ask how to take a portion of the data from a memo field.


"This is a test for a memo field and I like to setup a date 01/10/2012. This is somethig I need to do is to able to filter a certain filed in the memo data and take the first 14 character out into differnt querry field."

The answer that I expect in other querry field is:

date 01/10/2012

Please advice.


Under the Service Info Tab, I am trying to add the amount of weeks in Weeks Paid to the Start Date to self generate the Transition Date.

I got my formula to work in the Transition Query but now want it to show in the Accounts Form in the Service Info Tab in the Transition Date.

I need help in either moving the Transition Date value from the Transition Query to the Accounts Transition Date Table or just having the Transition Date value from the Transition Query showing in the Transition Date box in the Service Info Tab in the Accounts Form.

Hope this makes sense.

Can anyone help me?

Thanks! Attached Files (111.0 KB, 4 views) Reply With Quote 04-11-2012,†04:24 PM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,123 Have the calculation in the ControlSource of Transition Date textbox:

=[Start Date]+[Weeks Paid]*7

Set the textbox TabStop property to No.


I have a random number generated based on a query's record count. I would then like to retrieve a field value from that query based on the index that is generated.

The code below works and generates a random number based on the query's record count. But I do not know how to retrieve the EmployeeID field with the random index generated. I'm not sure if I need to open a recordset. I basically just want to go right to the record index that is generated and retrieve the EmployeeID, without having to loop through the records. Is this possible?

Code: Private Sub btnRandomEmployeeName_Click() Dim randomEmployeeNum As Integer randomEmployeeNum = Int((EmployeeCount - 1 + 1) * Rnd + 1) MsgBox randomEmployeeNum End Sub Private Function EmployeeCount() As Integer EmployeeCount = DCount("[EmpID]", "qryActiveEmployees") End Function

Not finding an answer? Try a Google search.