Tables :: Setting Relationship Between Contacts And Locality Table
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 Replies
ADVERTISEMENT
May 15, 2013
I need setting up a history table for contacts and the companies that they are associated with. I am sure this will be obvious to some of you database veterans but I am fairly new to Access and I can't seem to figure out the best way to accomplish what I am trying to do.
Here is what I need to do:
When a contact's employment status changes, I need to change the contact's current company association but somehow maintain his or her association with the previous company so that s/he can still be associated with past projects.
So, in my contacts table (TBLContacts), I have a foreign key field "CompanyFK" that links to my companies table (TBLCompaniesPK). There is a one to many relationship between TBLCompanies and TBLContacts.
I want the CompanyFK field to be the current company but somehow link the person with past companies too so that the project directories and subforms will continue to show the contact's association with the parent company.
Maybe I don't need a history table but something else?
I have a similar problem with companies that change name, too. How to deal with takeovers, name changes, mergers, etc.
View 5 Replies
View Related
Aug 8, 2007
Hello....
Here is what I am doing. It is an inventory database that also is an order tracking per se database.
When you are entering in the order, the top part is the vendor with an order number, date etc (will show table later).
The subform is the order details. This is the tricky part that I am having problems with.
In the subform I also need to have the unit that is placing the order for that particular item.
IE
product 1 pens black ball point unit=exams
product 2 pens red ball point unit=admin
product 3 paper legal color white unit=personnel
I can get it to work but the unit part is what is driving me nuts.
I am attaching a very stripped down version no queries etc....
Thanks
View 6 Replies
View Related
Jun 14, 2013
I have three tables.
Table 1: Group
Field 1: Group Text field ( Primary key)
Field 2: Group Description Text field
Field 3: Uidgroup( Autonumber)
Table 2: Subgroup Text field
Field 1: Group ( I want to bound this column to Table 1's Group field that is column 1) I have set bound column property to 1 and column count 1 and the Subgroup table is showing group fields as input perfectly no issues in that )
Field 2: Subgroup, Text field( Primary Key )
Field 3: uidsubg( Autonumber)
Table 3: Email
Field 1: Group ( Bound to Table1's Group ; showing values in combo box, setted bound column property to 1 and showing group field perfectly, no issue in that )
Field 2: Subgroup (I want to bound Table2's subgroup field, which is column number 2, so I wrote 2 in bound column property and row source is table subgroup ; Here is some error comes up, values from subgroup field of subgroup table not being shown up in Email Table's subgroup field as combo box. )
Field 3: Email Text field
Field 4: uideml (Autonumber ) primary key
I want to prepare a Data entry form should have all these fields from all the tables. That should work in following way, first user selects Group then User selects Sub Group and write Email and save the record.
What relationship should I set, or shall I change the table structure.
View 1 Replies
View Related
Apr 29, 2014
Ok, I have 3 tables. One lists a contact in conjunction with the branch of the company and the trips that contact takes. The second lists a contact in conjunction with the branch of the company and which team they work with (may work with many). The third should list their email address and their phone number. Is it possible to pull the contact name and branch of company from tables 1 and 2 into 3 automatically, such that all I have to input into table 3 is additional contact information? If that is possible, is it also possible to only pull each contact/branch of company pairing once (I don't want four entries for Joe Shmoe/Sales, even if he's taken 4 trips)?
Table 1
Branch of Company
Trip Dates
Trip Location
Contact
Sales
4/1/14-4/12/14
Chicago
Joe Shmoe
HR
6/2/13-6/4/13
New York
Jane Doe
Table 2
Branch of Company
Company Team
Contact
Sales
Blue Devils
John Deere
Sales
Jets
John Deere
Sales
Jets
Joe Shmoe
HR
Sharks
Jane Doe
Table 3Contact
Branch of Company
Email
Phone
Jane Doe
HR
jane.doe@company.com
800-555-1234
Joe Shmoe
Sales
joe.shmoe@company.com
800-555-1235
John Deere
Sales
john.deere@company.com
800-555-1236
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
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 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
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
Nov 30, 2012
Access 2010..One organization that we work with provides us with a block of numbers for each of the two types of contract products we order from them; we do order non-contract stuff from them also.The block of numbers are the same (i.e. 20000 to 30000 this year) for each of the two products. This means that each product can have the number 20000, for example. We call this the Tracking Number. If it is one of these products, we need to select the Contract Number.
For all other one off orders we have with them, we assign our own Tracking Number starting with 00001. This Tracking Number cannot duplicate unless it is one of the aforementioned two products.Both the Tracking Number and Contract Number are in the same table. The user selects the Contract Number from a form (connected to the Contract Number table that has all the details on the contract) and the Contract Number is populated in the same table that has the Tracking Number.Each order must have a Tracking Number (no null)..Not all orders need a Contract Number (null okay).The Tracking Number and Contract Number combination cannot duplicate.I tried setting the primary keys to more than one field in the table, but they cannot have null values.
If not... I have been working on Plan B.... an AfterUpdate on the form (either the form or a field... don't know yet) that looks at a query that only has results if there are duplicate values.
View 1 Replies
View Related
May 17, 2013
In a simple Access 2003 database that has two table. A contacts table and a Report table.
I would like to be able to prevent the deletion of any contact that is still being used in the Reports table. The problem is that they are in a backend of a linked database. So the enforce referential integrity doesn't work. Plus I don't think I would like it anyway as it seems kind of dangerous to the reports table.
Is this possible to do what I need through the use of form coding?
View 14 Replies
View Related
Jan 9, 2013
I have researched the 'party' data model but it is a bit too complex for what I'm seeking. For those familiar with it, I don't really need the intermediary relationship from-to tables.
I'm interested in ideas about setting up a data structure that will allow users to search contacts or select contacts in dropdowns regardless if the contact type is a person or an organization.
Obviously the fields needed for both are different and the biggest issue is the name field because the person contacts are
The way I am accomplishing it now is writing the company name, or "first name " & "last name" for a person, to kind of a bridge table when a new record is inserted into the person table or the organization table...kind of inefficient.
Is this a relationship thing or should I just write a function to create a temporary recordset when needed?
View 3 Replies
View Related
Dec 19, 2006
my custom have a big table in the outlook, in the contacts.
in the outlook table that possible to insert also the birthday.
my custom want to send mail day-before the birthday to wish.
I want to do it with access, to link the table to the contcts and send mail if the birthday is tomorrow.
but when I try to connect to the contacts in the outlook, i get the fileds:
first name
last name
email
....
but no the birthday and the anniversary
(the table design attach)
how can I use this filed in access?
View 1 Replies
View Related
Aug 20, 2005
Hello All,
At work we have a large and messy Contacts list so I decided to set one up using a database.
At present I have 3 tables:-
Companies (custID,companyname,address,etc)
People (nameID,firstname,middlename,lastname,custID)
Phones (phoneID,phonetype,areacode,number,?????)
My problem is this, Some of the phone nos belong to the individuals
and some belong to the company. If a person is replaced at a company I need to reasign the company phone nos to the new person whilst retaining the individuals and their personal phone nos. If a company is deleted I need to delete only the company phone nos. and if a person moves within the company I want the company nos to reasign to the new replacement but keep the personal nos of the individual. Now I see its going to be more complex than I thought.
Can anyone help me with the table layout and links. (nb this is only a simple database relating a person to a company without using departments etc.)
Its main use is to provide phone nos names and addresses quickly.
Many Thanks
Peter
View 4 Replies
View Related
Jul 11, 2005
Today is one of those days where I can't seem to wrap my head around anything, so here goes.
I have a database where the contact information is linked to an agency name via a third table which has the contact ID# and agency ID#, and nothing else.
In a fourth table there is a list of programs with three contacts associated with each program, for simplicity sake let's just say contact1, contact2 and contact3.
I am trying to create a select query which will link (via relationships) the programs, which are associated with an agency, to the contacts table via the agency/contact join table. I am getting a complete listing of the programs and their contacts, however it is coming out like:
Program Name Contact1 Contact1 Contact1
Program Name Contact2 Contact2 Contact2
etc.
I have the query set up as follows:
Program name, Contact1, Contact2, Contact3
If you need more information let me know.
Keith
View 1 Replies
View Related
Nov 16, 2013
I have a numeric field (long integer) in a table. I've set the format to fixed. No matter what I set the decimal places to, auto, 0 or 2, when I enter a value of 0.71, it displays as 1.00
In my form, I've also set the field property to fixed, but it displays the value as 1.
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
Dec 10, 2013
I have 5 tables and 2 forms. The primary form is what I input all the information into (Tracking) and the other form is to update employee information (update form).
The "Tracking" form is where I add information to 4 of the 5 tables. Here is where I'm stumbling. Would it be more practical to just have 1 table and just expand the fields, or have the form put the information into the separate tables. Those 4 tables are Employee, phone, spotter and radio.
I'm wanting to keep a running tally of who doesn't turn in what equipment on what day.
View 3 Replies
View Related
Mar 4, 2007
I am confusing myself more and more as i read on normalization and queries and everything else. I am trying to create a database for parts that our company sells and am having trouble deciding how to lay out the tables. I figure I should use the part number for the primary key as it will not repeat. from there though, i have a problem, each type of part has different fields nessesscary to decribe its attributes. for example, one part number may be for a box and its describing fields might be brand, width, length,load rating, and height. but another part number might be for a circuit breaker which would have completley different describing fields like brand, amperage rating, voltage rating, connection type, and trip type. Should i create 1 table for everything even though many fields would be blank depending on which type of item it was or should i create seperate table for each type of item with only the required fields to decribe that item?
View 14 Replies
View Related
Apr 18, 2006
Dear all,
i have having such trouble setting up some tables in a new db, and am wondering if someone could give me some tips as I really don't know what I'm doing (I am new to this).
I have one main table, called Client Information. The PK is CaseNumber. I also have 11 other tables. The PK in each of these is called things like CasedetailsID, witnessinfoID, etc. Each of these tables contains the CaseNumber field (supposed to be from the Client Information table). The Client Information table contains the PK from each of the other tables.
The way I have the relationships set up at the moment is that the Client Information Table is linked to each of the other tables via the fields called casedetailsID, witnessinfoID, casetypeID, etc. The relationships are all one to many (the 'many' side being on the Client Info table, the 'one' side being on the related tables). There is obviously something incorrect about the way I have the tables set up, however, as when I try to enter data into the form, although the data gets stored in the individual tables, none of my queries or reports seem to be working (ie, I try to run a report, but it doesn't show any data).
any tips or advice regarding a good way to go about setting up tables and relationships would be very much appreciated.
View 3 Replies
View Related
