RE: Subquery Question

Tech-Archive recommends: Speed Up your PC by fixing your registry

Hi Jerry,

Thanks for your response. Below is the query that I am using. I have
posted the entire query. The statement in question is near the from clause.

Thanks for your help.

SELECT Trim(dbo_dealer.ext_name) AS Dealer, Trim(dbo_deals.deal_no) AS
Quantum, dbo_fxdeals.contr_rate AS [Contract Rate], dbo_fxdeals.spot_rate AS
[Spot Rate], dbo_fxdeals.fwdptsccy, dbo_fxdeals.fwdptsamt,
dbo_fxdeals.domccy, dbo_fxdeals.base_type, dbo_fxdeals.deal_rate,
dbo_fxdeals.fwd_rate, dbo_deals.trans_type, dbo_deals.ccy, dbo_deals.ccy2,
dbo_deals.base_ccy, dbo_deals.face_value, dbo_deals.face_valu2,
dbo_deals.deal_dt, dbo_deals.settle_dt, dbo_deals.mature_dt,
dbo_deals.cur_mat_dt, dbo_deals.eff_mat_dt, dbo_deals.act_mat_dt,
dbo_deals.input_dt, dbo_sectype.name AS Instrument, dbo_bustruct.name AS
Entity, Trim(dbo_bustruct.code) AS EntityCode, dbo_cparty.name AS [Counter
Party], dbo_strategy.name AS Strategy,
IIf(dbo_fxdeals.domccy=dbo_deals.ccy2,dbo_deals.ccy2 & " " &
dbo_deals.ccy,dbo_deals.ccy & " " & dbo_deals.ccy2) AS FXPair, (Select top 1
rfsEntityandUser.functionalCurrency From rfsEntityandUser Where
rfsEntityandUser.entityCode = dbo_bustruct.code) AS functionalCurrency,
IIf(FunctionalCurrency=dbo_deals.ccy,dbo_deals.ccy2,dbo_deals.ccy) AS
nonFunctionalCCy,
IIf(dbo_deals.ccy=nonfunctionalCCY,dbo_deals.face_value,-dbo_deals.face_valu2)
AS NonFunctionalNotional, Replace(Trim(UCase(dbo_strategy.name)),"forecasted
","") AS natureOfRiskType,
IIf(dbo_strategy.thekey="4117","decreased","increased") AS
natureOfRiskPhrase, IIf(dbo_strategy.thekey="4117","receipts","payments") AS
PaymentType, rmTextLookup.Memo AS Comments_Strategy,
***Subquery***
(Select top 1 Afn.deal_no From dbo_deals Afn where
Afn.mature_dt=dbo_deals.cur_mat_dt and Afn.trans_type = "fx" and
Afn.ccy=dbo_deals.ccy and Afn.ccy2=dbo_deals.ccy2 order by Afn.deal_dt,
Afn.deal_no) AS firstNext,
***End: Subquery ***
IIf(firstNext=dbo_deals.deal_no,"first","next") AS firstNextText
FROM (dbo_strategy INNER JOIN (dbo_bustruct INNER JOIN (dbo_sectype INNER
JOIN (((dbo_fxdeals INNER JOIN dbo_deals ON
dbo_fxdeals.deal_no=dbo_deals.deal_no) INNER JOIN dbo_dealer ON
dbo_deals.dealer=dbo_dealer.thekey) INNER JOIN dbo_cparty ON
dbo_deals.cparty=dbo_cparty.thekey) ON dbo_sectype.thekey=dbo_deals.sectype)
ON dbo_bustruct.thekey=dbo_deals.entity) ON
dbo_strategy.thekey=dbo_deals.strategy) LEFT JOIN rmTextLookup ON
dbo_strategy.thekey=rmTextLookup.QuantumID
WHERE format(dbo_deals.deal_dt,"yyyy")=[Print documents for which year?] And
dbo_strategy.thekey=[Enter Quantum Strategy Key ID]
ORDER BY dbo_deals.deal_no DESC;

"Jerry Whittle" wrote:

Hi,

I'm assuming that you are doing this in a function? If so, we'll probably
need to see the whole thing to make sense of it. I will at least.

If nothing else, your first SQL statement isn't going to return much useful
information unless it contains an Order By clause. Otherwise Top 1000 will
just grab the first thousand records. Also as you are trimming deal_no, I'm
assuming that is a text field. As text sorting can be very difficult unless
you can convert it to a number or have a very good way of incrementing the
text.

I consider a subquery as a query inside of another query. It might be that
your problem could be handled that way.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"spartanmba" wrote:

Hello,

I am not very familair with subqueries and thus I am having trobubles. I
have a query that pulls back say 1000 records. Then I have a subquery in the
first query that I am using to determine if the a recorded pulled meets some
criteria for additional processing. The subquery seems to be causing a loop.
Like it is processing each 1000 records of the first query for each record
of the first.

I used a subquery because I could not get a SQL IF statement to run
correctly in Access. I am trying to determine if the records pulled from the
first query is the first record of its type in the database. The query that
I am running through Access is querying a SQL database through an ODBC call.

Below is part of the main query and the entire subquery.

***Main query***
SELECT TOP 1000 Trim(dbo_dealer.ext_name) AS Dealer, Trim(dbo_deals.deal_no)
AS Quantum

***Sub Query ***
(Select top 1 Afn.deal_no
From dbo_deals Afn
where Afn.mature_dt=dbo_deals.cur_mat_dt and Afn.trans_type = "fx" and
Afn.ccy=dbo_deals.ccy and Afn.ccy2=dbo_deals.ccy2 order by Afn.deal_dt,
Afn.deal_no) AS firstNext,

***Additional processing back in the main query after the sub runs***
IIf(firstNext=dbo_deals.deal_no,"first","next") AS firstNextText
.

Relevant Pages

  • RE: EXISTS reserved word in FROM clause
    ... we make a good pair because I know Access SQL ... from what you described the query syntax that I ... >The subquery should return the records: ...
    (microsoft.public.access.queries)
  • Re: export form filtered data to excel
    ... ' There's no ORDER BY in the SQL. ... ' Remove the semi-colon from the end, then append the WHERE clause ... Or, better yet, look at the actual SQL for your query and see what's ... Dim qdfTemp As DAO.QueryDef ...
    (microsoft.public.access.formscoding)
  • Re: Query to Count Duplicate Values in a Given Date Range
    ... Show" to note whether the client showed up at the pantry. ... Now, if I try to run the query with that field, it returns no results. ... Here's my final SQL code if you see anything else that may need adjusting. ... use a WHERE clause rather than HAVING for the dates, ...
    (microsoft.public.access.queries)
  • Re: Error: Specified field could refer to more than one table
    ... WeeklyMileage) in SELECT Clause my guess is that the reference exists ... Run the query and select Remove Filter/sort from the menu and then save the ... Copy the SQL text into a blank query and try switching into design view ... FROM clause of your SQL statement. ...
    (microsoft.public.access.queries)
  • Re: Error: Specified field could refer to more than one table
    ... WeeklyMileage) in SELECT Clause my guess is that the reference exists ... Run the query and select Remove Filter/sort from the menu and then save the ... Copy the SQL text into a blank query and try switching into design view ... FROM clause of your SQL statement. ...
    (microsoft.public.access.queries)