Line Numbering (Again)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

byeo - 01 Nov 2007 15:56 GMT

I saw the post from DB Girl about line numbering in a query which is almost
exacly what I want to do except the solution given isn't working for me...

What I want to get is:

VENDOR_NAME    VENDOR_SITE_CODE SITEID (generated number)
Bobs Supply    Charlotte            1
Bobs Supply    Houston            2
Bobs Supply    Phoenix            3
Jims Materials    San Diego            1
Franks Raw Goods    Charlotte            1
Franks Raw Goods    Charlotte            2

I am querying the table that stores my “sites” and am using this as a
subquery expression:

Expr1: (SELECT COUNT(*) FROM Supplier_Site_Load 20071031 AS T WHERE
T.VENDOR_NAME = Supplier_Site_Load 20071031.VENDOR_NAME And
T.VENDOR_SITE_CODE <= Supplier_Site_Load 20071031.VENDOR_SITE_CODE)

It gives me a “Syntax error in query expression when I run it. I appreciate
any help you can provide.

Thanks,
Bob.

Reply to this Message

Michel Walsh - 01 Nov 2007 16:50 GMT

You can try, as the whole query (in SQL view) :

SELECT a.vendor_name, a.vendor_site_code, COUNT(*) AS siteID
FROM [Supplier_Site_Load 20071031] AS a INNER JOIN [Supplier_Site_Load
20071031] AS b
ON a.vendor_name= b.vendor_name AND
a.vendor_site_code <= b.vendor_site_code
GROUP BY a.vendor_name, a.vendor_site_code

Note that Franks Raw Goods appears twice at Charlotte! The siteID, the
generated value, will then be 2, if this is not a typo, for each of the two
identical rows.

Hoping it may help,
Vanderghast, Access MVP

>I saw the post from DB Girl about line numbering in a query which is almost
> exacly what I want to do except the solution given isn't working for me...
[quoted text clipped - 22 lines]
> Thanks,
> Bob.

Reply to this Message

byeo - 01 Nov 2007 18:37 GMT

Thank you - no that is not a typo, and your answer validated what I suspected
after reading other posts related to the topic. I guess I need a primary key
in table to make it work...

> You can try, as the whole query (in SQL view) :
>
[quoted text clipped - 38 lines]
> > Thanks,
> > Bob.

Reply to this Message