P
pmu
I have a table for Customers and then a table for Prices. The Price table has
an autonumber field for "Price ID", but some old records have already been
deleted. (The Prices table is overly large and should be streamlined, but
that's another project.) There is also a "Report Date" field, which is the
date of the current updating. and a "Customer Update" field, where there may
or may not be a date provided by the customer.
Price information for various customers is updated in different months.
Combining the two tables in a query gives me a complete Price History.
The object is to create a monthly report showing the most recent prices as
"New" and the next most recent price as "Old", regardless of date. Also, if a
customer is new in a current month, there will be no "Old" price - so also
need to be able to pull the current price record ("New").
I want to create a query that retrieves a customer's last 2 price records
for a current report. Though each price record has "Report Date" field, one
customer's "old" price may be from July with a "new" price from December,
while another customer might show an "Old" price from August as well as a
newer "New" price from August...so sorting on "Report Date" will not
necessarily give me the last two price records for a particular customer.
In addition, my client insists she does not need any price information older
than the last two entries, but I'm reluctant to simply delete the oldest
prices in case they might be useful for future reports. So a query that give
mes the latest two price records entered should do the trick, right? (I
developed a workaround, but it's overly complex and time-consuming.)
My Access skills are self-taught - meaning, I struggle with Access
"language" - so simple terminology is appreciated. Thanks for any help out
there.
an autonumber field for "Price ID", but some old records have already been
deleted. (The Prices table is overly large and should be streamlined, but
that's another project.) There is also a "Report Date" field, which is the
date of the current updating. and a "Customer Update" field, where there may
or may not be a date provided by the customer.
Price information for various customers is updated in different months.
Combining the two tables in a query gives me a complete Price History.
The object is to create a monthly report showing the most recent prices as
"New" and the next most recent price as "Old", regardless of date. Also, if a
customer is new in a current month, there will be no "Old" price - so also
need to be able to pull the current price record ("New").
I want to create a query that retrieves a customer's last 2 price records
for a current report. Though each price record has "Report Date" field, one
customer's "old" price may be from July with a "new" price from December,
while another customer might show an "Old" price from August as well as a
newer "New" price from August...so sorting on "Report Date" will not
necessarily give me the last two price records for a particular customer.
In addition, my client insists she does not need any price information older
than the last two entries, but I'm reluctant to simply delete the oldest
prices in case they might be useful for future reports. So a query that give
mes the latest two price records entered should do the trick, right? (I
developed a workaround, but it's overly complex and time-consuming.)
My Access skills are self-taught - meaning, I struggle with Access
"language" - so simple terminology is appreciated. Thanks for any help out
there.