I'm trying to write a query in MS Access 365 on a SQL table (dbo_PARTS_SupplierCosts). This table is in a commercial program that we use and I can't modify the table. I realize it is a terrible design, not normalized etc. but I can't modify it.
The user wants to enter a vendors name and get a list of part numbers that we have purchased from them. Below is the code I used to capture the vendor, which could be in the vendor list from 01-10.
The second part of this process is the user also wants to see the last 3 vendors that we have purchased a part from. How can I go through this same table and select the last 3 vendors we used by the LastOrderDate again 01-10 vendors: Supplier01LastOrderedDate-Supplier10LastOrderedDate. I don't know SQL very well but I have a co-worker that is helping me.
SELECT dbo_PARTS_SupplierCosts.PartNumber, dbo_PARTS_SupplierCosts.Supplier01Code, dbo_PARTS_SupplierCosts.Supplier01LastOrderedDate, dbo_PARTS_SupplierCosts.Supplier01LastPurchaseOrderNumber, dbo_PARTS_SupplierCosts.Supplier1PartNumber, dbo_PARTS_SupplierCosts.Supplier02Code, dbo_PARTS_SupplierCosts.Supplier02LastOrderedDate, dbo_PARTS_SupplierCosts.Supplier02LastPurchaseOrderNumber, dbo_PARTS_SupplierCosts.Supplier2PartNumber, dbo_PARTS_SupplierCosts.Supplier03Code, dbo_PARTS_SupplierCosts.Supplier03LastOrderedDate, dbo_PARTS_SupplierCosts.Supplier03LastPurchaseOrderNumber, dbo_PARTS_SupplierCosts.Supplier3PartNumber, dbo_PARTS_SupplierCosts.Supplier04Code, dbo_PARTS_SupplierCosts.Supplier04LastOrderedDate, dbo_PARTS_SupplierCosts.Supplier04LastPurchaseOrderNumber, dbo_PARTS_SupplierCosts.Supplier4PartNumber, dbo_PARTS_SupplierCosts.Supplier05Code, dbo_PARTS_SupplierCosts.Supplier05LastOrderedDate, dbo_PARTS_SupplierCosts.Supplier5PartNumber, dbo_PARTS_SupplierCosts.Supplier05LastPurchaseOrderNumber, dbo_PARTS_SupplierCosts.Supplier06Code, dbo_PARTS_SupplierCosts.Supplier06LastOrderedDate, dbo_PARTS_SupplierCosts.Supplier06PartNumber, dbo_PARTS_SupplierCosts.Supplier06LastPurchaseOrderNumber, dbo_PARTS_SupplierCosts.Supplier07Code, dbo_PARTS_SupplierCosts.Supplier07LastOrderedDate, dbo_PARTS_SupplierCosts.Supplier07PartNumber, dbo_PARTS_SupplierCosts.Supplier07LastPurchaseOrderNumber, dbo_PARTS_SupplierCosts.Supplier08Code, dbo_PARTS_SupplierCosts.Supplier08LastOrderedDate, dbo_PARTS_SupplierCosts.Supplier08PartNumber, dbo_PARTS_SupplierCosts.Supplier08LastPurchaseOrderNumber, dbo_PARTS_SupplierCosts.Supplier09Code, dbo_PARTS_SupplierCosts.Supplier09LastOrderedDate, dbo_PARTS_SupplierCosts.Supplier09PartNumber, dbo_PARTS_SupplierCosts.Supplier09LastPurchaseOrderNumber, dbo_PARTS_SupplierCosts.Supplier10Code, dbo_PARTS_SupplierCosts.Supplier10LastOrderedDate, dbo_PARTS_SupplierCosts.Supplier10PartNumber, dbo_PARTS_SupplierCosts.Supplier10LastPurchaseOrderNumber INTO tblInventorySuppliers
FROM dbo_PARTS_SupplierCosts
WHERE (((dbo_PARTS_SupplierCosts.Supplier01Code)=[Forms]![frmInventoryReport]![txtVendorLookup]) AND ((dbo_PARTS_SupplierCosts.CompanyCode)="01")) OR (((dbo_PARTS_SupplierCosts.Supplier02Code)=[Forms]![frmInventoryReport]![txtVendorLookup]) AND ((dbo_PARTS_SupplierCosts.CompanyCode)="01")) OR (((dbo_PARTS_SupplierCosts.Supplier03Code)=[Forms]![frmInventoryReport]![txtVendorLookup]) AND ((dbo_PARTS_SupplierCosts.CompanyCode)="01")) OR (((dbo_PARTS_SupplierCosts.Supplier04Code)=[Forms]![frmInventoryReport]![txtVendorLookup]) AND ((dbo_PARTS_SupplierCosts.CompanyCode)="01")) OR (((dbo_PARTS_SupplierCosts.Supplier05Code)=[Forms]![frmInventoryReport]![txtVendorLookup]) AND ((dbo_PARTS_SupplierCosts.CompanyCode)="01")) OR (((dbo_PARTS_SupplierCosts.Supplier06Code)=[Forms]![frmInventoryReport]![txtVendorLookup]) AND ((dbo_PARTS_SupplierCosts.CompanyCode)="01")) OR (((dbo_PARTS_SupplierCosts.Supplier07Code)=[Forms]![frmInventoryReport]![txtVendorLookup]) AND ((dbo_PARTS_SupplierCosts.CompanyCode)="01")) OR (((dbo_PARTS_SupplierCosts.Supplier08Code)=[Forms]![frmInventoryReport]![txtVendorLookup]) AND ((dbo_PARTS_SupplierCosts.CompanyCode)="01")) OR (((dbo_PARTS_SupplierCosts.Supplier09Code)=[Forms]![frmInventoryReport]![txtVendorLookup]) AND ((dbo_PARTS_SupplierCosts.CompanyCode)="01")) OR (((dbo_PARTS_SupplierCosts.Supplier10Code)=[Forms]![frmInventoryReport]![txtVendorLookup]) AND ((dbo_PARTS_SupplierCosts.CompanyCode)="01"));
Posted by: jfakes@rocketmail.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (1) |
Tidak ada komentar:
Posting Komentar