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
Sequential Numbering in Query
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sequential Numbering in Query
Could you attach a stripped down and zipped sample database?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Sequential Numbering in Query
Hi Hans,
Thank You,
Please see the attached SequentialNumbering.zip
Kind Regards,
Mohamed
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.
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sequential Numbering in Query
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)
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)
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Sequential Numbering in Query
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
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
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sequential Numbering in Query
Yes, DCount is relatively slow, but I think that's unavoidable here.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 366
- Joined: 12 May 2010, 06:49
Re: Sequential Numbering in Query
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
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
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sequential Numbering in Query
Good idea. If performance is a bottleneck, a temporary table often helps.
Best wishes,
Hans
Hans