Re: Another sequential query field problem... are you out the Duan

You seem to have seen some solutions that involve a subquery. What have you
attempted in terms of your SQL statement and results?

--
Duane Hookom
MS Access MVP
--

"Bdavis" <Bdavis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:303F22A3-74EF-4C3F-9BC9-3896763082CE@xxxxxxxxxxxxxxxx
>I haven't attempted either but using Dcount has always been slow for me so
>I
> was leaning toward a subquery.
>
> I'm pretty sure. There is no native Transaction ID field generated by the
> system that exports this table. Additionally, you would have to
> concatenate
> virually every feild in the table inclusing the amount to generate a key.
>
> "Duane Hookom" wrote:
>
>> Are you sure you need to create a sequence number in order to allow you
>> to
>> "group debits with credits in this table"?
>>
>> Are you attempting to use a subquery or DCount()?
>>
>> --
>> Duane Hookom
>> MS Access MVP
>> --
>>
>> "Bdavis" <Bdavis@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:41A489BA-18BB-45AA-A168-AF484BB60D8B@xxxxxxxxxxxxxxxx
>> >I almost got there based on a post from Duane a while back but I'm
>> >hitting
>> > the wall.
>> >
>> > What I need to do is creat a field in my query that creates a sequence
>> > number that resets on each new day [dtPost] based on the sort order of
>> > the
>> > query.
>> >
>> > Then, I want to take the result and, if the sequence number is even,
>> > subtract 1. This, I think, will provide me with a query generated
>> > field
>> > that
>> > will allow me to group debits with credits in this table. The SQL so
>> > far
>> > is
>> > below:
>> >
>> >
>> > SELECT psq_GLArchive.sCliNum, psq_GLArchive.sCoNum,
>> > psq_GLArchive.sLoanNum,
>> > psq_GLArchive.dtPost, psq_GLArchive.sAssignNum,
>> > psq_GLArchive.sCollectNum,
>> > [cCreditAmt]+[cDebitAmt] AS Amount, psq_GLArchive.sCurCode,
>> > psq_GLArchive.dtAsOf, psq_GLArchive.sGLAcctNum,
>> > psq_GLArchive.sAcctTyFlg,
>> > psq_GLArchive.cDebitAmt, psq_GLArchive.cCreditAmt,
>> > psq_GLArchive.dtLstUpd
>> > FROM psq_GLArchive
>> > ORDER BY psq_GLArchive.sCliNum, psq_GLArchive.sCoNum,
>> > psq_GLArchive.sLoanNum, psq_GLArchive.dtPost DESC ,
>> > psq_GLArchive.sAssignNum,
>> > psq_GLArchive.sCollectNum, [cCreditAmt]+[cDebitAmt];
>> >
>>
>>
>>


.

Relevant Pages

  • Re: Counting days in a crosstab query
    ... How would those two fields fir into the sql statement you created. ... "Duane Hookom" wrote: ... > The SQL view of the query should be: ... > FROM tblDates ...
    (microsoft.public.access.reports)
  • Re: Slow SQL, too many logical reads ?
    ... I would be tempted to try reformating the SQL statement like this to ... Oracle is performing a nested loop join between the subquery and the ... The explain plan for... ... (Run your query) ...
    (comp.databases.oracle.server)
  • Re: Counting days in a crosstab query
    ... > How would those two fields fir into the sql statement you created. ... >> The SQL view of the query should be: ... >> Duane Hookom ... >> MS Access MVP ...
    (microsoft.public.access.reports)
  • Re: Avoid a subselect "where not equal to"?
    ... I think this query can scale very well, ... will normally execute much faster than the subquery method ... different version of Oracle may handle the SQL ... possibly rewriting the SQL statement into a more ...
    (comp.databases.oracle.misc)
  • Re: Ranking and restarting the rank on new product
    ... The thing you originally posted IS a subquery. ... A subquery is a query ... embedded within your SQL statement. ... >> Tom Ellison ...
    (microsoft.public.access.queries)