Displaying Data From A Related Table
Sep 5, 2006Hello,
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
ADVERTISEMENT
General :: Web App Displaying Fields From Another Table In Related Items
Jul 30, 2015I 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...
Stock Symbol Table And Look Up Data Type In Related Table
Jul 10, 2012I 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?
Search Forms - Finding A Contact And Displaying All Related Info
Nov 22, 2005Hi
I am pulling my hair out with what I am sure is a simple task, creating a search form that, when criteria are entered, finds a record and displays related data/results from 3 tables. I have tried every forum and web post I can find but I think there must be something fundamental I'm neglecting to grasp.
Quick background:
My database has 4 tables, Firm, Contacts, Mailout and FileNote
The database is contact-centric (ie, everything is linked to a contact record, multiple contacts are held against firms, mutiple file notes are held against a contact, contacts can be attached to multiple mailouts)
I need to search for a contact record based on multiple criteria (keeping it simple, lets say a combination of first_name & last_name OR first-name & firm_name OR their specific contact ID)
I need a 'results form' (not just a datasheet view) that displays all information related to that contact (i.e. all files notes + ability to create new ones, firm contact information, and mailout history)
I've created a Main form with Firm, Mailout, Contact & Filenote subforms embedded that enables me to scroll through every contact and view all related detail (as above) and add file notes & modify data very nicely. My problem is I can't search, I can only move through each record sequentially. Very handy when we have 4000 contacts!
I realise I'm probably a complete wally but could someone please explain to me the vital steps I'm missing? I've tried unbound fields, I've built SQL queries & command buttons (but results are dumped into a datasheet)....I'm lost.
Many many thanks.
Change Primary Key / Data In Related Table
Oct 18, 2011I am new to access 2010. I need to change the primary key in a table ("stations", Primary key is "station number"), because future entries will result in double entries for this primary key.
I have another table ("species&stations") which has records of Species for each "station number".
So I created a new Primary key for the "stations" table, now called "species id", being a bit better in defining a unique station (as it contains the year and the species number, i.e. "451_2010")
Now the table "species&stations" references/(is in relationship with) the old primary key, identifying a station number for each record.
How do I get it to recognise the new primary key from the "stations" table, and keeping the records (over 1000...) with all there info.
Approach so far: I tried to create a new field in "species&stations", which should look up the new primary key from "stations" and then autofill the field. i did not manage this though
I attached a screenshot to make the relationships more clear!
Forms :: Form Data To New Record Related Table
Aug 19, 2013I have a form based on a table which includes the mid field. I want to have a macro that takes the value of the current mid, and makes a new record in a 1-many related table (consisting of record id (auto), mid and trmntdate), paste the mid and insert the current date.For the life of me I cannot get it to work? The process should be something like:copy mid value, add new record to related table, paste value in mid, insert current date in trmntdate, save. I've tried append queries, experimented with copy etc, dabbled blindly with VBA and not got anywhere.
View 14 Replies View RelatedReports :: Show Info Even When Related Table Has No Data
Mar 26, 2014I would like to create a report that would really impress my supervisors, i just started at the company. I'm trying to create a call action plan, so i'm recording clients information on one table, and meetings we have had with each respective client on another table. Some clients will have multiple meetings, some few, and some none. I have a relationship set between them from the client's id number on the client table to the ClientID on the meetings table. one to many.
When I go to create the report, only the clients with meetings show up on the report, I would like client info to always show up on the report and meeting info to only show up under each respective client when it exists. I have worked out how to shrink and hide any text box without any info on it. It just seems like the existence of a meeting dictates where the client will show up at all in the report.
Displaying Data From Another Table
Jul 28, 2005I have a table of customers' data and another with the ISO country codes. They are joined. The country codes appear in the customers' table. The main table looks like this:
http://lemmikki.uw.hu/countries.JPG
On the form I would like to display the country's name instead of the ISO code.
How is it possible?
Automatically Copy Data From One Field In A Table To Other Related Fields In Other Tables
Aug 7, 2014I'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 RelatedDisplaying Record Data From Another Table To Another Table
Dec 31, 2005Hello Fellow Access programmers
I am wondering whether there is someone out there that can help me with a question, I have created two tables one is called categories and the other is called Vehicle List, what I would like to do is to display a specific record from one of the fields in the categories table and display it in a specific field in the vehicles list table but I dont know how I can do this, is there anyone out there that can help, basically, when the user starts to enter data into the vehicles list table and they get to the field that only needs to display a specific record from the categories table, I dont want the user to enter any data in this field, it will only display a specific record from a different table. I may or may not be right with regards to an expression that needs to be entered and if so does it have to go into the default value box and can someone please give me some idea as to what the expression should say, can someone please give me the formula that I need to type in.
Your cooperation is greatly appreciated and I look forward to hearing from you, best regards Mark
Tables :: Want To Export Data As A Table But Displaying Text Value Rather Than Key Value
Oct 26, 2013I have inherited 1000 records which need to fit into new criteria selected from combo boxes and to do this individually by the forms will take forever so i want to copy and paste the data in the table, but when I do it doesn't fill in the form.
This may be a problem with the cascading script in the form as the fields fill in when I press f9 but disappear every time I open the form.Finally I eventually want to export the data as a table but displaying the text value rather than the key value.
Modules & VBA :: Check Policy Number In Access Table And Populate Related Data In Excel Sheet
Aug 14, 2014See attached the Workbook. I need to check the policy Numbers in Column A of all the sheets in the attached workbook if its present in Access Table. If yes then write the corresponding ScanDate and BatchNo from Access table to columns I and J of all the sheets. I need to write VBA code to perform it.
In the attached workook, only Sheet1 contains the data but in actual there will be data in 5 sheets in the workbook.
Forms :: Enter Details In Main Table And Related Sub Table
May 5, 2013I 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.
Use One Form To Add Data To Two Related Tables
Jul 24, 2006I need help. I am not sure if this is a form issue or a logic issue i have a db for tracking students the classes they take and their notetakers for classes. Each class is most likely unique to each student but sometime there are multiple students in each class that need a notetaker and there should only be one notetaker per class. I have 3 tables and they are all linked through a unique iD called CRN (number).
I am looking for a way to add a student to a class and create a class at the same time in one form. I am trying to limit the redunancy by adding a class each time and then adding a student to the class.
Any suggestions would be great. I have gone through 10+ redesign and am just at a loss on how to accomplish this.
Data Entry Problem To Related Problems?
Sep 15, 2005Hi there,
We have designed a database which has six tables all joined together through one master table by one-to-one relationships, We have designed forms based on these related tables too.
However, it will let us type in any data to any field in one table but when we try to add data to another related table it comes up with the following message:
"you cannot add or change a record because a related record is required in table "xxxx".
This happens no matter where we try to enter data.
Referential integrity is set as are the cascade options,
Please help as this is an urgetn issue for us
Thanks
S
Opening Form2 With Data Related To Form1?
Jun 30, 2005Hi,
I've searched but cant find the answer,
I have an Account form (Form1) and it displays account information along with their many addresses for the account (in a datagrid embedded form). I hit "update or add address" button to launch form2 (Address Form). From this point I want to query the Address and AddressAccountLink table with a passed in Global Variable (AccountID) from Form 1 so that just the Addresses pertaining to that Account show up, instead of all the Addresses for all accounts.
How do I do this? I've tried a String Query in the Private Sub Form_Load() but it gives me errors, here that is:
sSQLQueryAddr = "SELECT tblLinkAccAdd.AccountID, tblLinkAccAdd.AddressID, tblAddress.* FROM tblAddress INNER JOIN tblLinkAccAdd ON tblAddress.AddressID = tblLinkAccAdd.AddressID WHERE (((tblLinkAccAdd.AccountID)=" & v_accountid & "))" (it's on one line in the code)
followed by:
DoCmd.SetWarnings False
DoCmd.RunSQL sSQLQueryAddr
DoCmd.SetWarnings True
Any ideas? Am I on the right track? Do I move this query elsewhere? The address form is made from a query -- querying the Address and address/account link table.
Oh, the v_accountid does have the correct value passed to it...
Please help , I'm going nuts here!! :confused:
Mike
Insert Data In Two Related Tables Simultaneously
Dec 11, 2004I want to insert a huge number of data (Customer and their adress) into 2 related tables in Access. Each table in access is related by some kind of ID (Autonumber-Primary key). The first table is CUSTOMER , the other one is CUSTOMER_ADRESS . Access relate each Customer to his Adress, so if I import data into one table and then import data into another table how would the database know that all the data pertains to the same person. How to insert the new list of customer and adress without using a form?
Thanks for your help.
Automatically Fill Fields With Related Data
Nov 30, 2005Hi!
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 To Store ID On List Box But Show Related Data
Oct 10, 2012I have 2 tables one, Contacts, primary key ContactID this table contains names, addresses etc and one group called Form primary key Form ID, foreign Key Contact ID. I want to store the related contact ID in my form but display firstname ad surname from Contaacts list. This works fine when I am selecting name. List box shows 2 columns with correct data but when I recall the form after making other entries it only displays the firstname. The correct ContactID is stored. Why does this happen.
If I can get this working I would like to concatenate the Firstname and surname to make it more readable but don't know where to begin.
Using Foreign Key To Get Related Values From One Table Onto Another Table?
Sep 25, 2013I 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?
How Do I Update Data Related To A Read-only Query On A Form?
May 22, 2006Hi all, I'm so tired of looking... please help me!!
I have an Access Data Access Page based on a query which is not updatable.
The query is based on a many-to-many table (e.g. OfficeProducts with foreign keys for OfficeID and ProductID and an Amount field). The query is not updatable as I have included outer joins to the Office and Product tables to get all their records back.
(I'm not sure if I've done this the best way, but need to be able to list all the possible Products for each Office whether they use them or not.)
I want the user to be able to update the Amount field, so e.g. if it is currently set to 50 to be able to change it to 40, or if it is currently null (as there is no record so far for that OfficeProduct) then to insert a record into OfficeProducts with the OfficeID, ProductID and Amount value entered.
I thought this would be straightforward by creating a new textbox in the same section as the current Amount field, calling it NewAmount and letting the user update this, fire an event trigger to either update or insert into the table, and refresh the query and record on the form/DAP so that Amount shows the newly updated/inserted value.
I have been searching for ages... cannot locate either the best event or events to use for this nor the code to enter!! Please please help!!!!!
(This is actually for a charity helping people suffering from emergencies in developing countries, not OfficeProducts, so your spot in heaven will be reserved if you can help ;) Thanks)
Jen
Forms :: Entering Data Into Related Tables Using A Form
Sep 12, 2013I have a form within my database in which the user will enter data which will go into 2 separate tables. These 2 tables (Job and Client) are related. At the moment I have a subform in which the user enters Job information, and the main form where the user will enter client information.
The problem being is that the 2 sets of data do not associate themselves with each other, despite being related (The Client will be related to a job number. A client can have many jobs but a job can only have one client etc). It has to be done manually in the table which is not ideal as the DB will be split and rolled out to users via Access Runtime. I have been working on this DB for a while now and the problem is most likely right in front of me but I cannot see it!
Forms :: Using A Form To Enter Data Across Related Tables
Mar 3, 2014I have a database I made to store a list of users and information about each user.
I have a UserDetail table, languages table, previous experience table, current experience table.
UserDetail table as follows:
UserID (PK)
First Name
Last Name
Full Name (calculated)
Department (using a multi select combo box. There are 3 departments and some people are in both).
I need to do the same thing on each table and each table is very similar so I'll just list one. This is the Previous Experience table:
PreviousXPID (PK)
Previous experience (e.g. IT, Marketing, Chemistry)
I have three junction tables. One for each Previous Experience, current experience and language table. The Previous experience junction table has:
ID (PK)
UserID
PreviousXPID
I created a relationship between the userdetail table and the junction table then the junction table to the Previous Experience table.
Now what I'd like to be able to do is edit people to add previous experience, current experience and languages to each. I already have a form that lets me add a new user to the database. It's just based on the UserDetails table. If I could have a way (perhaps using 3 multi select list boxes. One box for previous experience, one for current experience and one for languages) on that form to add the other details to that person, that would be ideal. I'd like to create a new user. E.g. Joe Bloggs in department 1 who speaks Dutch, used to have Marketing, Chemistry and aeronautical experience and now works in IT.
I can create the new user by putting last first/last name and selecting the department. But to add experience or languages, I have to go into that junction table and add the numbers myself. e.g. user 1, has experience 1,3,4 and 5. I'd like to see the name that relates to the ID and be able to select it from the list in the table I have.
Displaying Data
Sep 3, 2006Can't figure this one out. I am trying to get my query to show me which company's have not sent a supplier any money during a certain period.
However if I have the following data
abc company
1/10/02 £5
1/12/02 £10
an i search for any company who has not sent any money between 2/10/2 and 1/11/02 abc company does not show up because there is no data between these dates. If I had a record 2/11/02 £0 it shows up. So what criteria or query can I use to show this up?
many thanks
Data Not Displaying
Nov 7, 2006Hello All,
I recently added a couple cascading combo boxes to one of my forms and now the data from the underlying table that has already been entered does not show up unless I go in and reselect the options.
Anyone have any ideas on how to fix this?
Thanks,
Table Related To A Field
Nov 21, 2006Hello
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