Access SubQuery Help Needed.....................

[Hexman]

Don't know if this is the proper newsgroup to post to. Sorry if not.

Can't figure this one out. Using Access 2003. I have 2 tables, IMast
(table of part info) and THist (part transaction history).

I need to select all parts that have a product code = "CAST" and list
the last 10 history transaction if they are within 90 days of the run
date.

From the IMast table I need the columns: part, descr, pcode. From the
THist I need: wonumber, wodate, wocust, ..... I will need a few more
columns from each of the tables for the final report.

I've tried many variations of the Select statement without success.
Such as:

Select part, desc, pcode
from (Select top 10 wonumber, wodate, wocust
from THist
where (pcode = "CAST") and (part=wopart) and ((thisdate -
wodate) < 91)
order by part, wodate, wonumber

Looking for a solution,

Hexman

[John Griffiths]

FROM using subquery not a table(s) is not supported in SQL92.

<code lang="SQL" type="AirCode">

SELECT TOP 10 i.part, i.descr, i.pcode, p.wonumber, p.wodate, p.wocust
FROM IMast i INNER JOIN THist p ON i.part = p.wopart
WHERE i.pcode = "CAST"
AND ((thisdate - wodate) < 91)

</code>

Regards John

"Hexman" <He****@binary.com> wrote in message
news:48********************************@4ax.com...

Don't know if this is the proper newsgroup to post to. Sorry if not.

Can't figure this one out. Using Access 2003. I have 2 tables, IMast
(table of part info) and THist (part transaction history).

I need to select all parts that have a product code = "CAST" and list
the last 10 history transaction if they are within 90 days of the run
date.

From the IMast table I need the columns: part, descr, pcode. From the
THist I need: wonumber, wodate, wocust, ..... I will need a few more
columns from each of the tables for the final report.

I've tried many variations of the Select statement without success.
Such as:

Select part, desc, pcode
from (Select top 10 wonumber, wodate, wocust
from THist
where (pcode = "CAST") and (part=wopart) and ((thisdate -
wodate) < 91)
order by part, wodate, wonumber

Looking for a solution,

Hexman

[Kerry Moorman]

John,

An Order By clause is also needed, so the Top 10 will return the appropriate
data.

Kerry Moorman
"John Griffiths" wrote:

FROM using subquery not a table(s) is not supported in SQL92.

<code lang="SQL" type="AirCode">

SELECT TOP 10 i.part, i.descr, i.pcode, p.wonumber, p.wodate, p.wocust
FROM IMast i INNER JOIN THist p ON i.part = p.wopart
WHERE i.pcode = "CAST"
AND ((thisdate - wodate) < 91)

</code>

Regards John

"Hexman" <He****@binary.com> wrote in message
news:48********************************@4ax.com...

Don't know if this is the proper newsgroup to post to. Sorry if not.

Can't figure this one out. Using Access 2003. I have 2 tables, IMast
(table of part info) and THist (part transaction history).

I need to select all parts that have a product code = "CAST" and list
the last 10 history transaction if they are within 90 days of the run
date.

From the IMast table I need the columns: part, descr, pcode. From the
THist I need: wonumber, wodate, wocust, ..... I will need a few more
columns from each of the tables for the final report.

I've tried many variations of the Select statement without success.
Such as:

Select part, desc, pcode
from (Select top 10 wonumber, wodate, wocust
from THist
where (pcode = "CAST") and (part=wopart) and ((thisdate -
wodate) < 91)
order by part, wodate, wonumber

Looking for a solution,

Hexman