Pulling Contacts From Two Tables Into Third Contact Table
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 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
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
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
Oct 22, 2014
In my database I have a table for all of the customers emails which list the 3 titles for each district they serve, for example District 1 has across the President, Vice President and Clerk. each email is a hyperlink that when clicked once automatically takes you to outlook, however, how can I choose all 3 at once without having to go back and forth to the database and outlook after the one click? so all 3 recipients are included in the email at once. In Excel you can copy and paste the 3 emails but in Access it doesn't allow you to do that. How I can execute this?
View 5 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
Nov 8, 2004
I am trying to get an email address from a table by choosing a employee number from a combo box...it is almost working,
however, it is returning the employee number as opposed to the email address...here is the code I am trying to use:
Private Sub Combo0_AfterUpdate()
Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = myConnection
Dim mySQL As String
mySQL = "SELECT EmailName .*,[Employees] FROM EmployeeNumber"
myRecordSet.Open mySQL, , adOpenStatic, adLockOptimistic
myRecordSet.MoveFirst
myRecordSet.MoveNext
myRecordSet.Close
Set myRecordSet = Nothing
Set myConnection = Nothing
End Sub
Then, in the section for the command button and the Outlook code, I have:
eMailAddress = Me![Combo0].Value
like I said, it is returning the same value that is in the combo box...if anyone could help, it would be greatly appreciated
View 1 Replies
View Related
May 2, 2014
I have datasets in tables for some price data, which is monthly. Each month there is a list of unique ID codes for the products ("CAP Codes") and then a price for that product ("LM Retail").The table names are simple dates (2010 01, 2010 02 etc) I want to have a query which would create a table which follows the price of each product through the months. All of that would be simple except not all of the products appear every month, so when I set up a simple link between them all it only outputs products which appear every month. Is there a way that it'll keep the products with just some prices - leaving a blank in the table created when there isn't a price that month?
View 3 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
Feb 14, 2007
Hi guys.
For my coursework I was told I need complex queries to gian high grades.
This is my problem:
I have many many tables all identicle. They have the same field names just different information on them.
I wanna be able ot query ALL those tables to bring 1 result which I will then make a report out of.
I have tried everything, I fidled with the relationship but can't understand it.
Oh, and the tables have been linked form excel. I know i can't edit it then, but i dont need to.
I would really really apretiate all responses as this needs to be done asap.
Thanks in advanced.
View 9 Replies
View Related
Dec 13, 2007
I'm attempting to build a simple database for a martial arts tournament registration. The weight division (Fin, Fly, Heavy, etc.) depends on the age, the sex and the weight. I've tried this several different ways, most recently by using one table called "competitors" with all the entrant's information, and several other tables based on the age class/sex (i.e. I have a table called Age 8-9 Male with the weights and divisions in it, and other tables for the other ages/sex).
So, basically, what I'd like to have happen is a report that will lookup in the correct table the division, based on the weight entered. In other words, for a record where the age is 8-9 and the sex is Male, the report compares the weight to the values in the 8-9 Male table, or if the record is a 12-13 Female, it looks in the 12-13 Female table for the weight, and places the correct division in the Division field of the report.
Probably going about this wrong.....but any help would be appreciated.
View 5 Replies
View Related
Mar 6, 2014
So I have two tables:
users
primary key = user_id (AutoNumber)
surname (Text)
forename (Text)
...
user_change
primary key = user_change_id (AutoNumber)
user_id (Number) which relates to the user_id from "users" table
change_type (Text)
action_date (Date/Time)
...
In user_change I record any changes made to the users table.
What I am wanting to do, within a query, is pull basic details from the "users" table (forename, surname, etc.) which is working fine, but also add in SPECIFIC data from the user_change table if it exists.
I want to pull ALL rows from the users table, not just specific rows, and not just rows where my criteria for the user_change table match.
This is the data I want to pull from user_change...
The MOST RECENT action_date WHERE change_type is LIKE "*issued*".
However there won't be a change_type LIKE "*issued*" for everyone - I want it to be included only if it exists.
At the moment my query is ignoring any users who don't have a user_change record with "*issued*" in the change_type value. I'm also getting duplicate user rows where people have more than one value for "*issued*" - I only want the most recent one...
View 7 Replies
View Related
Dec 26, 2012
I need to pull data from multiple tables in order to show a "financial summary"..Currently I have: Company; BalanceSheet; Debt; Liabilities; Income..All tables have a lot of information (which is why I built them in multiple tables).
I need to build a form where I can use a combo box to select a company from a list.Once selected - I need to the form to pull selected information from each of the above tables. (As well as perform some math functions).I've been struggling with the relationships (They don't seem to make a difference) and I believe I am above and beyond what the wizards will accomplish.
I have read thread after thread but cannot seem to find a specific answer on how to accomplish this.To make matters more complex - Once finished I want to be able to select multiple companies and create a report from the fields mentioned above (IE: pick company A, B, and C and have all of there "current Assets" add up on one report)
This task was originally achieved using an Excel spreadsheet but it has become to confusing for users and difficult to save information for future use.I believe all of my fields are constructed correctly. how to compile the data from multiple tables into one form/report.
View 5 Replies
View Related
Dec 17, 2011
I Have a Contacts table, when I use the filter on the field that I want filtered I get what I want, no problem, however when I save that filter and close the table and then reopen it it the filter is gone, how can I save the filter that I set up?
View 2 Replies
View Related
Jun 13, 2012
I'm a bit new to Access but have managed to build a very simple database.
I have a main table which I need to add 4 columns onto the end of, the data for which comes from 4 tables linked to it. Below is how they are linked.
Main table------> Table 1
Main table------> Table 2
Main table------> Table 3
Main table------> Table 4
All 4 relationships have a join type of 2 (Include ALL records from 'Main table' and only those records from 'Table1/2/3/4' where the joined fields are equal)
However, 3 of them pull back the correct data when I refresh the Main table, but the 4th one doesn't - which appears to be to be set up in the exact same way. It does bring back data, but it's the wrong data or in the wrong order.
For example, if the related field in the Main table is "Sarah" - It needs to bring back "Programme", but some Sarah's pull through Programme, some are blank and some are another option altogether. Table 4 has no duplicates or typos etc and I've tried deleting it, loading it in again and creating a new relationship but nothing seems to work.
View 5 Replies
View Related
Aug 7, 2014
There are three tables. An [Action Register] table, a [Calls] table and a [tblContacts] table. The Contacts are common to both.
The Calls table records calls to customer by date
The Action Register table records issues that Customers send in by Open date.
I am trying to make a query where we see the latest date the customer was contacted regardless of which table.
I created two queries.
qryLastCallDate finds the max date from the Call table:
SELECT Max(Calls.CallDate) AS MaxOfCallDate, tblContacts.ContactName
FROM Calls LEFT JOIN tblContacts ON Calls.ContactID = tblContacts.ContactID
GROUP BY tblContacts.ContactName
ORDER BY Max(Calls.CallDate);
qryLastIssueDate finds the max date from the Action Register table:
SELECT Max([Action Register].Open) AS MaxOfOpen, tblContacts.ContactName
FROM tblContacts RIGHT JOIN [Action Register] ON tblContacts.ContactID = [Action Register].Contact
GROUP BY tblContacts.ContactName
ORDER BY Max([Action Register].Open);
The problem I am having is that if I use Left Join I can see all the records from the Calls table but not all from the Action Register table. And vis versa if I use Right Join. This is because sometimes we have calls but no issues in the Action Register table and sometimes issues with no calls.
This is my Left Join query using a Min Max Module I found here: [URL] ....
SELECT qryLastCallDate.ContactName, qryLastCallDate.MaxOfCallDate, qryLastIssueDate.MaxOfOpen, qryLastIssueDate.ContactName, DateValue(MaxOfList([MaxOfCallDate],[MaxOfOpen])) AS [Last Contact], ([Last Contact]+21) AS NextCall
FROM qryLastCallDate LEFT JOIN qryLastIssueDate ON qryLastCallDate.ContactName = qryLastIssueDate.ContactName;
How do I get to see ALL the records from both queries.
View 3 Replies
View Related
Jun 18, 2013
I have a query that gets it's data from tblContacts. In this table is a yes/no field for archiving and the query gets only those records with a No in the field. The default is no. Next I have a form based on this query, which I am using to hold a set of command buttons which act on a contact chosen from an unbound list. So far so good but now I would like to add a command that changes the contact's status in the archived field of the table to Yes, so that I can use the form to view/edit, or email, or archive.
View 1 Replies
View Related
Aug 6, 2012
I have an Access 2007 SQL database with 20 users. The DB has a list of about 3,000 email addresses that I want to export to Outlook for mass mailing. I've already got the services of an SMTP mail service that allows 1000 mails at a pop to be sent, and I've already written in the logic to separate the list into groups of 1k.
The problem I'm having is that I'm currently exporting from Access 2007 to Excel 2003 format (for some unknown reason Outlook 2007 does not directly interface with Access 2007), and then I have to manually go through the import steps within Outlook to bring the data from each 1k name group spreadsheet into a contact group for mailing.
It all works fine as it is, but it's clunky as hell. I want to automate that process, but I'm not very familiar with Outlook. I'm not even sure what the file format is for a group. Surely it must have its own format, like group1.xxx, or something. My ultimate objective is to export straight from access to the .xxx (whatever it is) format, direct it straight to the appropriate folder within Outlook and in the process eliminate the need for the more tedious steps required to import each Excel file within Outlook that I'm currently using.
View 8 Replies
View Related
Dec 11, 2005
I trying to build a usage tracker. Users will take a reading once a week and enter data. I want to open the form and have 2 fields filled in based off the last reading entered into the table. I have an ID field (autonumber) and I tried using that field -1 to populate my other fields but it does not work. any suggestions
jon
View 1 Replies
View Related
Dec 10, 2007
Hello all,
I would consider myself a novice to almost intermediate with access.
Anyways what I need to do seems pretty simple. I just need to make a web page that pulls values from a table we have stored on our server. The users WILL NOT need to modify the info, they simple need to view it.
I want it set up so that when users modify the table locally here at our company, people from another site can view the changes on the internet...
Thanks for your time!
View 1 Replies
View Related
Jun 20, 2007
I'm querying off of a teradata table in Access. I have the teradata table linked to a table I made in Access which will only pull specific cars(example). How can I make the query pull.. like car* for every record I have in the table?For example..My made table has20120220321Q24Retc...the teradata table shows this201G101201G102201P202203A12021QP131the record means ...the first 3 characters is the car name, the middle two char is the location, and the last 2 is the day of the week.Btw in the query I use in my other querying system it has an option "Begins With" which works, I don't know if acces has this? Can anyone assist me on this? If I am unclear let me know!!
View 14 Replies
View Related
Mar 16, 2012
I have created a database to track the paper files that I am accountable for. Every day I get requests from workers for various files. I have split the database so that multiple users can access the db and edit or change the status of files. In the be I have the main db with fields required for the files and I have another database (table 2) with similar fields that i use to generate an email form that workers fill out to request files. Access then automatically pulls that data from the email form and inserts in to table 2. I pull those requested files then enter the data in to table 1 so that I know the who/what/where of a files location. I want to be able to automatically insert info from table 2 into table one after I have pulled the file and checked it out.
View 2 Replies
View Related
Jun 2, 2015
I have two tables; one has just variances entered, the other is the total number of events for the month (normal events + variances). The variances become the numerator, and the total events is the denominator.The report does counts of variances based on location, type of variance, etc.This is working just fine, but I also need to show on the report the number of variance/total events (%).
I am struggling with pulling the data for total events into the variance report.The variances all have dates, the totals have start and end dates (monthly). For the variances, my query asks for date range, but I want this same date range to be used for pulling in totals from the other table.I am trying to use DSUM("monthlytotal", "tblTOTALS",Between "StartDate=input start date from report query" And "input end date from report query").Should I setup a form that after the start/end dates are enter, the report is launched using these dates?
View 5 Replies
View Related
Aug 18, 2014
I am trying to figure out a way to pull a date from a table and add it to another table automatically. I'm building a database for a harvest/trucking operation and we want to be to set the date everyday and then it pull that date on all of our tickets, reports, etc. Most days we run till after midnight but we want to still record the date as the day we started so I don't think pulling the date from the computer itself would be a good option.
View 2 Replies
View Related
