Queries :: Adding Fields In A Table That Are Related To Other Tables
May 13, 2013
I am a beginner in access and I want to populate a new field (REPUN_1 which corresponds to SEGMT_ID in the other table) in my table and this field is coming from another table. The values of the fields need to correspond to the row of my actual table (JMTable) having the same CO_ID, MOVEPLANCD and TTY_NO as the table I want to extract the values from (DI_Treaty_Crosswalk).
Here is my query:
UPDATE JMTable SET [REPUN_1] = DI_Treaty_Crosswalk.SEGMT_ID WHERE JMTable.TTY_NO = DI_Treaty_Crosswalk.TTY_NO AND JMTable.CO_ID = DI_Treaty_Crosswalk.CO_ID AND JMTable.MOVEPLANCD = DI_Treaty_Crosswalk.MOVE_PLAN_CD
and it doesn't work since access ask me to enter a parameter value.
I decide to add a new field to its related table. I always wait to create the form until I think my table is complete, but sometimes I just end up needing to add more info. Is there a quick way to update the form to include my new fields?
I'm trying to create a system where if I enter data into one field it will automatically appear in the corresponding field in another table. For instance if I enter the values 10,12,15 into a field called QID in table A I would like this to appear automatically in a field called QID in table B. Both tables are related and I have enforced referential integrity and 'cascade' options but this still doesn't seem to allow one table to automatically update another. My ultimate aim would be to have a form that you filled in data for the field QID once that then propagated to both Table A and Table B.
Hello I need to add daily records to a related table using a form, from a button or subform displayed on a form updating the master table. This would enter the related key to the new form ready for insertion etc. Can this be done?
I am currently working on an Access Database that houses our security clearance information. Most of the system is up and running but the most recent form has got me spinning my wheels. I have a Word User Form that users will download and complete, once completed the macros will automatically send us the document to be added to our database. Most of this is working the problem is that this portion of the database has multiple related tables and at any given time a user may require multiple records in the related tables. I have created the code to copy most of the information but am getting stuck adding a new record on the sub-form when multiple items are required. Here is a breakdown of the scenario
Word Doc Table 1 = Basic Organization Info Word Doc Table 2+ = Sites to Visit (There could be more than 1 table added here) Word Doc Table 3+ = People to go on site (this might not be the third table based on user interaction for sites)
So far I can get Table 1 and Table 2 data but if there are more than 1 site I cant seem to get the system to create a new record on the related table it is creating a new record on the main form. Here are the lines I used to try to create the related record..
When I put just the above code on a button it seems to have worked as the sub-form showed an additional record was created but when using this on my macro the sub-form is not taking the focus for some reason.
How do I use fields in related tables to create a calc field? The wizard only shows me the current table's fields. Can I do create me desired field by manually creating the calculation and bypassing the wizard? Or is this another restriction of Access?
I'm building a database which will generate work orders, and those have a opening date and closing date, besides that it has a work order number that is of the following format:
YYYY/ NNN (being the NNN the work order number in that year)
to create the number of the work order I'm trying to fill the WOYear field with the year of the WODate field...but I'm not being able to do it... How should I do it?
I have three tables: Event related on to many with Procedure Procedure relates on a one to one basis with the description of the procedure in a table called ProcedureCodes.
I wish to have a query which outputs three columns:
Event - Prcedure1, Procedure2, etc - Description1, Description2, etc.
I have tried the Allen Browne module [URL] .... This gives me:
Event - Procedure1, Procedure2, etc using the expresion:
Expr1: ConcatRelated("[Procedure Code]","[tbl-procedures]","[tbl-procedures].[Event number]=" & [Event no]) to concatenate the procedures.
But I am struggling to get the final column! I have tried the following:
I have two tables to track our engineer visits, one tracks the visit as a whole and the other tracks the individual instruments the engineer worked on during that visit. This way I can track visits to customer sites separately to the visits made to an individual instrument.
Right now, I create a visit and then add Visit Lines (containing the details of the instrument visited). These instruments, or 'Visit Lines', are being displayed in the Visits Table via a related items box. Visit lines are associated with Instruments in the Instrument Table via a serial number lookup. All this works great...
However, I want to display both the serial number and the instrument description in the related items control in the Visits Table. Since the Visit Lines table only has the instrument ID lookup and not the instrument description I can't display it in the control.
I need to either:
a) Create an instrument description in the Visit Lines table as a lookup and have this automatically pull in the description based on the serial number the user selects.... which I can't figure out how to do - it's just an autocomplete.
b) Create an instrument description in Visit Lines and have a macro grab the corresponding description from the Instruments table based on the serial number input - but just for this record...
I'm trying to split a table up because I now realize it won't be able to do what I need in the future. It wasn't designed properly at the outset, and I'm trying to correct it now.
This is a database of pregnancies and deliveries.
The single table does not cater well for multiple pregnancies (twins, triplets, etc), and also I foresee problems when mothers come back in future for another delivery.
Therefore I am trying to separate data into 3 tables: Mother, Delivery and Baby.
This is because each mother can deliver more than once, and each delivery can have more than one baby.
I have set up a trial database, with just a few fields in each table to see if this works. An screenshot of the table relationships is attached.
The primary key of the Mother table is linked to the Delivery table, and ditto the primary key of the Delivery table and the Baby table.
I'm not sure how to migrate the data over, in terms of the primary keys of each table, because in the new tables, these should be an AutoNumber field, so that they are unique numbers.
I reached the limit of 255 fields in a table. I just need to add one more field so I deleted several fields I no longer needed thinking I would then be able to add one more new field. However, I am still unable to add one more field. How to free up fields that are no longer needed?
I'm not sure why a couple tables are not being updated after entering data. I think my relationships are correct. I've attached the database.
steps: open Main form add addres then try to add an owner. it doesn't work the first time but it does work the second time. add test data and that works. now if you look at the related tables (OwnerInfo, WellLocation, TestData) they all have p_id which is the wellLocation Id
now open gernalinfo form enter in info (tests requested doesn't work right now but you can click on them in the tbl GeneralInfo) Now if you go to the table OwnerInfo and TestData the key p_id has not been updated. But it's there in WellLocation (ID).
OK, so I have a database with four tables (Well, more than that, but these are the relevant ones). It's to be used for recording the results of site inspections.
"Tbl_Typicals" is a list of products. We'll call its key "Typical_ID". "Tbl_Actions" is a list of tests performed on each product. A given product may have many tests, but each test applies to only one product. Its key is "Action_ID". Each row contains a Typical_ID to link on. "Tbl_PlantComponents" records which products are installed on which site. Its key is "Component_ID". Each row contains a Typical_ID to link on.
The fourth table ("Tbl_Results") contains the results of each test. As a result of the relationships above, each row is specific to a single Action which applies to a particular PlantComponent, which is to say, each row has both a Component_ID and an Action_ID to link on.
So, what I need is a query that pulls all of these together, such that I can use these details as the line items of a subform.
The main form displays the details of the PlantComponent, which is a simple query to relate line items in Tbl_PlantComponents with the data about that particular product in Tbl_Typicals. So far, so easy.
The subform shows the details of each test applicable to that product. It then has toggle buttons and a comment field to indicate the results of the test, the results of which should be stored as a line item in Tbl_Results.
The "easy" way is to use an append query to generate Tbl_Results in advance. This works, but it raises a variety of new issues.
The nice way would be to use a normal SELECT query and have Access fill in the necessary linking fields (the Action_ID and Component_ID) on each row automatically. Now for trivial examples, this is very easy - my main form query manages just that: I created a link between Tbl_PlantComponents and Tbl_Comments (which stores general comments about each PlantComponent which aren't related to a specific test) based on the Component_ID and that works fine - when I edit the Comments field, the row is automatically created and the linked ID field filled in for me.
However, when I need to do it with 2 links, it all falls apart. I've tried everything I can think of, including generating a single-column unique ID to use for the link, but Access just won't autofill for me. It just makes those fields on the form (or in the datasheet view of the query) non-editable because there's no associated row in Tbl_Results. If I create a matching row in Tbl_Results the query works fine, but that's not the point.
Implementation of the query is non-trivial because it requires two outer joins involving 3 tables - All from Tbl_Actions to matching in Tbl_Results, and All from Tbl_PlantComponents to matching in Tbl_Actions. This necessiates splitting the query into two - the first relates Tbl_PlantComponents, Tbl_Typicals and Tbl_Actions (returning one row for each Action for every Component), and the second performs a single outer join (using an AND) between the first query and Tbl_Results.
Note : 1) A class can contain one or more students (one-to-many between Class and Student tables)
The table "Score" is a junction table between three tables : Student , Course and Term because it contains three foreign keys ( I could use a combination of 3 foreign keys to make a primary key ! ) .
The tables ; "Class", "Student", " Course ", "Term " already contain data for each table I created a data entry form .
My biggest problem is how to create a form to enter students' grades or scores for each student that belong to his class.
I do not know how to do it especially since the idea is that :
On a form I would like to use ComboBox to select a class that displays student's list from class selected and a ComboBox in same form to select course and another ComboBox to select a term and then enter grades or scores for each student
This is my general idea to enter students' grades. The rules are :
A class contains one or more students Each student takes one or more subjects. Each student gets scores for each subject and each term(quarter)
I have a totalquery that runs fine and give me the sum for both fields I'm looking for but I can't get the outputs to fill the fields on the form. I have tried the Dcount query in the control source but that just returns an error and locks up access.
Code: SELECT [Tble-wcDelays].Causedby, Sum([Tble-wcDelays].HoursDelay) AS SumOfHoursDelay FROM [Tble-wcDelays] GROUP BY [Tble-wcDelays].Causedby, [Tble-wcDelays].LinkingID HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-ePlusCent]![cleanID]));
i can do simple additions in access but i have been asked to calculate fields in different tables
all i need to do is
i have 2 tables in my database Table 1:main_database Table 2:Security 2_3
and i have in table 1 got 1 field called Estimated security value and in table 2 i have 2 fields called Sec 2 estimated value and Sec 3 estimated value
all i need to do is add all of the fields together in query or field in a form
i have tried =([Estimated security value]+[Security 2 & 3]![Sec 2 estimated value])+[Security 2 & 3]![Sec 3 estimated value]
I have created a database for storing, query and deleting data. Now the user wants to delete a row from a table and create two new rows. In my opinion this will need lots of work in order to replace all related queries forms and reports in the form. Is there any whay to do my job more easy in modifing a table and all related items be modified by themself? I still do not have any data in the table but if I did will I loose those data? Thanks.
I have a table (tblrecords) with fields, entered by, recieved by and date and time,entered by and recieved by are both referenced in tblemployee.i want a query that will retrieve
fname, lname (of entered by from tblemployee), fname, lname (of recieved by from tbl employee) and the date and time from tblrecords)however i cant think how to do it with both names coming frm the same table?
Many sessions can have many employees - thus the joining table has been included.
When trying to delete an employee from the database using a form, I encounter the error:
The record cannot be deleted or changed because table 'tblEmployeeSessions' includes related records
Is there a problem with my table relationship structure? Or is it 'correct' that as the employee is supervising a session he/she cannot be deleted as this would interfere and maybe mess up the session record?
I created a form from the wizard. In the wizard, I added fields from two tables. Now, after the form is complete (and I've edited it in Design view so that it looks how I want it), I want to add another field from a third table.
Although I can add fields from the first two tables that were originally included (by clicking on the "Field List" icon and dragging fields from that list), I can't find a way to add fields from any other tables in my database. The "Field List" dialog box doesn't contain any fields from anything but the first two tables.
How to I get fields from other tables into the "Field List" dialog box?
I am creating a table in access 2010 for my consumable and bench stock report. I made a 12 fields which I name it the month of the year and another 1 field to add the total disburse materials in one whole year. I did this formula to add the 12 fields
But the problem is its just adding the complete consecutive months that I disburse and the row with blank section the total disburse doesn't show on the total disburse for the whole year. I try to use the code =Nz([Total Disburse],0) but it shows on the screen i cannot be used in calculated column.
