Queries :: Connecting Data - Adding Field From Another Table
Jun 15, 2015
I have a query all set up and now I have to add one field from another table in it. I am looking for a date which has the criteria Now() - Last Movement Date. Last Movement Date is the column I am taking from the other table which I just added which is the ZLX02 table. When I run the query, everything but the Last Movement Date shows up. What can I do to get the Last Movement Date to show? Check out the attached pics.
I have two tables: "Tbl_CM_Project_Details" and "Tbl_CM_Inventory"
that have the data that I am trying to connect with a third table: Tbl_CM_Proj_CMI_Connector.
Tbl_CM_Project_Details connecting field is PK_Project_Num to Tbl_CM_Proj_CMI_Connector field Connecting_Project_Num Tbl_CM_Inventory connecting field is CMI_ID to Tbl_CM_Proj_CMI_Connector field Connecting_CMI_ID
On the form I have a SQL Query where I enter the PK_Project_Num and the CMI_ID Auto enters. What I need is for this information to automatically fill the connecting table so that the information is connected.
Table 1 has all of my unique data within it. People within this table may have watched the video once or several times. People within Table 2 have watched the video several times.
I want to add a flag for "Multi" to Table 1. And, where a person is not a "Multi" create a flag that reads "Single".
I've spent countless hours (probably days) trying to achieve this and have failed miserably. Whatever approach I take I end up creating a new table that just contains the "multi" people.
I have tblCategory and tblExercise. The data in tblExercise can sometimes match with more than one record in tbCategory. When inputting data into tblExercise how can I choose to have that stored under more than one field in tblCategory?
Example:
tblCategory: Arms tblExercise: TRX Biceps Curls so TRX Biceps Curls can also be under tblCategory: TRX
Right now I have to input TRX Biceps Curls under Categories Arms and TRX. Very time consuming.
I am looking to create a query that adds a new field to the resulting table. The field should be 'Yes/No' and for every entry the default should be 'Yes'. The query looks something like this:
Code:
Select .*, [here the new field] From MyTable1 Union Select .*, [here the new field] Form MyTable2
The Union statement isn't really relevant, just for completion's sake.
I wonder if someone can help with what must be an easy solution to this problem.
I have a table with 350,000 records, but without a unique identifier field. I just want a simple incrementing number field to become the primary key but dont know how to add this in. I cannot add an autonumber as there are two many records - it falls over.
Ive tried exporting it all to excel, but as well as being cumbersome, something went wrong and so I'm back to square one.
I think there must be a way to add an incrementing number either via a query or in VBA but I dont know how. Please can somebody provide a solution.
I'm using Access 2003...I have a query that searches a parts table by description:
Like "*" & [Enter in Part Description to search] & "*"
I need to add another search to this query, I added another field to the parts table call manufacturer. I add this field to the above query and added this parameter to it:
Like [Enter in Manufacturer] & "*"
I then added a manufacturer to one of the fields for test purposes. For some odd reason this doesn't work. If I take out the manufacturer parameter and don't enter in anything into the part description the query returns all the records. When i type a description into the part description field the query returns the proper records. When I add back the manufacturer parameter and enter through both parameters only the one record returns showing the test record instead of all of them. If I put a part description in and enter through the manufacturer parameter no records show when they should.
I've tried adding a test field to a different table and tried a different query using the like parameter. The parameter works in a field already in the table but won't work with the new field I added. I've done compact and repair.
There is a table with many columns. I want to add many rows to this table. The easiest way is to write a query to do that. But most of the columns have validation criteria, lookup tables, default values, nulls not accepted condition and what not. Where as my new records to be added have only the values in 4 columns and the rest are null. Is there a parameter that can be used to bypass all these controls and add the records? You are going to say "You will destroy the integrity and consistency of the database".
I have an "order details" table that needs to populate a field called "Voucher" with data from another table called "codes". The "codes" table also has a true/false field called "allocated" because once allocated the code cannot be re-used.
I am trying to work out how to automatically allocate the next unallocated code in the "codes" table to each record in the "order details" table when that order details record has a DiscountID of "92".
Order Details Table Fields and conditions/criteria: ID - primary key DiscountID - only when the DiscountID = 92 Voucher - only populated when Discount ID = 92
Codes table Fields and conditions/criteria: ID - primary key code = text field with a code like "einstein01", "einstein02" Allocated = False
Is there a way to put the next available code into the order details record then mark that code as allocated in the codes table. Then, move on to the next order details record that has a discountID = 92, input the next unallocated code and mark that code etc. etc.
Ideally, I would like to do this to happen via an event when the Order forms button "Close" is clicked.
Hi there, I'm currently writing an accounting system and i got stuck with this section of trying to produce the account name in a query.
i have four tables
tblPurchase PurchaseID -Number
tblAccounts AccountID - Number AccountName - Text, usually has names like asset, cost of sales, expense, telephone, electricity, etc...
tblItems ItemID, Number AssetNum (tblAccounts.AccountName foreign key1), Number ExpenseNum(tblAccounts.AccountName foreign key2), Number IncomeNum(tblAccounts.AccountName foreign key3), Number
tblPurchaseLines PurchaseNum (foreign key for tblPurchase.PurchaseID), Number ItemNum (foreign key for tblItems.ItemID), Number
My question is how can i generate the query with the following fields:
PurchaseID ItemID AccountName of AssetNum AccountName of ExpenseNum AccountName of IncomeNum
I am aware that the query would produce three PurchaseID's for every ItemID it would encounter for every PurchaseLine.
A school table is there in old and new data base, if i give school key as 001 (which is the column of school table) i need to compare old database school table "001 key" and new database school table "001 key" and if it is not matched it should be displayed. Please give me detailed dicription with example.
I have a field in a table that keeps rounding the data entry off. It has identical properties to the field next to it but one rounds and the other does not. I tried deleting the field and creating a new one.
Is there a way I can take each entry in a memo field and put it into a text field in a separate table. The database is getting really big and the customer notes field for each record has lots of entries. The memo field looks like this:
I want to take each line and put put the date in a date field and the text in a text field in a separate table linked by CustID. Is there a way to do that?
I'm trying to write a query that will validate a field in an existing table. I want the query to show where any of the 412,000 records fail this strict validation:
AAnnnnnnA
...where A is any letter A-Z and n is a digit 0-9
Clearly I can use the LEN function to check for invalid lengths (anything <>9), but I'm looking for a simple neat expression I can use that will check the format. Does an existing function already exist before I resort to writing my own function in VBA (again)?
I want to set a table field's default value to whatever is displayed in a certain field on a certain form at the time.In other words, say I have a database with a table called TABLE1, and two fields called NAME and SCHEDULENUMBER. I have a form called CreateSchedule with a SCHEDULE NUMBERCONTROL form and a NAME form, and I can enter names onto it, and it records to the proper SCHEDULENUMBER. So if I pull up SCHEDULENUMBER 4, and add three names, when I go back into TABLE1, I can see those three new names, and each one has the SCHEDULENUMBER set to 4.
What I'm trying to do is write an APPEND QUERY to copy a list of names from a different table, and paste them into TABLE1. The problem is that the other table doesn't have a SCHEDULENUMBER field. What I want to do is put a button on the CreateSchedule form that runs an APPEND QUERY, and sets the SCHEDULENUMBER to whatever value is displayed on CreateSchedule's SCHEDULENUMBERCONTROL field.
I tried setting a default value in TABLE1's field properties for that SCHEDULENUMBERCONTROL field, but I keep getting error messages. I just want TABLE1, whenever I add a new record (regardless of how I add the record: manually typing it or clicking the append query button) to look at the form CreateSchedule, and set it's own SCHEDULENUMBER field to whatever is displayed in CreateSchedule's SCHEDULENUMBERCONTROL form.
1. t_Employee. It consists of the following fields: EmployeeID Name Job Title Contract Start Date Contract End Date
2. t_Login. It has the ff fields: UserID UserName Password
3. t_AuditTrail w/ the ff fields (this will used for historical data for Job title, Contract Start Date, Contract End Date, etc.): AuditTrailID TableID (in this case t_Employee) FieldName (JobTitle) RecordID (EmployeeID) OldValue NewValue ChangeDate (date edited) ChangeBy (UserName)
I've already set up t_AuditTrail by putting several (& separate) After Update Data Macros.
Now, I have a form for t_Employee. It has a button that would open a report. This report contains the Job Title history of an employee.
The report is based on a query w/ the ff SQL:
Code: SELECT t_AuditTrail.atTableID, t_AuditTrail.atFieldName, t_AuditTrail.atRecordID, t_AuditTrail.atOldValue, t_AuditTrail.atNewValue FROM t_AuditTrail WHERE (((t_AuditTrail.atTableID)="t_Employee") AND ((t_AuditTrail.atFieldName)="eJobTitleID"));
So the report only shows historical data for Job Title. Which means that Job Title from t_AuditTrail is not related to Contract Start Date or Contract End Date.
Problem(s)/Question(s):I want my report to show the Job Title History and the corresponding contract start date and contract end date (not the date a record was edited). When an employee changes a job title, his/her contract dates change.However, when i start to make a report based on quesries q_AuditTrail_JobTitle and q_AuditTrail_ContractStartDate and q_AuditTrail_ContractEndDate, Access tells me that they are not connected so it cannot make a report. How do I go about this? How do I let user see the Job Title relative to its contract start and end dates?
I decide I want to make a quick Database to merely keep track of a few job details. My use only mainly. Well the new guy decides that to get in the good graces of the production manager we're going to track all this other stuff with it and make the data easy to enter so anyone can do it. Well apparently HE bit off more then WE can chew. And now it's a matter of US working on this by HIM looking over my shoulder as I muddle my way through this. So now I turn to experts for help....
The Problem:
What I've got is a form that allows me to enter a variety of info on the projects we're doing. Nothing special. What I now need to be able to do is enter time employees spent on this job so we can better track how much each department spent on it. The problem is to enter the time ATM we have to rummage through every person's time sheet on a particular job and add all the times and enter it. This is sort of a grueling task at best and leaves ample room for error. What I'd like is another form specifically for entering the time. Chose the Job # from a drop down menu. Then chose which piece of the job from the drop down menu. From there I'd like to merely be able to select which department, (Also via drop down menu I suppose). And then merely have a box in which you enter a dept time and it ADDs it to the time already in the table for that dept. That way it should be easy to go through one employees time sheet, enter all there times and then go to the next one and have it added to a total dept time value in the table. Probably not overly complex. Merely beyond my limitations in Access.
So as an example.
All the data is entered on the project itself aside from time. So I want to be able to look at Mike's time sheet and say ok he worked for 2 hours on job # 4434 Unit 3. Pick #4434 from a drop down menu. Have the second menu sort to know that there's only Units 1,2,3, & 4 on Job #4434. (Got this accomplished.) Be able to pick a production department, let's say graphics. Enter 2 hours. And then when I get to Eric's time sheet I want to enter his time on that Job# and Unit# and have it added to that 2 hours giving a total department time. Got that? Any help or insight anyone?
I want to connect an Excel table with an Access table so when I make changes to excel table these changes to appear in the access table and use the access table for my web site. The excel table looks like:
Room Type01/05/06-06/06/06 07/06/06-15/07/06 21/09/06-30/09/0601/10/06-31/10/06 * Standard Single 26 34 * Standard Double 44 59 * Standard Triple 60 80
When I try to connect this table with access I am getting an error reffering to the second line of the excel table where there are not numbers but dates. The access connected table looks like:
Room Type01/05/06-06/06/06 07/06/06-15/07/06 #Number! #Number! * Standard Single 26 34 * Standard Double 44 59 * Standard Triple 60 80
I just created a database and need to connect it to the data source. The data comes from a http website (intranet from work). When I open the link using firefox, I can view the website with the data in it, but when I open it from Internet Explorer, I get a save as pop-up message to save a csv file which contains all the data. The extension of the http website ends with csv. So it is something like http (slash slash...) Intranetname/referral_dbase.csv
Currently, I am opening the file using firefox, copying all the data manually, and pasting it in a text file using notepad. After that, I import the file into access. The delimiter of the data is this symbol: |
I am trying to find a way to link my database to the website where the data is located so that I can skip the manual process of opening the website and copying the data and saving it into a text file and then importing that file into access. I was thinking to have like a form in access with a bottom that will automatically import that data from this link and paste it into a table in access using the delimiter symbol mentioned above.
Is this too complicated? Is it even possible in access 2010?
Hi. I have a table with a NAME field and a GENDER field.
How would I code a query so that if the gender is MALE than MR. would be added to the front of the existing data in the NAME field? MRS. if gender is FEMALE?
i.e., the result would be MR. SMITH, MR. JONES, MRS. CARSON...
Been using the access database created by a previous employee for a year without any issues. All of the sudden, the data I am entering in the table is not showing up in the query or report.
hi I had a form with TO,DATE,BODY and a button . on click of a button this fileds should get added to a table. I have created a table with same fields. Thanxx
