Select last two records entered

[Guest]

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.

 

[Guest]

You have table "Customers" related to table "Prices". One customer has many
Price records. If referential integrity was not enforced, you could have
price records without a customer record (orphan records).

If you are using "Report Date" to determine New and Old prices, you *cannot*
leave it empty (null). If the customer doesn't provide a date, you should
enter a date or auto-enter a date by having the default value property set to
Date(). So if the new price date isn't provided, the date the record was
created would be used.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Comments Inline....



You should delete any orphan records in table Prices.

I'm still slogging through the Access learning process...what are orphan
records?

Is "Report Date" field date and time? Is the date(/time) entered manually?

This is a default date I enter in the table, i.e. 01/31/2006 for January
price report. Originally thought I could use this date to sort on. For
example, January would be "New" and December would be "Old", and so on. But
since dates for old and new prices for each customer are always different,
this does not work. So the only thing "Report Date" does for me is tell me
what report I was working on when a price was updated. Customers usually
provide an update date, which is entered manually. When customer does not
provide a date, this field is left empty.

I would have a (hidden) field where the date/time is auto entered each time
a new record is entered (the default property should be set to "Date()"
without the quotes)

This is what I'm using "Report Date" for.

By "updated", do you mean the price in the record is changed? Or is a new
record created each time the price changes?

Various customers submit price changes in a month, and currently I enter a
new price record for this...this is what I refer to as "updated."

I think this is a wise. Clients always seem to want more and more.... <g>

In order to give client the first report, I have had to delete the 3rd price
record already. But going forward, I would like to keep the complete price
history just in case.

So, without knowing the field names and table names or how the record for
the price change is time stamped, here is a query that will have up to 2
records for each client.

1) Create a query with the fields from the tables for Customers and for
Prices.
2) Save it.
3) Create another query. Don't select any tables (close the dialog box).
Switch to SQL view and paste in the following:

SELECT Q1.[U-LName], Q1.[U-FName], Q1.ProductName, Q1.Amount, Q1.TimeStamp
FROM qryUserProd AS Q1
WHERE (((Q1.TimeStamp) In (SELECT TOP 2 TimeStamp FROM qryUserProd AS Q2
WHERE Q2.User_ID=Q1.User_ID ORDER BY TimeStamp DESC)))
ORDER BY Q1.[U-LName], Q1.[U-FName], Q1.TimeStamp DESC;


4) Change "qryUserProd" to the name of the query you created in step 1 (2
places)
Change "User_ID" to your customer ID field name (2 places)
Change "TimeStamp" to your field name that is the date/time changed
(Report Date?) (5 places)
Change the field names to your fields you want to use in the report.
(Many places)
You can change the "Select Q1.[U-LName], ..." to "Select Q1.*" if you
want to have all fields available.

DO NOT change "Q1" or "Q2"

5) Save this query. If all goes well, you will have a list of clients with
the (up to ) 2 latest price changes.


***

I appreciate your time...will see if I can translate this to my project.
Remember what I said about the Prices table being overly large? (Sigh)

(BTW, don't use spaces in object names "qryUserProd" is just as easy to
read as "qry User Prod"
- but the spaces makes it harder to use the name in queries and code)
***

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

PS - if there are any errors, it is the computer's fault...

 

[Guest]

Comments Inline....



You should delete any orphan records in table Prices.

I'm still slogging through the Access learning process...what are orphan
records?

Is "Report Date" field date and time? Is the date(/time) entered manually?

This is a default date I enter in the table, i.e. 01/31/2006 for January
price report. Originally thought I could use this date to sort on. For
example, January would be "New" and December would be "Old", and so on. But
since dates for old and new prices for each customer are always different,
this does not work. So the only thing "Report Date" does for me is tell me
what report I was working on when a price was updated. Customers usually
provide an update date, which is entered manually. When customer does not
provide a date, this field is left empty.

I would have a (hidden) field where the date/time is auto entered each time
a new record is entered (the default property should be set to "Date()"
without the quotes)

This is what I'm using "Report Date" for.

By "updated", do you mean the price in the record is changed? Or is a new
record created each time the price changes?

Various customers submit price changes in a month, and currently I enter a
new price record for this...this is what I refer to as "updated."

I think this is a wise. Clients always seem to want more and more.... <g>

In order to give client the first report, I have had to delete the 3rd price
record already. But going forward, I would like to keep the complete price
history just in case.

So, without knowing the field names and table names or how the record for
the price change is time stamped, here is a query that will have up to 2
records for each client.

1) Create a query with the fields from the tables for Customers and for
Prices.
2) Save it.
3) Create another query. Don't select any tables (close the dialog box).
Switch to SQL view and paste in the following:

SELECT Q1.[U-LName], Q1.[U-FName], Q1.ProductName, Q1.Amount, Q1.TimeStamp
FROM qryUserProd AS Q1
WHERE (((Q1.TimeStamp) In (SELECT TOP 2 TimeStamp FROM qryUserProd AS Q2
WHERE Q2.User_ID=Q1.User_ID ORDER BY TimeStamp DESC)))
ORDER BY Q1.[U-LName], Q1.[U-FName], Q1.TimeStamp DESC;


4) Change "qryUserProd" to the name of the query you created in step 1 (2
places)
Change "User_ID" to your customer ID field name (2 places)
Change "TimeStamp" to your field name that is the date/time changed
(Report Date?) (5 places)
Change the field names to your fields you want to use in the report.
(Many places)
You can change the "Select Q1.[U-LName], ..." to "Select Q1.*" if you
want to have all fields available.

DO NOT change "Q1" or "Q2"

5) Save this query. If all goes well, you will have a list of clients with
the (up to ) 2 latest price changes.


***

I appreciate your time...will see if I can translate this to my project.
Remember what I said about the Prices table being overly large? (Sigh)

 

[Guest]

Comments Inline....

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

You should delete any orphan records in table Prices.

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.

Is "Report Date" field date and time? Is the date(/time) entered manually?

I would have a (hidden) field where the date/time is auto entered each time
a new record is entered (the default property should be set to "Date()"
without the quotes)

Price information for various customers is updated in different months.
Combining the two tables in a query gives me a complete Price History.

By "updated", do you mean the price in the record is changed? Or is a new
record created each time the price changes?

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.)

I think this is a wise. Clients always seem to want more and more.... <g>


So, without knowing the field names and table names or how the record for
the price change is time stamped, here is a query that will have up to 2
records for each client.

1) Create a query with the fields from the tables for Customers and for
Prices.
2) Save it.
3) Create another query. Don't select any tables (close the dialog box).
Switch to SQL view and paste in the following:

SELECT Q1.[U-LName], Q1.[U-FName], Q1.ProductName, Q1.Amount, Q1.TimeStamp
FROM qryUserProd AS Q1
WHERE (((Q1.TimeStamp) In (SELECT TOP 2 TimeStamp FROM qryUserProd AS Q2
WHERE Q2.User_ID=Q1.User_ID ORDER BY TimeStamp DESC)))
ORDER BY Q1.[U-LName], Q1.[U-FName], Q1.TimeStamp DESC;


4) Change "qryUserProd" to the name of the query you created in step 1 (2
places)
Change "User_ID" to your customer ID field name (2 places)
Change "TimeStamp" to your field name that is the date/time changed
(Report Date?) (5 places)
Change the field names to your fields you want to use in the report.
(Many places)
You can change the "Select Q1.[U-LName], ..." to "Select Q1.*" if you
want to have all fields available.

DO NOT change "Q1" or "Q2"

5) Save this query. If all goes well, you will have a list of clients with
the (up to ) 2 latest price changes.


***
(BTW, don't use spaces in object names "qryUserProd" is just as easy to
read as "qry User Prod"
- but the spaces makes it harder to use the name in queries and code)
***

HTH