Tables :: Many-to-many Relationship Inside One Table?
Aug 5, 2015
I have a table with all my contacts. Manufacturers, distributers and outlet shops. I want to be able to make relationsships that show which manufacturers sell to which distributers and outlet shops, but at the same time I also want to be able to see which manufacturers are being sold by a particular outlet shop or distributer.
I am reading everywhere that you need to make a table to link the 2 tables together for every one relationship you have and this I understand. Most of the time it is relations between 2 tables that you want to establish. But in this case all these companies are in the same table. Can I still make a many-to-many relations ship between different items that are all in the same table using one single extra table that holds these relationships?
View Replies
ADVERTISEMENT
May 25, 2015
I have a linked table(tblxyz) having property set as ORDER BY ID DESC, ID is autonumber, so my table view gives me latest record on top.
Now I have a subform , where i am calling this Table.....
[Forms]![MainForm]![Sub_DisplayFm].SourceObject = "Table.tblxyz"
This gives me datasheet view of the table inside subform but its not showing sorted data
Interesting thing is it give sorted data view when my table is not linked and is in same access file.
View 4 Replies
View Related
May 29, 2014
I have two tables of data, each relating to three business branches (branches A, B and C).
Table 1 shows the expenditure of each branch (by fuel, premises and wages).
Table 2 shows a number of units for each branch (mileage, floorspace and sales).
What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.
View 6 Replies
View Related
Nov 13, 2013
I am in the beginning of setting up a database and have NOT messed with multiple tables. I just want to make sure that I am setting up the tables as well as the relationships correctly.Here is what I currently have, 4 tables and they are named. Employee, phone, radio and spotter. Employee table is where my primary key is located and I currently have a 1-to-many relationship between Employee and the others.
What I want to be able to do is to store information in the multiple tables from 1 form, which I don't think will be an issue and then at a later date be able to pull up information. EX.Thus being able to skip anything being put into the spotter table. Then at a later date be able to pull up only the select information I originally put it?
View 5 Replies
View Related
Nov 12, 2013
i am making a database from which i can calculate fuel required from one point to other in any particular type of vehicle.the problem is fuel from Pt A to Pt B is not same as from Pt B to Pt A.A sample table is attached as zip. qp.bmpis there any way i can cross refer field values with that of rows .
View 9 Replies
View Related
Aug 18, 2013
When I originally created my access database, I used a text field/column to connect 2 tables together. I would like to change this to reference the Primary Key/ID column instead of the text field.
Is there an easy way to go about doing this? My original thought was to create some vba code to replace the data in the column of the sub table with the autonumber in the primary table but I cannot figure out how to do it.
View 4 Replies
View Related
Nov 25, 2013
I attached a screenshot with notes that describes my problem.
View 2 Replies
View Related
Sep 24, 2012
I have a contacts database and I am trying to set the relationship between the contacts table and the locality table. The contacts table has a LocalityID field that is a long integer and the Locality table has an autonumber as the PK. When I drag the LocalityID on one table to the other LocalityID I get the Can't create this relationship. When I look at the Edit Relationship dialog box the primary table is the Locality table not the Contacts table. I want set up a lookup on the contacts form that relates to locality.
View 4 Replies
View Related
Nov 29, 2012
I have two tables containing data from two separate sources. The unique "link" between the two is a JobNumber field. I need to set up a relationship between the two tables using the JobNumber field.
Problem:
In one table, the JobNumber has been input in this format: 12-00345-01
In 2nd table, the JobNumber has been input in this format: 12-00345-1
How can I tie these when the 2nd table is missing the "0"?
View 8 Replies
View Related
Feb 4, 2014
I have a Client database table in Access. I now need to add a simple order table (related) to the client table. I have a client ID field set to autonumber in the client table. As I start to create the orders table I'm not sure how to link the two so that I'm not entering data twice and have assurance that they are tied together.
View 4 Replies
View Related
Apr 27, 2013
I'm currently using Access 2010 and I'm working on a database project. My question is related to table relationships. Within that project I do have a table that is related to other three tables where that table is the parent. The problem here is that once that table is updated or have a new value it never cascades it down to the other tables.
1- How to have multi relationship to the same field on the same table from different tables?
2- how to cascade the updates to the related fields?
3- Is there any way to force the data update to other tables?
View 5 Replies
View Related
May 12, 2006
Hi
I have 5 PC clients and a server.each user upon shutting down his PC,is prompted to send all the information inside a table "Patron" to a table inside the server.That way each user backs up his information to a table inside the server and at the end of the day,the table on the server side contains all the data from the 5 PCs.I am new to access,can anyone guide me through the steps I need to accomplish this project?
thanks in advance
View 1 Replies
View Related
Oct 16, 2013
i add a tab control, but i want to insert a table inside my tab how can i do that?..cant find any tables in my design tab when i work on my tab control
View 1 Replies
View Related
Jun 14, 2005
I've been testing my scripts so my database has a whole bunch of bogus records in it. I'd like to simply delete everything out of my orders table (there aren't any legit ones in there yet) so that I can start over when I go live and not have all that crap in there.
Is there an easy way to do this without re-creating a blank database and copying/pasting table only over?
View 2 Replies
View Related
May 5, 2013
I have 3 tables.
Table 1: contains staff names and contact numbers
Table 2: contains training above staff have been on or need to go on
Table 3: contains pc and printer asset numbers of above staff
I used a form and entered some new members of staff in table 1. They got their auto numbers etc but when I open table 2 and table 3 those new members are not showing up in those tables. I have checked the relationship status between the 3 tables and the staffID from Table 1 is associated to table 2 and to table 3.
What's stopping the new entries from showing up in tables 2 and 3 ?
View 10 Replies
View Related
Mar 4, 2013
I am about to set up a database but wanted to check the relationship of the main tables before I add to it. I have attached the relationship design
For a PROJECT, there can be many TESTS, for a TEST, there can be many PRODUCTS
Is my design reasonably sensible?
View 6 Replies
View Related
Apr 27, 2005
Hi experts. i wrote this code that supposed to create table inside the access db but when i run it i
get this error:
compile error:
user -defined type not defined for Dim db as Database
Itt is a command button that calls CreateDatabase. I be happy if some one help me fix this. Thanks
My code:
Option Compare Database
Public Sub CreateDatabase()
' Routines to create the tables.
CreateTablePlayers
'CreateTablePenalties
' Routine to create relations for the tables.
CreateRefInt
End Sub
Private Sub CreateTablePlayers()
' Local variables
Dim db As Database
Dim tbl As TableDef
Dim fld As Field
Dim idx As Index
Set db = CurrentDb()
Set tbl = db.CreateTableDef("Players")
' Adding fields
Set fld = tbl.CreateField("playerno", dbInteger, 0)
fld.Required = True
tbl.Fields.Append fld
Set fld = tbl.CreateField("name", dbText, 25)
fld.Required = True
tbl.Fields.Append fld
Set fld = tbl.CreateField("initials", dbText, 3)
fld.Required = True
tbl.Fields.Append fld
Set fld = tbl.CreateField("birth_date", dbDate, 0)
fld.Required = False
tbl.Fields.Append fld
Set fld = tbl.CreateField("sex", dbText, 1)
fld.Required = True
tbl.Fields.Append fld
Set fld = tbl.CreateField("joined", dbInteger, 16)
fld.Required = False
tbl.Fields.Append fld
Set fld = tbl.CreateField("leagueno", dbText, 4)
fld.Required = False
tbl.Fields.Append fld
' Adding primary key
Set idx = tbl.CreateIndex("Players_PK")
idx.Primary = True
idx.Unique = True
Set fld = idx.CreateField("playerno")
idx.Fields.Append fld
tbl.Indexes.Append idx
' Add Table to the database
db.TableDefs.Append tbl
End Sub
' Adding Referential Integrity
Private Sub CreateRefInt()
' Local variables
Dim db As Database
Dim fld As Field
Dim rel As Relation
Set db = CurrentDb()
Set rel = db.CreateRelation("PlayersPenaltiesRel", "Players", "Penalties")
rel.Attributes = dbRelationUpdateCascade
Set fld = rel.CreateField("playerno")
fld.ForeignName = "playerno"
rel.Fields.Append fld
db.Relations.Append rel
End Sub
Private Sub Command1_click()
CreateDatabase
End Sub
View 2 Replies
View Related
Dec 16, 2013
I'm having a problem with the syntax of a recordset of a Datasheet inside a subform which is also inside a Main Form.
Main Form - frm_1_0_LMS
Subform - frm_1_4_0_TeamApprovals
Subform(Datasheet) - frm_1_4_1_TeamApprovalsList
Here is my code:
Code:
Dim rs As DAO.Recordset
Set rs = Forms!frm_1_0_LMS.frm_1_4_0_TeamApprovals.frm_1_4_1_TeamApprovalsList.Form.Recordset
If Not (rs.EOF And rs.BOF) Then
Forms!frm_1_4_2_ApproveDeclineUserLeave.Controls("lblFiledDateLeave").Caption = rs!Leave_Date
End If
I am getting this error: Object doesn't support this property or method
View 1 Replies
View Related
Sep 9, 2013
I've only just started using Access 2007 at my new job. I've been asked to create a database that will show appointments for all 10 of the employees. I have created a table for the main schedule (where ill put all the data) then one for each of the employees. I've managed to link the tables no problem but it wont let me create and updating relationship. It keeps saying "no unique index found for the referenced field of the primary table". How do I fix this?
I want it to automatically update the date, time, location, customer name and description, if its changed on the main schedule for a certain appointment on the corresponding employees schedule.
View 1 Replies
View Related
Dec 11, 2012
I created two tables, but i don't know what kind of relationship i should create.
In the first table, i would like to put all different tests (medical tests, such as EMG test, and so on), in the second table, i want to add the settingup for each test, say, recording site, stimulating site et.al, then i want to use one form to populate data into these two tables, what should i do?
View 6 Replies
View Related
Sep 19, 2013
I am trying to define a relationship between tables. However, the unique nature of my data doesn't seem to be allowing typical relationships. I am not sure if I need to somehow create a relationship (junction table?) or just keep things as they are. Do I need a Foreign Key? FYI, I am only querying the data - no updates.
I have two tables that I am able to join by using a field in Table1 tied to a portion of a field in Table2. No other columns in either table can reliably relate the two tables. For example:
Table1.ColA has a 5 character string.
Table2.ColA has a 10 character string.
I need to match Table1.ColA to Table2.ColA where the first five characters in Table2.ColA match Table1.ColA.
This match, produces 1 to Many results (for every Table1.ColA string, there are 1 to many Table2.ColA records that match.
IMPORTANT FACTORS:
- Table1 contains data at a SYSTEM level.
- Table2 contains the parts that make up the "SYSTEM" in Table1
- The PARTS in Table2 can be in 1 to many SYSTEMS from Table1
- Table1.ColA is not unique by itself (it's part of a composite PK in Table1).
- Table2.ColA is not unique by itself (it's part of a composite PK in Table2).
View 2 Replies
View Related
Jan 17, 2006
Hi,
I have a db with two table.
Table1 Trade has the following among other fields: RefNo (autoNo) PK, TradeDate, HostName, Methodology, etc.
Table2 Market has the following among other fields: MarketDate as Date ( ), USD/EUR_Rate, USD/CHF_Rate, USD/BRL_Rate, etc.
I need to link both tables so that the TradeDate correspondes to the MarketDate. what is that say on TradeDate if it is on 15/01/06 I can see what the Market values for the MarketDate corresponding to that TradeDate.
I tried to link MarketDate as PK on Market table2 to TradeDate on Trade Table1.
It seems to work but it does not seem right as sometimes I get errors in entering data, etc on the Market table2.
The MarketDate is unique meaning only one data of values for a given date, they can be modified but not duplicated.
Can anyone help, on the best solution for the links to work?
Thanks
dfuas
View 14 Replies
View Related
Feb 27, 2008
I feel confused about something and I have the need for assistance because solving this small thing would make my life easier. I very much appreciate you taking the time to read this.
I'll focus in on a small part of my database:
----------------
| tblBreakpoint|
----------------
| BreakpointId |
| Remarks |
| ... |
----------------
each Breakpoint can have only one Module and a Module can have many Breakpoints
----------------
| tblModule |
----------------
| ModuleId |
| ModuleName |
| .... |
----------------
each module can have only one Function and a Function can have only one module
----------------
| tblFunction |
----------------
| FunctionId |
| FunctionName |
| ... |
----------------
I want to normalize my database as much as possible.
I will make a make a form for Breakpoint containing two comboboxes. The top combobox will contain all Modules and I want the second combobox to contain only the Functions that are related to the selected Module from the first combobox.
I know this can be programmed, that's how I do it now: in the onChange-event of the first combobox, I load the content of the second combobox.
However: what I would like to know specifically 3 things:
--> what is a common way to solve this?
--> Is there a way to solve this using just table-relationships? (drop 2 cbo's on the form and with correct relationships access populates the second and/or the first automatically)
--> What are the correct table relationships for this and therefore what missing ID's (used for relationships) have to be filled in in the above 3 tables?
View 3 Replies
View Related
Oct 23, 2005
Hi:
Tables:
I have 2 tables, there are same fields: Name, Date, and Invoice #
I make a relationship between 2 tables, connect Name, Date, and Invoice #
And click "Enforce Referential Integrity", "Cascade Update Related Fields", and "Cascade Delete Related Records".
One-to-One.
After that, I go table 1, create a record, then close it, then open table 2, the record I created in the table 1 is NOT over there. Does the relationship create record? It only update and delete. What about Add ?
Please let me know about it. Thanks.
View 2 Replies
View Related
Dec 20, 2004
First time user!!!
I work for the school dist. and we have 2 tables for 2 different groups.
I need to pull data from them both and create a new Query.
Example
Table 1:
Name, address, phone
Table 2:
Name, address, phone
(No similarities in ether database, and we'd like to NOT export data)
I'm hoping it's an easy fix that I've overlooked.
Michelle
View 2 Replies
View Related
Jan 22, 2006
Hi guys i know this is a simple one but i have been struggling for ages now i would very much love some help. I have a customer database that has a number of tables customer, quotes and materials. I keep getting errors all the time from the database when i try enter information im not sure whether it is my relationships or my tables setup.
The first table that will be displayed is the customer one once all the information has been entered into that one then the customer database should be linked to the quotes table and then from the customers database it drops down into the quotes with the little + sign and then the user enters all the information to do with the job but the field Materials must call a dropdown box, and then display the contents from the materials database, but only the material name.
If anyone can help please do as i have been struggling with this for over a week now and have tryed to solve it myself but with no joy Thanks in advanced i have included the database link below:
View 3 Replies
View Related
