Need Some Tips In Creating Relationship Join Lines Between Tables
May 16, 2005
Hi, I am new here and need some help with MS Access97. I am looking for a more convenient way to join tables together. The usual way that I know is to drag the field(s) in one table and drop it in the other table (within the relationship window).
Is there any other way that I can do the same without drag and drop (keyboard instead of mouse)? This is real problem to me when I need to do some modification and have lots of tables scatter across few pages and the vertical scroll of the Relationship window is not working properly with drag & drop technique.
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
I have made a form I am trying to bound 3 tables to.
The 3 tables are
tblPersonalInfo tblBookingInfo tblSkills
How I am trying to set it up is that I enter someones personal details in once. But using the same personal details someone can have more then one booking. With the skills....each person can have lots of different skills.
Basically I work for a recruitment agency and I have been asked to build a database that can add new people.add skills to that person.....then add different jobs to the same person.
I have joined all tables togeather (I think wrong but trying) the tblbookinginfo is joined to tblbooking using a 1 - Many relationship using the primary key of tblPersonalInfo to the field PersonalID in the table tblBookingInfo I have joined tblSkills to the table tblbookingID through SkillsID
I created a query using all 3 tables and have used this to bound the form....
The form contains 3 pages and the 1st page works ok (it only contains info from the tblPersonalInfo) the 2 other pages don't seem to work as I want them to.
I would like to be able to click to add a new person and also new booking and new skills
Currently I am able to add new personal details but nothing else.
I have a table called MiscORders where all the orders are tracked. There is an ID, order number , other fields and ParentorderID fields in it. Whenever an order is modified they create a new order with ordernumber and have the previous order number as the parent. The next time it is amended or closed another order is created with the parent order attached. Here is some sample data
Order ID OrderNum Parent order id 1 MISC 2013-10 2 MISC 2013-10A MISC 2013-10 3 MISC 2013-10B MISC 2013-10
Now I am creating a query that shows the lifecycle of the order
MISC 2013-10 Issue and deadline date MISC 2013-10A issue and deadline date MISC 2013-10B issue and deadline date.I tried to create a query using self joins to the same table. SQL is attached
SELECT MISCORDER.OrderNum, MISCORDER.[Date Issued], MISCORDER.[Deadline Date], MISCORDER_1.OrderNum FROM MISCORDER LEFT JOIN MISCORDER AS MISCORDER_1 ON MISCORDER.OrderNum = MISCORDER_1.RescindedOrderID WHERE (((MISCORDER.OrderNum) Not Like '*A' And (MISCORDER.OrderNum) Not Like '*B' And (MISCORDER.OrderNum) Not Like '*C' And (MISCORDER.OrderNum) Not Like '*D') AND ((MISCORDER_1.OrderNum) Not Like '*B'));
How can I put a condition on the table to show only records with order num ending with A. It is not working if i use it in the where condition..
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.
I've been tasked with creating an Access database..We have a main table that includes information found on every lease, however there are 3 leases types. Each which lead to another table or two. Basically, I want to create a form that allows me to link the main table with the corresponding variable tables.
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?
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?
I am creating a one to many relationship with the tblEmployee EmployeeID and tblWorkHistory EmployeeID because one Employee can have many past jobs
where I run in a cloud is that in access under the edit relationship dialog window it has an button called JOIN TYPE when I click on that button it open the Join Properties dialog window and presents me with three options
1. Only include rows where the joined fields from both tables are equal
2. Include all recoreds from the 'tblEmployee' and only those records from 'tblWorkHistory' where the joined fields are equal
3. Include all records from 'tblWorkHistory' and only those records from 'tblEmployee' where the joined fields are equal
Can someone explain what access is trying to ask me as it pertains to my example. I am new to access to a basic answer would help me with future relationship issues and a better understand of the concept
I have a table with all the job information for each of our jobs. I want to create another table with a job notes field that is linked to the job info table above. My main goal is to be able to make a query or report for the job notes on all our jobs on a specific date.
Q1. Is there a way to link the job info field with the job notes field where the job # and job name information will automatically populate into the query?
Q2. How do I build a relationship between the job info field and the job notes field. I tried to link them, but when I do the query the job # and job name fields aren't being populated, it only shows a single blank row for the whole query.
I have a database assignment where I need to link some tables. One is a table with a composite primary key and the other is a table with a regular primary key. The composite key is already linked in a one-to-many relationship with the first of its two fields; the problem is the second field also needs to be in its own one-to-many relationship with a different table. Access keeps creating a one-to-one relationship. I need it to be a one-to-many relationship.why this might be happening?
i have joined a jobs table and payout table together and performed a query. however i would like to perform SUM by the job_id but crew_id fields are duplicated.
(the payout table comes from a subform that doesnt differentiate crew type so my SUM totals all the payouts whereas i want to sum the payouts by job_id when the crew id are repeated.
I have started a new project where I have tables of people, organization, and roles, as well as one that is supposed to contain the relationships among them. In all four tables there is an AutoNumber ID field as the primary key. The fourth table, the relationship table, has as foreign keys, the primary keys from the first three.
I have created a form with a combo box for each of the three main tables. The intent is for the user to select a person, an organization, and a role, and then click on a button control that will create the relationship in the relationship table. What I am trying to do in the code is, using an AfterUpdate event procedure for each of the combo boxes, open the appropriate table as a recordset, find the selected record, and set a form-local variable to the value of the ID field for that record. So far, I can't get past the first combo box. Here is the code I have written, including both the form variable declarations and the AfterUpdate code:
Option Compare Database Dim personID As Integer Dim orgID As Integer Dim roleID As Integer Dim rsPerson As Recordset Dim rsOrg As Recordset Dim rsRole As Recordset Dim rsRel As Recordset
[code]...
The error message I get is Compile error: Object required, with the personID reference in the last statement highlighted. (qryPerson is a query based on tblPeople that combines first and last names into LNFN.)
I have about 600,000 records in an excel sheet and tried using the table analyzer but I run out of memory. Instead i have broken up my giant table into a few other tables with their own primary keys and have populated from a maketable query. I related the tables together in a 1 to many and many to many table and one of the subforms is working great. The problem i am having now is with the many to many table (WorkedJunctionTbl). I don't want to have to manually input thousands of lines of data, so is there a way i can populate the data from the original data correctly using the new autonumber primary keys?I can't seem to get an append query to work correctly.
Hi, I’m having a problem creating a form for easy data entry. Let me give you a little background. I have a database for a tutor program, students request a tutor for a class, and then I fill out a follow up report to find out if they are being tutored or changed their minds. Next I enter the contact information indicating the date and time of their tutoring session.
Students can be tutored in more than one class but they can only have one tutor for each class.
I have a form where I enter the tutor request info and sub forms for their classes, follow ups, and sessions. Ideally I would also like a form just to enter the session info. I want a combo box to select the tutor and then a sub form to select the tutee with a combo box which will auto fill the Subject, and Course fields, and field to enter the date and contact hrs.
I think the problem is with my relationships. I’m just kind of stuck. I think I need to create a form based on a query but I haven't done that before. Any help would be appreciated. Attached is a screen shot of my relationships and of my current form for your reference.
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:
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.
I have a relationship between two tables; the parent table is "Client Information" with a primary key of "Client_ID", the child table is "Event Information" and the foreign key is "Client_ID".
I know that within the Event Information table I can select which Client_ID to use as the link. I want to have that done within a form, however. I have a form for Client Information with a subform for Event Information. If I go into the table and manually set the Client_ID then everything displays fine.
How can I set the Client_ID in the subform to automatically match the Client_ID in the main form? I can only assume that these solutions require the parent/child relationship to already have been established.
Each each record in table1 has a unique four character (alpha-numeric) code to identify it. The first two numbers of this code represent the group it is in. (Ie. 15AB and 1502 are both grouped together) The second table stores values that apply to the entire group. I need to create a relationship between these two tables based on the first two characters in the ID field.
Things I've tried: * Making a calculated field with left$() formula - Access doesn't allow relationships on calculated fields * Create a new field for just the first two characters and create a data macro for after update and after insert to update that field with the expression - cannot edit the field the user is on
Now that i have read this again, i think it could be summed up into one question...if i have a form based off a query with an outer join that has various duplicate records, is there a way to use the recordset in an if statement that says something like if this recordID = that recordID then dont show one of them...hence not showing the duplicate field data in the form.If you want a more specific description of the problem, read on, otherwise don't read on.Hi All,So I hope I can explain this ok....here goes....I have a search using dynamic queries: I have a form where the user can put in various information he wants to search to find a record. In this case it is searching for Hotels. So the user can search a country to see all of the hotels in that country. Also, the user can search an interest like Beach or Nature to see those hotels that apply. Obviously each hotel may have more than one interest so I have a 1-many relationship with a table called Hotels_Interests.The kicker, and you can likely already see why, is that the user does not have to fill out every search field. He may search Country&Interest, or just one or the other, or leave everything blank to see all hotels in the database. The results are simply ordered by HotelID or something like that in a form that is based off the dynamic query. The dynamic query is of course just based off the query i explained, but with criteria added in.The problem is with the query that i am basing this search off of. Right now it has the main Hotels table as well as the 1-Many table Hotels_Interests and even another that is 1-Many Hotels_HotelTypes (say All Inclusive, Resort, etc.). So this query has various 1-Many tables as well as the main Hotels. Now, if i fill in all of those fields in the search form, there will obviously not be any duplicates returned, which is super. But if i leave Hotel_HotelTypes search field blank, i will be returned with the same hotel twice or more times, which is my problem, because i want nice search results.I have heard of people using Union queries to get rid of duplicates but this obviously does not solve my problem as i do not want to just get rid of these entries. What i think i want is some VBA method or whatever of showing in my search results each HotelID that meets the search criteria only one time.Right now i have it working with If statements that say if the user has left a specific search criteria blank then base the search off a different query. This is obviously crazy and is only a temp fix. Now that i want three or more 1-many tables in my query, i would be talking about if statements for like 6 or more queries, insane.I apologize for the length of this, but i wanted to be perfectly clear. I feel like it should be not too hard, like using a record set for the form and not showing certain records or something, but i am not sure how to do it.Thanks so much. Dillon
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.
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 ?
I was just wondering if any one had any tips or insider secrets on how to lower the size of a database and/or get it to run faster and more smoothly. I know you can split it but is there any other way? I'm using alot of macros and going from screen to screen there is sometimes a lag. I dont know much in the way of code but im willing to learn. Any links or advice on code how too's would be appreciated aswell...thanks alot I'd also like to say these posts have helped me a great deal and i cant wait until I am confident enough with Access to help out some other people.
