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

7 Jan 2006 11:55 PM

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

8 Jan 2006 1:15 AM

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

Show quoteHide quote

"Hexman" <Hex***@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
>

8 Jan 2006 2:30 AM

Kerry Moorman

John,

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

Kerry Moorman

Show quoteHide quote

"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" <Hex***@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
> >
>
>
>