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!!!

 

[John Spencer]

Assumptions:
Two records for every bespokeID - one buyer, one seller

SELECT A.BespokeID, A.Price as Buyer, B.Price as Seller
FROM YourQuery as A INNER JOIN YourQuery As B
ON A.BespokeID = B.BespokeID
WHERE A.Price is not null and B.Price Is Not Null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks John, that worked perfectly. Now I have another problem and is stated
on your assumption. Some times there is only one record (buyer or seller
price) for a single bespoke. So those bespokes that don't have 2 records
don't appear. Is there any way to fix that??

Thanks a lot!

 

[Guest]

Seems to me that what you are looking for is a CrossTab Query. Have you
tried that?

Dale

 

[John Spencer]

Not sure, you might try dropping the WHERE clause and see if that gives you
what you want.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..