Another sequential query field problem... are you out the Duane Ho

[Bdavis]

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

 

[Duane Hookom]

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()?

 

[Bdavis]

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.

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

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

 

[Duane Hookom]

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

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.

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

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