Sequential Numbering in Query

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Sequential Numbering in Query

Post by MSingh »

Hi,

I have tblTempSales with a 1:M relationship with tblTempSalesLineItems.
The invoices generated are reviewed before finalisation so notes are appended to the Invoice line Items.
Not all Invoice line Items will have notes.
There would be several Invoices being prepare simultaneously.

I need to sequentially number tblTempSalesLineItems where tblTempSalesLineItems.SalesLineItemOtherDetails is Not Null.

In my qryTempSalesLineItems I have the ffg. to allocate a number to the tblTempSalesLineItems .
NoteNum: IIf(Len([SalesLineItemOtherDetails])>0,(Select Count(*) From tblTempSalesLineItems As T1 Where Len(T1.SalesLineItemOtherDetails) > 0 And T1.SalesLineItemsID<= tblTempSalesLineItems.SalesLineItemsID),Null)
which does allot a unique number to tblTempSalesLineItems.SalesLineItemOtherDetails that have notes, however, they are not sequential.
This is because (I suspect): Select Count(*) From tblTempSalesLineItems is counting all records with notes.

If I use a query to limit the Count to qrySalesLineItemsWithNotes, the query runs correctly sequentially numbering the line Items with notes and omitting those without notes. However, rsubSalesLineItemsWithNotes generates the error: "Multi-Level Group By Clause Not Allowed in a subquery.
This happens even if I run rsubSalesLineItemsWithNotes independently ie. without it embedded in rptTempInvoice.
Please could you suggest how I could correct this.

Kind Regards,
Mohamed

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sequential Numbering in Query

Post by HansV »

Could you attach a stripped down and zipped sample database?
Best wishes,
Hans

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Re: Sequential Numbering in Query

Post by MSingh »

Hi Hans,

Thank You,
Please see the attached SequentialNumbering.zip

Kind Regards,
Mohamed
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sequential Numbering in Query

Post by HansV »

It took me a while to grasp what you were doing, but I think I have a workaround. In general, using a subquery is better than using DLookup, DCount etc. but in this situation, DCount works better.
Define the column RmkNum in qryTempSalesInvoice_Detail_Notes as follows:

RmkNum: IIf(Len([qryTempSalesNotes.SalesLineItemOtherDetails])>0,DCount("*","qryTempSalesNotes","Len(SalesLineItemOtherDetails) > 0 And SalesLineItemsID<=" & [qryTempSalesNotes].[SalesLineItemsID]),Null)
S0677.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Re: Sequential Numbering in Query

Post by MSingh »

Hi Hans,

Thank You - your solution works great as always!

rptInvoice loads a bit slow, perhaps that's because of the DCount.
We'll have to live with it for the convenience of having the numbered notes.

Kind Regards,
Mohamed

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sequential Numbering in Query

Post by HansV »

Yes, DCount is relatively slow, but I think that's unavoidable here.
Best wishes,
Hans

MSingh
3StarLounger
Posts: 366
Joined: 12 May 2010, 06:49

Re: Sequential Numbering in Query

Post by MSingh »

Hi Hans,

In the original solution the query runs fast enough but rptInvoice took a relatively longer time to load.
So I managed to get rptInvoice to load quicker by first writing (the DCount query in your solution) to a temp table.

Kind Regards,
Mohamed

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Sequential Numbering in Query

Post by HansV »

Good idea. If performance is a bottleneck, a temporary table often helps.
Best wishes,
Hans