Criteria Question

[splint1906]

I have a Parts Master table with PartNo, Vendor01, Vendor02 and Vendor03 as its fields.

I am trying to write a query that will list all PartNo that have the same vendor number in either the Vendor01, Vendor02 or Vendor03 fields.

For example, the table has the following data:
PartNo Vendor01 Vendor02 Vendor03
AAA 123 77 195
BBB 4323 123 098
CCC 32 8855 123
DDD 66 24 5200

When Vendor number is 123, I'd like the query results to be PartNo AAA, BBB & CCC.

 

[SkipVought]

splint1906,

You have a problem with the structure of your table. You have non-normalized data. Check out

http://www.abcdataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

Your table ought to look something like this...
[tt]
PartsMaster:
PartNo
VendorNo
[/tt]
The you query would be
[tt]
select PartNo From PartsMaster where VendorNo='123'
[/tt]
I assume that VendorNo is TEXT.

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[SkipVought]

or...
[tt]
select PartNo from PartsMaster where Vendor01='123'
union
select PartNo from PartsMaster where Vendor02='123'
union
select PartNo from PartsMaster where Vendor03='123'
[/tt]


Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[splint1906]

Sorry guys/gals. I found my answer after scrolling through some of the threads in this forum.

My SQL looks like this:
SELECT PPSC.ItemNo, PPSC.FullDescription, *
FROM PPSC
WHERE (((PPSC.Vendor01)=[forms]![frmNetSavings]![lstSupplierName])) OR (((PPSC.Vendor02)=[forms]![frmNetSavings]![lstSupplierName])) OR (((PPSC.Vendor03)=[forms]![frmNetSavings]![lstSupplierName]))
ORDER BY PPSC.ItemNo;

Thanks

 

[lespaul]

Again, your queries will be much easier in the long run with a normalized structure. This query would be:

SELECT PPSC.ItemNo, PPSC.FullDescription, *
FROM PPSC
WHERE (PPSC.Vendor01)=[forms]![frmNetSavings]![lstSupplierName])
ORDER BY PPSC.ItemNo;

I also have an unnormalized database (not my choice!). Here's a small query I built to get the defendant's name, the officer's name, the attorney's names, the case number, hearing time, type/description and location (courtroom), charges and status, and the judge on the case:

SELECT DISTINCT DEFNAM, Trim(MFNAME) || ' ' ||  Trim(MLNAME) AS OFFICER, ATYNAM,  CMPHERMF.CASPRE, CMPHERMF.CASNUM, CMPHERMF.DEFSEQ, CMPHERMF.HERTIM, CMPHERMF.JUDCOD, CMPHERMF.CRTROM, JUDNAM, HERDSL, CMPHERMF.HERTYP, STSDSC, CHGABV 
FROM CMPHERMF 
INNER JOIN CMPJUDNM ON CMPHERMF.JUDCOD = CMPJUDNM.JUDCOD
LEFT JOIN CMPSTSCD ON CMPHERMF.STSCOD = CMPSTSCD.RECSTS 
LEFT JOIN CMPHERTP ON CMPHERMF.HERTYP = CMPHERTP.HERTYP 
LEFT JOIN CMPCASEATY ON CMPCASEATY.CASPRE = CMPHERMF.CASPRE AND CMPCASEATY.CASNUM = CMPHERMF.CASNUM AND CMPCASEATY.HERTYP = 'EA' AND CMPCASEATY.ATYSTS = 'A'
INNER JOIN CMPATYMF ON CMPCASEATY.DATCOD = CMPATYMF.ATYCOD INNER JOIN CMPDEFMF ON CMPHERMF.CASPRE = CMPDEFMF.CASPRE AND CMPHERMF.CASNUM = CMPDEFMF.CASNUM AND CMPHERMF.DEFSEQ = CMPDEFMF.DEFSEQ 
INNER JOIN CMPCHGMF ON CMPHERMF.CASPRE = CMPCHGMF.CASPRE AND CMPHERMF.CASNUM = CMPCHGMF.CASNUM AND CMPCHGMF.DEFSEQ = CMPDEFMF.DEFSEQ 
LEFT JOIN CMPOFFCR ON CMPDEFMF.ARROFN = CMPOFFCR.OFFNUM AND CMPDEFMF.ARRAGC = CMPOFFCR.MAGENC 

WHERE CMPHERMF.HERNGDAT = ' + HearingDate +' and CMPHERMF.JUDCOD = 1 and FILNAM = 'CMPHERMF' AND CMPCHGMF.CHGSEQ = 1 AND CALJUD = 'Y' AND CMPHERMF.CRTROM <> '320' AND CMPHERMF.HERTYP NOT IN ('CA', 'FE') AND CMPHERMF.HERTIM BETWEEN ' + FormatDateTime('HMM', IncMinute(Now, -PriorDispTime)) + ' AND ' +  FormatDateTime('HMM', IncMinute(Now, PostDispTime)) ORDER BY CMPHERMF.HERTIM, HERDSL, DEFNAM

and that was an easy one!!!

If you can redesign your table structure, you are better off doing it now and not waiting years and years for your queries to start looking like this!!!

Leslie

 

[splint1906]

Leslie,

I don't understand what you mean by 'Normalized data structure', but, I am going to read the document you linked.

And, by the way, I don't have the ability to change the table structure b/c it is a 'data dump' from our archaic mainframe.

Thanks

 

[lespaul]

If you are dumping it into a separate system, you absolutely can change the structure!! You can create a normalized structure and then create an import routine that takes the unnormalized main frame information and transforms it correctly!

There are plenty of people around here who will help you accomplish it!

Leslie

 

[SkipVought]

AMEN!

This is standard operating procedure in the IT industry. If you get data that has problems, you CORRECT the problems before you go about using storing and using the data rather than perpetrate and exacerbate the problems.

Skip,
[sub]
[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive! [/sub]

 

[splint1906]

Sorry. I have been away for a long weekend.

I don't think you guys totally understand my table structure. In the current system, one part number can have up to 3 vendors assigned to it. So, I do think the database is normalized. I can't (nor do I want) to go back and set up the table such that there are only 2 fields, PartNo & Vendor01. In that scenario there would be multiple entries of the same PartNo with different Vendor01.

 

[SQLScholar]

But if you did such - it would make the queries easier to work with. Is there any particular thing that you dont think the normalised DB could do?

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[lespaul]

In that scenario there would be multiple entries of the same PartNo with different Vendor01.

That's the definition of a many to many relationship. Structurally this is represented as the following three tables:

tblVendors
VendorID
other Vendor Information

tblParts
PartNo
PartDescription

tblVendorParts
PartNo
VendorID
Cost


Did you even read the 'Fundamentals of Relational Database Design' that was provided above? This is one of the basics of relational databases.



Leslie

 

[splint1906]

These particular records have PartNo, Vendor01, Vendor01PurchaseUnit, Vendor01InvoiceDate, Vendor01InvoicePrice, Vendor01ListPrice, etc. And those same fields are repeated for Vendor02 and for Vendor03.
I don't see/understand how normalizing this will prove to be any better than they way it currently is set up.

But, I'm no expert. I also did not write the data retrieve code that dumps the data into the table. I only specified the fields I needed. I can go back to my IT support and discuss normalization with them... I guess.