Reference previous record

[Jasper Recto]

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

 

[John W. Vinson]

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.

 

[Jasper Recto]

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

 

[Jasper Recto]

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

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]

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.