Access Table Query to Combine Records

**smoothlarryhughes** · 09-13-2012, 08:46 PM

Is there a way to query this table back to combine records that have like accounts and like cusips?

For example records 1 and 2 have the same account and cusip...can i put them in the same line (Account - CUSIP - AMOUNT L - AMOUNT S - DIFFERENCE)? Also PH9217 does not have an offset...i would just have AMOUNT S be 0. L stands for Ledger S for Statement.

ID account cusip amount L/S
1 PH4056 123456789 500 L
2 PH4056 123456789 600 S
3 PH4055 676767677 1000 L
4 PH4055 676767677 1000 S
5 PH9217 123456789 500.01 L

**smoothlarryhughes** · 09-14-2012, 09:53 AM

any ideas?

the only way i was thinking is maybe query out Ledger items and statement items into 2 different tables and then union them together.

**alansidman** · 09-14-2012, 06:48 PM

I was able to do a self join on the table and get all that had both items but could not get the item 5 to list. Give your idea a shot and let us know if it works or doesn't.

**smoothlarryhughes** · 09-14-2012, 08:26 PM

do you think you could show me your query you created? i tried something i thought might work but was unable to get them on the same line.

**alansidman** · 09-14-2012, 08:45 PM

Please Login or Register  to view this content.

Alan

**smoothlarryhughes** · 09-15-2012, 08:51 PM

This seems to be almost perfect...i think the join has something to do with why item 5 won't show up...bc the query is looking for accounts that are the same. anyone know how i might change this query to show item 5 even if it doesn't have a corresponding "s" amount? this is the result i get using
alan's query.

account cusip Sheet1.amount Sheet1.L/S Sheet1_1.amount Sheet1_1.L/S
PH4055 676767677 1000 L 1000 S
PH4056 123456789 500 L 600 S