How to join two fields by filling in Null values???

[Guest]

Hello,
I have a query that basically shows a few records with the recordID being
the first column, and then two prices (Buyer price second column and Seller
Price the third one).
It looks like this:
BespokeID Buyer Seller
Curro 45
Curro 47
Bianca 12
Bianca 9

The ID values (Curro for example) repeat because the price from the buyer
and the seller are already taken and separated from a previous table where
they are both on the same column. The records on that table would look like
this:
BespokeID Buyer/Seller Price
Curro Buyer 45
Curro Seller 47
Bianca Seller 12
Bianca Buyer 9
So on the query I just created two fields (Buyer and Seller) that take
respectively the price value that corresponds to a buyer, and to a seller.
Wich returns what is shown on the first table/example.

My question is: how do I make another query, where both buyer and seller
prices are placed on the same row (for the corresponding BespokeID)?? That
is, I would like it to give me this:
BespokeID Buyer Seller
Curro 45 47
Bianca 9 12
Instead of having two lines, one with the buyer price and a null value for
seller, and another one with a null value for buyer and the price of the
offer.
I need to to this without having to creating two different fields in the
original table where everything comes from (example 2) if possible...

Thanks a lot for any possible advise!!!

 

[Marshall Barton]

I have a query that basically shows a few records with the recordID being
the first column, and then two prices (Buyer price second column and Seller
Price the third one).
It looks like this:
BespokeID Buyer Seller
Curro 45
Curro 47
Bianca 12
Bianca 9

The ID values (Curro for example) repeat because the price from the buyer
and the seller are already taken and separated from a previous table where
they are both on the same column. The records on that table would look like
this:
BespokeID Buyer/Seller Price
Curro Buyer 45
Curro Seller 47
Bianca Seller 12
Bianca Buyer 9
So on the query I just created two fields (Buyer and Seller) that take
respectively the price value that corresponds to a buyer, and to a seller.
Wich returns what is shown on the first table/example.

My question is: how do I make another query, where both buyer and seller
prices are placed on the same row (for the corresponding BespokeID)?? That
is, I would like it to give me this:
BespokeID Buyer Seller
Curro 45 47
Bianca 9 12
Instead of having two lines, one with the buyer price and a null value for
seller, and another one with a null value for buyer and the price of the
offer.

SELECT B.BespokeID, B.Price As Buyer, S.Price As Seller
FROM originaltable As B INNER JOIN originaltable As S
ON B.BespokeID = S.BespokeID
WNERE B.[Buyer/Seller] = "Buyer"
AND S.[Buyer/Seller] = "Seller"