How to include a column with sequential numbering in a Union Query

[Guest]

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

 

[Marshall Barton]

MS Access 2K, Windows XP
====================
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.

I think it would be more like:

WHERE (A.SponsorID <=quniSponsorType.SponsorID)
AND (A.SponsorTypeID <= quniSponsorType.SponsorTypeID)
) AS SerialNo

 

[Guest]

MS Access 2K, Windows XP
====================
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.

I think it would be more like:

WHERE (A.SponsorID <=quniSponsorType.SponsorID)
AND (A.SponsorTypeID <= quniSponsorType.SponsorTypeID)
) AS SerialNo

Hi Marshall,

I tried that and it didn't work either. Here are some actual values for the
2 columns in the query (SponsorID, SponsorTypeID) that might help with the
query code:

SponsorID SponsorTypeID
4 4
4 2
5 2
5 4
10 3
11 3
12 3
13 3
16 1
21 1
559 1
560 1
..... ....

Based on these values, I get duplicates in the SerialNo column using either
code. If you have any ideas, please let me know. I think the issue is that
neither of the two columns (SponsorID and SponsorTypeID) have unique values.

Thanks!

-Amit

 

[Marshall Barton]

MS Access 2K, Windows XP
====================
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.

I think it would be more like:

WHERE (A.SponsorID <=quniSponsorType.SponsorID)
AND (A.SponsorTypeID <= quniSponsorType.SponsorTypeID)
) AS SerialNo

Hi Marshall,

I tried that and it didn't work either. Here are some actual values for the
2 columns in the query (SponsorID, SponsorTypeID) that might help with the
query code:

SponsorID SponsorTypeID
4 4
4 2
5 2
5 4
10 3
11 3
12 3
13 3
16 1
21 1
559 1
560 1
.... ....

Based on these values, I get duplicates in the SerialNo column using either
code. If you have any ideas, please let me know. I think the issue is that
neither of the two columns (SponsorID and SponsorTypeID) have unique values.

Sorry Amit, try this one

WHERE (A.SponsorID < FreightCarrierZones.SponsorID)
OR (A.SponsorID = quniSponsorType.SponsorID
AND A.SponsorTypeID <= quniSponsorType.SponsorTypeID)

 

[Guest]

Thanks, Marshall. That worked beautifully!

-Amit

Amit wrote:
MS Access 2K, Windows XP
====================
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.


I think it would be more like:

WHERE (A.SponsorID <=quniSponsorType.SponsorID)
AND (A.SponsorTypeID <= quniSponsorType.SponsorTypeID)
) AS SerialNo

Hi Marshall,

I tried that and it didn't work either. Here are some actual values for the
2 columns in the query (SponsorID, SponsorTypeID) that might help with the
query code:

SponsorID SponsorTypeID
4 4
4 2
5 2
5 4
10 3
11 3
12 3
13 3
16 1
21 1
559 1
560 1
.... ....

Based on these values, I get duplicates in the SerialNo column using either
code. If you have any ideas, please let me know. I think the issue is that
neither of the two columns (SponsorID and SponsorTypeID) have unique values.

Sorry Amit, try this one

WHERE (A.SponsorID < FreightCarrierZones.SponsorID)
OR (A.SponsorID = quniSponsorType.SponsorID
AND A.SponsorTypeID <= quniSponsorType.SponsorTypeID)