A
Amit
MS Access 2K, Windows XP
====================
Hi,
I have a Union query with the following columns: SponsorID, SponsorTypeID
and SponsorName. If this were a table, then [SponsorID + SponsorTypeID] would
form the PK, as two different kinds of sponsors can have the same ID.
I want to use this query as the source for a combo-box, and would like
another column that would be a sequential numbering (similar to autonumber)
of all the records in the query.
What I've tried so far:
1. Googled and searched the NGs, but didn't come up with an answer that fits
my needs.
2. I coded the following query based on some replies in the NG, but it
doesn't quite work, as I don't have a column in the original union query with
unique ID:
SELECT quniSponsorType.*,
(Select Count(*) FROM quniSponsorType As A
WHERE A.SponsorID <=quniSponsorType.SponsorID) AS SerialNo
FROM quniSponsorType;
I get some duplicate values in the SerialNo column, as there are duplicate
values for SponsorID in the union query.
Will appreciate any help with this.
Thanks!
-Amit
====================
Hi,
I have a Union query with the following columns: SponsorID, SponsorTypeID
and SponsorName. If this were a table, then [SponsorID + SponsorTypeID] would
form the PK, as two different kinds of sponsors can have the same ID.
I want to use this query as the source for a combo-box, and would like
another column that would be a sequential numbering (similar to autonumber)
of all the records in the query.
What I've tried so far:
1. Googled and searched the NGs, but didn't come up with an answer that fits
my needs.
2. I coded the following query based on some replies in the NG, but it
doesn't quite work, as I don't have a column in the original union query with
unique ID:
SELECT quniSponsorType.*,
(Select Count(*) FROM quniSponsorType As A
WHERE A.SponsorID <=quniSponsorType.SponsorID) AS SerialNo
FROM quniSponsorType;
I get some duplicate values in the SerialNo column, as there are duplicate
values for SponsorID in the union query.
Will appreciate any help with this.
Thanks!
-Amit