Date Last Changed

[FA]

Please help.
I'm trying to produce a product price list from a table that records all the
price history for each item. The query should show only the last date it was
updated for each item in the list. This price would be the current price for
each item. How can I do this?

Any suggestions?

 

[KARL DEWEY]

See post "audit trail" or "history."

 

[FA]

I can't find anything that helps.
Do I need to explain further? Please help.

 

[KARL DEWEY]

Build the price history table.

PriceHist—
ProductID – data type to match your existing fields
OldPrice – data type to match your existing fields
NewPrice – data type to match your existing fields
Update – Datetime field – default =Now() puts time tag as you may change
more than once in a day

Build an append query to append to PriceHist. Put this field in the design
view grid field row.
X : [Forms]![YourFormForPriceChange]![ProductID]
Y: [Forms]![YourFormForPriceChange]![OldPriceField]
Z: [Forms]![YourFormForPriceChange]![NewPriceField]

Create a macro that opens the above query and requery.

In your form that you would use to change prices, have a price field and new
price field that is unbound. In the new price field properties have it call
the macro on change.

Build a Totals query using the PriceHist table and Max the Update field.

Your product query would join product table and the Totals query on ProductID.

 

[FA]

Thanks.
My list has:
"item" "date price" "category" "customer" "market" "product line"
I want the list to show only the last price of each item.
Is that possible?

Build the price history table.

PriceHist—
ProductID – data type to match your existing fields
OldPrice – data type to match your existing fields
NewPrice – data type to match your existing fields
Update – Datetime field – default =Now() puts time tag as you may change
more than once in a day

Build an append query to append to PriceHist. Put this field in the design
view grid field row.
X : [Forms]![YourFormForPriceChange]![ProductID]
Y: [Forms]![YourFormForPriceChange]![OldPriceField]
Z: [Forms]![YourFormForPriceChange]![NewPriceField]

Create a macro that opens the above query and requery.

In your form that you would use to change prices, have a price field and new
price field that is unbound. In the new price field properties have it call
the macro on change.

Build a Totals query using the PriceHist table and Max the Update field.

Your product query would join product table and the Totals query on ProductID.

I can't find anything that helps.
Do I need to explain further? Please help.

 

[FA]

Correction
My table has:
"item" "date" "price" "category" "customer" "market" "product line"
"comments"

I want a query that show only the last date of each item.

Build the price history table.

PriceHist—
ProductID – data type to match your existing fields
OldPrice – data type to match your existing fields
NewPrice – data type to match your existing fields
Update – Datetime field – default =Now() puts time tag as you may change
more than once in a day

Build an append query to append to PriceHist. Put this field in the design
view grid field row.
X : [Forms]![YourFormForPriceChange]![ProductID]
Y: [Forms]![YourFormForPriceChange]![OldPriceField]
Z: [Forms]![YourFormForPriceChange]![NewPriceField]

Create a macro that opens the above query and requery.

In your form that you would use to change prices, have a price field and new
price field that is unbound. In the new price field properties have it call
the macro on change.

Build a Totals query using the PriceHist table and Max the Update field.

Your product query would join product table and the Totals query on ProductID.

I can't find anything that helps.
Do I need to explain further? Please help.

 

[John Spencer (MVP)]

One method:

SELECT YourTable.*
FROM YourTable
WHERE [Date] = (SELECT MAX([Date]) FROM YourTable as Temp WHERE Temp.Item = YourTable.Item)

Correction
My table has:
"item" "date" "price" "category" "customer" "market" "product line"
"comments"

I want a query that show only the last date of each item.

Build the price history table.

PriceHist—
ProductID – data type to match your existing fields
OldPrice – data type to match your existing fields
NewPrice – data type to match your existing fields
Update – Datetime field – default =Now() puts time tag as you may change
more than once in a day

Build an append query to append to PriceHist. Put this field in the design
view grid field row.
X : [Forms]![YourFormForPriceChange]![ProductID]
Y: [Forms]![YourFormForPriceChange]![OldPriceField]
Z: [Forms]![YourFormForPriceChange]![NewPriceField]

Create a macro that opens the above query and requery.

In your form that you would use to change prices, have a price field and new
price field that is unbound. In the new price field properties have it call
the macro on change.

Build a Totals query using the PriceHist table and Max the Update field.

Your product query would join product table and the Totals query on ProductID.

I can't find anything that helps.
Do I need to explain further? Please help.

:

See post "audit trail" or "history."

:

Please help.
I'm trying to produce a product price list from a table that records all the
price history for each item. The query should show only the last date it was
updated for each item in the list. This price would be the current price for
each item. How can I do this?

Any suggestions?

 

[FA]

Thank you very much this worked just fine:

SELECT Price_Table.*, [WAC]*(12+0.0000000001) AS DOZ
FROM Price_Table
WHERE (((Price_Table.Cliente)="Company1") AND ((Price_Table.Fecha)=(SELECT
MAX([Fecha]) FROM Price_Table as Temp WHERE Temp.NDC = Price_Table.NDC)));

But I still I have a problem: If I update the price for one customer the
item stop appearing in other customers lists. Any suggestions?

One method:

SELECT YourTable.*
FROM YourTable
WHERE [Date] = (SELECT MAX([Date]) FROM YourTable as Temp WHERE Temp.Item = YourTable.Item)

Correction
My table has:
"item" "date" "price" "category" "customer" "market" "product line"
"comments"

I want a query that show only the last date of each item.

Build the price history table.

PriceHistâ€â€
ProductID – data type to match your existing fields
OldPrice – data type to match your existing fields
NewPrice – data type to match your existing fields
Update – Datetime field – default =Now() puts time tag as you may change
more than once in a day

Build an append query to append to PriceHist. Put this field in the design
view grid field row.
X : [Forms]![YourFormForPriceChange]![ProductID]
Y: [Forms]![YourFormForPriceChange]![OldPriceField]
Z: [Forms]![YourFormForPriceChange]![NewPriceField]

Create a macro that opens the above query and requery.

In your form that you would use to change prices, have a price field and new
price field that is unbound. In the new price field properties have it call
the macro on change.

Build a Totals query using the PriceHist table and Max the Update field.

Your product query would join product table and the Totals query on ProductID.


:

I can't find anything that helps.
Do I need to explain further? Please help.

:

See post "audit trail" or "history."

:

Please help.
I'm trying to produce a product price list from a table that records all the
price history for each item. The query should show only the last date it was
updated for each item in the list. This price would be the current price for
each item. How can I do this?

Any suggestions?

 

[John Spencer (MVP)]

So you have a price for each product for each customer. THen you will need to
change the sub-query to reflect that.
SELECT ...
AND PriceTable.Fecha = (SELECT MAX([Fecha]) FROM Price_Table as Temp
WHERE Temp.NDC = Price_Table.NDC AND
Temp.Customer=PriceTable.Customer)

Thank you very much this worked just fine:

SELECT Price_Table.*, [WAC]*(12+0.0000000001) AS DOZ
FROM Price_Table
WHERE (((Price_Table.Cliente)="Company1") AND ((Price_Table.Fecha)=(SELECT
MAX([Fecha]) FROM Price_Table as Temp WHERE Temp.NDC = Price_Table.NDC)));

But I still I have a problem: If I update the price for one customer the
item stop appearing in other customers lists. Any suggestions?

One method:

SELECT YourTable.*
FROM YourTable
WHERE [Date] = (SELECT MAX([Date]) FROM YourTable as Temp WHERE Temp.Item = YourTable.Item)

Correction
My table has:
"item" "date" "price" "category" "customer" "market" "product line"
"comments"

I want a query that show only the last date of each item.

:

Build the price history table.

PriceHistâ€â€*
ProductID – data type to match your existing fields
OldPrice – data type to match your existing fields
NewPrice – data type to match your existing fields
Update – Datetime field – default =Now() puts time tag as you may change
more than once in a day

Build an append query to append to PriceHist. Put this field in the design
view grid field row.
X : [Forms]![YourFormForPriceChange]![ProductID]
Y: [Forms]![YourFormForPriceChange]![OldPriceField]
Z: [Forms]![YourFormForPriceChange]![NewPriceField]

Create a macro that opens the above query and requery.

In your form that you would use to change prices, have a price field and new
price field that is unbound. In the new price field properties have it call
the macro on change.

Build a Totals query using the PriceHist table and Max the Update field.

Your product query would join product table and the Totals query on ProductID.


:

I can't find anything that helps.
Do I need to explain further? Please help.

:

See post "audit trail" or "history."

:

Please help.
I'm trying to produce a product price list from a table that records all the
price history for each item. The query should show only the last date it was
updated for each item in the list. This price would be the current price for
each item. How can I do this?

Any suggestions?

 

[FA]

Thank you very much!

So you have a price for each product for each customer. THen you will need to
change the sub-query to reflect that.
SELECT ...
AND PriceTable.Fecha = (SELECT MAX([Fecha]) FROM Price_Table as Temp
WHERE Temp.NDC = Price_Table.NDC AND
Temp.Customer=PriceTable.Customer)

Thank you very much this worked just fine:

SELECT Price_Table.*, [WAC]*(12+0.0000000001) AS DOZ
FROM Price_Table
WHERE (((Price_Table.Cliente)="Company1") AND ((Price_Table.Fecha)=(SELECT
MAX([Fecha]) FROM Price_Table as Temp WHERE Temp.NDC = Price_Table.NDC)));

But I still I have a problem: If I update the price for one customer the
item stop appearing in other customers lists. Any suggestions?

One method:

SELECT YourTable.*
FROM YourTable
WHERE [Date] = (SELECT MAX([Date]) FROM YourTable as Temp WHERE Temp.Item = YourTable.Item)

FA wrote:

Correction
My table has:
"item" "date" "price" "category" "customer" "market" "product line"
"comments"

I want a query that show only the last date of each item.

:

Build the price history table.

PriceHistâ€â€*
ProductID – data type to match your existing fields
OldPrice – data type to match your existing fields
NewPrice – data type to match your existing fields
Update – Datetime field – default =Now() puts time tag as you may change
more than once in a day

Build an append query to append to PriceHist. Put this field in the design
view grid field row.
X : [Forms]![YourFormForPriceChange]![ProductID]
Y: [Forms]![YourFormForPriceChange]![OldPriceField]
Z: [Forms]![YourFormForPriceChange]![NewPriceField]

Create a macro that opens the above query and requery.

In your form that you would use to change prices, have a price field and new
price field that is unbound. In the new price field properties have it call
the macro on change.

Build a Totals query using the PriceHist table and Max the Update field.

Your product query would join product table and the Totals query on ProductID.


:

I can't find anything that helps.
Do I need to explain further? Please help.

:

See post "audit trail" or "history."

:

Please help.
I'm trying to produce a product price list from a table that records all the
price history for each item. The query should show only the last date it was
updated for each item in the list. This price would be the current price for
each item. How can I do this?

Any suggestions?