Incrementing Counter in Query

[Rawb]

Is there any way to make a "counter" variable in a SQL Query?



For example, I have the following table structure:
Customers

CustID|CustName|Addr1|CreditLimit
1|Rawb's Hoity Toity Imports|123 Street St.|100000
2|Mr. Joe's Pawn Shop|124 Street St.|15000
3|Back-alley Trader's Hovel|100 Back Alley Way|25

Can I get a result like so:

RecordCounter|CustName|Addr1|CreditLimit
1|Back-alley Trader's Hovel|100 Back Alley Way|25
2|Mr. Joe's Pawn Shop|124 Street St.|15000

Obviously, the SQL code for above (without the RecordCounter variable) would be pretty simple:

SELECT [CustName], [Addr1], [CreditLimit]
FROM Customers
WHERE [CreditLimit]<=15000
ORDER BY [CreditLimit]

[Rawb]

OK, I've done some looking into Sub-SELECTs and think I may have found the potential answer to my question.

The problem now is that I can't get the Sub-SELECT to work! I've never used them before so I'm not discounting the possibility that my syntax is wrong, but I keep getting the following error message:

You tried to execute a query that does not include the specified expression 'ShpCustSoldTo' as part of an aggregate function.

My SQL Query is as follows:

SELECT 
    CustShipTo_1.ShpCustSoldTo, 
    CustShipTo_1.ShpCustShipTo, 
    CustShipTo_1.ShpCustName, 
    (
        SELECT 
            COUNT(*) + 1 
        FROM 
            CustShipTo AS CustShipTo_2 
        WHERE 
            CustShipTo_1.ShpCustSoldTo = CustShipTo_2.ShpCustSoldTo AND 
            CustShipTo_1.ShpCustShipTo > CustShipTo_2.ShpCustShipTo 
        ORDER BY 
            CustShipTo_2,ShpCustSoldTo, 
            CustShipTo_2.ShpCustShipTo
    ) AS RowNum 
FROM 
    CustShipTo AS CustShipTo_1 
ORDER BY 
    CustShipTo_1.ShpCustSoldTo, 
    CustShipTo_1.ShpCustShipTo;

[Rawb]

Ummm, wow. It would probably also help to see the Table I'm Querying from too, huh. . .

CustShipTo Table

• ShpCustSoldTo - Foreign Key (to Customers Table). Text (10 chars)
• ShpCustShipTo - Combined with ShpCustShipTo (above) to make the Table's Primary Key. Text (10 chars)
• ShpCustName - The Division/Customer/Warehouse name of the supplied shipping address. Text (50 chars)

There are, of course additional fields, but the three above are the only ones I'm really interested in. The idea is to JOIN the Query with the Customers Table to get a list of all the Customers, their billing records (which are being saved as separate Customer records - with .XX appended to their customer number - for some reason), and shipping addresses so that I can import them into another database.

[weekend00]

Please refer to this example (col1 is unique):
Select RowNum=(Select Count(*) From TableName
Where Col1<=t1.Col1),
Col1, Col2, Col3, Col4
From TableName t1
Order By Col1

[Rawb]

I may not be understanding your SQL correctly but it looks like you're returning a specific row of a result set.

What I want to do is add a new field to my result set. This field will be a number representing the row in the result set.

However I also need this field to only be "unique for certain criteria." Consider the following:

I have an order management Database that's poorly designed. This database has two tables for customers: one for customers/billing information and one for shipping information.

This means that if a customer has two different billing addressed (say, different divisions or subsidiaries), they have to have different customer numbers! As a "workaround," our sales personnel have been adding new "customers" and just appending characters to the customer number (for example: Customer A with Billing Address 1 is CustNo "000001" while Customer A with Billing Address 2 is CustNo "000001.1"). However, even that isn't standardized. Some of the appended chars are ".X" where X is a number. Some are ".X" where X is a letter, and some don't have a "." at all, just letters or numbers added at the end.

What I'm trying to do is split this into 3 tables: One for company info, one for billing info, and one for shipping info. However, to do this, I need to be able to tell what goes with what.

So, I'm trying to come up with a result set that looks like the following:

Old Customer/Billing Table:

CustID|CustName|CustBillName|(Other Fields)
000001|Rawb's Imports|Rawb's Imports|. . .
000001.1|Rawb's Imports, Inc.|Rawb's Imports Warehouse|. . .
000001.A|Rawbs Imports|Rawb's Refurbs|. . .
0000011|RAWBS IMPORTS INC|RAWB'S TRADING POST|. . .

Old Shipping Table:

CustID|CustShipToID|(Other Fields)
000001|000001.1|. . .
000001.1|1.1|. . .
000001.1|1.A|. . .
000001.A|0000012|. . .
0000011|A.0000011|. . .

Customer Matrix (Query Result Set):

OldCustID|OldShipID|NewCustID|* NewBillID|* NewShipID
000001|000001.1|000001|1|1
000001.1|1.1|000001|2|1
000001.1|1.A|000001|2|2
000001.A|0000012|000001|3|1
0000011|A.0000011|000001|4|1

The two starred Fields (NewBillID and NewShipID) are the ones that I need to be "unique for certain criteria." I already know how to get the NewCustID values (Left([OldCustID], 6)).

Also, while the above only shows a single customer, I'd like for all the customers to be there.

And finally, while I know I can do this using temp Tables and VB code, I'd prefer a straight SQL method if at all possible.

[weekend00]

you project is far more complicated than to make a "counter" ( or row number) which my query is for.

Since you have your solution already, there is no benefit to dig into a very complicated query.

[Rawb]

Well, if your SQL Query actually creates a new field in the Query with the row number, then I may actually be able to use it to get the results I want.

I thought it was to return only a specific row of a query based on certain criteria (which didn't really make sense since that's what "LIMIT" (or "TOP n" in Access) is for). . .

I'll take another look at it and see if I can't get it to work.

[Rawb]

WOOHOO!!!! I'm officially 1/3rd the way to my goal!

So it turns out that I was trying to skip a step. You see, before I can "uniquely" number the Shipping Records, I needed to number the Billing Records.

I was able to finally pull that off, by using the following Query:

SELECT 
    [CST2].[CustNo], 
    [CST2].[CustInvNo], 
    [CST2].[CustName], 
    ( 
        SELECT 
            Count([CST].[CustNo]) AS MyCount
        FROM 
            Customers AS CST
        WHERE 
            [CST].[CustNo] LIKE Left([CST2].[CustNo], 6) & "*" AND
            [CST].[CustNo] <= [CST2].[CustNo]
    ) AS [CustRowNum] 
FROM 
    Customers AS CST2 
ORDER BY 
    [CST2].[CustNo], 
    [CST2].[CustInvNo];

This creates a "counter" that resets every time the base customer number (determined by the first 6 characters of the "recorded" customer number) changes.

Step 2: Create a similar Query that does the same for the Shipping Table
Step 3: Find a way to combine the two Queries
Step 4: PROFIT!!!

[weekend00]

Good luck!

[Rawb]

Ladies and gentlemen,

I give you THE FRANKENQUERY!

SELECT 
     CUST2.CustNo AS OldCustID, 
     CUST2.CustInvNo AS OldBillToID, 
     CST2.ShpCustShipTo AS OldShipToID, 
     Left([CustNo],6) AS NewCustID, 
     ( 
          SELECT 
               Count([CUST].[CustNo]) 
          FROM 
               Customers AS CUST 
          WHERE 
               [CUST].[CustNo] LIKE Left([CUST2].[CustNo], 6) & "*" AND 
               [CUST].[CustNo] <= [CUST2].[CustNo] 
     ) AS NewBillToID, 
     (
          SELECT 
               Count([CST].[ShpCustShipTo]) 
          FROM 
               CustShipTo AS CST 
          WHERE 
               [CST].[ShpCustSoldTo] = [CUST2].[CustInvNo] AND 
               [CST].[ShpCustShipTo] <= [CST2].[ShpCustShipTo] 
     ) AS NewShipToID 
FROM 
     CustShipTo AS CST2 
     INNER JOIN 
          Customers AS CUST2 
     ON 
          CST2.ShpCustSoldTo = CUST2.CustInvNo 
ORDER BY 
     CustNo, 
     CustInvNo, 
     ShpCustShipTo;