Rabu, 01 Mei 2019

Re: [MS_AccessPros] Vendor select by last ordered date

 

I would create a pass-through query to the SQL server with a UNION query SQL to normalize the data and make it much easier to question:

SELECT CompanyCode, PartNumber, 1 as Supplier,
 Supplier01Code SupplierCode, Supplier01LastOrderedDate SupplierLastOrderDate, Supplier01LastPurchaseOrderNumber SupplierLastPurchaseOrderNumber, Supplier1PartNumber SupplierPartNumber
FROM PARTS_SupplierCosts
UNION ALL
SELECT CompanyCode, PartNumber, 2, Supplier02Code, Supplier02LastOrderedDate, Supplier02LastPurchaseOrderNumber, Supplier2PartNumber
FROM PARTS_SupplierCosts
UNION ALL
SELECT CompanyCode, PartNumber, 3, Supplier03Code, Supplier03LastOrderedDate, Supplier03LastPurchaseOrderNumber, Supplier3PartNumber
FROM PARTS_SupplierCosts
UNION ALL
SELECT CompanyCode, PartNumber, 4, Supplier04Code, Supplier04LastOrderedDate, Supplier04LastPurchaseOrderNumber, Supplier4PartNumber
FROM PARTS_SupplierCosts
UNION ALL
SELECT CompanyCode, PartNumber, 5, Supplier05Code, Supplier05LastOrderedDate, Supplier05LastPurchaseOrderNumber, Supplier5PartNumber
FROM PART_SupplierCosts
... through 10 ...
FROM PART_SupplierCosts

Then you can create a nice query of

SELECT *
FROM [My Passthrough Name]
WHERE SupplierCode = [Forms]![frmInventoryReport]![txtVendorLookup] and CompanyCode = "01"

You could use a subquery to find the most recent 3 based on the union query's SupplierLastOrderDate.

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of jfakes@rocketmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, May 1, 2019 12:16 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Vendor select by last ordered date
 


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: Duane Hookom <duanehookom@hotmail.com>
Reply via web post • Reply to sender • Reply to group • Start a New Topic • Messages in this topic (2)
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

SPONSORED LINKS
.

__,_._,___
Diposting oleh di

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)