Help With Queries In Access

[Haas C]

Hey all,

I am struggling with the queries for some tasks I am trying to finish.
being a newbie, I can't seem to figure this out - perhaps you guys can
help:

I have a field: TransactionDate. This field is different for the same
product, but would like to run a query which would return the latest
date. For example:

ProductName, TransactionDate, QuantitySold, Price
Glue12, 2/28/08, 19, 85.00
Glue12, 2/29/08, 29, 75.00
Glue12, 5/1/08,12, 78.00
Eraser10, 2/17/08, 10, 0.25
Eraser10, 2/25/08, 12, 0.53
etc.

What I want to do is find out my last transaction via a Query: I just
want to see this output for the products Glue 12 and Eraser10:

ProductName, TransactionDate, QuantitySold, Price
Glue12, 5/1/08,12, 78.00
Eraser10, 2/25/08, 12, 0.53

Thanks in advance for any or all help!
-Haas

[KC-Mass]

Haas

Try this (with a real table name):

Regards

Kevin

SELECT tblTest.ProductName, Max(tblTest.TransactionDate) AS
MaxOfTransactionDate, _
Last(tblTest.QuantitySold) AS LastOfQuantitySold, Last(tblTest.Price) AS
LastOfPrice
FROM tblTest
GROUP BY tblTest.ProductName;
"Haas C" <ha*****@yahoo.comwrote in message
news:00**********************************@l77g2000 hse.googlegroups.com...

Hey all,

I am struggling with the queries for some tasks I am trying to finish.
being a newbie, I can't seem to figure this out - perhaps you guys can
help:

I have a field: TransactionDate. This field is different for the same
product, but would like to run a query which would return the latest
date. For example:

ProductName, TransactionDate, QuantitySold, Price
Glue12, 2/28/08, 19, 85.00
Glue12, 2/29/08, 29, 75.00
Glue12, 5/1/08,12, 78.00
Eraser10, 2/17/08, 10, 0.25
Eraser10, 2/25/08, 12, 0.53
etc.

What I want to do is find out my last transaction via a Query: I just
want to see this output for the products Glue 12 and Eraser10:

ProductName, TransactionDate, QuantitySold, Price
Glue12, 5/1/08,12, 78.00
Eraser10, 2/25/08, 12, 0.53

Thanks in advance for any or all help!
-Haas

[John]

KC-Mass wrote:

Haas

Try this (with a real table name):

Regards

Kevin

SELECT tblTest.ProductName, Max(tblTest.TransactionDate) AS
MaxOfTransactionDate, _
Last(tblTest.QuantitySold) AS LastOfQuantitySold, Last(tblTest.Price) AS
LastOfPrice
FROM tblTest
GROUP BY tblTest.ProductName;

I'm not sure exactly how the LAST function works - I thought it just
gave you the record closest to the end of the table, which may not be
the one with the most recent date. In fact, it may return results that
are not actually stored in the same record. For example, if your data
looks like this:

Glue12, 5/1/08,12, 78.00
Glue12, 2/28/08, 19, 85.00
Glue12, 2/29/08, 29, 75.00

Then the above query should return this:

Glue12, 5/1/08, 29, 75.00

Note that the quantity and price returned do not match the quantity and
price from the transaction on 5/1/08.

The way I've always handled situations like this is with two queries.
First, create a query that tells you the most recent transaction date
for each product, something like this:

SELECT ProductName, Max(TransactionDate) AS LatestDate FROM tblTest
GROUP BY ProductName;

Then, create a second query that uses the first query joined to tblTest
with both the ProductName field and the LatestDate field. This should
give you what you want, but if the database is large and those fields
aren't indexed, it could run rather slowly. Note that this method will
return all transactions on the latest date (i.e. if there were two
records for Glue12 with 5/1/08 as the transaction date, it would return
both of them).

[bobh]

On Oct 24, 11:38*am, Haas C <[e-mail]:

Hey all,

I am struggling with the queries for some tasks I am trying to finish.
being a newbie, I can't seem to figure this out - perhaps you guys can
help:

I have a field: TransactionDate. This field is different for the same
product, but would like to run a query which would return the latest
date. For example:

ProductName, TransactionDate, QuantitySold, Price
Glue12, 2/28/08, 19, 85.00
Glue12, 2/29/08, 29, 75.00
Glue12, 5/1/08,12, 78.00
Eraser10, 2/17/08, 10, 0.25
Eraser10, 2/25/08, 12, 0.53
etc.

What I want to do is find out my last transaction via a Query: I just
want to see this output for the products Glue 12 and Eraser10:

ProductName, TransactionDate, QuantitySold, Price
Glue12, 5/1/08,12, 78.00
Eraser10, 2/25/08, 12, 0.53

Thanks in advance for any or all help!
-Haas

If I understand your question correctly it will take two queries to
get what you want. In this example you will need to sub yout table and
field names.

qry1 - create qry1 and paste this in it

SELECT Table1.ProductNme, Max(Table1.TrxDte) AS MaxOfTrxDte
FROM Table1
GROUP BY Table1.ProductNme;
qry2 - create a second query and paste this sql into it

SELECT Table1.ProductNme, Table1.TrxDte, Table1.QtySold, Table1.Price
FROM Table1 INNER JOIN Query1 ON (Table1.ProductNme =
Query1.ProductNme) AND (Table1.TrxDte = Query1.MaxOfTrxDte);

hope this helps
bobh.

[Haas C]

On Oct 27, 1:20*pm, bobh <[e-mail]:

On Oct 24, 11:38*am, Haas C <[e-mail]:

Hey all,

I am struggling with the queries for some tasks I am trying to finish.
being a newbie, I can't seem to figure this out - perhaps you guys can
help:

I have a field: TransactionDate. This field is different for the same
product, but would like to run a query which would return the latest
date. For example:

ProductName, TransactionDate, QuantitySold, Price
Glue12, 2/28/08, 19, 85.00
Glue12, 2/29/08, 29, 75.00
Glue12, 5/1/08,12, 78.00
Eraser10, 2/17/08, 10, 0.25
Eraser10, 2/25/08, 12, 0.53
etc.

What I want to do is find out my last transaction via a Query: I just
want to see this output for the products Glue 12 and Eraser10:

ProductName, TransactionDate, QuantitySold, Price
Glue12, 5/1/08,12, 78.00
Eraser10, 2/25/08, 12, 0.53

Thanks in advance for any or all help!
-Haas

If I understand your question correctly it will take two queries to
get what you want. In this example you will need to sub yout table and
field names.

qry1 - create qry1 and paste this in it

SELECT Table1.ProductNme, Max(Table1.TrxDte) AS MaxOfTrxDte
FROM Table1
GROUP BY Table1.ProductNme;

qry2 - create a second query and paste this sql into it

SELECT Table1.ProductNme, Table1.TrxDte, Table1.QtySold, Table1.Price
FROM Table1 INNER JOIN Query1 ON (Table1.ProductNme =
Query1.ProductNme) AND (Table1.TrxDte = Query1.MaxOfTrxDte);

hope this helps
bobh.- Hide quoted text -

- Show quoted text -

That helps greatly! Thank you!