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.
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.
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!
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!)
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.
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.
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.
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?
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.
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.
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?
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
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.
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.
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?
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).
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.
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?
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?
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
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...
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.
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...
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:
