Query to Merge 2 records in same table

[sippyuconn]

Hi

I have a table with each record having 2 rows - 1st is original data, 2nd
row is changed data. I am trying to write a query where I can display the 2
rows as 1 and have the changed data side by side - like below

productid productname price

001 battery 11.00
001 battery 12.00

what I'd like to see from the query is

productid productname originalprice changed price
001 battery 11.00 12.00


Any ideas???

Thanks

 

[Wolfgang Kais]

Hello "sippyuconn".

I have a table with each record having 2 rows - 1st is original data,
2nd row is changed data. I am trying to write a query where I can
display the 2 rows as 1 and have the changed data side by side -
like below

productid productname price
001 battery 11.00
001 battery 12.00

what I'd like to see from the query is

productid productname originalprice changed price
001 battery 11.00 12.00

Any ideas???

Yes, I vae one, BUT You need to have a field that can be used to
decide whether a record is a 1st or a 2nd record, then you could
create two queries from the table, one selecting all 1st records
ans one selecting only 2nd records. Then you could create a third
query based on these two queries that joins them on productid and
productname and that selects productid, productname and price (as
originalprice) from the "1sts query" and price (as [changed price]
from the "2nds query".

 

[John Spencer]

IF you can ASSUME that the new price is larger than the old price. Then
the following would work.

SELECT A.ProductID, A.ProductName
, A.Price as OriginalPrice
, B.Price as ChangedPrice
FROM YourTable as A LEFT JOIN YourTable as B
ON A.ProductID = B.ProductID
AND A.Price < B.Price

Otherwise, you are going to have to tell use what data in a record tells
you that a price is an original price or updated price. And don't say
it is because you see one record before the other. In a database, that
is not reliable. You either need something within a record or some way
to order the records (by date of record creation if you have added that
field to your table or by a sequence number or by some other means)

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

 

[sippyuconn]

Hi Sorry

productid productname price rectype
001 battery 11.00 1
001 battery 12.00 2

1=original
2-= modified record

Hello "sippyuconn".

I have a table with each record having 2 rows - 1st is original data,
2nd row is changed data. I am trying to write a query where I can
display the 2 rows as 1 and have the changed data side by side -
like below

productid productname price
001 battery 11.00
001 battery 12.00

what I'd like to see from the query is

productid productname originalprice changed price
001 battery 11.00 12.00

Any ideas???

Yes, I vae one, BUT You need to have a field that can be used to
decide whether a record is a 1st or a 2nd record, then you could
create two queries from the table, one selecting all 1st records
ans one selecting only 2nd records. Then you could create a third
query based on these two queries that joins them on productid and
productname and that selects productid, productname and price (as
originalprice) from the "1sts query" and price (as [changed price]
from the "2nds query".

 

[Wolfgang Kais]

Hello "sippyuconn".

I have a table with each record having 2 rows - 1st is original data
2nd row is changed data. I am trying to write a query where I can
display the 2 rows as 1 and have the changed data side by side -
like below

productid productname price
001 battery 11.00
001 battery 12.00

what I'd like to see from the query is

productid productname originalprice changed price
001 battery 11.00 12.00

Any ideas???

Yes, I vae one, BUT You need to have a field that can be used to
decide whether a record is a 1st or a 2nd record, then you could
create two queries from the table, one selecting all 1st records
ans one selecting only 2nd records. Then you could create a third
query based on these two queries that joins them on productid and
productname and that selects productid, productname and price (as
originalprice) from the "1sts query" and price (as [changed price]
from the "2nds query".

Hi Sorry

productid productname price rectype
001 battery 11.00 1
001 battery 12.00 2

1=original
2-= modified record

Then it's maybe this (supposed that theTable is the name of the table):
SELECT originaldata.productid, originaldata.productname,
originaldata.price AS originalprice, changeddata.price AS [changed price]
FROM theTable AS originaldata INNER JOIN theTable AS changeddata
ON (originaldata.productname = changeddata.productname) AND
(originaldata.productid = changeddata.productid)
WHERE (((originaldata.rectype)=1) AND ((changeddata.rectype)=2));