Query for two primary keys on same record

[Sammy25]

We are trying to query an access 2000 database for records belonging to two different primary key values for the same record in the result.

 

[PHV]

Any chance you could post more info on your database schema with some input samples and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Sammy25]

The primary key represents a combination of three seperate elements. However we are endeavouring to query for different combinations of any two of the three elements. For example, our ID consists of Year, Industry and Price and we want ot be able to display records containing either Price and Industry across any Year or any other omcbination of the three. The rationale behind this is we want to make side by side comparisons of the combinations.

Does this make sense.

Thanks PHV

 

[lespaul]

it would make more sense if you presented it like this:

I have a table with these fields:

Field1 field2 field3
values values values
values values values
values values values
values values values
values values values
values values values


I want to run a query that gives me this information where the values above are blah blah blah:

Field1 field2 field3
info info info

Leslie

 

[PHV]

Sorry, I don't see where is the problem.
What have you so far and where are you stuck ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Sammy25]

Cool. Example of our database is

Field 1 Field 2
P1-I1-Y1 Data
P1-I1-Y2 Data
P1-I2-Y1 Data
.....

The query result we aim to achieve

Y1 Y2
P1-I1-(Y1&Y2) Data Y1 Data Y2

In the query result example above we have queried across Years when our primary key is defined as a combination of the three elements. Is this possible?

 

[PHV]

Are the length of Px, Iy and Yz fixed ?
If yes, then you may try something like this:
SELECT *
FROM yourTable A INNER JOIN yourTable B
ON Left(A.Field1, 5) = Left(B.Field1, 5)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Sammy25]

Looks good. Will give it a go. Thanks PHV

 

[Sammy25]

Hi PHV. You still around?

When we tried your suggestion (query below) we recieved the message "undefined function 'left' in expression".

SELECT W7_Weighted_MeansTbl.Means, W8_Weighted_MeansTbl.Means
FROM W8_Weighted_MeansTbl INNER JOIN W7_Weighted_MeansTbl
ON Left(W8_Weighted_MeansTbl.ID,7) = Left(W7_Weighted_MeansTbl.ID, 7);

Any suggestions?

 

[PHV]

Which version of access ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Sammy25]

Access 2000

Thanks