Greetings to everyone,
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?
Thanks.
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 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?
View 7 Replies View RelatedThe "PersonalDetails" table is related to the "Application" table using the autonumber from table, "PersonalDetails".
The "PersonalDetails" table has "StudentID" (autonumber), "First Name" and "Last Name" fields.
The "Application" table also has "StudentID", "First Name" and "Last Name" fields.
The "Application" table is related to the "PersonalDetails" table using the "StudentID" field.
How do I make it so that the first and last names on the "Application" table are automatically updated when the StudentID is entered?
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.
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.
View 2 Replies View RelatedIs it possible to connect data so that when one field of a record is entered the rest of the fields associated with that first field of the record automatically fill in?
Example: If I were to enter a new contact record name in the name field I want the corresponding fields of address, city, state, zip, phone....etc. to fill in automatically.
Hi!
I have a table with several fields. I would like to have a lookup in one field (combo list - get data from another table field) and for 2 other related field have the coressponding data appear automatically according to the selection in the lookup field.
eg.
Table1
|Field1...................|Field2...............|F ield3...............|
|LookupField1Table2 |AutoField2Table2|AutoField3Table2|
Table2
Field1...|Field2... |Field3...|
Data1...|Data2...|Data3...|
I am not quite sure if my question is clear... I had trouble in describing it in my post...
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?
View 1 Replies View RelatedI have a table TO-det and another table DO-DET.The table DO-det will have details about all DO for each TOID record.Both have a common field name TOID The tables are related under ONE-MANY relationship.One TO-DET record can have many DO-DET record
Now I wanted to create a form where if i add a new record to TOID i must also be able to add data for DO-DET for that corresponding TOID.
I previously created 2 tables:
One lists all the stock symbols and company names = SYMBOL
The second table lists the purchase information for each stock = PURCHASE
I then created many queries, etc. using this data. Symbol is the key link between the various tables, queries, etc.
NOW that I understand the lookup wizard in the data type, I would like to change the symbol field in the purchase table to a Lookup field. I, of course, receive a message. I am told to delete the relationship with the other tables. If I remove the relationships and change the data type, can I then replace the relationship with out damaging all the queries and forms?
I created 2 table that are linked to a link table so to get a many to many relationship.I have a table for locations and a table for parts.One location can have many parts and One part can belong to many locations, that is way the many to many relationship.
how to get values from the parts table to the Location table using the linked Primary Key in the foreign key linked field.The values also have to be interactive, if I change the value in the part table it needs to update in the location table.
What can I put in the field of the location table to get a specific value from the parts table?
I tried setting the field to calculated field and put the Dlookup function but that didn't work. The lookup wizard seems to be the solution, but I can only create combobox or list box and I need to select the value manually. I just need a value to be put automatically by looking at the foreign key.
If I put the Dlookup function on the default value, those it update if a value is changed in the part table?
Hello
Could you help me ?
My problem is :
I'm developing a ms database for the a "repair service" .
I created a table witch contains basic data from customer, including its unique ID number (key field), name and contacts ...
Now I need to store the "repair service" data from each costumer ( its time consumption, replaced materials...) in a separate table but, If I'm thinking well, I must have a table for each costumer so, I will need to link each individual "repair service" data table from each costumer to each costumer itself.
Do you know how to do this ?
Lots of thanks in advance,
Miguel
I have two databases, one containing the tables and one containing the forms and queries. The forms/query database is built from linked tables from the tables database. I would like to expand one of the fields from 7 characters to 9 characters. I planned on doing the in the table database, then updating the linked table. However, the table I need to alter has relationships with about 10 other tables and I get the (you cannot make changes to a related table error message). Is there a way I can make this change without deleting all the relationships?
For future reference, should the relationships have been created in the form/query database?
Thanks, N
Hi :confused:
I am trying to set up a query. i have to work out the cost of a phone call with the use of three fields--
i have: lenght of call, country call code-e.g england is +44(these are fields in my query) and then the cost per minute values for each country call code(but these are in a table)
e.g.Country CodeRegion Cost per minute
+1 United States, Canada, Caribbean£0.20
+20Egypt £0.30
+212Morocco £0.30
How would i set up a query to solve this problem?#Can someone please help me???
I have a very simple database with two tables. One stores Schools that I work for and the other holds people that work at that school.
I have a form that displays information from the schools table and I have a button that I want have open a form that displays the people that are related to that school.
THe problem is I can't figure out how to limit the recordset to just the people from the school record I was just viewing.
For example. I am looking at a record with School ID 1. I want to run a query on the People table that will return just the people that work for school id 1. I just can't figure out the criteria part.
Any help is appreciated.
Thanks
Hi, I'm a total newbie at Access, and know nothing about scripts. I've been operating at the level of using the Wizards and drop-down menus. I am trying to create a report that does two things:
1. Displays the results of queries that sum data from a table (I think I have this figured) and
2. Displays those sums as a percentage of a number that is input each time the report is run. (This is only one number that comes from a totally different place and has no prior relation to the data.)
Any help/advice that anyone could offer would be much appreciated!
Thanks!
Hello,
I have two related table: table_Company and table_contact. Now i have:
Table_Contact Table_Company
James Bolton -----------> MacDonalds
Hellen Kidman ----------> MacDonalds
Now, i wanna have one "MacDonalds" in my Company table, and delete the others "MacDonalds" and still have the relationship between my contacts and their company.
Hello,
I have a form based on 1 table and I am trying to find out if I can display data from another table if I have a relationship between the 2 tables?
Thanks
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?
For example
Master table:
Key 1
Related table:
1 Key 1
2 Key 1
3 Key 1
jamo
I have two related tables. (related with AcqID)
tblAcq and tblAcqDetail
In tblAcq I have information about the delivery and in tblAcqDetail delivered articles, price and quantity.
tblAcq
[AcqID] ; [Contractor]; [TransportCosts]; [CustomDuties]; [SumWithoutVAT]; [LimSUM](this total cost of shipment Goods+other cost)
[Code].....
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).
Why are these tables not getting updated?
I have a table with an ID and PartNumber field.
Parts tbl
PartNumID PK(Auto)
PartNumber
Heres some example data:
1 M24308/2-2F
2 M24308/4-2F
3 ORD9F0000
4 ORD9M0000
5 D90000VLO
6 D90000JOO
How would I design the table to allow me to define alternatepart numbers and also related parts? For example:
Record 1 is the primary part and record 3 is the alternatepart.
Record 2 is the primary part and record 4 is the alternatepart.
Records 5 and 6 are related parts to records 1, 2, 3 and 4.
I have a form that allows the user to select a part and displaythe part data. But I also want the alternate parts and related parts displayedon the form, maybe in a subform with tabs. I havent gotten there yet because Icant determine the table design.
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.
Hello,
I'm not sure why I'm drawing a blank but I am. Could I please get a few suggestions on how you might organize these fields into tables?
These are not baseball stats fields. I'm keeping track of how often teams win over the years. I would like to do generate a team webpages showing all historical wins, losses and championships.
TeamID
TeamName
Year
Wins
Losses
AL_DivisionChampion (3 divisions in each league EAST,WEST, CENTRAL)
NL_DivisionChampion (3 divisions in each league EAST,WEST, CENTRAL)
AL_WildcardChampion
NL_WildcardChampion
ALCS_Champion
NLCS_Champion
WorldSeriesChampion
Initially I thought about YES/NO fields but there are so many variables. I need to know STL won the division but also that it was the NL Central.
Any help would be greatly appreciated.
Thanks
Rob