Forms :: How To Join Tables Using A Link Table In A Form
Mar 10, 2013
I have been asked to maintain a directory for our local Scout district. It's currently in a spreadsheet, but is crying out to be a database.I have created a "group" table, this lists all the groups in the district.I have a 2nd table called "people" this holds details of all the people in the district.As some people can have a role in multiple groups, I have created a 3rd table called "link", this is to link the people to groups.
Having read various different posts on sub forms, I am still at a loss on how to create a form / subform to populate the link table.My initial thoughts were to have a combo box on the main form, listing the people and then have a list of groups on the sub form (the are only 24). I would have a check box next to each group and if checked, it mean that the person selected in the combo is associated with that group.However my issue is that the tick box would be a "yes/no" field and the entry in the link table needs to be a number.
View Replies
ADVERTISEMENT
Jun 4, 2015
I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.
If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.
Note: except the Name column, none of these tables have any other columns in common How do I go about this?
View 7 Replies
View Related
Oct 28, 2005
I am creating a mail-merge application.
The data stored will be dealing with Students and Qualifications that they have attained. The link table in the relationships shown/attached, allows a Student to have multiple qualifications.
When I merge the data, I will need to list all of the qualifications that each student has attained, which will need to be either in a item list, or in a comma-separated list.
The list will need to be pulled from the link table (StudentQualifications). For example:
StudentID #1 >> Qualification #1 Description
StudentID #1 >> Qualification #2 Description
Etc
Etc
So, when the data is merged, it will specify that:
StudentID #1 has acheived Qualification #1 and Qualification #2 and Etc, Etc.
How can I create this list in the first place, which can then be used in the source of the merge for the document.
Any ideas as to how I can pull multiple information into either a single string to pass to the merge or into a list in the document would be appreciated.
Graham
View 2 Replies
View Related
Jul 13, 2005
Have some problems with a Purchase Order (PO) System I'm trying to do.
Figure 1 is the PO entry form. The upper part of the form shows the Header, while the bottom part shows the Line, containing all the ordered item details.
I separated the information keyed in into 2 tables, namely Header table (Figure 2) and Line table (Figure 3). The header table shows only the header details. With the order ID, the header is linked to the Line table where all the ordered items detail can be found.
I want to join the 2 tables and put the data into a new table, which will later be exported to text/spreadsheet for other purposes. I know it can be done using the make-table query. But my concern is about the layout/format of the table.
I would want to put my data where there's one header row followed by the corresponding line rows. Then another new header row with its line rows. I'll give an example:
H XXX XXXXXXXX XX X
L XXXXX XXX XX
L XXXXX XXX XX
L XXXXX XXX XX
H XXX XXXXXXXX XX X
L XXXXX XXX XX
L XXXXX XXX XX
L XXXXX XXX XX
L XXXXX XXX XX
Note that not all the fields for Header row and Line rows are the same. So can I put different data into the same column?
Would appreciate it if anyone could solve my problems. Thanks. ;)
View 1 Replies
View Related
Nov 16, 2013
And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.
Code:
SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins
FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID)
INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID )
ON trains.trainID = bookingLeg.tid) as firstJOIN
Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?
View 6 Replies
View Related
Oct 24, 2013
I would like to join my two tables in vba together so I can reference them in the same way,
ie.
Set rs = dbs.OpenRecordset(SQLstr)
Me.Summarystr = Nz(rs![Summary], Null)
so I want to be able to combine two tables together in the SQL string in order to have both information inside the same record set...
Code:
SQLstr = "SELECT * FROM [table1] " & _
"FULL OUTER JOIN [table2] ON [table1].[sequence]=[table2].[sequence] " & _
"WHERE [table1].[sequence]='" & _
Me.seq.Value & "'"
I just tried this but it didnt seem to like it
View 3 Replies
View Related
Sep 5, 2014
I am trying to create a cross join or Cartesian product TABLE, not a query.
I am creating a training database. For each and employee and each training event, I want to know - is this event required, who approved it, and when was it completed. The table I envision looks like this:
Code:
EMPLOYEE_ID EVENT_ID REQUIRED APPROVER EVENT_DATE
1 1 Y WPD 9/5/14
1 2 N
I currently have 39 employees and 473 events - 39*473 = 18447 records
I was able to make a cross join query and use make table, but whenever I add a new employee or event, if I update the make table query I will lose all my existing data.
View 3 Replies
View Related
Nov 23, 2004
What I need is a table that will hold a primary key field and 16 text fields at 255 characters per field. (NO memo fields) This will total about 4,080 characters but Access 2000 limits the record size to 2,000 characters.
I have two tables with 9 fields in each table in a one to one relationship as follows:
Table1: Primary Key = MyKey = autonumber field.
Table1: 8 fields = text at 255 characters each field.
(i.e. Data1, Data2, Data3, Data4, Data5, Data6, Data7, Data8)
Table2: Primary Key = MyKey = number field.
Table2: 8 fields = text at 255 characters each field
(i.e. Data9, Data10, Data11, Data12, Data13, Data14, Data15, Data16)
The problem is I want all the fields to act as though they are from a single table and to be on a single form. I don’t know how to go about the LinkChildFields and LinkMasterFields without using a SubForm. A SubForm don’t work because it expects the second table to have many records.
Thanks Stan
View 3 Replies
View Related
Mar 14, 2013
I have a table "Product" and in this table I have two fields "StoragePlaceID1" and "StoragePlaceID2". Both these fields link to the look-up table "StoragePlace"
*Attached Image "Product_StoragePlace" from the Access Relationship Window"*
When I want to add a new Product from my inter face i get the error you can see in the Attached Image "Save_Error".I think the problem is that the Relationship is defined as One-to-Many,there a way to define the relationship as Zero-to-Many.
View 5 Replies
View Related
Jun 21, 2013
I'm not very familiar with linking data from Access to Excel but I think it's what I'm going to want to do for my operation. I have a huge spreadsheet that my boss handed me and he wanted to see if it were possible to only present a part of it in access. I already have a form to a table that I created however, it came to my attention that creating a link also creates a new table so I guess my question is: Is there an easy way to have the form always correspond to the linked table? Or another question: is this the right way of thinking about linking the data?
View 1 Replies
View Related
Apr 22, 2013
I have just updated a big mdb database to accdb. But we have two associated databases that cannot be updated - they are for UPS and Fedex data, which presently does not support accdb. Can I link to mdb tables in another database from my main accdb database?
View 1 Replies
View Related
Aug 18, 2014
So, I have two Primary Keys in my table, how then do I link another tables to it?
tblAdmissions
PatientID Number Primary Key
Visits Number Primary Key
tblRoomPhoneNumbers
PhoneID AutoNumber Primary Key
View 3 Replies
View Related
Apr 6, 2015
I have two tables (A) and (B).
Table (A) contains key-field name "position1".
table (B) contains key-filed name "position2".
How to make a link between key-field "position1" and part of key-field "position2".
View 5 Replies
View Related
Feb 25, 2015
I've nearly cracked where I want to be. I have essentially had to do the core of the back-end in SQL in order to organize the data in a way that actually makes sense to ACCESS.
Following this I now have a table with the following fields
Stock No
SK Code
Free Stock Qty
Stk Qty Pd
Actual Min
Actual Max
Forecast EoM
Forecast +1Month
Forecast +2Month
Essentially this is a stock forecast sheet. It takes into consideration incoming stock and estimated usage to provide an output as a "Forecast" stock level at the end of the forthcoming months.
The next phase of developing this is to allow users to manually change the front end by Group and Part Number with Quantity entry so that it auto updates the Estimated EoM, +1Month or +2Month figures depending on which month figure they are looking at. In addition, this information would need to be retained and ideally stored as individual tables or other .
View 2 Replies
View Related
Jul 25, 2014
We have an MS Access 2010 Database that uses Local Tables, External MS Access Tables, and ODBC Linked Oracle Tables (Accessed for Read Only). The unsual issue occurs with the ODBC Linked Oracle Tables.
One of the more important aspects of the project is to modify the ODBC Links to point to upgraded Oracle Database Tables. Up until today, all of the Links had been able to be remediated with a simple refresh and test.
This morning, however, it was determined that one of the Database Tables did not exist in the Schema. Instead, it was an Oracle Synonym for a Table that existed in a different Schema. We believe that we have the proper authorization for access to all of the Schemas involved, and despite this fact, MS Access was unable to link to the Table properly.
View 1 Replies
View Related
Jul 30, 2013
I have a risk table containing risks and risk owners (many owners for one risk), meaning that I have two risk owners columns. What I would like to do is to connect both risk owner columns (containing the name IDs) to the Names table. But Access does not allow to connect more than 1 column to another.
See the attached file...
View 6 Replies
View Related
Aug 19, 2013
I was wondering if it was possible to have a master table where say 10 of my regulatory tables and their data once inputted from the forms will appear there? Is this possible? Because right now, each of my regulatory tables has a form where the data is inputted and appears within said table.
All these tables are separate, obviously. I want to consolidate all the information into one spot, is this a possibility within access?
View 1 Replies
View Related
Jun 22, 2005
I am a newbie when it comes to Access and ASP but I am trying. I am in the need for some help.
I have a large table in Access 2003 and need to break it into smaller tables (not using a query) where the fields are the same except each of the smaller, new tables will hold info for a specific person. These smaller tables would need tro be linked to the larger.
Is there an easy way to do this? I need to keep it in tables due to the ASP software I use to generate the asp pages.
Is this possible?
My goal is for each user to be able to only view/edit their data and for the large table to reflect any changes made to the smaller table.
Thanks,
Dan
View 4 Replies
View Related
Jun 27, 2013
I simply want to link a table from one database to another but am getting an Error -1002 with the error description: "'|' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long."
I have tried just creating a basic table with one field and linking it to another database and still getting the error. It happens when I compact and repair on close.
View 5 Replies
View Related
Mar 21, 2006
Hi there,
I have a question: Is it possible to link more than one table to the same Form in order to keep previous data?
Thanks a lot
View 1 Replies
View Related
Nov 25, 2014
Basically, I want to be able to click on the New (blank) record button, and then start adding in my Attraction, Date, etc fields in the main form, however I keep coming up with the error:
Cannot add record(s); join key of table 'OrdersList' is not in recordset.
I've gone back and checked all my relationships, and the query the form is based on, and all appears to be working there fine. I can manually enter information on to the tables & queries just fine, (but obviously I don't want other users to be able to see these).
I'm wondering if it's something to do with the Auto Number, which is also my PK in table 'OrdersList'. As the first design of this database had this field set to a Text field and I would manually enter the next sequence and I didn't have any issues adding new Orders to the form.
The form is 'Orders', which is based on a query called 'Orders List'.
View 12 Replies
View Related
Apr 14, 2015
I have a form that gets its info from a query, I would like to be able to add a new item, Customer, etc, etc. But, when I try to add one (I have a button using VBA code, ill post that at the end) it gives me the error
Code:
Cannot add record(s); join key or table tblitems not in recordset.
Code:
Private Sub cmdEventNewI_Click()
Me.Visible = False
DoCmd.OpenForm "frmItemsEdit1", acNormal, , , acFormAdd, acDialog
Me.Visible = True
Me.lstItems.Requery
End Sub ' cmdEventNewI_Click
View 8 Replies
View Related
Oct 13, 2013
I'm doing a TAFE assignment (I'm from Australia) and I have tried to populate a field in my subform called RestaurantID with data and it has given me the error'Cannot add record(s) join key table Restaurant not in recordset.
View 7 Replies
View Related
May 4, 2015
I have 2 forms, Form1 is used to enter staff personal and salary information while Form2 will be used to enter Bank information. I am using three tables to maintain this information:
1- Personal Info
2- Salary
3- Bank Details
All tables are joined in relationship by using a field StaffID which have to be entered manually as I can not use auto number. I have enabled cascade update and delete in relationships
The problem is if i use the Form1 to enter the personal & salary information, the StaffID which I enter in Form1 is automatically updated in salary table as Form1 is using both personal & Salary tables. But the StaffID is not updated in table Bank Details. This is crucial as this StaffID is a link between Personal and Bank table. Is there any way that when I enter StaffID in Form1 it will automatically append StaffID in Bank Detail table? Or can I use any query to append StaffID to Bank Details table.
View 2 Replies
View Related
Oct 5, 2004
Hello, exactly how Access 2002 and SQL operate is still a bit fuzzy to me.
In brief:
tableAccounts has fields for Signatory1ID and Signatory2ID, and other stuff
tableSignatories has ID for a primary key and has SignatoryTypeID as a field
tableSignatoryTypes has ID for a primary key and SignatoryType as a field
For each account, on a Form I want to show the Signatory and SignatoryType info for both signatory1 and signatory2. I have tried creating various queries and INNER JOINing the tables and/or queries together, but whenever I manage to get all the information displayed, none of it is editable. Access seems to lock the various text boxes and combo boxes.
Any thoughts?
View 2 Replies
View Related
Aug 18, 2005
Hiya
Please be gentle I am no expert at this sort off thing.....
Is there a way of clicking on a line in a table and the relavant form opens in a new window. You can then edit the form and the table is updated.
Thanks
Gibby
View 3 Replies
View Related
