If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Reference previous record
I have a query that lists our parts and their cost. Some parts have 1
record and some parts have 2 records because their cost have changed. How can I have the query compare the previous cost for that part if one exists? Thanks, Jasper |
#2
|
|||
|
|||
Reference previous record
On Tue, 20 Apr 2010 15:37:06 -0400, "Jasper Recto" wrote:
I have a query that lists our parts and their cost. Some parts have 1 record and some parts have 2 records because their cost have changed. How can I have the query compare the previous cost for that part if one exists? Thanks, Jasper You may want to reconsider your table design! Point one: tables *have no order*. There's no such thing as "the previous record", any more than there is a "previous marble" in a bag of marbles. Point two: if you have two costs, might you have three, or four? If each Part has (historically) multiple prices, a proper design would be a Parts table (with no cost information) related one to many to a PartCosts table. Is that how your table is structured? If it isn't, please explain your tables and their relationships. Perhaps it would help to post the SQL view of your current query. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Reference previous record
I'm using access to query our main database. I'm linking 2 tables together,
the Vendor table and the Vendor Part table. The Vendor part table contains the part number and any multiple of cost records so you are correct that there can be more than 2 records for each part number. However, I'm only interested in the latest effective date and the previous effective date. I can not do anything with the tables. I can only query them. My goal is to be able to have a query that gives me each part number and the price DIFFERENCE between the most current effective date and the effective date before it. below is my sql statement: SELECT PUB_Vendor.Company, PUB_Vendor.VendorID, PUB_VendPart.PartNum, PUB_VendPart.EffectiveDate, PUB_VendPart.BaseUnitPrice, PUB_VendPart.VenPartNum FROM PUB_Vendor INNER JOIN PUB_VendPart ON (PUB_Vendor.VendorNum = PUB_VendPart.VendorNum) AND (PUB_Vendor.Company = PUB_VendPart.Company) WHERE (((PUB_Vendor.Company)="Loc") AND ((PUB_Vendor.VendorID)="M702")); Any ideas? Thanks! Jasper "John W. Vinson" wrote in message ... On Tue, 20 Apr 2010 15:37:06 -0400, "Jasper Recto" wrote: I have a query that lists our parts and their cost. Some parts have 1 record and some parts have 2 records because their cost have changed. How can I have the query compare the previous cost for that part if one exists? Thanks, Jasper You may want to reconsider your table design! Point one: tables *have no order*. There's no such thing as "the previous record", any more than there is a "previous marble" in a bag of marbles. Point two: if you have two costs, might you have three, or four? If each Part has (historically) multiple prices, a proper design would be a Parts table (with no cost information) related one to many to a PartCosts table. Is that how your table is structured? If it isn't, please explain your tables and their relationships. Perhaps it would help to post the SQL view of your current query. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Reference previous record
Actually, this is my sql statement:
SELECT PUB_Vendor.Company, PUB_Vendor.VendorID, PUB_VendPart.PartNum, PUB_VendPart.EffectiveDate, PUB_VendPart.BaseUnitPrice, PUB_VendPart.VenPartNum FROM PUB_Vendor INNER JOIN PUB_VendPart ON (PUB_Vendor.VendorNum = PUB_VendPart.VendorNum) AND (PUB_Vendor.Company = PUB_VendPart.Company) WHERE (((PUB_Vendor.Company)="Loc") AND ((PUB_Vendor.VendorID)="M702")) ORDER BY PUB_VendPart.PartNum, PUB_VendPart.EffectiveDate; Thanks! Jasper "Jasper Recto" wrote in message ... I'm using access to query our main database. I'm linking 2 tables together, the Vendor table and the Vendor Part table. The Vendor part table contains the part number and any multiple of cost records so you are correct that there can be more than 2 records for each part number. However, I'm only interested in the latest effective date and the previous effective date. I can not do anything with the tables. I can only query them. My goal is to be able to have a query that gives me each part number and the price DIFFERENCE between the most current effective date and the effective date before it. below is my sql statement: SELECT PUB_Vendor.Company, PUB_Vendor.VendorID, PUB_VendPart.PartNum, PUB_VendPart.EffectiveDate, PUB_VendPart.BaseUnitPrice, PUB_VendPart.VenPartNum FROM PUB_Vendor INNER JOIN PUB_VendPart ON (PUB_Vendor.VendorNum = PUB_VendPart.VendorNum) AND (PUB_Vendor.Company = PUB_VendPart.Company) WHERE (((PUB_Vendor.Company)="Loc") AND ((PUB_Vendor.VendorID)="M702")); Any ideas? Thanks! Jasper "John W. Vinson" wrote in message ... On Tue, 20 Apr 2010 15:37:06 -0400, "Jasper Recto" wrote: I have a query that lists our parts and their cost. Some parts have 1 record and some parts have 2 records because their cost have changed. How can I have the query compare the previous cost for that part if one exists? Thanks, Jasper You may want to reconsider your table design! Point one: tables *have no order*. There's no such thing as "the previous record", any more than there is a "previous marble" in a bag of marbles. Point two: if you have two costs, might you have three, or four? If each Part has (historically) multiple prices, a proper design would be a Parts table (with no cost information) related one to many to a PartCosts table. Is that how your table is structured? If it isn't, please explain your tables and their relationships. Perhaps it would help to post the SQL view of your current query. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Reference previous record
On Tue, 20 Apr 2010 16:08:43 -0400, "Jasper Recto" wrote:
I'm using access to query our main database. I'm linking 2 tables together, the Vendor table and the Vendor Part table. The Vendor part table contains the part number and any multiple of cost records so you are correct that there can be more than 2 records for each part number. However, I'm only interested in the latest effective date and the previous effective date. I can not do anything with the tables. I can only query them. My goal is to be able to have a query that gives me each part number and the price DIFFERENCE between the most current effective date and the effective date before it. You should be able to use a Subquery to search the table of costs. Something like: SELECT PUB_Vendor.Company, PUB_Vendor.VendorID, PUB_VendPart.PartNum, PUB_VendPart.EffectiveDate, PUB_VendPart.BaseUnitPrice, PUB_VendPart.VenPartNum, PUB_VendPart.BaseUnitPrice - (SELECT TOP 1 BaseUnitPrice FROM PUB_VendPart AS Y WHERE Y.PartNum = PUB_VendPart.PartNum AND Y.EffectiveDate PUB_VendPart.EffectiveDate) FROM PUB_Vendor INNER JOIN PUB_VendPart ON (PUB_Vendor.VendorNum = PUB_VendPart.VendorNum) AND (PUB_Vendor.Company = PUB_VendPart.Company) WHERE (((PUB_Vendor.Company)="Loc") AND ((PUB_Vendor.VendorID)="M702") AND EffectiveDate = (SELECT Max(EffectiveDate) FROM PUB_VendPart AS X WHERE X.PartNum = Pub_VendPart.PartNum) ORDER BY PUB_VendPart.PartNum; This is untested air code, and assumes that there always *is* a previous price. You may want to add an NZ() function to handle cases where there isn't. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|