Combine records in a table

[smoothlarryhughes]

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

[June7]

Will this table have only one L and one S record for each account/CUSIP combination? Is each CUSIP unique to each account?

1. You need a master data source of all accounts.

2. Do a query to retrieve all L records

3. Do a query to retrieve all S records

4. Join queries 2 and 3 to the master

[smoothlarryhughes]

Sometimes there might be more than 1 L and 1 S record for each cusip...the same cusip can be in multiple accounts. any chance you could give me an example of your queries using the sample data that I provided?

[June7]

Simple select queries.

SELECT * FROM table WHERE [L/S] = "L";
SELECT * FROM table WHERE [L/S] = "S";

Then try a query that joins each of those to the master. Do you have a table of all account and CUSIP combinations? If not, build one by query of the sample table to return all combinations used in that table.
SELECT DISTINCT Account, CUSIP FROM table;
Or do you have a table of accounts and a table of CUSIP's? A query that includes both tables without a join clause will create all possible combinations.

Now join the first two to the 3rd on the account and CUSIP fields (compound key join). Join type "Include all records from Query3 ..."