Line Numbering (Again)

[Guest]

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.

 

[Michel Walsh]

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

 

[Guest]

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) :


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...

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.