Joins To The Same Table
Nov 1, 2006
Hi there,
if i have a table with columns:
Teacher ID1 | Teacher Comment1 | Teacher ID2 | Teacher Comment 2 |
i also have another table that links the teacher ID with their names called [Staff Profiles]
how do i create a query that returns the names of both teacher 1 & 2. i have tried:
SELECT *
FROM [Subject Assessment] INNER JOIN [Staff Profiles] ON [Subject Assessment].[Teacher ID1]=[Staff Profiles].[Teacher ID]) INNER JOIN [Staff Profiles] ON [Subject Assessment].[Teacher ID2]=[Staff Profiles].[Teacher ID];
This throws an error. I have tried Aliasing but this also throws an error.
don't know whether this makes a difference, but the table which i gave was a join in the first place.....
i.e. teacherID1 | Teacher Comment1 is the tutor report of which there is one per student
teacher ID2 | teacher Comment2 is the subject report of which there are many per student
thanks in advance
View Replies
ADVERTISEMENT
Apr 14, 2005
I need to link two tables on the Name Field. The trouble is that the names are not enterred the same in each table, so I can't do a direct = comparison.
For instance, one table might have "The Heart Center of Indiana", while the other has "Heart Center of Indiana" Or one might have "St. John's Medical Center" and the other has "St Johns Medical Center" (or, god help me, "St John's Hospital")
My only thoughts are somehow building a matching rank by saying that 85% of the characters in "The Heart Center of Indiana" match "Heart Center of Indiana". There are thousands of names in each list, and I would very much not like to have to manually try to spot them.
I doubt there is a direct solution to my problem, so any tips on how I can make a translation table is aoppreciated.
Thanks,
David
View 4 Replies
View Related
Jun 5, 2007
I have this query in design view and in an asp page and it works fine:
SELECT dbo_feedback.*, dbo_origin.originName, dbo_product.prodname, dbo_category.catName FROM dbo_product INNER JOIN (dbo_origin INNER JOIN ((dbo_feedback INNER JOIN (dbo_category INNER JOIN links_cat ON dbo_category.catID = links_cat.CatID) ON dbo_feedback.id = links_cat.FeedbackID) INNER JOIN links_product ON dbo_feedback.id = links_product.FeedbackID) ON dbo_origin.originID = dbo_feedback.origin) ON dbo_product.prodID = links_product.ProductID;
BUT, I want to return all feedback entries, even if they have no matching Product or Category. :confused: When I change the inner joins to outer joins I get a syntax error in the browser window. I changed the join type in the relationship diagram in Access and tried to recreate this in query designer, but Access says the statement cannot be executed because of ambiguous outer joins.
FYI, one feedback can have many products, many categories, and only one origin. I have the joins correct and enforced.
Please help, thanks!!!
View 1 Replies
View Related
Aug 14, 2007
Hi again,
I just restructured my DB and I was wondering if anyone can give me some advice on whether or not my joins/relations are correct. I left some joins/relations out because I wasn't sure what relation I should use.
Any Advice will be greatly appreciated
18418
View 2 Replies
View Related
Sep 21, 2004
I am trying to create a simple Search form in Access where a user can select a desired record and query multiple tables using the inputs.
I would like them to be able to query Retailers, Distributors and Products.
The 6 tables are linked as follows:
Although some of these tables are not included in the query, they are required to ensure relationships.
Retailers -- Uses (RetailerID,DistributorID) -- Distributors
Retailers -- Orders (RetailerID,ProductID) -- Products
All retailers have at least one distributor BUT a retailer may or may not have ordered any products.
I have created my form but the query linked to the form is having some trouble. It is only selecting those records that have ordered products. For example, if I query a retailer name only and it does not have any ordered products, it will not display. Is there a problem with the table joins? The SQL for the query is displayed here:
Code:
View 5 Replies
View Related
Feb 5, 2008
Hi there,
Consider 2 simple tables
Name ID Pet
A 00 dog
B 11 cat
C 22 hamster
Name ID hasJob
A 00 no
B 11 yes
D 33 yes
how do I make a join to get this table?
Name ID Pet hasJob
A 00 dog no
B 11 cat yes
C 22 hamster --
D 33 -- yes
Help much appreciated.. thank you
View 3 Replies
View Related
Oct 16, 2006
Hey everyone! I just have a relatively quick question.
Situation:
I have a database where i have 2 tables. One table has items in one column, and the width, length, and height of the item. Another table has the exact same fields, except the only items are ones that need updated as far as their dimensions. The fields with those items include their new dimensions. How can I create a query to pull down all of the items with the correct dimensions?
Any help is greatly appreciated!
View 2 Replies
View Related
Apr 15, 2008
I've got a table with a self join which represents a tree structure that can have variable depth.
I want to get a spreadsheet view of this for project review meetings. I can do a bunch of nested queries until I get all the branches but i would like something a little more dynamic, i.e. something that will automatically show the spreadsheet view of all branches no matter the depth of the tree.
So anyone got any suggestions on how to handle this?
View 3 Replies
View Related
Apr 29, 2008
Hi Kids i'm a Newbie so be nice!
Only been using Access for a little over 6 months. It's an ongoing struggle but a worthy string to my bow.
I have been using a simple join to filter out matching fields from a bigger table that exist in a smaller table. If you like i create the smaller table around what i need to see from the bigger one. I hope that makes sense.
So what i want to achieve now is what i'm calling a "compliment join". This is where i use the smaller table to filter from the bigger table but I am left with everything but what was in the smaller table.
Any ideas...??
View 2 Replies
View Related
May 9, 2007
Hi All,
Can anyone help me. I have a database table called orders. This contains and order status id. In a separate table i have the order status id and the what the id means i.e delivered, awaiting payment etc.
I need a record set that returns all the fields from the order table and the actual order status not the order ID.
I know i need to use a inner join but just can get it right.
Can anyone help me with this.
This is what i have but it return nothing:
Code:SELECT a.*, c.OrderStatusFROM Orders AS a INNER JOIN OrderStatuses AS c ON a.OrderStatusID = c.OrderStatusIDORDER BY OrderDate desc
EDIT:
Actually that is right and it does what, stupid me, frazzled brain at this time of night.
What i actually meant was how can i also pull the customers name from the database table Customer, based on the orderID?
Thanks in advance
Tom
View 3 Replies
View Related
May 12, 2005
I have a form with a drop down menu of people to filter a report of projects with the managers and up to 3 assistant managers. When I choose someone from the dropdown menu, I want all of their projects to come up on the report. Currently, only the projects that person is managing come up, not the ones they are assistant managing.
In my query that is powering this report, I have joined the manager_id number in table A to an id_num field in table B. To make the assistant managers come up in the report, I need to join the assist1_id, assist 2_id, and assist3_id to id_num also. When I join assist1_id to id_num, I get the following error:
The SQL statement couldn't be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in the SQL statement.
The way I would like to fix this problem is by creating subqueries in the SQL view, but I don’t know how to code it. If you know how or if you know a better way of doing this, please help! :)
View 1 Replies
View Related
Nov 9, 2005
I would like to know the difference between these two concepts.
Would anyone care to explain it to me?
View 4 Replies
View Related
Dec 5, 2005
Is there a limit to the number of joins allowed in a query?
View 2 Replies
View Related
Feb 4, 2007
hi
i have 4 tables: tbEmploye, tbCirculaire, , tbCategorie, lienCirculaireEmploye
tbCategorie (every circulaire have a category)
-cirIdCategorie
-cirCategorie
tbEmploye
- empID
- empPrenom
- empNom
- empNumeroEmploye
- empMotPasse
tbCirculaire
- cirID
- cirIdCategorie
- cirNumero
- cirDescription
- cirDate
- cirNomFichier
lienCirculaireEmploye (link circulaire to employe
- cirNumero
- empNumeroEmploye
- dateLecture
SELECT tbCirculaire.cirNumero,
tbCategorie.cirCategorie,
tbCirculaire.cirDescription,
tbCirculaire.cirNomFichier,
tbCirculaire.cirDate,
tbEmploye.empNumeroEmploye,
tbEmploye.empPrenom ,
tbEmploye.empNom,
lienCirculaireEmploye.dateLecture
FROM (tbCirculaire INNER JOIN lienCirculaireEmploye ON tbCirculaire.cirNumero = lienCirculaireEmploye.cirNumero)
INNER JOIN tbEmploye ON lienCirculaireEmploye.empNumeroEmploye = tbEmploye.empNumeroEmploye
INNER JOIN tbCategorie ON tbCirculaire.cirIdCategorie = tbCategorie.cirIdCategorie
WHERE tbCategorie.cirIdCategorie IN ( 1,2 )
with this query, i get error: 3075
Syntax error (missin operator) in query expression
lienCirculaireEmploye ON tbCirculaire.cirNumero = lienCirculaireEmploye.cirNumero)
INNER JOIN tbEmploye ON lienCirculaireEmploye.empNumeroEmploye = tbEmploye.empNumeroEmploye
INNER JOIN tbCategorie ON tbCirculaire.cirIdCategorie = tbCategorie.cirIdCategorie
any idea?
View 2 Replies
View Related
May 29, 2007
Wierd Join needed...Here's my problem. it's been bothering for a bit...I have 2 tables, one with a date, and the 2nd table with 2 dates. I need to only pull the records from the 2nd table where the date in the first table come between them.TABLE 1DATEJOB#EMPLOYEE#TABLE2 JOB#EMPLOYEE#STARTDATEENDDATEThe query should take every line from TABLE1 and ONLY the lines from TABLE 2 that qualify.
View 5 Replies
View Related
Jul 24, 2007
Hello,
I have the following code for a multiple join:
INSERT INTO [AppendAllFields]SELECT [TreatyList].[Treaty] AS [Treaty],[MLAC 42 Treaty Xref ER].[tai treaty] AS [TreatyType],[txn 01/04].[Policy_Number]
(and more other fields from [txn 01/04] table)
FROM [txn 01/04] INNER JOIN [MLAC 42 Treaty Xref ER] ON TRIM([txn 01/04].[Policy_Number]) = TRIM([MLAC 42 Treaty Xref ER].[Polnum])
INNER JOIN [TreatyList] ON TRIM([MLAC 42 Treaty Xref ER].[tai treaty]) = TRIM([TreatyList].[TreatyNo]);
Basically, the txn 01/04 table has a corresponding Polnum field in the MLAC 42 table, and MLAC 42 table has a tai treaty field, which corresponds to TreatyNo in TreatyList table.
However, when I tried to run this, I got an Syntax error.
Could anyone please help?
Thanks!
View 4 Replies
View Related
Aug 31, 2007
Hi,
I have 3 tables.
From table 1, I join fields A, B, and C to fields A, B, and C on table 2. From Table 2, I join Fields 1 and 2 to Table 3. All the joins are Join 1.
When I pulled (Queried) fields D, E, and F from Table 1, field D from Table 2, and field D from table 3, I have a sum of $1000 under field (column) E from Table 1.
The second time I pulled data, I added fields A and B from table 1 to the query. However, I get a total of $1500 from the same column. i.e Field E from Table 1. I can understand that there will be more rows to provide further data breakdown, but I could not understnad why the total change.
Please help.
Thanks.
View 3 Replies
View Related
Sep 4, 2007
Hi all,
Simple problem, but my access and sql skills are very limited.
I have two tables. One containing a group of frequent customers with a column called 'member status'. Another table containing a group of non-customers. The addresses in both tables have been matched using group1 software.
I would like to create a query that shows me all the customers with 'member status' = 'A' and all the non-customers who live at the same address.
When i run the query, everybody comes up as 'member status'=A. I think this is because there is no 'member status' field in the non-customers table, and i have failed to make the appropriate join or parameters. Is there a way to design a query that will show 'member status' for those who have it, and will display a null for those who do not?
Any help is appreciated thank you!
View 4 Replies
View Related
Dec 4, 2007
Good day all
I have a simple query linking two tables using the primary keys from each. The problem is that every time I go into design mode of the form that uses the query to populate a list box I get a Data Type Mismatch error and when I look at the query, Access has changed the join from the Pk in one table to the field after the PK in the other field. I have attached an image to show the change. Note that the join should be from the first fields on each table. I am really stuck with this guys, it is gettin me down and preventing me from developing the database further and my boss is on my case. Can anyone please help me? I have checked all the table relationships and they are fine. Thank you.
Gareth:(
View 13 Replies
View Related
Jul 26, 2005
Attached is a pdf of the query window showing the relationships and table structure; (sorry for the quality) the linkage is also permanent at the relationship window. I created a form (columnar) of Rooms; loaded a subform (columnar) of the projects; and then loaded the students (tabular) as a subform on the projects subform. The data entry is flawless; tabs through each field and form to form in sequence.
After entering several rooms data I tested it at the query level by loading the three tables: rooms, projects, and students, and the permanentely established linkage with junctions came in automatically. I thought I was home free--but when I run the query, I get zero records.
When I attempt various joins, thinking this will yeild all records from the many tables and their match, I get "ambiguous outer joins" and it says to run a separate query and add it to the SQL Statement?
Thanks for any help,
Almost funtional in Ann Arbor........
Oh, and thanks Pat Hartman for the tip on linkage -- although I may have screwed it up anyway.
View 11 Replies
View Related
Jan 20, 2006
I have two tables...'Suppliers' and 'Despatches'.
I'd like to create a query that shows all the suppliers that are in the Suppliers table as well as the last date of despatch (if any). Thus, this would be the maximum date that relate to that supplier ID. However, despatches that have a quantity of 0 should be excluded.
Currently I have the following, it's working fine but suppliers who doesn't have a corresponding despatch are not being listed. I need the report to list all the suppliers.
SELECT [Suppliers].[fldSupplierFullName], [Suppliers].[ID], Max([Despatches].[DespDate]) AS MaxOfDespDate
FROM Despatches LEFT JOIN Suppliers ON [Despatches].[SupplierRef]=[Suppliers].[ID]
WHERE ((([Despatches].[QtyLoaded])<>0))
GROUP BY [Suppliers].[fldSupplierFullName], [Suppliers].[ID];
Can anyone give me any direction?
Thanks :)
View 10 Replies
View Related
May 3, 2006
I am trying to set up a file compare process. I load the two files into two "matching" tables (there is a key field). I have a series of queries which find any differences in the various fields and displays them. My problem is that one field (Field4 - a tran code) is coded 1,2,3 in one file and A,B,C in the other. I have set up a third translation table with two columns, each row showing the matching codes (1/A, 2/B, 3/C, etc). I want to find corresponding rows in FILE1 and FILE2 (matched on KEY) where tran codes (Field4) do not match (ie a "1" in File1 but NOT a "A" in FILE2).
I have not been able to get the two inner joins to work. Can some one help.
This is my last attempt:
FILE1: KEY-Field1-Field2-Field3-Field4
FILE2: KEY-Field1-Field2-Field3-Field4
(Field4 is the code that needs to be translated)
FILE3/numeric/alphabetic
1 A
2 B
3 C
4 D
SELECT FILE1.KEY,FILE1.Field4, FILE2.Field4
FROM
FILE1 INNER JOIN FILE2 ON FILE1.key = FILE2.key
INNER JOIN [FILE3] ON (FILE1.Field4 = FILE3.alphabetic)
WHERE FILE2.Field4 <> FILE3.numeric.
Hope this makes sense.
Mac
View 3 Replies
View Related
Jun 9, 2006
Hi,
I have two tables:
tblListeners
-----------
ID (PK)
FirstName
LastName
etc
tblReference
------------
ID (PK)
ListenerID
ReferenceTypeID (FK)
ReferenceDate
etc
(Btw I am aware of the unconventional naming of the PKs but I'm running the B/E on SharePoint so I have no choice!)
So listeners have to periodically do a reference. What i want is a query that tracks if listeners either have not done a reference EVER or haven't done one for a while, but broken down by the FK in tblReference.
Here is my reasonably simple SQL so far:
SELECT tblListeners.ID AS ListenerID, tblListeners.FirstName, tblListeners.LastName, tblReference.ReferenceTypeID, tblReference.ReferenceDate
FROM tblListeners LEFT JOIN tblReference ON tblListeners.ID = tblReference.ListenerID
WHERE (((tblReference.ListenerID) Is Null) AND ((tblReference.ReferenceDate) Is Null)) OR (((tblReference.ReferenceDate)<DateAdd("m",-6,Date())))
ORDER BY tblListeners.FirstName, tblListeners.LastName;
In the current query the results ignore the FK so the so a listener will be missing if they have done one type of reference - i want them to be there (or not be there) for each type of reference. Hope that makes sense!
Now i know people may suggest a crosstab for this but: a) I don't get on with them and wouldn't know how to implement it and b) this will need to be in a report and I don't want to venture down the 'dynamic crosstab report' path!
Any ideas?
Cheers,
Bobadopolis
View 7 Replies
View Related
Feb 5, 2007
I have made a database and have made a query that takes the info from the tables and creates a report. I have a subtable in the form and have added another sub table. When I try to add the new subtable info into the query for the report, I get "ambigous outer joins"??? What the heck is that?????
View 4 Replies
View Related
Nov 23, 2004
I'm new to the Access SQL world but I have a fairly complicated query that needs to be written for a project I'm working on. I am having trouble grasping the join concepts in access. My Query so far is:
SELECT u.user_logon_tx, ur.aff_sec_role_id, r.sec_role_ds, c.sec_cls_ds, o.sec_obj_ds, ci.aff_sec_cls_id, ci.sec_cls_item_ds, IIf(p.perm_in Is Null,0,p.perm_in) AS perm
FROM aff_sec_cls_item AS ci, aff_sec_user AS u, aff_sec_role AS r, aff_sec_user_role AS ur, aff_sec_obj AS o, aff_sec_cls AS c, aff_sec_obj_item_role_perm p,
ci LEFT OUTER JOIN p ON ci.aff_sec_cls_item_id=p.aff_sec_cls_item_id
WHERE u.aff_sec_user_id=1
And ur.aff_sec_user_id=u.aff_sec_user_id
And ur.aff_sec_role_id=r.aff_sec_role_id
And o.aff_sec_obj_id=1155
And o.aff_sec_cls_id=ci.aff_sec_cls_id
And o.aff_sec_cls_id=c.aff_sec_cls_id
And ur.aff_sec_role_id=p.aff_sec_role_id
And o.aff_sec_cls_id=p.aff_sec_cls_id
And o.aff_sec_obj_id=p.aff_sec_obj_id;
I have gotten it to work by using just the tables I'm doing the outer join on but when I add everything else I get the Ambiguous Join error. I wish I was using Oracle Thanks for the help.
View 1 Replies
View Related
Oct 30, 2007
I have a database in which i am trying to create some forms in order to search for records. The database contains several tables. Some tables are linked using a compound key while others have a single PK. I created the search form which has different searching criteria. Once a user selects the criteria, they hit the search button (there are different buttons for each type of search) the button opens up a form to display all the data. The problem is that there are many tables to report on. I created a form with a subform. The tables associated with the main form are all being retrieved fine. however the subform doesn't seem to work. I created the subform using a query to select all the fields from the desired tables, i gave this query a criteria based on the main form so that the Id's would match. (ie. Main form ID = Firstable in subform.ID)
I got a blank sheet in the subform when i used the search. i presumed that my query was not pulling records because of the referential integrity rules. i went in and changed the relationship rules so that it would include all records from the first table and matching records from the second table. (middle option in the list when editing a relationship) I now get the following error when i try to run the query.
"The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement."
Could someone please tell me what this means as the help is... how shall we say it.. written in true Microsoft Fashion.
And though it tells me how to fix it, i'm not really sure what it's telling me to do. I am using Access2003 But the Database is in an access 200 file format.
Thanks.
View 3 Replies
View Related
