Relationships between table - 3 fields in 1 table related to another same table


I have a table which have 3 columns - peoples' id. They are in 3 columns, as each column is one kind of department. e.g HR, SALES, TECHNICAL. Each row of data is referring to each project. All people's ids are in 1 single table containing the key id and their names. When I set the relationship between 2 tables. It only allows me to link 1 time, but I have 3 fields needed to link to the PEOPLE table. What can I do?

Post your answer or comment

comments powered by Disqus
I am new to access and am having problems understanding relationships between tables. I tried my best to read help articles but I could not fit them into my situation. This might be a long & tricky question. I apologize for that. I thank the reader for his/her patience and time. Now the question...

I am trying to create a database for a hospital. Patients get admitted to the hospital, get monitored every day and then get discharged. To collect their data I made 3 tables.
1) Patient entry table - Has background details of the patient when he comes first to the hospital
2) Daily monitoring table - For entry of daily parameters (heart rate, temperature, blood pressure, etc.)
3) Patient discharge (exit) table - Has certain other parameters which are checked at his exit

Of these Tables 1 & 3 are filled only once i.e. at entry and exit while table 2 is filled daily for a patient.

Each patient is given an unique hospital ID.

I had made 3 separate forms (for each table) with the fields from the tables for data entry (as the fields will require labels to explain certain details for an inexperienced user who tries to enter data). Forms are meant for data entry... Am I right?

My questions are
1) How will I link the entries in the 3 tables to a single patient (i.e. to a single hospital ID). Should I enter the hospital ID for each form every time and then match the same ID in the 3 tables. Is there a way to easily link all data of one patient?
2) When I enter daily data of a single patient, I am not allowed to enter details of a patient again because the hospital ID is the primary key. I can circumvent this problem by removing the primary key and by entering the day of stay (D1, D2, D3 etc) and the hospital ID every time. But is there a better way?
3) Since the forms were quite big, I had to split them and use them in a navigation form (with entry, daily & exit tabs horizontally at the top and their respective split forms on the left as vertical tabs). For example, the entry form tab has 3 parts on the left. How do I connect these 3 parts to a single patient.

I have attached the databases i have created with this post. Please point out where I am wrong if possible.

My intuition is telling me that I am making a mistake somewhere. If there is a better way to make this database (especially making the tables) please guide me. Any links, suggestions will be greatly appreciated. Again thanks for your patience and your time! Attached Files Full (268.9 KB, 3 views) (39.7 KB, 1 views) Reply With Quote 09-17-2011, 10:29 AM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,121 First, I don't understand why two patient tables (entry and exit). These tables can have only one record per patient and will always be a record for each patient in both tables. This is all info about patient and should be one table. Now I have concerns about the fields. Will every field always have a value for every patient? What are all the medical sounding (Ceftriaxone, Aztreonam, etc) name fields for? I suspect some sort of child table is called for. Also, you have a lot of Yes/No fields that maybe could be combined as one field. For instance, Transf_Out and Death fields are indicating case resolution. Why not one field with choices of Transfer or Death? Or instead of the Yes/No, wouldn't a date be more informative?

The primary/foreign key fields seem okay but you did not set up Relationships. This is not critical for database functionality but can be helpful when building queries.

To allow patient to have multiple visits you need a table for general patient info such as patientID (primary key) name, address, insurance, DOB, etc. Then you would have a Visits table for visitID (primary key), patientID (foreign key) date of visit, complaint, diagnosis, date of exit, etc. Then you would have the daily data table with foreign key field link to the visits table, same for culture.

Your form needs a main form bound to patient info table then dependent subform and subsubform would be for the visits and daily/culture tables. You used the Navigation form wizard. I don't like the wizards. Not sure this structure is appropriate for your situation. I don't see any way to link the subforms on the navigation tabs to a parent form. I would use the old subform container control to build this structure.

I have a list box that displays records that contain 3 fields. I would like to select 1 record (all 3 fields) and copy/append it to a new table 1 record at a time. After each time a record is copied/appended that record should no longer be available in the list box. I am guessing that a proper query will accomplish this, but I am unable to set up a table that can receive these 3 fields appropriately, could someone please help me with this one, MANY THANKS!

As the title says, I was wondering how to create a validation rule in relation to another fieldname in the same table?
For example, the first field line is the serial number, and when something is entered for the serial number the OTHER field line (which is quantity) needs to be 1, otherwise it stays blank.

I use access 2007 or 2010 depending what computer I'm using.

Thanks, Luke

Hi I have tried searching for an answer with no luck.
I am writing a table to do a filing cabinet archive.

I have a table with 3 fields in one is box no, second is item no and third is a description.

I have the first 2 linked as a primary key. The description is just text.
I want to be able to enter a box no and to have the second (item) to enter a number automatically following a sequential no which is relevant to that box. ie
box no Item No
1...........4 this one takes the next no available for box no 1
2...........3 and this one does the same for box no 2

if you go back to a box and start reinputting it picks up the next number.
should i sack trying to do it with the autonumber?

How do you remove relationships between tables?

anyone know how to do a query update from on field in one table to another field in another table.
table 1
cno serno
1 123
2 345

table 2
cno serno host
123 elmer
345 fudd

i want the cno in table 2 updated where ser no matches.
anyone? (using query area in access).

I have a Table called 'TblSupport' There is a field called 'SupportStatus' This relates to another Table 'TblSupportStatus'

In my main form I have created a list box basedon a query off the TblSupport. i used to display the values in a table and everything worked fine.

Now i have created a ListBox, any fields which are as a result of a lookup now only display the record number. I'm guessing thats because in the Table 'TblSupport' it sown the number relating to the field value rather than the field value but i can't seem to find a way around it other than dispensing with the related table which i dont want to do because i know its not correct to have loads of redundant data.

Any clues, much appriciated.

Have a combobox that lets user see 3 fields in a pull down list. After he selects the right item from the list based on the three values, I can only display the first value. How can I get the other two fields to display?

I was wondering how to create relationships between tables?

Hi Folks,
Is it Possible to get a field in a subform to fill with the value of the same field in previous record......easy with Alpha Five !! and then to recognise when the value has changed.
I have to do this without using linked fields to the mainform, as the data can change from record to record. (some fields are linked, and do fill as required) Not sure if this is possible.
i.e. the subform is linked on a [batch_number] field between child and parent. This batch number can remain the same over several shifts, which means the date can change, as can the shift. I would like to not have to type in the date and shift for every record entered in the subform, bearing in mind that the records may be typed in several days after the event, and they may be lots of them !
two of the fields are [month] format "mmmm"and [year] "yyyy", I guess it should be possible to fill those with some sort of function based on the date field "dd/mm/yyyy" ?

I have tried searching the posts, can find nothing that suits.



Hi - I am having troubles copying data from one form to another..

I have two tables customer and vehicle, the primary key in the customer table is CUSTOMER_ID (autonumber, long integer, indexed with no duplicates) this is the foreign key in the vehicle table CUSTOMER_ID (long integer, general number, indexed, duplicates ok).

When the user has entered the customer details in form CUSTOMER they click on a save button which gives them the option to add vehicle details. The VEHICLE form opens with a blank record.

I have been trying to get the CUSTOMER_ID in CUSTOMER to be copied into the CUSTOMER_ID field in VEHICLE when the form opens using the following code..

DoCmd.OpenForm "VEHICLE",OpenArgs:=CUSTOMER_ID

then in the Form_Open procedure of the form VEHICLE:

Private Sub Form_Open(Cancel as Integer)


End Sub

This works if the CUSTOMER_ID field in VEHICLE is unbound but not when its control property is CUSTOMER_ID, so therefore does not update the VEHICLE table with the value copied just leaves it blank. Having fiddled around for ages is there anyway of copying this value into the CUSTOMER_ID field and underlying table?

Any help is much appreciated


I am having problems taking values from 1 form to another. I have used this exact code before and never had any problems. However, it seems that it is not working for this particular form and I need some help figuring out why.

I have an IF clause within my source form. At the moment, this is simply choosing whether the destination form opens to a current record or a new record. When it opens to a new record I want to take values from my combo boxes into the destination form so that this data will then be used in the new record.

This is the simple code:

Private Sub cmdEnterResults_Click()

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_monitoring_responses"

If (Nz(DLookup("URN", "tblSrvRspns", "URN = " & Me!cboURN), 0) = 0) Then

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Forms!frm_monitoring_search.Controls!cboURN = Forms!frm_monitoring_responses.Controls!cURN
Forms!frm_monitoring_search.Controls!cboSrvID = Forms!frm_monitoring_responses.Controls!cSrvID


DoCmd.OpenForm stDocName, , , stLinkCriteria

End If

End Sub

When it opens to a new record it is doing so with 0 and 0 in the cURN and cSrvID fields. Is there any reason why it would not enter these details into the destination form.

FYI, cURN and cSrvID are both text boxes bound to the record. It is however possible to enter values manually into these boxes so I don't know why it will not bring the values in from the source form. Any ideas?

I have a 'quote form' and a 'new case form'. Before a new case a quote must be done. This quote is given a reference number. If it becomes a new case i select the refernce number from a lookup on my new case form. This then finds the correct record in my quote form. However certain fields i want to be automatically entered into the new case form from the quote form. Eg-on selecting refrence number 10 i want the name, address from the quote form to be entered into the name, address fields on my new caes form.
In code i have tried things like -
name = Forms![frmquote].name. Nothing seems to work though! Can anyone give me any ideas on how i can sipmply dupicate fields from one form to another??

Hi Friends,

I am trying to create and delete relationship between two tables and to enforce referential integrity between the two tables. The tables are in a 1 to many relationship. I am not able to create and delete the relationships. I would like to have suggestions from your side. Thanks

PHP Code:
Dim tbl1 As TableDef

Dim tbl2 As TableDef

Dim db As Database
Dim relnew As Relation

Set db = CurrentDb()

With db

Set tbl1 = db.TableDefs("CLAIMS")

'tbl 2 is a tabledef you just created
Set relnew = .CreateRelation(CLAIMS, PART)
relnew.Fields.Append relnew.CreateField("KEY") 
relnew.Fields!table1id.ForeignName = "KEY"

.TableDefs.Append PART

.Relations.Append relnew

End With

End Sub 

Originally posted this in Query forum but it seems that it would be impossible to do via query. After much searching I found this code by Joe McDonnell on Tek-Tips. Problem is I have never used a module to return a data result and have no idea how to use this code. Can someone please help me?

Original post
Combine data
Help! I'm being overrun by data!

In building my latest db I have run into a problem that I don't know how to solve. I have a table that stores part information related to engineering changes. A part can be changed by many engineering changes so a part can appear many times in the table. I want to take one field (Comments) from each record and combine it into one result.

For example

AutoNumberID.........Part Number......................Comments
12...............................100001........... ..............Implementation 5/03
1053...........................100001............. ............Use up old stock first
2563...........................100001............. ............Price increase 01/01

The return that I would like is...

100001......Implementation 05/03, Use up old stock first, Price increase 01/01

tblECNParts, PartNumber, Comments are the table and fields. I've read about using Union queries but this seems to be for multiple tables or queries. Can I use it on one table, same field, many times?

	Option Compare Database
Option Explicit

Function fConcatChild(strChildTable As String, _
                    strIDName As String, _
                    strFldConcat As String, _
                    strIDType As String, _
                    varIDvalue As Variant) _
                    As String
'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'Usage Examples:
'   ?fConcatChild("Order Details", "OrderID", "Quantity", _
                "Long", 10255)
'Where  Order Details = Many side table
'       OrderID       = Primary Key of One side table
'       Quantity      = Field name to concatenate
'       Long          = DataType of Primary Key of One Side Table
'       10255         = Value on which return concatenated Quantity
Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
    On Error GoTo Err_fConcatChild
    varConcat = Null
    Set db = CurrentDb
    strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
    strSQL = strSQL & " Where "
    Select Case strIDType
        Case "String":
            strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
        Case "Long", "Integer", "Double":    'AutoNumber is Type Long
            strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
        Case Else
            GoTo Err_fConcatChild
    End Select
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

    'Are we sure that 'sub' records exist
    With rs
        If .RecordCount  0 Then
            'start concatenating records
            Do While Not rs.EOF
                varConcat = varConcat & rs(strFldConcat) & ";"
        End If
    End With
    'That's it... you should have a concatenated string now
    'Just Trim the trailing ;
    fConcatChild = Left(varConcat, Len(varConcat) - 1)
    Set rs = Nothing: Set db = Nothing
    Exit Function
    Resume Exit_fConcatChild
End Function

Hello all.
I'd really appreciate any help that you could offer I can't seem to find the answer to my question anywhere. My knowledge of Access is quite limited as I have only been using it for about 3 weeks. Unfortunately I do not work in IT so I also know nothing about VB either. However, I am at least partially computer savvy. So please answer in the simplest terms with specific instructions assuming that I know nothing about Access. I will try to explain my problem as completely as possible.

I created a database with 5 different tables.

There is a 1 general table with demographic info (age, sex, etc) and 4 specific tables linked to the general table in a 1 to many relationship based on the patients name.
Each of the specific tables contains data about a certain type of procedure that the child had on a particular date. Here are my 4 tables and date fields:

I want to create a query (and eventually a report) with results for each child that is sorted in order by the date regardless of which table that field came from. I don't want to specify the order of those four tables. Said differently each child has multiple entries in each of the tables (the one-many relationship) and I want to know the order of entries in relation to each other regardless of the table the entry came from. What I don't want is a query with 4 sorted date fields in columns next to each other.

For example:
Child#1 OSAQuestionnaire on 1-1-04; Surgical_data 1-1-03; OSAQuestionnaire 1-1-02 etc.
Child#2 SpeechEval 1-2-03; Surgical_data 1-2-02; SpeechEval 1-2-01

The idea is to see how the various surgeries have affected the results of the other items over time. For child #2 I would like to compare his 2 SpeechEvals knowing (from my query) that the first SpeechEval was before surgery and the second SpeechEval was after surgery.
Thank you in advance for any help that you can lend to me,


I've already searched the forum for this "newbie question", but I found no answer to this.

I'm wondering if queries run faster if I set relationships between the used tables via the menu ("extras", "relationships") or in the queries (in the design view by pulling one field on the other and establishing the desired join).

Barbarossa II

Hi all! Here is what I want to do, and what I have done so far:

I have one table called "AUTOS" This is a table imported from pc file, and tweaked to make it work well in Access. I learned alot doing this, and now have workable forms, queries, and reports being generated on this table. I learned on here not to let my users "work" with the table, only through forms. So that all works very nicely!

Here is the next step. First a simple background: We are a Used car dealer. Table "AUTOS" contains rows, each one consisting of many Fields. The main one being "Stock NO". Then all the remaining fields are vehicle info (many fields, but all relating to the vehicle type etc), cost, sales price, etc.

What I want to do is create a new table, called RECONDITIONING. In this table I want to store everything done to a vehicle after purchase. For example, if we purchase a vehicle, then change the oil, put on new tires, and detail it, I want these items in the reconditioning table. But I want them to remain linked to the "correct" stock number in the main table.

I believe I have done this, by doing this: I created a second table called reconditioning. Added the fields I wanted, the first one being "Stock NO". I made "Stock NO" in the main table autos the primary key, and then opened relationships, and linked the reconditioning table to the main table via that field.

It appears to have done what I wanted too, because if I open the reconditioning table, and type in a vehicles stock no, then the reconditioning info, then go back to the main table, there is now a + next to the row. If I click this plus, a box drops down with each item I added to the reconditioning table. So far so good!

Here is the main question: Now that I have this info in what seems like the proper fashion, how do I work with it?

I need to create a form, containing elements from EACH table. The design I have in mind is like this:

Across the top, I want vehicle info:

Stock No Year Make Model Date In Cost
5922 1994 Ford Focus 1/1/05 1000.00

Then under that, I want a "table" in a form, which pulls from my "reconditioning" table, listing each item that has a stock No that matches the one on that record:

5922 Replaced Windsheild Wipers 1/8/05 20.00
5922 Tuned Up 1/10/05 75.00

Ok, now, I want to allow my users to add to that on this form, to type in new entrys. I think I got the idea how to do that, but suggestions are welcome. Now, on this form, I want at the bottom, the following:

Vehicle Notes: (Just a text field for "notes" on a particular vehicle)

Total Reconditioning cost: (this is a field on the FIRST table, AUTOS, that I want to calculate by adding the total from the list above, IE in this case, 95.00)

Total_ACV: This is a field from the first table as well, which I want to calculate using (Vehicle cost: Which is in the first table)+(Total Reconditioning Cost: which is above)

Like I said, I think I have the table layout and design correct, I am just not sure of how to work with values in linked tables like I have done, and I am not sure get the right info in my list on the form, then add the totals properly.

Any suggestions are appreciated!


I'm working on a DB that will primarily be used as a Data Entry form for recording the actions and their results, of various teams in various regions of the same city. Without going into too much detail - it's already mentioned in other threads on this forum - I currently have a small number (3) of tables to build on. These are t_Bases (Bases sending teams) t_Teams (teams themselves) and t_Regions (regions receiving teams).

My query is this - I have provisionally set up a 4th table, t_Nations because we have numerous Bases sending teams from the same countries, eg Australia, UK and USA each have sent or are sending more than one team to the city we are working in (currently London, other cities to be added in the future). I've related it to the table t_Bases using NationsID as an FK in t_Bases. Do I need this table or can I get away with just entering the info into a field within the Bases table?

I realise that much of the info input by our users will be repeating information, especially where a base sends more than one team and more than one base is located in the same country. Our reports are strictly on a team-by-team basis ie each team will have it's own record on our DB.

I’m trying to design a fairly complex database (at least to me). Right now I’m trying to figure out the best way to handle fields that will be combined in an and/or relationship.

For example, I have a field called “Test” and a field that is related to the “Test” field in an and/or way. The related field is called “Method”. A single test can be related to a single method, or it can be related to two or more methods or it can be related to one of two different methods.

Test |Method
TestA |MethodA
TestB |MethodA AND MethodB AND MethodC
TestC |MethodA OR MethodB

What is the best way to build the tables for these two fields? I was thinking along the lines of Method being in a table as a primary key and then a foreign key that would be related to the Test field in that table. The second table would have Test as a primary key only.

tblMethods |tblTests
Method (PK) |Test (PK)

That way you could add tests, but any new methods would require an associated test. So, I’m not sure?


I started using access on my own last November and I have received already some very useful tips through this forum, so I hope to repeat this experience with something which seems to me now a lot more sophisticated than last time.

I have now 3 tables, one containing my main records which I update via a form, and two minor tables which I update only in order not to update manually drop-down lists on my form.

On my form, there are two fields called, one "Sector", and one "Industry". So I created a table for the Sectors, which appear as a drop-down list in the field "sector" on my form - so far so good.

I created a table Industry, in which I created my industries linked to their respective sectors (many industries per sector but only one sector for each industry).

So when I update the field Industry on my form, I expect to see only the industries linked to the sector chosen before, in a drop-down list. That means, when I update the field Sector, I expect to see, in the drop-down list Industry, only the industries linked to this sector as per my table Industry.

What I have at the present is, in my drop-down list, all the industries that I have created in my table industry, disregarding the sector associated; which is still ok, since I can find the one I am looking for, but not very safe since I don't want to be able to chose an industry that is not related to the chosen sector.

Hope this is clear. Ah, and I am using Access 2007.

Thanks for your help.

hi i have a query created for a table with 3 fields like,
1)name 2)sum of credit 3)sum of debit
Now i need an extra field like
4)sum that is [sum of credit]-[sum of debit] but when i give this in a query if the difference is there then it is coming but if there is no value in debit then the value in credit should come or if no value in credit - value should come



Tom 100
purchace 5000
salse 1000
jon 4000 1000 3000



Tom 100 100
purchace 5000 5000
salse 1000 -1000
jon 4000 1000 3000
Peter 1000 4000 -3000

I wish combine 2 table field into 1 field in the report

Bank code
ID Bank Currency

Table Product
ID Product Unit Price
1. AAA 6.40
2. BBB 3.00

Report as :
Product Price
AAA RM6.40
Not working with this : =[Bank code][Currency] &""&[Product][Unit Price]

Before I launch into the detailed description, I've foud a simple way of expressing what I need. But please feel free to read the long bits too:

If more than one order # exists for a specific customer, print all order numbers in one field spearated by commas.

my database has several tables with several one to many and many tomany relationships. I am trying to write a summary-type query which will yield all the related information from all of the tables. The query is sorted by Customer Name, but in instances where there are two orders attached to the same customer, the query produces several rows for each customer name:

Customer Name Order #
Joe Smith 11111
Joe Smith 13489
Joe Smith 99999
Bill Smith 44444
Bill Smith 66666

What I'd love to see happen is the following:

Customer Name Order #
Joe Smith 11111, 13489, 99999
Bill Smith 44444, 66666

Can this be done?

Ultimately, I'm trying to create a way that the user can view all the information for a single customer by itself. I'm attempting to do this by creating a form with a drop down list (combo box) of all the customers, retrieve all the information from a single customer (including the relationship issue described above) and then open a query which displays the records for that customer alone.

This query could then be exported to excel and the user could have separate documents for each customer.

I know I should be doing something more fun with a Friday afternoon, but...

Thanks and have a great weekend,


[This message has been edited by ymalcolm (edited 11-30-2001).]

Not finding an answer? Try a Google search.