Relationship produced unanticipated Query results 1

[keithhbs]

A portion of my tables/relationships is shown below:
PK=Primary Key; fk=Foreign Key; ... indicates there are more fields not shown; the dotted line shows a relationship that was removed.

tblSalesperson
SRInitials (PK) <------------|
.... |
|
tblSalesTerritory |
TerritoryCode (PK) |
SRInitials (fk) |
... |
|
tblCustomerFile |
CustomerID (PK) |
TerritoryCode (fk) |
... |
|
tblOrders |
Order# (PK) |
CustomerID (fk) |
SRInitials --------------->| (fk)
...

tblOrders!SRInitials is present (along with several other fields including compensation rate) to accomodate the fact that changes will occur that would cause wrong info to be associated with an order (e.g. Sales Rep leaves, comp rate changes, etc).

When new orders are created the Sales Rep is known once the Customer is identified and this info is pulled into tblOrders.

Initially I set up tblOrders.SRInitials (fk) to also relate back to tblSalesPerson!SRInitials (PK) because ... it seemed innocuous(?)

When printing the monthly order report there were several orders missing. Ultimately I discovered that I needed to remove this relationship to obtain the correct data.

Is this truly a "flaw" or was the real flaw with my Query?

 

[MikeRBS]

The relationships don't directly affect what comes out in a report. Joins have to be made each time you design a query although Access sometimes defaults these for you using the relationships you have declared to Access (if you have done so). Most likely in your case, your query needs an outer join somewhere. Post the SQL for us to see.

 

[lespaul]

I bet that you have SRIntials in tblOrders that don't exist in tblSalesperson. Right click on the relationship line IN THE QUERY between tblSalesPerson.SRIntials and tblOrders.SRIntials. Change the join type to the one that says:

Return all records from tblOrders and only those that match from tblSalesperson

(or something along those lines) anyway, it's creating a LEFT or RIGHT join instead of an INNER join.

HTH

Leslie

 

[keithhbs]

Sorry to do this, but you asked for it ("tblOrdersAgent" is what I previously called "tblOrders"):

THIS ONE WORKS:

SELECT tblOrdersAgent.OrderDate, tblOrderDetailAgent.[Order#], tblOrderDetailAgent.[Unit#], Year([OrderDate]) AS OrderYear, Month([OrderDate]) AS OrderMonth, [tblOrderDetailAgent]![Order#] & "-" & [tblOrderDetailAgent]![Unit#] AS OrderUnit, IIf(IsNothing([tblCustomerFile]![CustomerName2]),[tblCustomerFile]![CustomerName1],[tblCustomerFile]![CustomerName1] & " - " & [tblCustomerFile]![CustomerName2]) AS CustName, tblOrderDetailAgent.ModelID, tblOrderDetailAgent.TrxCode, tblOrderDetailAgent.Quantity, tblEquipmentTypes.Multiplier, [Multiplier]*[EquipRev] AS EqRevNet, [Multiplier]*[FinanceRev] AS FinRevNet, tblOrderDetailAgent.ContractCode, tblOrdersAgent.Analyst, tblOrdersAgent.CustEducation, tblOrdersAgent.SREqCompRate, tblCustomerFile.TerritoryCode, [tblSalesPerson]![LastName] & ", " & [tblSalesPerson]![FirstName] AS SRName, [tblOrderDetailAgent]![EquipRev]*[tblSystem]![HBSCommissionEquip]*[tblOrdersAgent]![SREqCompRate] AS [Comp]
FROM tblSystem, tblEquipmentTypes INNER JOIN (tblSalesTerritory INNER JOIN (tblCustomerFile INNER JOIN ((tblSalesPerson INNER JOIN tblOrdersAgent ON tblSalesPerson.SRInitials = tblOrdersAgent.SRInitials) INNER JOIN tblOrderDetailAgent ON tblOrdersAgent.[Order#] = tblOrderDetailAgent.[Order#]) ON tblCustomerFile.CustID = tblOrdersAgent.CustID) ON tblSalesTerritory.TerritoryCode = tblCustomerFile.TerritoryCode) ON tblEquipmentTypes.ModelID = tblOrderDetailAgent.ModelID
ORDER BY tblOrdersAgent.OrderDate, tblOrderDetailAgent.[Order#], tblOrderDetailAgent.[Unit#];

THIS ONE DOES NOT:

SELECT tblOrdersAgent.OrderDate, tblOrderDetailAgent.[Order#], tblOrderDetailAgent.[Unit#], Year([OrderDate]) AS OrderYear, Month([OrderDate]) AS OrderMonth, [tblOrderDetailAgent]![Order#] & "-" & [tblOrderDetailAgent]![Unit#] AS OrderUnit, IIf(IsNothing([tblCustomerFile]![CustomerName2]),[tblCustomerFile]![CustomerName1],[tblCustomerFile]![CustomerName1] & " - " & [tblCustomerFile]![CustomerName2]) AS CustName, tblOrderDetailAgent.ModelID, tblOrderDetailAgent.TrxCode, tblOrderDetailAgent.Quantity, tblEquipmentTypes.Multiplier, [Multiplier]*[EquipRev] AS EqRevNet, [Multiplier]*[FinanceRev] AS FinRevNet, tblOrderDetailAgent.ContractCode, tblOrdersAgent.Analyst, tblOrdersAgent.CustEducation, tblOrdersAgent.SREqCompRate, tblCustomerFile.TerritoryCode, [tblSalesPerson]![LastName] & ", " & [tblSalesPerson]![FirstName] AS SRName, [tblOrderDetailAgent]![EquipRev]*[tblSystem]![HBSCommissionEquip]*[tblOrdersAgent]![SREqCompRate] AS [Comp]
FROM tblSystem, (tblEquipmentTypes INNER JOIN (tblSalesTerritory INNER JOIN (tblCustomerFile INNER JOIN (tblOrdersAgent INNER JOIN tblOrderDetailAgent ON tblOrdersAgent.[Order#] = tblOrderDetailAgent.[Order#]) ON tblCustomerFile.CustID = tblOrdersAgent.CustID) ON tblSalesTerritory.TerritoryCode = tblCustomerFile.TerritoryCode) ON tblEquipmentTypes.ModelID = tblOrderDetailAgent.ModelID) INNER JOIN tblSalesPerson ON (tblOrdersAgent.SRInitials = tblSalesPerson.SRInitials) AND (tblSalesTerritory.SRInitials = tblSalesPerson.SRInitials)
ORDER BY tblOrdersAgent.OrderDate, tblOrderDetailAgent.[Order#], tblOrderDetailAgent.[Unit#];

Thanks for responding; I especially appreciate MikeRBS's comment about the relationships. Prior to learning Access my I spent several years working with DataFlex (DOS); I assumed that the relationships operated the same, but it appears that Access allows them to be more flexible.

Lespaul, I don't think the TYPE of join is the problem because I have experimented with that. If I connect (relate) to the Sales Rep file from the Order File AND the Sales Territory File the number of records produced by the Query drops by about 25%. Turns out that I can connect from either file but not both to get a correct output.