Access Query Sorting Problem

[Kanga Keith]

Hi Everybody
I have a problem with a small database which l am designing. I've created a simple query using 4 fields:

Sales_ID Pack_Number SOP_Number SOP_Line

When displayed the data looks like this:
1 3 20462 1
2 2 20465 1
3 5 20470 2
4 7 20470 2
5 4 20470 5

As you can see Sales_ID 3 and 4 contain the same data in the SOP_Number and SOP_Line. I would like to display the data but ignore any duplicates within the SOP_Number and SOP_Line fields so that in the above records only 4 records would be displayed 1,2,3,5.

Any help would be most appreciated.

[ADezii]

Assuming your Table Name is tblSales, all Fields, namely: [Sales_ID], [Pack_Number], [SOP_Number], and [SOP_Line] are Numeric (LONG), and [Sales_ID] is the Primary Key, this solution will work. Be advised that SQL is not my strong point, and a better, and shorter, solution will probably be forthcoming.

SELECT DISTINCT DLast("[Sales_ID]","tblSales","[SOP_Number] = " & [SOP_Number] & _
" And [SOP_Line] = " & [SOP_Line]) AS SalesID, DLookUp("[Pack_Number]","tblSales","[Sales_ID] = " &_
 [SalesID] & " And [SOP_Number] = " & [SOP_Number] & " And [SOP_Line] = " & _
[SOP_Line]) AS PackNum, tblSales.SOP_Number, tblSales.SOP_Line
FROM tblSales;

OUTPUT:

SalesID    PackNum    SOP_Number      SOP_Line
1              3        20462             1
2              2        20465             1
4              7        20470             2
5              4        20470             5

[Kanga Keith]

Hi ADezii
Thanks for replying so promptly. As this is my first forum question l was really impressed. I've tried your reply but am having a problem with an error being reported:

The syntax of the subquery in this expression is incorrect.
Check the subquery's syntax and enclose the subquery in parentheses.

Not being very experienced in queries l've tried to track down the error but not having any luck. Any suggestions most appreciated.
Regards

[ADezii]

Hi ADezii
Thanks for replying so promptly. As this is my first forum question l was really impressed. I've tried your reply but am having a problem with an error being reported:

The syntax of the subquery in this expression is incorrect.
Check the subquery's syntax and enclose the subquery in parentheses.

Not being very experienced in queries l've tried to track down the error but not having any luck. Any suggestions most appreciated.
Regards

A picture is worth a thousand words, so I made the Test Database available to you as an Attachment. When the DB opens, the Test Query (qrySales) will be selected in the Database Window.

[Kanga Keith]

ADezii your a star.
Its now works a treat - l was trying to enter the whole of your code in the criteria of the Sales_ID.

Thanks again for your help.
Regards
Keith

[ADezii]

ADezii your a star.
Its now works a treat - l was trying to enter the whole of your code in the criteria of the Sales_ID.

Thanks again for your help.
Regards
Keith

Any time Keith, as previously stated, there may be a better solution. I'll ask a couple of the other Members who are more 'SQL Savy' than I to take a look.

[FishVal]

Hello, all.

Well ... Actually the same could be done with SQL for less money. ;)

SELECT tblSales.SOP_Number, tblSales.SOP_Line, First(tblSales.Pack_Number) AS FirstOfPack_Number, First(tblSales.Sales_ID) AS FirstOfSales_ID
FROM tblSales
GROUP BY tblSales.SOP_Number, tblSales.SOP_Line;
 

or

SELECT tblSales.SOP_Number, tblSales.SOP_Line, Last(tblSales.Pack_Number) AS LastOfPack_Number, Last(tblSales.Sales_ID) AS LastOfSales_ID
FROM tblSales
GROUP BY tblSales.SOP_Number, tblSales.SOP_Line;
 

whatever you like.

@ADezii

Oh ... Now I see. You are really a hardened and shameless coder. :D

Regards,
Fish

[Stewart Ross]

Hi. In addition to ADezii's solution here's a subquery-based SQL solution for you to try which may be more efficient in operation as it does not use domain aggregate functions.

To enter the SQL into the Access Query Editor, select the Queries tab, New, Design View, and instead of adding tables to the grid select Close in the table add dialogue. With the empty grid visible select View from the main menu, SQL View, then copy and paste the SQL below in to the SQL window. Substitute the correct name of your table for tblSales in the two places it occurs in the SQL.

SELECT * FROM tblSALES as A 
WHERE  A.Sales_ID in 
  (SELECT Min(B.Sales_ID) AS MinSales_ID
   FROM tblSALES as B
   GROUP BY B.SOP_Number, B.SOP_Line);

Results

Sales_ID Pack_Number SOP_Number SOP_Line
     1       3         20462       1
     2       2         20465       1
     3       5         20470       2
     5       4         20470       5

-Stewart

[ADezii]

@ FishVal & Stewart Ross Inverness, thanks guys at least my hunch was right in that either of you as well as NeoPa or missinglinq would come up with a better, more efficient, solution. Always a pleasure to be in your presence!

[ADezii]

Kanga, I seriously hope that you decided to check back on this Thread as I had previously suggested. Two of my 'famous' colleagues, namely FishVal and Stewart Ross Inverness, have come up with better and more efficient solutions (Posts #7 and #8 respectively). If you did not check back in, well, it's your loss. Take care.

[NeoPa]

Hello, all.

Well ... Actually the same could be done with SQL for less money. ;)

SELECT tblSales.SOP_Number, tblSales.SOP_Line, First(tblSales.Pack_Number) AS FirstOfPack_Number, First(tblSales.Sales_ID) AS FirstOfSales_ID
FROM tblSales
GROUP BY tblSales.SOP_Number, tblSales.SOP_Line;
 

or

SELECT tblSales.SOP_Number, tblSales.SOP_Line, Last(tblSales.Pack_Number) AS LastOfPack_Number, Last(tblSales.Sales_ID) AS LastOfSales_ID
FROM tblSales
GROUP BY tblSales.SOP_Number, tblSales.SOP_Line;
 

whatever you like.

Technically, as far as the question was asked, the first solution here is the exact match.

@ADezii

Oh ... Now I see. You are really a hardened and shameless coder. :D

Regards,
Fish

It's taken you all this time to realise that... :D

@Stewart,
There are no Domain Aggregate functions in this SQL. Aggregate functions (as here), as they are entirely handled within the SQL engine itself, within the normal flow of the data, do not suffer from the degradation of performance that can often effect the Domain Aggregate functions.

[NeoPa]

I suppose I should add that, if the first of the duplicate records is not specifically required, but the one matching the lowest value of a particular field, then Stewart's SQL using Min() would actually be the more correct. We can only really go on the question as asked though of course.

[Kanga Keith]

Just like to thank everybody for the help you've given me. As this is my first time on this forum l.m greatly impressed. I'll try out the other suggestions but once again thanks.
Regards
Keith

[ADezii]

Technically, as far as the question was asked, the first solution here is the exact match.

It's taken you all this time to realise that... :D

@Stewart,
There are no Domain Aggregate functions in this SQL. Aggregate functions (as here), as they are entirely handled within the SQL engine itself, within the normal flow of the data, do not suffer from the degradation of performance that can often effect the Domain Aggregate functions.

Thanks for the help, NeoPa, just for the Record you are also considered a 'famous' colleague! (LOL)!

[Stewart Ross]

@Stewart,
There are no Domain Aggregate functions in this SQL

Hi NeoPa. I was referring to the DLast and DLookup functions in ADezii's original response (post #2), not to Fish's SQL (posted as mine was being prepared) which I had not seen at the time I posted.

I agree entirely that there are no domain aggregate functions in Fish's SQL - but I did not actually claim there were.

-Stewart

[NeoPa]

I'm sorry I misunderstood Stewart. I was a little confused I must admit. I didn't expect such a mistake from you. Your explanation makes far more sense and, once again, sorry for doubting you.

I hope my post didn't sound critical. I didn't intend it to anyway. Just wanted to clarify the position.

[Stewart Ross]

No worries NeoPa - I was I must admit somewhat taken aback that you thought I had confused First with one of the Dxxxxx range of domain aggregates (DLookup, DCount, DSum et al). It would have been a really bad reflection on me had I not known the difference!

Anyways, no harm done at all.

I'm just glad that ADezii, Fish and I have all supplied viable solutions that provide choice for the original poster to play with and see what works best in his application.

@ADezii - Whilst my SQL skills are certainly useful I am nowhere near as accomplished a programmer as you are. I consider it a privilege to be able to assist you in some small way with a thread like this.

Regards

Stewart

[ADezii]

No worries NeoPa - I was I must admit somewhat taken aback that you thought I had confused First with one of the Dxxxxx range of domain aggregates (DLookup, DCount, DSum et al). It would have been a really bad reflection on me had I not known the difference!

Anyways, no harm done at all.

I'm just glad that ADezii, Fish and I have all supplied viable solutions that provide choice for the original poster to play with and see what works best in his application.

@ADezii - Whilst my SQL skills are certainly useful I am nowhere near as accomplished a programmer as you are. I consider it a privilege to be able to assist you in some small way with a thread like this.

Regards

Stewart

Thanks again Stewart, and the rest of you guys. When I am truly stuck in the mud, I know that I can always turn to the 'Fab Four'! (LOL).

[NeoPa]

I'll be the stretchy one :D