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 2 1386
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Sean C. |
last post by:
Helpful folks,
Most of my previous experience with DB2 was on s390 mainframe systems
and the optimizer on this platform always seemed very predictable and
consistent. Since moving to a WinNT/UDB...
|
by: Steve Jorgensen |
last post by:
Hi all,
Over the years, I have had to keep dealing with the same Access restriction -
that you can't update a table in a statement that joins it to another
non-updateable query or employs a...
|
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a...
|
by: Warren |
last post by:
I am trying to create a report that does the following:
Access Data in Query:
NAME | DATE | SALE TYPE |
-------------------------
John DOE | 1282003 | TYPE A
Jane DOE | 1282003 | TYPE C...
|
by: kandiko |
last post by:
Hi all,
Here is my dilemma.
I have two tables, one called HS DATA and one called
ImportedHighSchools.
Both tables contain an ID code for each record.
| |
by: Steve Jorgensen |
last post by:
Hi all,
Frankly, this is such an off-beat thing, I don't know if anyone else here will
ever have the same issue. Just in case anyone does, though...
I needed to be able to search for the...
|
by: Hexman |
last post by:
Hello All,
How do I limit the number of detail records selected in a Master-Detail set using SQL?
I want to select all master records for a date, but only the first 3 records for the details...
|
by: timn |
last post by:
Translating Access SQL queries into SQL subqueries.
--------------------------------------------------------------------------------
I have a query in Access that uses a subquery, I would like...
|
by: NeoPa |
last post by:
Access QueryDefs Mis-save Subquery SQL
Access stores its SQL for Subqueries in a strange manner :s It seems to replace the parentheses "()"with square brackets "" and (often) add an extraneous...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |