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

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,
HexmanFROM 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" <Hexman@.binary.com> wrote in message
news:48l0s1pr5k6osekqsuvfb7avr327rf4mor@.
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
>|||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" <Hexman@.binary.com> wrote in message
> news:48l0s1pr5k6osekqsuvfb7avr327rf4mor@.
4ax.com...
>
>

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

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,
HexmanHexman,
What are the common columns for the 2 tables? If you are trying to get join
the 2 tables, you have a use a join statement such as this. Then put your
condiction in the where column.
SELECT [Orders].[OrderID], [Orders].[CustomerID], [Orders].[EmployeeID] FROM
Employees INNER JOIN Orders ON [Employees].[EmployeeID]
=[Orders].[EmployeeID];
Sam
"Hexman" wrote:

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