Select SQl query for more then 1 record

[mstrcmtr]

There are 5 Accounts in a Table and want to get all sale transactions from sale table in one cursor of all five accounts through Select SQL .

e.g.
Accounts Table Name = [Acnts]

Sale Table Name = [SaleH]

Record1 Code = [00001AAA]

Record2 Code = [00002BBB]

Record3 Code = [00003CCC]

Record4 Code = [00004DDD]

Record5 Code = [00005EEE]

 

[Olaf Doschke]

The typical table structure of accounts and sales is having two tables accounts and sales.

To get all sales you only do SELECT * FROM sales, you may want to sort by account, but that even doesn't require a join or where clause.

What you give is two table names, so far so good. It's totally unclear to me, what you mean with RecordN Codes. Of which table?

So far you would only need to do

Select * From SaleH ORDER By Account && <--- adapt the field name within the [SaleH] table, which denotes the account of the sale transaction

You might want to sum, you might want to order by transaction datetime, but that's not clear from your question and as you only give table names, we can't help you with details about that, not knowing fields of the tables.

Bye, Olaf.

 

[mstrcmtr]

Here requirement is merging of all transaction of all selected accounts selected by user to create 1 ledger of all selected accounts

so what technique will be applied to get all this merging

 

[mjcmkrsr]

Hi mstrcmtr,

You need to link the two tables on a common field (e.g. cID) and write a SQL statement similar to this

SELECT * FROM Acnts, SaleH JOIN SaleH ON Acnts.cID = SaleH.cID ORDER BY Acnts.cID WHERE ... (you add more filter criteria) INTO ...

hth

MK

 

[Olaf Doschke]

You'll sum() group by account:

SELECT account, SUM(amount) FROM SalesH GROUP BY account ORDER BY account

Again, not knowing your fields that's just the general idea. You might want to know the time span covered. Something along the lines of

SELECT account, SUM(amount), MIN(transactiondate) as startdate, MAX(transactiondate) as enddate FROM SalesH GROUP BY account ORDER BY account

Bye, Olaf.