complex join issue - access 2003

G

Guest

I need help with a small database (less than 500 lines / table) that I'm
working on. I have a parts list that I'm breaking down by manufacturer,
seller, model number, new / old number, etc. I need to have a query pull in
all the information based on old number, some of the old numbers will not
have new numbers, will not have manufacturer, will not have a brand name,
etc, but I still want all of the old numbers to come in, and a <NULL> value
assigned to the fields that are not relavent. I was trying to accomplish this
with the use of left and right outer joins, but for some reason when I put it
all together, I get an error about the order of the join and for some reason
can't seem to figure out what's wrong. I have tried using other queries to
pull in some of the data and piece it together and it still only returns 139
records, where I am guessing it should be closer to 300 if working correctly.
Tables I have are
[OldPartNumber]
-----
OldPartID
ModelNo

[ModelNumber]
-----
ModelID
ModelName
ModelReplaced
ProductType
Dimensions

[NewPartNumber]
----
NewPartID
OldPartID

[SKUs]
----
SKUID
BrandID

[BrandName]
-----
BrandID
BrandName
IsRelavent

[ProductTypes]
-----
ProductTypeID
ProductTypeName

[XRef]
SKUID
OldPartID

[NewXRef]
NewPartID
OldPartID
.....

What I want to do is join the data so that I can bring in Model Name, Model
Replaced, Model Dimensions, Product Type Name, Brand Name, SKU#, and New
Number.

Code: 
SELECT ProductLines.ProdcutLineName, BrandName.BrandName, XRef.SKUNo,
XRef.OldPartID, NewPartNumber.NewPartID, ModelNumbers.ModelName,
ModelNumbers.ModelReplaced, ModelNumbers.Dimensions
FROM ((BrandName INNER JOIN SKUs ON BrandName.BrandNameID = SKUs.BrandID)
INNER JOIN (ProductLines INNER JOIN (ModelNumbers INNER JOIN (OldPartNumber
INNER JOIN XRef ON OldPartNumber.OldPartID = XRef.OldPartID) ON
ModelNumbers.ModelID = OldPartNumber.OldPartName) ON
ProductLines.ProductLineID = ModelNumbers.ProductLineID) ON SKUs.SKUNo =
XRef.SKUNo) INNER JOIN NBXRef ON OldPartNumber.OldPartID = NewXRef.OldPartID
ORDER BY ProductLines.ProdcutLineName, XRef.SKUNo;

This returns 110 records, I modded it slightly by using 2 different queries
for the XRef links, but that returned only 139 lines. I don't have the
modification any more because when it failed I removed the query.

Thanks for your help.
 
R

Rob

This was a tad confusing, since your table names and field names seemed
to changed mid-post. It seems to me that you just want a series of
left joins, in which something that looks like:

SELECT *
FROM ((((BrandName LEFT JOIN SKUs ON BrandName.BrandID = SKUs.BrandID)
LEFT JOIN XRef ON SKUs.SKUID = XRef.SKUID) LEFT JOIN OldPartNumber ON
XRef.OldPartID = OldPartNumber.oldpartid) LEFT JOIN ModelNumber ON
OldPartNumber.modelno = ModelNumber.modelID) LEFT JOIN ProductLines ON
ModelNumber.ProductType = ProductLines.ProductTypeID;
(etc.)

should be in the neighborhood. You wanted everything based on the 'old
number'; I'm not sure what the old number is here. I based everything
on the BrandName, but the query you ultimately build should be
strutured essentially along these lines.

Rob



I need help with a small database (less than 500 lines / table) that I'm
working on. I have a parts list that I'm breaking down by manufacturer,
seller, model number, new / old number, etc. I need to have a query pull in
all the information based on old number, some of the old numbers will not
have new numbers, will not have manufacturer, will not have a brand name,
etc, but I still want all of the old numbers to come in, and a <NULL> value
assigned to the fields that are not relavent. I was trying to accomplish this
with the use of left and right outer joins, but for some reason when I put it
all together, I get an error about the order of the join and for some reason
can't seem to figure out what's wrong. I have tried using other queries to
pull in some of the data and piece it together and it still only returns 139
records, where I am guessing it should be closer to 300 if working correctly.
Tables I have are
[OldPartNumber]
-----
OldPartID
ModelNo

[ModelNumber]
-----
ModelID
ModelName
ModelReplaced
ProductType
Dimensions

[NewPartNumber]
----
NewPartID
OldPartID

[SKUs]
----
SKUID
BrandID

[BrandName]
-----
BrandID
BrandName
IsRelavent

[ProductTypes]
-----
ProductTypeID
ProductTypeName

[XRef]
SKUID
OldPartID

[NewXRef]
NewPartID
OldPartID
....

What I want to do is join the data so that I can bring in Model Name, Model
Replaced, Model Dimensions, Product Type Name, Brand Name, SKU#, and New
Number.

Code: 
SELECT ProductLines.ProdcutLineName, BrandName.BrandName, XRef.SKUNo,
XRef.OldPartID, NewPartNumber.NewPartID, ModelNumbers.ModelName,
ModelNumbers.ModelReplaced, ModelNumbers.Dimensions
FROM ((BrandName INNER JOIN SKUs ON BrandName.BrandNameID = SKUs.BrandID)
INNER JOIN (ProductLines INNER JOIN (ModelNumbers INNER JOIN (OldPartNumber
INNER JOIN XRef ON OldPartNumber.OldPartID = XRef.OldPartID) ON
ModelNumbers.ModelID = OldPartNumber.OldPartName) ON
ProductLines.ProductLineID = ModelNumbers.ProductLineID) ON SKUs.SKUNo =
XRef.SKUNo) INNER JOIN NBXRef ON OldPartNumber.OldPartID = NewXRef.OldPartID
ORDER BY ProductLines.ProdcutLineName, XRef.SKUNo;

This returns 110 records, I modded it slightly by using 2 different queries
for the XRef links, but that returned only 139 lines. I don't have the
modification any more because when it failed I removed the query.

Thanks for your help.
Click to expand...
 
G

Guest

Thank you for your response. I attempted to replace all inner joins with
left joins, and I get the same error about having ambigious joins. I would
have thought it would have worked in Access. What I had to do to make it
work was create a lot of little queries with only 1 left join, and chain them
all together. It gives me what I wanted, but now I have a bunch of queries
that basically do nothing more than just produce one query. What I
ultimately wanted was all records from the old part number table to link
together the information from the other tables, if the information existed,
and if it didn't have information in the other table to return a null value.
So if part 111111 had a record in the SKU table, but not a new number, I
wanted the information to come over as 111111 - SKU Number - NULL, along with
the other information as well. It does that now, but I wanted to do it with
just one query.

Rob said:
This was a tad confusing, since your table names and field names seemed
to changed mid-post. It seems to me that you just want a series of
left joins, in which something that looks like:

SELECT *
FROM ((((BrandName LEFT JOIN SKUs ON BrandName.BrandID = SKUs.BrandID)
LEFT JOIN XRef ON SKUs.SKUID = XRef.SKUID) LEFT JOIN OldPartNumber ON
XRef.OldPartID = OldPartNumber.oldpartid) LEFT JOIN ModelNumber ON
OldPartNumber.modelno = ModelNumber.modelID) LEFT JOIN ProductLines ON
ModelNumber.ProductType = ProductLines.ProductTypeID;
(etc.)

should be in the neighborhood. You wanted everything based on the 'old
number'; I'm not sure what the old number is here. I based everything
on the BrandName, but the query you ultimately build should be
strutured essentially along these lines.

Rob



I need help with a small database (less than 500 lines / table) that I'm
working on. I have a parts list that I'm breaking down by manufacturer,
seller, model number, new / old number, etc. I need to have a query pull in
all the information based on old number, some of the old numbers will not
have new numbers, will not have manufacturer, will not have a brand name,
etc, but I still want all of the old numbers to come in, and a <NULL> value
assigned to the fields that are not relavent. I was trying to accomplish this
with the use of left and right outer joins, but for some reason when I put it
all together, I get an error about the order of the join and for some reason
can't seem to figure out what's wrong. I have tried using other queries to
pull in some of the data and piece it together and it still only returns 139
records, where I am guessing it should be closer to 300 if working correctly.
Tables I have are
[OldPartNumber]
-----
OldPartID
ModelNo

[ModelNumber]
-----
ModelID
ModelName
ModelReplaced
ProductType
Dimensions

[NewPartNumber]
----
NewPartID
OldPartID

[SKUs]
----
SKUID
BrandID

[BrandName]
-----
BrandID
BrandName
IsRelavent

[ProductTypes]
-----
ProductTypeID
ProductTypeName

[XRef]
SKUID
OldPartID

[NewXRef]
NewPartID
OldPartID
....

What I want to do is join the data so that I can bring in Model Name, Model
Replaced, Model Dimensions, Product Type Name, Brand Name, SKU#, and New
Number.

Code: 
SELECT ProductLines.ProdcutLineName, BrandName.BrandName, XRef.SKUNo,
XRef.OldPartID, NewPartNumber.NewPartID, ModelNumbers.ModelName,
ModelNumbers.ModelReplaced, ModelNumbers.Dimensions
FROM ((BrandName INNER JOIN SKUs ON BrandName.BrandNameID = SKUs.BrandID)
INNER JOIN (ProductLines INNER JOIN (ModelNumbers INNER JOIN (OldPartNumber
INNER JOIN XRef ON OldPartNumber.OldPartID = XRef.OldPartID) ON
ModelNumbers.ModelID = OldPartNumber.OldPartName) ON
ProductLines.ProductLineID = ModelNumbers.ProductLineID) ON SKUs.SKUNo =
XRef.SKUNo) INNER JOIN NBXRef ON OldPartNumber.OldPartID = NewXRef.OldPartID
ORDER BY ProductLines.ProdcutLineName, XRef.SKUNo;

This returns 110 records, I modded it slightly by using 2 different queries
for the XRef links, but that returned only 139 lines. I don't have the
modification any more because when it failed I removed the query.

Thanks for your help.
Click to expand...
Click to expand...
 
Top