Extracting a st number of lines from a table

bscs1963 · Feb 9, 2012

I have a table that has two fields(Order number and Sku). There are 2 orders (133 lines and 171 lines) in the table. I need to grab the first 10 sku's from each order, and write it it a new table. I tried a "For" statment and that has not worked well at all.

Any suggestions?

dhookom · Feb 9, 2012

bscs1963,
Can you define "first"? Records in a table are like marbles in a bag. There isn't any reliable order that identifies first or second or last.

Duane
Hook'D on Access
MS Access MVP

Katerine · Feb 9, 2012

Assuming that you mean you want the 10 "smallest" SKU's for each order number:

(Please note that this is off the top of my head, untested, and being typed into the Tek-Tips window, so it should definitely be used as pseudocode, not copy-and-paste code

)

(Also note that there are a few assumptions - I'm assuming that both fields are text, for example. Also that this is stored in the current Access database (or in linked tables), and not in SQL Server (where there's actually a much better, non-VBA way that I won't go into because I'm assuming these are Access tables

)).

I'm calling the table, "MyTable" for the sake of this example. And the Append table, "AppendTable." Assuming that the field names are the same in both tables.

Code:

private sub AddTopTen()
on error goto Err_AddTopTen

dim cnn as New ADODB.Connection
dim rsOrders as New ADODB.Recordset
dim rsSKU as New ADODB.Recordset
dim iLoop as Integer

set cnn = CurrentProject.Connection
rsOrders.Open "SELECT DISTINCT [Order number] FROM MyTable", cnn, adOpenForwardOnly

docmd.setwarnings false
do until rsOrders.EOF
 rsSKU.Open = "SELECT [Order number], [Sku] FROM MyTable WHERE [Order number = '" & rsOrders![Order Number] & "' ORDER BY [Sku]", cnn, adOpenForwardOnly
 for iLoop = 1 to 10
  if not rsSKU.EOF then
   docmd.openquery "INSERT INTO AppendTable ([Order number], [Sku]) SELECT '" & rsSku![Order Number] & "' AS OrderNum, '" & rsSku![Sku] & "' AS Sku"
   rsSku.MoveNext
  end if
 next iLoop
 rsSku.Close
 rsOrders.MoveNext
loop

rsOrders.Close
set rsOrders = nothing
set rsSku = nothing
cnn.close
set cnn=nothing

Exit_AddTopTen:
 docmd.setwarnings true
 exit sub

Err_AddTopTen:
 msgbox err.description,, "AddTopTen: " & err.number
 resume Exit_AddTopTen
end sub

...and another option, using just one recordset, which is a little less intuitive but is also more efficient:

Code:

private sub AddTopTen()
on error goto Err_AddTopTen

dim cnn as New ADODB.Connection
dim rsOrders as New ADODB.Recordset
dim iLoop as Integer, sPrevOrderNum as string

set cnn = CurrentProject.Connection
rsOrders.Open "SELECT [Order number], [Sku] FROM MyTable ORDER BY [Order number], [Sku]", cnn, adOpenForwardOnly

sPrevOrderNum = ""

docmd.setwarnings false
do until rsOrders.EOF
 if rsOrders![Order number] = sPrevOrderNum then iLoop = iLoop + 1 Else iLoop = 1
 if iLoop<=10 then
   docmd.openquery "INSERT INTO AppendTable ([Order number], [Sku]) SELECT '" & rsOrders![Order Number] & "' AS OrderNum, '" & rsOrders![Sku] & "' AS Sku"
 end if
 sPrevOrderNum = rsOrders![Order number]
 rsOrders.MoveNext
loop

rsOrders.Close
set rsOrders = nothing
cnn.close
set cnn=nothing

Exit_AddTopTen:
 docmd.setwarnings true
 exit sub

Err_AddTopTen:
 msgbox err.description,, "AddTopTen: " & err.number
 resume Exit_AddTopTen
end sub

HTH!

Katie

MajP · Feb 10, 2012

I think Duane is suggesting this can be done in a simple query that returns the "top" 10 skus per order (group), if the table is designed correctly and we know what "top" means. Database tables do not ensure "first in first out", you have to have a field to sort on, especially if you are unaware of the indices. Adding a simple autonumber field would ensure this. Maybe SKU is sortable, but that was not stated.

Following on what Duane is pointing out, that code may not even provide the expected results. There is no guarantee that:
"SELECT [Order number], [Sku] FROM MyTable ORDER BY [Order number], [Sku]"
will return the records in the order they are entered or appear in a datasheet view. It can only ensure that order number 1 comes before order number 2. In truth it probably will, but it is not something you want to assume.

MajP · Feb 10, 2012

Here is a simple demo. This is how I entered the orders.

Code:

OrderNumber   SKU   entryOrder
ABC9         $12.00    1
ABC9         $11.00    2
ABC9         $5.00     3
ABC9         $8.00     4
ABC9         $13.00    5
ABC10        $21.00    6
ABC10        $15.00    7
ABC10        $22.00    8
ABC10        $17.00    9
ABC10        $3.00     10

Code:

Public Sub IsEntered()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("select * from table1 order by OrderNumber")
  Do While Not rs.EOF
    Debug.Print rs!OrderNumber & " " & rs!SKU & " " & rs!entryOrder
    rs.MoveNext
  Loop
End Sub

in fact all records returned in reverse order.

Code:

ABC10 3 10
ABC10 17 9
ABC10 22 8
ABC10 15 7
ABC10 21 6
ABC9 13  5
ABC9 8   4
ABC9 5   3
ABC9 11  2
ABC9 12  1

and if changing the sql to this
"select * from table1 order by OrderNumber DESC"

Code:

ABC9 13  5
ABC9 8   4
ABC9 5   3
ABC9 11  2
ABC9 12  1
ABC10 3  10
ABC10 17 9
ABC10 22 8
ABC10 15 7
ABC10 21 6

What about no sort order
"select * from table1"
Should this return them as entered? The SKU field just happens to have an index on it.

Code:

ABC10 3  10
ABC9 5   3
ABC9 8   4
ABC9 11  2
ABC9 12  1
ABC9 13  5
ABC10 15 7
ABC10 17 9
ABC10 21 6
ABC10 22 8

Katerine · Feb 10, 2012

To clarify my earlier post, I was assuming that:
[ul]
[li]Sku is either a number or a short text field, and therefore sortable.

(Actually, I assumed it was a short text field, because that makes the coding a little more thorough... if it's a numeric field, then you just remove the quotes; you don't have to add things)

[li]The table is a linking table with only two fields in it: Order number, and Sku.

[li]Every record in that table is unique (no duplicate records with the same Order number AND the same Sku)

[li]You wanted the "first" 10 Sku numbers... either alphabetically or numerically (depending on whether the field is text or number).[/li][/ul]

That is, if your table is like this:

Code:

Order number          Sku
94682                 16-5873
94682                 16-584
94682                 16-5867
94682                 16-5824
94682                 16-5896
94682                 16-5848
94682                 16-5835
94682                 16-5628
94682                 16-5849
94682                 16-5792
94682                 16-5766
94682                 16-5834
94682                 16-59
94682                 16-5725
94682                 16-5766
94682                 16-5888
94682                 16-5712
94683                 16-5712

...then you wanted the code to fill in:

Code:

Order   Sku
94682	16-5628
94682	16-5712
94682	16-5725
94682	16-5766
94682	16-5766
94682	16-5792
94682	16-5824
94682	16-5834
94682	16-5835
94682	16-584

94683	16-5712

(Note: I just added the space before the 94683 order to make it a little easier to read).

As you can see, these are the "first" 10 Sku's... alphabetically. For lack of more description or another field that could be sorted on, I just assumed this was what you wanted.

As others have mentioned, if you want the first ten entered, then you can do that only if there's a pre-existing field, either in this table or in the master tables (again, assuming that this is a linking table), that can be sorted on. Either an autonumber or, better yet, a date field that defaults to "Now()" will do the trick for those purposes.

Katie

bscs1963 · Feb 10, 2012

Thanks everyone for your help. This worked really well.

MajP · Feb 10, 2012

Katerine, I think you are missing the point. That code could work, but there are many instances where it will fail. Besides being overly complicated. This can be done very simply in sql.

If you had something like this

Code:

ID OrderNumber	SKU
1    94682   16-5873
2    94682   16-584
3    94682   16-5867
4    94682   16-5824
5    94682   16-5896
6    94682   16-5848
7    94682   16-5835
8    94682   16-5628
9    94682   16-5849
10   94682   16-5792
11   94682   16-5768
12   94682   16-5834
13   94682   16-59
14   94682   16-5725
15   94682   16-5766
16   94682   16-5888
17   94682   16-5712
18   94683   16-5713
19   94683   16-5711
20   94683   16-5722
21   94683   16-5742
22   94683   16-5719
23   94683   16-5733
24   94683   16-5745
25   94683   16-5703

Then if you want to get the top 5 per order (where TOP means the lowest SKU) then simply

Code:

SELECT 
 tblSKU.OrderNumber, 
 tblSKU.SKU
FROM 
 tblSKU
WHERE 
 tblSKU.SKU In 
 (Select TOP 5 A.SKU from tblSKU as A WHERE A.OrderNumber = tblSKU.orderNumber order by SKU)
ORDER BY 
 tblSKU.OrderNumber, 
 tblSKU.SKU;

results

Code:

OrderNumber	SKU
94682	16-5628
94682	16-5712
94682	16-5725
94682	16-5766
94682	16-5768
94683	16-5703
94683	16-5711
94683	16-5713
94683	16-5719
94683	16-5722

If top means first entered then

Code:

SELECT 
 tblSKU.ID, 
 tblSKU.OrderNumber, 
 tblSKU.SKU
FROM 
 tblSKU
WHERE tblSKU.SKU In 
 (Select TOP 5 A.SKU from tblSKU as A WHERE A.OrderNumber = tblSKU.orderNumber order by ID)
ORDER BY tblSKU.ID, tblSKU.OrderNumber, tblSKU.SKU;

Code:

ID	OrderNumber	SKU
1	94682	16-5873
2	94682	16-584
3	94682	16-5867
4	94682	16-5824
5	94682	16-5896
18	94683	16-5713
19	94683	16-5711
20	94683	16-5722
21	94683	16-5742
22	94683	16-5719

PHV · Feb 10, 2012

You even don't nead to use the TOP predicate:

Code:

SELECT A.OrderNumber,A.SKU
FROM tblSKU AS A INNER JOIN tblSKU AS B ON A.OrderNumber=B.OrderNumber AND A.SKU>=B.SKU
GROUP BY A.OrderNumber,A.SKU
HAVING Count(*)<=5

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

MajP · Feb 10, 2012

And that is probably more efficient because subqueries in Access are not very efficient. Bottom line this really should be done with some SQL query with something to force the sort order. Cannot rely on "table" order to guarantee the output.

Katerine · Feb 10, 2012

(sorry about the formatting issues in my earlier post; it's been a while since I've last done Tek Tips, and I'd forgotten a lot about TGML!)

MajP:
Didn't realize the SQL options would work in Access - I thought that TOP 5 was a TSQL thing. Learn something new every day... thanks.

I did understand the point about sorting, btw. [neutral]

But you'll note that in the code sample (the latter one), it forces a sort by Order number AND Sku... not just Order number. So I can't think of any instance where it would fail (at least, not at its intended function, which is to return the first 10 alphabetical Sku's (again, assuming that Sku is an text field)).

I was also assuming, btw, that there was no autonumber or other field that could be sorted in order of entered date in the table (or either of the FK tables), because that was implied in the original post.

PHV: Could you please do me a favor and explain the logic of that syntax? I understand a lot of SQL but sometimes it still stumps me.

When I look at that, I see either, "Give me all of the records where there are only 5 or fewer records per order number," or possibly, "Give me the 5 greatest SKU numbers for each order number." Clearly, I'm missing something.

)

Katie

Katerine · Feb 12, 2012

PHV: NM... I finally got my head around it. [dazed]

It's just a completely different way of thinking.

Just in case anybody else was having trouble with the logic, here it is:

PHV's original statement:

Code:

SELECT A.OrderNumber,A.SKU
FROM tblSKU AS A INNER JOIN tblSKU AS B ON A.OrderNumber=B.OrderNumber AND A.SKU>=B.SKU
GROUP BY A.OrderNumber,A.SKU
HAVING Count(*)<=5

Using data filched from MajP:

Code:

ID OrderNumber    SKU
1    94682   16-5873
2    94682   16-584
3    94682   16-5867
4    94682   16-5824
5    94682   16-5896
6    94682   16-5848
7    94682   16-5835
8    94682   16-5628
9    94682   16-5849
10   94682   16-5792
11   94682   16-5768
12   94682   16-5834
13   94682   16-59
14   94682   16-5725
15   94682   16-5766
16   94682   16-5888
17   94682   16-5712
18   94683   16-5713
19   94683   16-5711
20   94683   16-5722
21   94683   16-5742
22   94683   16-5719
23   94683   16-5733
24   94683   16-5745
25   94683   16-5703

If we temporarily take out the "GROUP BY" and the "HAVING" from PHV's statement (and sort it for readability), we end up with this ("OrderNumber" is abbreviated for formatting):

Code:

Ord	SKU
94682	16-5628
94682	16-5712
94682	16-5712
94682	16-5725
94682	16-5725
94682	16-5725
94682	16-5766
94682	16-5766
94682	16-5766
94682	16-5766
94682	16-5768
94682	16-5768
94682	16-5768
94682	16-5768
94682	16-5768
94682	16-5792
94682	16-5792
94682	16-5792
94682	16-5792
94682	16-5792
94682	16-5792
94682	16-5824
94682	16-5824
94682	16-5824
94682	16-5824
94682	16-5824
94682	16-5824
94682	16-5824
94682	16-5834
94682	16-5834
94682	16-5834
94682	16-5834
94682	16-5834
94682	16-5834
94682	16-5834
94682	16-5834
94682	16-5835
94682	16-5835
94682	16-5835
94682	16-5835
94682	16-5835
94682	16-5835
94682	16-5835
94682	16-5835
94682	16-5835
94682	16-584
94682	16-584
94682	16-584
94682	16-584
94682	16-584
94682	16-584
94682	16-584
94682	16-584
94682	16-584
94682	16-584
94682	16-5848
94682	16-5848
94682	16-5848
94682	16-5848
94682	16-5848
94682	16-5848
94682	16-5848
94682	16-5848
94682	16-5848
94682	16-5848
94682	16-5848
94682	16-5849
94682	16-5849
94682	16-5849
94682	16-5849
94682	16-5849
94682	16-5849
94682	16-5849
94682	16-5849
94682	16-5849
94682	16-5849
94682	16-5849
94682	16-5849
94682	16-5867
94682	16-5867
94682	16-5867
94682	16-5867
94682	16-5867
94682	16-5867
94682	16-5867
94682	16-5867
94682	16-5867
94682	16-5867
94682	16-5867
94682	16-5867
94682	16-5867
94682	16-5873
94682	16-5873
94682	16-5873
94682	16-5873
94682	16-5873
94682	16-5873
94682	16-5873
94682	16-5873
94682	16-5873
94682	16-5873
94682	16-5873
94682	16-5873
94682	16-5873
94682	16-5873
94682	16-5888
94682	16-5888
94682	16-5888
94682	16-5888
94682	16-5888
94682	16-5888
94682	16-5888
94682	16-5888
94682	16-5888
94682	16-5888
94682	16-5888
94682	16-5888
94682	16-5888
94682	16-5888
94682	16-5888
94682	16-5896
94682	16-5896
94682	16-5896
94682	16-5896
94682	16-5896
94682	16-5896
94682	16-5896
94682	16-5896
94682	16-5896
94682	16-5896
94682	16-5896
94682	16-5896
94682	16-5896
94682	16-5896
94682	16-5896
94682	16-5896
94682	16-59
94682	16-59
94682	16-59
94682	16-59
94682	16-59
94682	16-59
94682	16-59
94682	16-59
94682	16-59
94682	16-59
94682	16-59
94682	16-59
94682	16-59
94682	16-59
94682	16-59
94682	16-59
94682	16-59
94683	16-5703
94683	16-5711
94683	16-5711
94683	16-5713
94683	16-5713
94683	16-5713
94683	16-5719
94683	16-5719
94683	16-5719
94683	16-5719
94683	16-5722
94683	16-5722
94683	16-5722
94683	16-5722
94683	16-5722
94683	16-5733
94683	16-5733
94683	16-5733
94683	16-5733
94683	16-5733
94683	16-5733
94683	16-5742
94683	16-5742
94683	16-5742
94683	16-5742
94683	16-5742
94683	16-5742
94683	16-5742
94683	16-5745
94683	16-5745
94683	16-5745
94683	16-5745
94683	16-5745
94683	16-5745
94683	16-5745
94683	16-5745

The reason is this. Here's the same thing, also showing the "B" columns. It's returning everything where A>=B:

Code:

SELECT A.OrderNumber,A.SKU, B.OrderNumber, B.SKU
FROM tblSKU AS A INNER JOIN tblSKU AS B ON A.OrderNumber=B.OrderNumber AND A.SKU>=B.SKU
GROUP BY A.OrderNumber,A.SKU, B.OrderNumber, B.SKU

Code:

A.Ord	A.SKU	B.Ord	B.SKU
94682	16-5628	94682	16-5628
94682	16-5712	94682	16-5628
94682	16-5712	94682	16-5712
94682	16-5725	94682	16-5628
94682	16-5725	94682	16-5712
94682	16-5725	94682	16-5725
94682	16-5766	94682	16-5628
94682	16-5766	94682	16-5712
94682	16-5766	94682	16-5725
94682	16-5766	94682	16-5766
94682	16-5768	94682	16-5628
94682	16-5768	94682	16-5712
94682	16-5768	94682	16-5725
94682	16-5768	94682	16-5766
94682	16-5768	94682	16-5768
94682	16-5792	94682	16-5628
94682	16-5792	94682	16-5712
94682	16-5792	94682	16-5725
94682	16-5792	94682	16-5766
94682	16-5792	94682	16-5768
94682	16-5792	94682	16-5792
94682	16-5824	94682	16-5628
94682	16-5824	94682	16-5712
94682	16-5824	94682	16-5725
94682	16-5824	94682	16-5766
94682	16-5824	94682	16-5768
94682	16-5824	94682	16-5792
94682	16-5824	94682	16-5824
94682	16-5834	94682	16-5628
94682	16-5834	94682	16-5712
94682	16-5834	94682	16-5725
94682	16-5834	94682	16-5766
94682	16-5834	94682	16-5768
94682	16-5834	94682	16-5792
94682	16-5834	94682	16-5824
94682	16-5834	94682	16-5834
94682	16-5835	94682	16-5628
94682	16-5835	94682	16-5712
94682	16-5835	94682	16-5725
94682	16-5835	94682	16-5766
94682	16-5835	94682	16-5768
94682	16-5835	94682	16-5792
94682	16-5835	94682	16-5824
94682	16-5835	94682	16-5834
94682	16-5835	94682	16-5835
94682	16-584	94682	16-5628
94682	16-584	94682	16-5712
94682	16-584	94682	16-5725
94682	16-584	94682	16-5766
94682	16-584	94682	16-5768
94682	16-584	94682	16-5792
94682	16-584	94682	16-5824
94682	16-584	94682	16-5834
94682	16-584	94682	16-5835
94682	16-584	94682	16-584
94682	16-5848	94682	16-5628
94682	16-5848	94682	16-5712
94682	16-5848	94682	16-5725
94682	16-5848	94682	16-5766
94682	16-5848	94682	16-5768
94682	16-5848	94682	16-5792
94682	16-5848	94682	16-5824
94682	16-5848	94682	16-5834
94682	16-5848	94682	16-5835
94682	16-5848	94682	16-584
94682	16-5848	94682	16-5848
94682	16-5849	94682	16-5628
94682	16-5849	94682	16-5712
94682	16-5849	94682	16-5725
94682	16-5849	94682	16-5766
94682	16-5849	94682	16-5768
94682	16-5849	94682	16-5792
94682	16-5849	94682	16-5824
94682	16-5849	94682	16-5834
94682	16-5849	94682	16-5835
94682	16-5849	94682	16-584
94682	16-5849	94682	16-5848
94682	16-5849	94682	16-5849
94682	16-5867	94682	16-5628
94682	16-5867	94682	16-5712
94682	16-5867	94682	16-5725
94682	16-5867	94682	16-5766
94682	16-5867	94682	16-5768
94682	16-5867	94682	16-5792
94682	16-5867	94682	16-5824
94682	16-5867	94682	16-5834
94682	16-5867	94682	16-5835
94682	16-5867	94682	16-584
94682	16-5867	94682	16-5848
94682	16-5867	94682	16-5849
94682	16-5867	94682	16-5867
94682	16-5873	94682	16-5628
94682	16-5873	94682	16-5712
94682	16-5873	94682	16-5725
94682	16-5873	94682	16-5766
94682	16-5873	94682	16-5768
94682	16-5873	94682	16-5792
94682	16-5873	94682	16-5824
94682	16-5873	94682	16-5834
94682	16-5873	94682	16-5835
94682	16-5873	94682	16-584
94682	16-5873	94682	16-5848
94682	16-5873	94682	16-5849
94682	16-5873	94682	16-5867
94682	16-5873	94682	16-5873
94682	16-5888	94682	16-5628
94682	16-5888	94682	16-5712
94682	16-5888	94682	16-5725
94682	16-5888	94682	16-5766
94682	16-5888	94682	16-5768
94682	16-5888	94682	16-5792
94682	16-5888	94682	16-5824
94682	16-5888	94682	16-5834
94682	16-5888	94682	16-5835
94682	16-5888	94682	16-584
94682	16-5888	94682	16-5848
94682	16-5888	94682	16-5849
94682	16-5888	94682	16-5867
94682	16-5888	94682	16-5873
94682	16-5888	94682	16-5888
94682	16-5896	94682	16-5628
94682	16-5896	94682	16-5712
94682	16-5896	94682	16-5725
94682	16-5896	94682	16-5766
94682	16-5896	94682	16-5768
94682	16-5896	94682	16-5792
94682	16-5896	94682	16-5824
94682	16-5896	94682	16-5834
94682	16-5896	94682	16-5835
94682	16-5896	94682	16-584
94682	16-5896	94682	16-5848
94682	16-5896	94682	16-5849
94682	16-5896	94682	16-5867
94682	16-5896	94682	16-5873
94682	16-5896	94682	16-5888
94682	16-5896	94682	16-5896
94682	16-59	94682	16-5628
94682	16-59	94682	16-5712
94682	16-59	94682	16-5725
94682	16-59	94682	16-5766
94682	16-59	94682	16-5768
94682	16-59	94682	16-5792
94682	16-59	94682	16-5824
94682	16-59	94682	16-5834
94682	16-59	94682	16-5835
94682	16-59	94682	16-584
94682	16-59	94682	16-5848
94682	16-59	94682	16-5849
94682	16-59	94682	16-5867
94682	16-59	94682	16-5873
94682	16-59	94682	16-5888
94682	16-59	94682	16-5896
94682	16-59	94682	16-59
94683	16-5703	94683	16-5703
94683	16-5711	94683	16-5703
94683	16-5711	94683	16-5711
94683	16-5713	94683	16-5703
94683	16-5713	94683	16-5711
94683	16-5713	94683	16-5713
94683	16-5719	94683	16-5703
94683	16-5719	94683	16-5711
94683	16-5719	94683	16-5713
94683	16-5719	94683	16-5719
94683	16-5722	94683	16-5703
94683	16-5722	94683	16-5711
94683	16-5722	94683	16-5713
94683	16-5722	94683	16-5719
94683	16-5722	94683	16-5722
94683	16-5733	94683	16-5703
94683	16-5733	94683	16-5711
94683	16-5733	94683	16-5713
94683	16-5733	94683	16-5719
94683	16-5733	94683	16-5722
94683	16-5733	94683	16-5733
94683	16-5742	94683	16-5703
94683	16-5742	94683	16-5711
94683	16-5742	94683	16-5713
94683	16-5742	94683	16-5719
94683	16-5742	94683	16-5722
94683	16-5742	94683	16-5733
94683	16-5742	94683	16-5742
94683	16-5745	94683	16-5703
94683	16-5745	94683	16-5711
94683	16-5745	94683	16-5713
94683	16-5745	94683	16-5719
94683	16-5745	94683	16-5722
94683	16-5745	94683	16-5733
94683	16-5745	94683	16-5742
94683	16-5745	94683	16-5745

Now we just need to get the distinct "A" columns, and filter for those records that are duplicated 5 or fewer times:

Code:

Ord	SKU
94682	16-5628
94682	16-5712
94682	16-5725
94682	16-5766
94682	16-5768
94683	16-5703
94683	16-5711
94683	16-5713
94683	16-5719
94683	16-5722

Thanks, PHV, for sharing this... it's always good to learn new ways of programming things!

Sadly, the reason I'd originally asked was that, I'd hoped, if I could understand it, then I could maybe adapt it to let me get details about latest payments (which is something I have to do constantly) without subqueries, but it looks like that's not what this logic is for... I'd still have to use subqueries for that in Access. Ah well... I guess I'm just starting to get spoiled by TSQL.

Anyway, even though I'm not the original poster, I'd still like to thank everybody in this thread for sharing... guess I'm never finished learning. [glasses]

Katie

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Extracting a st number of lines from a table

bscs1963

Programmer

dhookom

Programmer

Katerine

Programmer

MajP

Technical User

MajP

Technical User

Katerine

Programmer

bscs1963

Programmer

MajP

Technical User

PHV

MIS

MajP

Technical User

Katerine

Programmer

Katerine

Programmer

Similar threads

Part and Inventory Search

Sponsor