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 RepliesI 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?
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
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 RelatedI 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 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.
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 RelatedI 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).
A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is
Code:
dim my_var as String
my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _
& " FROM Table1 " _
& " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _
& "FROM Table2 " _
& " WHERE Emp_ID = 3 "
Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )
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?
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
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
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?
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
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 RelatedI 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.
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?
I have 3 separate tables with the following fields: Year; Name of School; Emis No. (Primary Key); No. wrote; No. passed; Pass %. These tables were created in Excel and imported in Access.
I would like to do the following (see table below): 1. create a form that would allow me to view all the data per school in datasheet view and also allow me to enter new data annually; 2. create a new table where this information can be stored
.
YEAREMIS NOSCHOOLNO WROTENO PASSEDPASS %2009109964Bhuqwini4012302010109964Bhuqwini6126432011109964Bhuqwini6422342012109964Bhuqwini
I have 3 separate tables with the following fields: Year; Name of School; Emis No. (Primary Key); No. wrote; No. passed; Pass %. These tables were imported from Excel. I would like to do the following: create a form that would allow me to view the all the data per school in datasheet view and also allow me to enter new data annually.
View 2 Replies View RelatedSo I have a form, it has a few fields from a related table in it, related by the name. I click 'new' on the form, but when I click save Access complains about the related table does not have a matching record. How do I have it create a new record?
View 2 Replies View RelatedWe are trying to use access as a data entry for field reports. The reports are taken from Excel. I have copied the reports we used in Excel and inserted them into Access so they will look the exact same. For example, one of the forms tests the concrete strength over time. The goal of this report is to be able to take all the final strengths from multiple tests and create another report. This isn't the issue, I just want you to know what we are using Access for. I know this isn't the typical use for Access but it should work.
My problem. One of the reports has over 375 data points. I know a table has 255 fields. So, I need two. We will be inserting the data into a form, have it save into the table and publish the final report in a report.How do I insert two tables into one form? The two tables will share a test location field.
I have a form where the no. of fields is more that 255. say 320. So i have created 2 tables, TblX and TblXOne. I am using sql server 2000 as the backend. i have an autogenerated ID in TblX and X_ID column in TblXOne.
255 cols are stored in Tblx table which is the max. so remaining cols of the same form are stored in TblxOne table. I use X_ID as the primary key of TblXOne table. X_ID is same as ID.
I am importing from an excel the data into the form fields, doing some calculation in the form fields and then saving them to the database.
While i am doing this, i get an error message: Cannot find primary key of TblXOne. How ishould be able to add records into both tables..
Some one pls help..
i want to able to update two different table that are not linked at all in anyway.... in a single form... is tt possible?
View 1 Replies View RelatedHi There!
I have two tables 'table1' and 'table2'. They both have 'clientnumber' as the keystring.
I want three textboxes in one form:
1) Textbox1 bound to 'clientnumber' (key) in table1
2) Textbox2 bound to 'clientname' in table1
3) Textbox3 bound to 'xxxxx' in table2 where 'clientnumber' is the same as in textbox1.
My question is, how do i make textbox number 3???
Hi all. Here I go again ...the form I have (frmSupRFA) pulls data from two different places - a table (Supplier_Table) and the table (Suppliers) the form is bound to. The Suppliers table is update by the user by clicking on a command button that initiates an append query that appends data from the main table to the Suppliers table. After the user performs the append, then he choses a log number from a combo box to work on. That all works fine.
The issue I am having is the data that is pulled from the Supplier_Table - I cannot get it to save to the Suppliers table that the form is bound too. It is selected from the table by using an unbound combo box (SupplierName) that has six columns (SupplierID, ContactName, Address, City, State_Province, Postal Code). Its row source is a query called qrySupContact. These six columns populate six unbound textboxes (which works fine) using the following code:
Private Sub SupplierName_AfterUpdate()
Me.SupplierID = Me.SupplierName.Column(1)
Me.ContactName = Me.SupplierName.Column(2)
Me.Address = Me.SupplierName.Column(3)
Me.City = Me.SupplierName.Column(4)
Me.State__Province = Me.SupplierName.Column(5)
Me.PostalCode = Me.SupplierName.Column(6)
End Sub
How do I get the results to save to the Suppliers table with the rest of the record please? I know this is easy, but I cannot think of how to do it.
Thanks in advance for your help.
Camille
I have two tables A and B - I am creating a form for data entry into Table A. The officeID field is a combo box that gets the information from Table B
Sometimes the information is not in Table B - so I would like to be able to enter the information from the Data Entry Form for Table A
Table A Table B
lastname officeID
firstname office
officeID
I have no idea how to do this - I tried a subform, but I could not enter data into Table B from it.
Please help