Conditional Relationship for Query

A

Amanda Knott

I have two tables in Access that need to be connected using UPC codes.
This would be easy to create a relationship if one table didn't have
the buying UPC (Each UPC, Box UPC or Case UPC) and the other didn't
have Each UPC, Box UPC AND Case UPCs. I need to run a query that ties
those UPCs to pull the item number and cost however this means I need
to create a relationship that says the buying UPC in my first table
can match the Each UPC, Box UPC OR Case UPC from my second table.
Does anyone have any suggestions on how to create that relationship?
Any information on this would be extremely helpful!
TIA
Amanda
 
T

Tom Ellison

Dear Amanda:

I thought of 2 approaches. Both seem to work.

I created the table ProductUPC for the UPC of the products being sought, and
LookupUPC to have the 3 different UPCs that it might match.

This is a filtered cross-product:

SELECT *
FROM ProductUPC, LookupUPC
WHERE UPC = ProdUPC OR UPC = BoxUPC OR UPC = CaseUPC

This is an unusual JOIN:

SELECT *
FROM ProductUPC P
INNER JOIN LookupUPC L
ON L.ProdUPC = P.UPC OR L.BoxUPC = P.UPC OR L.CaseUPC = P.UPC

Both are "barely" tested but seem to work.

Tom Ellison
 
A

Amanda Knott

Tom - Thank you for your quick response, however I can't see to get it
to pull records... Here is what I have in my SQL statement

SELECT tblSinclairPB.Item, tblSetList.[UPC #],
tblSinclairPB.Description, tblSinclairPB.SellRatio,
tblSinclairPB.SellUnit, tblSinclairPB.BuyCost, tblSinclairPB.EachCost,
tblSinclairPB.Margin, tblSinclairPB.Retail, tblSetList.Set
FROM tblSetList, tblSinclairPB WHERE (tblSetList.[UPC
#]=tblSinclairPB.EachUPC) OR (tblSetList.[UPC #]=tblSinclairPB.BoxUPC)
OR (tblSetList.[UPC #]=tblSinclairPB.CaseUPC)

Can you help me figure out what is wrong?
 
T

Tom Ellison

Dear Amanda:

Your SQL lookx OK. Don't know right off the top what's wrong.

Please take it one step at a time. I recommend trying this first:

SELECT tblSinclairPB.Item, tblSetList.[UPC #],
tblSinclairPB.Description, tblSinclairPB.SellRatio,
tblSinclairPB.SellUnit, tblSinclairPB.BuyCost, tblSinclairPB.EachCost,
tblSinclairPB.Margin, tblSinclairPB.Retail, tblSetList.Set
FROM tblSetList, tblSinclairPB
WHERE (tblSetList.[UPC #]=tblSinclairPB.EachUPC)

This isn't the solution, but a test. Does it find any matches? Should it?

Does the INNER JOIN solution have the same problem?

Tom Ellison


Amanda Knott said:
Tom - Thank you for your quick response, however I can't see to get it
to pull records... Here is what I have in my SQL statement

SELECT tblSinclairPB.Item, tblSetList.[UPC #],
tblSinclairPB.Description, tblSinclairPB.SellRatio,
tblSinclairPB.SellUnit, tblSinclairPB.BuyCost, tblSinclairPB.EachCost,
tblSinclairPB.Margin, tblSinclairPB.Retail, tblSetList.Set
FROM tblSetList, tblSinclairPB WHERE (tblSetList.[UPC
#]=tblSinclairPB.EachUPC) OR (tblSetList.[UPC #]=tblSinclairPB.BoxUPC)
OR (tblSetList.[UPC #]=tblSinclairPB.CaseUPC)

Can you help me figure out what is wrong?


Dear Amanda:

I thought of 2 approaches. Both seem to work.

I created the table ProductUPC for the UPC of the products being sought,
and
LookupUPC to have the 3 different UPCs that it might match.

This is a filtered cross-product:

SELECT *
FROM ProductUPC, LookupUPC
WHERE UPC = ProdUPC OR UPC = BoxUPC OR UPC = CaseUPC

This is an unusual JOIN:

SELECT *
FROM ProductUPC P
INNER JOIN LookupUPC L
ON L.ProdUPC = P.UPC OR L.BoxUPC = P.UPC OR L.CaseUPC = P.UPC

Both are "barely" tested but seem to work.

Tom Ellison
Click to expand...
Click to expand...
 
A

Amanda Knott

No it doesn't pull anything up at all. Yes it should find matches.

Dear Amanda:

Your SQL lookx OK. Don't know right off the top what's wrong.

Please take it one step at a time. I recommend trying this first:

SELECT tblSinclairPB.Item, tblSetList.[UPC #],
tblSinclairPB.Description, tblSinclairPB.SellRatio,
tblSinclairPB.SellUnit, tblSinclairPB.BuyCost, tblSinclairPB.EachCost,
tblSinclairPB.Margin, tblSinclairPB.Retail, tblSetList.Set
FROM tblSetList, tblSinclairPB
WHERE (tblSetList.[UPC #]=tblSinclairPB.EachUPC)

This isn't the solution, but a test. Does it find any matches? Should it?

Does the INNER JOIN solution have the same problem?

Tom Ellison


Amanda Knott said:
Tom - Thank you for your quick response, however I can't see to get it
to pull records... Here is what I have in my SQL statement

SELECT tblSinclairPB.Item, tblSetList.[UPC #],
tblSinclairPB.Description, tblSinclairPB.SellRatio,
tblSinclairPB.SellUnit, tblSinclairPB.BuyCost, tblSinclairPB.EachCost,
tblSinclairPB.Margin, tblSinclairPB.Retail, tblSetList.Set
FROM tblSetList, tblSinclairPB WHERE (tblSetList.[UPC
#]=tblSinclairPB.EachUPC) OR (tblSetList.[UPC #]=tblSinclairPB.BoxUPC)
OR (tblSetList.[UPC #]=tblSinclairPB.CaseUPC)

Can you help me figure out what is wrong?


Dear Amanda:

I thought of 2 approaches. Both seem to work.

I created the table ProductUPC for the UPC of the products being sought,
and
LookupUPC to have the 3 different UPCs that it might match.

This is a filtered cross-product:

SELECT *
FROM ProductUPC, LookupUPC
WHERE UPC = ProdUPC OR UPC = BoxUPC OR UPC = CaseUPC

This is an unusual JOIN:

SELECT *
FROM ProductUPC P
INNER JOIN LookupUPC L
ON L.ProdUPC = P.UPC OR L.BoxUPC = P.UPC OR L.CaseUPC = P.UPC

Both are "barely" tested but seem to work.

Tom Ellison


I have two tables in Access that need to be connected using UPC codes.
This would be easy to create a relationship if one table didn't have
the buying UPC (Each UPC, Box UPC or Case UPC) and the other didn't
have Each UPC, Box UPC AND Case UPCs. I need to run a query that ties
those UPCs to pull the item number and cost however this means I need
to create a relationship that says the buying UPC in my first table
can match the Each UPC, Box UPC OR Case UPC from my second table.
Does anyone have any suggestions on how to create that relationship?
Any information on this would be extremely helpful!
TIA
Amanda
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Amanda:

The problem is probably that you THINK they match, but they DON'T.

The difference could be something like leading or trailing spaces. They
wouldn't be visible.

Only some investigation by you is going to reveal that.

Tom Ellison


Amanda Knott said:
No it doesn't pull anything up at all. Yes it should find matches.

Dear Amanda:

Your SQL lookx OK. Don't know right off the top what's wrong.

Please take it one step at a time. I recommend trying this first:

SELECT tblSinclairPB.Item, tblSetList.[UPC #],
tblSinclairPB.Description, tblSinclairPB.SellRatio,
tblSinclairPB.SellUnit, tblSinclairPB.BuyCost, tblSinclairPB.EachCost,
tblSinclairPB.Margin, tblSinclairPB.Retail, tblSetList.Set
FROM tblSetList, tblSinclairPB
WHERE (tblSetList.[UPC #]=tblSinclairPB.EachUPC)

This isn't the solution, but a test. Does it find any matches? Should
it?

Does the INNER JOIN solution have the same problem?

Tom Ellison


Amanda Knott said:
Tom - Thank you for your quick response, however I can't see to get it
to pull records... Here is what I have in my SQL statement

SELECT tblSinclairPB.Item, tblSetList.[UPC #],
tblSinclairPB.Description, tblSinclairPB.SellRatio,
tblSinclairPB.SellUnit, tblSinclairPB.BuyCost, tblSinclairPB.EachCost,
tblSinclairPB.Margin, tblSinclairPB.Retail, tblSetList.Set
FROM tblSetList, tblSinclairPB WHERE (tblSetList.[UPC
#]=tblSinclairPB.EachUPC) OR (tblSetList.[UPC #]=tblSinclairPB.BoxUPC)
OR (tblSetList.[UPC #]=tblSinclairPB.CaseUPC)

Can you help me figure out what is wrong?


On Fri, 7 Apr 2006 10:59:21 -0500, "Tom Ellison"

Dear Amanda:

I thought of 2 approaches. Both seem to work.

I created the table ProductUPC for the UPC of the products being sought,
and
LookupUPC to have the 3 different UPCs that it might match.

This is a filtered cross-product:

SELECT *
FROM ProductUPC, LookupUPC
WHERE UPC = ProdUPC OR UPC = BoxUPC OR UPC = CaseUPC

This is an unusual JOIN:

SELECT *
FROM ProductUPC P
INNER JOIN LookupUPC L
ON L.ProdUPC = P.UPC OR L.BoxUPC = P.UPC OR L.CaseUPC = P.UPC

Both are "barely" tested but seem to work.

Tom Ellison


I have two tables in Access that need to be connected using UPC codes.
This would be easy to create a relationship if one table didn't have
the buying UPC (Each UPC, Box UPC or Case UPC) and the other didn't
have Each UPC, Box UPC AND Case UPCs. I need to run a query that ties
those UPCs to pull the item number and cost however this means I need
to create a relationship that says the buying UPC in my first table
can match the Each UPC, Box UPC OR Case UPC from my second table.
Does anyone have any suggestions on how to create that relationship?
Any information on this would be extremely helpful!
TIA
Amanda
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union Query to show all items and duplicates by lowest price? 1
Select Records With Most Current Date 2
Top # query question 3
Combining tables in an update query 2
iif functin in report 5
Syntax error(missing operator) 0
event procedure 2
relation question 5

Top