How To Query Unrelated Table To Do A Calculation?
Apr 30, 2006
Hi all, I have a problem in returning values from 2 different tables because they are not related. Let me explain:
I'm trying to do "Payment Due" query by substracting the amount in the "Cost" table with the amount in the "Payment" table ([CostAmount]-[PaymentAmount]). However, since no payment has been made, the table contains no related record.
At first I thought the problem lies in null values the table return hence I tried to use NZ function to convert null to zeros. Then I realised that no values has been returned from the table due to no related record available.
Can anyone help me?
Cheers
View Replies
ADVERTISEMENT
Feb 29, 2008
Hi,
Is it possible to create a lookup in a query against a table which has not relationship but has a match?
E.g. iif(spec(in query)=Spec (match table) then bring back cost in (match table)
Hope this makes sense?
Thanks
View 1 Replies
View Related
Jan 31, 2007
Hi All,
My database has two tables:
1. Complete Address Data
2. Address conditions.
Table 1. has verbose customer [address] data like 11 smith st, 14 Kent Rd etc.
Table 2. has [std] address conditions, ST, RD, Street, Road etc.
How can I query ALL records by field [address] in table 1 by using ALL [std] address conditions in table 2?
The address conditions can occur anywhere in the [address] string, so I was thinking about maybe a CONTAINS condition, but I'm not sure
View 2 Replies
View Related
Dec 26, 2014
I have tried to combine data from 3 different tables (unrelated) to make a new one. However, as I understood I can not do this because these 3 tables are unrelated. In my case my 3 tables are for 3 labs' material requirements. I am planning that each lab will fill a form which directly related to there field and direct to me. That is why I intend to have separate tables for each. However, I want to create a table where I will be able to see all the requirements of different Labs altogether. This new table will be kind of Orders.
View 7 Replies
View Related
Aug 31, 2004
I am trying to design a report that will Give information about down time and work time during the day.
I have three tables that I am trying to query:
TimeSheet - Employee name, date, etc.TimeSheetSub - Times worked (related to TimeSheet)Orders - Including Start and Complete dates and timesI want to find out on TimeSheet.Date Between TimeSheetSub.Arrived And TimeSheetSub.Departed how many minutes were worked on an order and how many many were not (Orders.Start And Orders.Complete).
When I try to combine these three tables in a query, though, it either ends with tons of records or doesn't work because the tables are unrelated. I was able to do it by making a subquery for the Orders part, but this will not work to make a report.
Any help on this matter would be greatly appreciated!
Regards,
Philip
Email Me
www.321green.com
View 4 Replies
View Related
Feb 16, 2005
hi - i have tried search for the solution to my query - but to no avail, so here goes:
I have a query which is based on more than one table. In the query, I have specified a calculation, eg. Final Price: SUM([Sale Price] - [Discount])
From this query I have a form, just showing everything. Details are put into the form, and viola, they appear in the query if checked. However, they do not appear in the table
My assumption why this is not happening was because the Final Price is no longer "record source"d from the original table. How can I combat this so that it does appear in the table?
Thank you (sorry if it is easy - but i dont have a clue!)
View 6 Replies
View Related
Apr 13, 2008
Hi, I am wondering If I can Sum the value of rows in one field of my Query Table and Inserting that Calculated value into the bottom of the summed Row in the VBA or through Query Design View.
Please let me know, Thank YOU!
Below I have attached the picture of the data that Im trying to sum and insert into a new row that Hopefully can be created through MS access Query.
View 1 Replies
View Related
Jun 11, 2007
N.B. This is not your usual 'Bad practice to Store calculated values in a table' post
I have a table of items.
Each item has a different formula attached which I need to store as a literal value in the table and execute it as a normal expression in a query or vb.
e.g
Columns:Item, QTY, Hrs, mins, ItemFormula
Data: Car, 2, 7, 3, [QTY]+ [Hrs]/[mins]
So ' [QTY]+ [Hrs]/[mins]' would literally be stored for that rercod and is how it would be displayed in a table, form, report etc as it is important for the user to see how the calculation is performed as well as seeing the end result which will be calculated as normal in vb or a query.
The formula will vary from record to record.
How do I go about this?
TIA
View 6 Replies
View Related
Feb 29, 2008
I have three tables DriversName, Route and Orderlines. My job, if I should accept, is to create a form for the dispatcher to assign a driver and a route to each order. Preferably in a query so I can create reports based on Route and Driver, for the loaders & production, and Driver and Route for the driver.
I have a nice query already built to capture the order lines to be delivered daily, averaging around 800 lines. My problem is relationship. There is no relationship to Orders from either the DriversName or Route tables. Unfortunately the Orders table cannot be added to as the next data download would just remove the fields. I have tried just to slam in the tables in my query but of course I get a repeat data line for every driver name and Route letter.
I need this form to have the dispatcher call up the order by number and have two comboboxes for them to lookup and assign order numbers to Driver and Route. Thanks in advance for any suggestions.
My question is, does someone have a suggestion as to how I could build a relationship of Drivers and Routes to the Order query?
View 9 Replies
View Related
Aug 26, 2004
Im a new user to access and I was wondering if someone could tell me if it is possible to put two completly unrelated tables into a form. All I want to be able to do is look at the information from both at the same time without having to switch between forms. Ill apreciate any help you can give. Thanks.
View 2 Replies
View Related
Dec 6, 2005
I need to have a field show up in my report that is unrelated to all the other information in the report. It is a one-time entry that the person enters upon opening the database. It's basically the expected amount of work we'll be doing for the year. I need to display that at the top of my report and then utilize that number for calculations with other fields in the report to produce a couple of extra calculated fields in the report. I've tried it a few ways and I've either gotten #name? or #error.
Any ideas?
View 1 Replies
View Related
Oct 22, 2006
This may be a simple question, but I'm not sure what to even search for. I have a form with multiple subforms. All the subforms have related fields that are linked via relationships. But one subform has no fields in common with the parent form.
I need to be able to have the subform update itself whenever the record is changed on the parent form. I can't figure out which event procedure to use. It updates correctly if I assign my procedure to the form_click sub, and then click each time I want it updated, but that's a pain. I've tried on load, on data change, on activate, on data set change, and a couple others, but nothing works automatically.
How can I make the subform run a subroutine each time the parent form changes records?
View 4 Replies
View Related
Jan 2, 2007
I have a 'Services' subform that allows a user to enter payment information, but the amount entered is over a specified amount then a field's value in another data is supposed to change (in this case, the 'Contract Status' should change from its default 'Active' to 'Void'). I was able to get an error message to appear upon this occurrence, but I have no idea how to change the value in the other table. here is my code:
Private Sub MaxComp_AfterUpdate()
If Me.MaxComp > 50000 Then
MsgBox "The specified amount is above the $50,000.00 limit. This contract will be flagged and reviewed by HCC to ensure compliance.", vbExclamation, "FMV Limit Exceeded"
'*** [sbfrmContracts]![Contracts]![ContractStatus] = "VOID"
End If
End Sub
***:This is the line I attempted to use to change the field value on the other table
Thank you in advance and happy new year.
View 14 Replies
View Related
Jul 2, 2012
I am trying to take similar fields from unrelated tables. both fields contain information about the age of my clients. The tables they are in are unrelated, as they are different types of clients, and therefore can not find anyway to relate the tables. I need to come up with a total number of clients at particular age groups. Meaning i need to know how many clients from both tables are between the ages of 8-20, even though the records have no relation. All i need is a count, i dont need to display the records, i dont need to change anything, I just need to know how many.
View 4 Replies
View Related
Jul 2, 2015
I have a form based on Table A. When a yes/no tick is clicked in the form, I want to delete a record from Table B. The tables are joined by ID numbers.
View 1 Replies
View Related
Aug 9, 2014
I am fairly new to Access 2013 but am trying to create a query that will select random records from three totally unrelated tables and display the results together as if one table -- think video slot machine wheels. Each table has two fields - ID which is the primary key and NAME. The data in the tables are names of states, names of colors, and types of animals. Each table has a different number of records. My end result is a table that selects X number of random records from each table and displays them side by side like this:
Desired Result:
Animal Color State
cat red Ohio
dog blue Texas
fox green Iowa
I have been able to create three individual queries that will pull X number of random records by using:
SELECT TOP 10 Animals.[ID], Animals.[Name] FROM Animals ORDER BY Rnd(-(100000*[ID])*Time());
SELECT TOP 10 Colors.[ID], Colors.[Name] FROM Colors ORDER BY Rnd(-(100000*[ID])*Time());
SELECT TOP 10 States.[ID], States.[LongName] FROM States ORDER BY Rnd(-(100000*[ID])*Time());
Using the three queries above I get three separate lists. how to make one query that will randomly pull from all three tables and make the display above?
View 14 Replies
View Related
Feb 5, 2014
I am trying to link two unrelated sub forms to a main form so I am able to query data all at once and make a report that displays all this data at once. I do not know if this is possible. I will tell you to the best of my ability about what I have going on.
My main form is a shift report. The primary key is a auto number ID. The rest of the fields are date, name, shift, vehicle. etc.
The first sub form is area attendance. Field are as follows auto number ID (primary key), report ID(which comes from the main form, linked), the area, and the area attendance.
The second sub form is the event log. Fields are as follows auto number ID (primary key), report ID(which comes from the main form, linked), time in, and events.
My relationship now is simply primary key from the shift report (the autonumber) going to the first and second subforms report ID's.
Problem is I can not query two distinct subforms like this (I realized).
View 1 Replies
View Related
Apr 11, 2014
I have two forms:
frmOpeartions
frmManagers
frmOperations allows the user to assign a manager to an operation by selecting the manager record from a combobox. Occasionally the user may need to setup a new Manager record if one hasn't been setup already. In this case there is a "New" "button" (it's actually a label with an on click event) that the user can click to open frmManagers and add the new manager record.
The code to open frmManagers is:
Private Sub lblNewManager_Click()
DoCmd.OpenForm "frmManagers", acNormal, , , acFormAdd, acDialog
Forms!frmManagers!cboMoveTo.Visible = False
Forms!frmManagers!lblManagers.Visible = True
End Sub
Once frmManagers is open the user creates the new Manager record and then closes the form using a similar label with an on click event:
Code:
Private Sub lblClose_Click()
DoCmd.Close acForm, "frmManagers", acSaveNo
End Sub
frmMangers also has an OnClose event that will refresh any comboboxes on other forms that refer to tblManagers to make sure that new Manager records will be available immediately for the user to choose from:
Code:
Private Sub Form_Close()
If CurrentProject.AllForms("frmPlants").IsLoaded Then
Forms!frmPlants!cboPlantManager.Requery
Forms!frmPlants!cboQCManager.Requery
[Code] .....
So the problem comes when the user clicks the Close label (acting like a button) on the frmManagers. The code successfully closes the form and the on close event successfully refreshes any comboboxes on forms that may be open, but then for some reason it attempts to run again or perhaps continue running the onClick event that opens frmManagers. Since the form is already closed it gets hung up on trying to change the visible properties of the controls and the code fails.
View 6 Replies
View Related
Feb 17, 2013
My Access 2007 database with one main table. There are associated queries and reports that sort under that table when I have tables and related views checked. The problem I am seeing is some clearly related objects fall under unrelated objects and not with the table.
Short of using a custom group and sorting them manually?
View 2 Replies
View Related
Feb 14, 2008
Hi all - I have a table that is keeping track of projects we are working on. There is a field with the overall cost of the project and a field with the percent paid. I would like a field that calculates that amount still owed. Is this possible?
Thanks!
Amelia
View 2 Replies
View Related
Oct 18, 2004
Hi all.
I have a TableA with 3 fields: FieldA, FieldB, Result. A Form1 based on these fields. User will put data in FieldA, FieldB and calculate in Result. Problem is how to keep result of calculation in TableA?
Thanks
View 4 Replies
View Related
Feb 22, 2006
First off, I am not sure if this belongs in the queries forum but it seems most of the question is geared towards a table so I have posted it here.
At the moment I have a query that contains several fields that use nested IIf statements to determine which set of rules to apply. I was wondering if it is possible to somehow set the IIf variables up in a table and then for just to lookup the table in the query.
For example, one of my simpler IIf's looks like this...
Cost:
IIf([AType]='Skill',
IIf([StartDate]<#01/08/2004#,
IIf([AOP]>0,
([BasicRate]-[Frame])/[AOP]/100*[Proportion]),
([BasicRate]-[Frame])/[OP]/100*[Proportion]),
([BasicRate]-[Frame])/[OP]/100*[Proportion])
So I was thinking could I setup a table to show:
FieldName AType StartDate AOP Calculation
Cost Skill <#01/08/2004# >0 ([BasicRate]-[Frame])/[AOP]/100*[Proportion])
Cost <>Skill Is Not Null Null ([BasicRate]-[Frame])/[OP]/100*[Proportion])
and then somehow perform the calculation rather than just look it up.
The main reason is it would be so much easier to see what is being calculated rather than try and weed my way through nested IIf statements.
Can anyone shed any light on this or maybe even a better way to accomplish what I am after?
View 1 Replies
View Related
Sep 30, 2007
Is it posible to perform calculations within a table or data entry form. I have a date of birth field and an age field. I have an Update Action Query that automatically works out the date, (See below). However if I design the form with a close form event that triggers the query I get the confirmation message. I can only supress all Action queries confirmation messages or none at all and I do not want to disable this. Therefore I would like the age to be calculated within the table, (or form if that was possible). However I do not know how to do it.
DateDiff("yyyy",[DateofBirth],Now())+Int(Format(Now(),"mmdd")<Format([DateofBirth],"mmdd"))
View 2 Replies
View Related
Jun 27, 2006
I am new to Access. Is there a way to do a automatic calculation in a table field?
eg fieldA divided by fieldB and have the result show up in fieldC
Thanks
View 7 Replies
View Related
Jan 2, 2008
Is there a way to take a value entered in a table to calculate a new value to populate a second field in the table? For example, if the user enters that the company has received $100 from a client, I need to show that $100 in the income field of the table, but then also calculate the commission based on a formula, and save that new commission value as well.
I was doing this in a query, but then the value doesn't get stored...
Thanks...
View 8 Replies
View Related
Apr 30, 2015
I'm currently using a Dlookup function to display a calculated field from a query. This works fine when only one record is entered on the many side; however when additional records are entered it doesn't add the additional records. What function/code can I use to add each record's total as it's entered on the many side?
This is what is working when the many side only has one record:
=(DLookUp("[qryTotalBalance]![NetBalance]","[qryTotalBalance]","[CustID]= " & Nz([CustID],0)))
View 14 Replies
View Related
