INNER, OUTER, LEFT, RIGHT JOINs- totally confused.
-
I rried to post a little bit ago but that must have bombed off. I'm using Access 2007 in query design mode At this point I have gotten to where I show the 24 test records for Material Inventory in the correct storage yards. The SQL statement is:
SELECT MatInv.MatInvID, Location.LocationName, MatInv.LocationID, MatInv.Units, MatInv.CostCenterID, MatInv.Condition, MatInv.TasksID, MatInv.MaterialsID, MatInv.CostCenterID, Materials.MaterialsID, Materials.Material
FROM Location INNER JOIN (Materials INNER JOIN MatInv ON Materials.MaterialsID = MatInv.MaterialsID) ON Location.LocationID = MatInv.LocationID;Now I need to show where two 2 items of material in each storage yard came from a DIFFERENT CostCenterID than the CostCenterID of the storage yard. The storage yards are costed based upon the lease they are loacted on. There can be several hundred wells for each lease- all with the same CostCenterID. SOMETIMES, material from one well on one lease (one CostCenterID) may be stored in another leases storage yard (another CostCenterID). I need to be able to track it and point it out. So I add in the table Well and relate the fields Well.CostCenterID to MatInv.CostCenterID and I get 36000+ records. Not the original 24 test records. So obviously the Query designer isn't what I need so I am trying to manipulate things in SQL view- STILL without any luck. Trying to follow info on Access help that is just about useless. Current SQL statement is:
SELECT Well.CostCenterID, Well.Well, MatInv.MatInvID, Location.LocationName, MatInv.LocationID, MatInv.Units, MatInv.CostCenterID, MatInv.Condition, MatInv.TasksID, MatInv.MaterialsID, MatInv.CostCenterID, Materials.MaterialsID, Materials.Material
FROM Well INNER JOIN (Location INNER JOIN (Materials INNER JOIN MatInv ON Materials.MaterialsID = MatInv.MaterialsID) ON Location.LocationID = MatInv.LocationID) ON Well.CostCenterID = MatInv.CostCenterID;Any assisance is appreciated, Larry
-
I rried to post a little bit ago but that must have bombed off. I'm using Access 2007 in query design mode At this point I have gotten to where I show the 24 test records for Material Inventory in the correct storage yards. The SQL statement is:
SELECT MatInv.MatInvID, Location.LocationName, MatInv.LocationID, MatInv.Units, MatInv.CostCenterID, MatInv.Condition, MatInv.TasksID, MatInv.MaterialsID, MatInv.CostCenterID, Materials.MaterialsID, Materials.Material
FROM Location INNER JOIN (Materials INNER JOIN MatInv ON Materials.MaterialsID = MatInv.MaterialsID) ON Location.LocationID = MatInv.LocationID;Now I need to show where two 2 items of material in each storage yard came from a DIFFERENT CostCenterID than the CostCenterID of the storage yard. The storage yards are costed based upon the lease they are loacted on. There can be several hundred wells for each lease- all with the same CostCenterID. SOMETIMES, material from one well on one lease (one CostCenterID) may be stored in another leases storage yard (another CostCenterID). I need to be able to track it and point it out. So I add in the table Well and relate the fields Well.CostCenterID to MatInv.CostCenterID and I get 36000+ records. Not the original 24 test records. So obviously the Query designer isn't what I need so I am trying to manipulate things in SQL view- STILL without any luck. Trying to follow info on Access help that is just about useless. Current SQL statement is:
SELECT Well.CostCenterID, Well.Well, MatInv.MatInvID, Location.LocationName, MatInv.LocationID, MatInv.Units, MatInv.CostCenterID, MatInv.Condition, MatInv.TasksID, MatInv.MaterialsID, MatInv.CostCenterID, Materials.MaterialsID, Materials.Material
FROM Well INNER JOIN (Location INNER JOIN (Materials INNER JOIN MatInv ON Materials.MaterialsID = MatInv.MaterialsID) ON Location.LocationID = MatInv.LocationID) ON Well.CostCenterID = MatInv.CostCenterID;Any assisance is appreciated, Larry
This article may help. Caveat - I use SQL Server not Access I find it easier to lay out my own joins so they are more human readable. Start with the minimum tables to get the minimum result, then expand on it. Get a list of material and their storage location with cost center Get a list of wells and their cost centres get the relationship between well and material, compare cost centers
Never underestimate the power of human stupidity RAH
-
This article may help. Caveat - I use SQL Server not Access I find it easier to lay out my own joins so they are more human readable. Start with the minimum tables to get the minimum result, then expand on it. Get a list of material and their storage location with cost center Get a list of wells and their cost centres get the relationship between well and material, compare cost centers
Never underestimate the power of human stupidity RAH
I liked the link- useful. Right on, I had started back with just two tables and worked my way up from there. I finally decided to add another ID field to one of the tables (breaking normalcy rules) but it helped make it an easier sql select statement. I only had to change a small bit of code to make sure that everything gets saved properly to the tables. So it works now- thanks. Larry
-
I liked the link- useful. Right on, I had started back with just two tables and worked my way up from there. I finally decided to add another ID field to one of the tables (breaking normalcy rules) but it helped make it an easier sql select statement. I only had to change a small bit of code to make sure that everything gets saved properly to the tables. So it works now- thanks. Larry
lemarshall wrote:
(breaking normalcy rules)
These should be known as guidlines as they are subject to reality and business requirement. Having said that I alway looks very closely at a design that does not conform to these guidelines, it is usually wrong somewhere. You got it fixed - always satisfying.
Never underestimate the power of human stupidity RAH