Combining two records.

[Dragon]

I have a table with the following fields:

Date
Commodity
Price
Buyer
Seller

In the first record, the data looks like this:

Date: 8/20/2003
Commodity: Banana
Price: 1.1500
Buyer: Fred
Seller

In the second record, the data looks like this:

Date: 8/20/2003
Commodity: Banana
Price: 1.1500
Buyer:
Seller: John

I would like to be able to combine these records (that are within the same table) into one record (within a different table) that looks like this:

Date: 8/20/2003
Commodity: Banana
Price: 1.1500
Buyer: Fred
Seller: John

As long as the date, commodity and price match, combine them. If one of these fields (date, commodity or price) don’t match, I wouldn’t want the records combined.

Any suggestions? Thanks.

 

[dcx693]

I assume all records are in the same table. If you've got a primary key field like an autonumber ID or some transaction ID in this table, then it's possible by joining the table to a copy of itself. (If you don't have one, perhaps you can add one.) Join the original to the copy via the first three fields and specify a criteria where the primary key field in not equal (so you don't match up the record to itself). Then run an update query on it and set the buyer of one record equal to the seller of the other record.

 

[Jack Cowley]

I would suggest that you set up three tables as the way you are doing it is the hard way, in my humble opinion....

tblCommodities
CommodityID (PK and autonumber)
CommodityName
CommodityDate
Price

tblBuyersSellers
BuyerSellerID (PK and autonumber)
CommodityID (FK)
BuyerSellerName
BuyerOrSellerID (FK)

tblBuyerOrSeller
BuyerOrSellerID (PK and autonumber)

tblBuyerSeller is just a lookup (it would have only two records, Buyer and Seller) so you know in the tblBuyersSellers which is the Seller and which is the Buyer.

This my look complex, but it will be much easier to get your data vs. the way you are doing it now.

Jack

 

[Dragon]

Thank you for your help and suggestions.

The data is coming to me in a single table so I have no control over that.

Since the table is already populated with data when I get it, I need to manipulate it afterwards. Currently, I am running some ugly programming that throws the data into Excel (where I am a bit more comfortable) and I run a macro there. The macro sorts and compares each record (or row) and adds the seller or buyer information to the appropriate record. It then throws the data back into Access where it is appended to a table.

It is as ugly as it sounds but it works.

I would preferably like to do the processing within Access if I can. I am by no means an experienced Access programmer so feel free to use really small words.

Again, all this data resides in one table and I can’t change that. I need to be able to compare records within a table and merge records that meet specific criteria.

Again, thank you for your assistance.

 

[Jack Cowley]

You have a mess on your hands. If you are not familiar with Access and Access code then I would suggest you stick with your current solution...unless you want to learn Access. What you want to do goes against most database principals so I do not understand why your company insists on doing it this way, but it is not for me to judge. Follow some of the suggestions from dcx693 as that should get you started...

Good luck with this!

Jack

 

[Dragon]

Thanks. Yeah, I know this is ugly. It’s not my company that is providing this data. It’s an outside source that, unfortunately, we have little or no control over.

Thanks for trying anyway.

 

[neileg]

As an accountant I have to hack about with other people's data all the time!

The way I would tackle this is as follows. It's not nice, it's not clever, but it should work.

First you need an ID that will single out the transactions. So you simply concatenate Date + Commodity + Price to give another field in a query and include all the original fields too. This should give you a common field for buyer and seller records.

Now take this query and create three queries based on this.
1) Extract Composite ID, Date, Commodity, Price Make this a unique values query so you get one record per transaction
2) Extract Composite ID, Buyer where Buyer is not null
3) Extract Composite ID, Seller where Seller is not null

Now create a further query and bring in the three queries you just created and join them on the Composite ID. Extract the fields you need, ie Date Commodity Price Buyer Seller

Hope this is clear. If not, let me know.

 

[Dragon]

Thank you Neil. I will give this a go.

 

[Dragon]

Thanks to Neil, I think I got this figured out.

What I did was create 4 queries.

The first query contains all the fields of the table plus a NEW field that is formatted like this:

Expr1: [DATE] & [number] & [COMMODITY] & [PRICE] & [FIRM] & [VOLUME] & [FIRM 2]

It’s concatenates the critical fields into a sort of proprietary name.

Those are the fields that need to be identical for the records to be a match.

The second query has the NEW field and all the fields in the table except the BUYER and SELLER

The third query has the NEW field and the BUYER (is not null)

The forth query has the NEW field and the SELLER (is not null)

The fifth query joins the 2nd, 3rd and 4th queries together.

All I need to do is filter duplicates and I am left with combined record.

I hope I explained that okay.

I’m sure some Access guru will choke at the site of this but I’m happy. It works!

Again, thanks to all.

 

[neileg]

Pleased my idea worked. I suggested making your second query a unique values query which would remove the need to filter your final query.

It doesn't matter if Access purists like the approach or not if it solves your problem!

 

[Dragon]

Pleased my idea worked. I suggested making your second query a unique values query which would remove the need to filter your final query.

It doesn't matter if Access purists like the approach or not if it solves your problem!

Yes, I did that. Thanks again for the help.