SQL Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Conley - 30 Jun 2006 16:13 GMT

We have a MS Access database that pulls information from our JD Edward SQL
Database. There is a query that list all trucks and what product was last
loaded in them last. We had to add a special tax to everything we sell.
This tax is set up like an product. So now when we pull the report, from the
query, it shows this tax "product" as the last item that was loaded. Is
there anyway to setup may criteria to show, if the item is this "tax product"
then show the next to the last record? Thanks

Reply to this Message

MGFoster - 30 Jun 2006 18:40 GMT

> We have a MS Access database that pulls information from our JD Edward SQL
> Database. There is a query that list all trucks and what product was last
[quoted text clipped - 3 lines]
> there anyway to setup may criteria to show, if the item is this "tax product"
> then show the next to the last record? Thanks

By definition a table is unordered; therefore, you have to have a
definition of "last" that uses the columns (fields) per row (record)
that indicates which row is the last row. Is it a sequential number,
date, time, etc.? For example, say I have a table that has a date
column and the rows are entered in chronological order. I can find the
"last" row based on the latest date (Max date):

SELECT *
FROM table1
WHERE date_column = (SELECT MAX(date_column) FROM table1)

If you wish to exclude the tax "product" you can include that criteria
in the WHERE clause:

SELECT ...
FROM ...
WHERE product_id <> XXXX
AND date_column = (SELECT MAX(date_column) FROM table1)

where XXXX is the tax product ID.

For a better answer to your problem you'll have to show the SQL of your
query and the design (column names, data types, PK & FK) of all tables
involved in your query.

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Reply to this Message