How do I give a Union Query a "Primary Key"?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have merged two tables together using a union query. The tables I have
merged are linked tables from a SQL Database and cannot be amended.

I the tables have multiple rows with the same "order no." (see example
below). I am using the query to pick up prices on a form, however if you
select a row with an order no. (10) and specific item no. (70) it is picking
up the price from the first row (50) with the "order no" value rather than
the one selected (value should be 105)
ie
Order no Item No Price
10 50 50
10 60 45
10 70 105

Is there any way to get the union query to give each row a unique id
(autonumber).

Thanks

Suzanne
 
You can't get Access to assign an autonumber to each row, unless you turn
the UNION query into a MakeTable or Append and add the records to a table
that has an autunumber field.

However, you should be able to make the records unique in the UNION query by
adding another literal field. Assuming that the records within each SELECT
are unique, you could add a literal value to distinguish them, e.g.:

SELECT [Order No], [Item No], Price, F2, "Table1" As SourceTable
FROM Table1
UNION
SELECT [Order No], [Item No], Price, F2, "Table3" As SourceTable
FROM Table2
UNION
SELECT [Order No], [Item No], Price, F2, "Table3" As SourceTable
FROM Table3;
 
Allen,

Unfotuntaley these Order No's have multiple values in the source tables.
Any other ideas?
Thanks

Suzanne

Allen Browne said:
You can't get Access to assign an autonumber to each row, unless you turn
the UNION query into a MakeTable or Append and add the records to a table
that has an autunumber field.

However, you should be able to make the records unique in the UNION query by
adding another literal field. Assuming that the records within each SELECT
are unique, you could add a literal value to distinguish them, e.g.:

SELECT [Order No], [Item No], Price, F2, "Table1" As SourceTable
FROM Table1
UNION
SELECT [Order No], [Item No], Price, F2, "Table3" As SourceTable
FROM Table2
UNION
SELECT [Order No], [Item No], Price, F2, "Table3" As SourceTable
FROM Table3;

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

SuzanneShah said:
I have merged two tables together using a union query. The tables I have
merged are linked tables from a SQL Database and cannot be amended.

I the tables have multiple rows with the same "order no." (see example
below). I am using the query to pick up prices on a form, however if you
select a row with an order no. (10) and specific item no. (70) it is
picking
up the price from the first row (50) with the "order no" value rather than
the one selected (value should be 105)
ie
Order no Item No Price
10 50 50
10 60 45
10 70 105

Is there any way to get the union query to give each row a unique id
(autonumber).

Thanks

Suzanne
Click to expand...
Click to expand...
 
Tell us more about these tables.

They have no primary key, so, even without the UNION you would still have
this problem? Or each query produces non-distinct groupings? Or you don't
know how to include enough fields in the UNION because they don't match all
tables? Or...?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

SuzanneShah said:
Allen,

Unfotuntaley these Order No's have multiple values in the source tables.
Any other ideas?
Thanks

Suzanne

Allen Browne said:
You can't get Access to assign an autonumber to each row, unless you turn
the UNION query into a MakeTable or Append and add the records to a table
that has an autunumber field.

However, you should be able to make the records unique in the UNION query
by
adding another literal field. Assuming that the records within each
SELECT
are unique, you could add a literal value to distinguish them, e.g.:

SELECT [Order No], [Item No], Price, F2, "Table1" As SourceTable
FROM Table1
UNION
SELECT [Order No], [Item No], Price, F2, "Table3" As SourceTable
FROM Table2
UNION
SELECT [Order No], [Item No], Price, F2, "Table3" As SourceTable
FROM Table3;

SuzanneShah said:
I have merged two tables together using a union query. The tables I
have
merged are linked tables from a SQL Database and cannot be amended.

I the tables have multiple rows with the same "order no." (see example
below). I am using the query to pick up prices on a form, however if
you
select a row with an order no. (10) and specific item no. (70) it is
picking
up the price from the first row (50) with the "order no" value rather
than
the one selected (value should be 105)
ie
Order no Item No Price
10 50 50
10 60 45
10 70 105

Is there any way to get the union query to give each row a unique id
(autonumber).
Click to expand...
Click to expand...
Click to expand...
 
Back
Top