Querry can't figure out

J

JUAN

Trying to figure out to do the following: I have a table
with following fileds:
PART SUPPLIER PART2 SUPPLIER2 PART3 SUPPLIER3
XYZ VSH XYZZ KIZZZ ZZZZ KMT
ZYSSA KMT ZXYUA KIMM KDDIE VSH
KADFJ VSH ADF VSH BOBBB ARCO

I want to do a query that will give me part, part2 and
part 3 for only VSH Supplier.
So something like this
PART SUPPLIER PART2 SUPPLIER2 PART3 SUPPLIER3
XYZ VSH ADF VSH KDDIE VSH
KADFJ VSH

If I put in criteria under SUPPLIER, SUPPLIER2, and
SUPPLIER 3 VSH, I will get no data. So not sure if I can
get such data with the way the table is set up.
Please advise any info, would appreciate it alot.
thanks,
Juan
 
G

Guest

HI Juan:
In order for this to work, you might want to re-think the
way this data is sotred.
You need 2 tables:
'Parts'
IdPart PartName SuplID
2 xyz 1
3 zyssa 5
4 kadfy 1
5 xyzz 3
6 zxyua 4
7 adf 1
8 zzzz 2
9 kddie 1
10 bobbb 6

'IdPart' is primary KEY.
'SuplId is Foreign KEY. (Linked to SplID in 'suplier'
table.

This table is your central hub. Tells the relationship
between the parts and the suplier as you've put them...

The other table:
'Suplier'
SplID SuplName
1 vsh
2 kmt
3 kizzz
4 kimm
5 kmt
6 arco

'SplID' is primary KEY.

This table asigns a number to a suplier.

And the query would look something like this:

SELECT *
FROM Suplier INNER JOIN Parts ON Suplier.SplID =
Parts.SuplID
WHERE Suplier.SuplName="VSH"

By executing this query, the desired results will happen...
I hope that you're able to change your table setup. If
not, you might have a big problem on you're hands.

HTHs,
PAtrick
 
J

JUAN

Patrick,
thanks. I figured that I would have to do another table.
This is how an excel file is sent to us. So we need to
extract the parts associated to our company.
Thanks for all your help.

JUAN
 
Top