fetching data from multiple tables

[Ratib Hussaini]

I have 3 tables, one is parent and two are child tables.
A table named Accounts is parent table and Purchase Journal and Cash Disbursement journal are child tables.
1: Account table contains account information
2: Purchase Journal contains following field.
ID, Date, Explanation, A/C ID, Credit Amount

3: Cash Disbursement Journal contains following field
ID, Date, Explanation, A/C ID, Cash Cr, Inventory Cr, Dr Amt

I want a query to generate following information

A/C ID, Date, Explanation, Credit Amount, Dr Amt

I tried but shows some error. Please help me, Thanks

 

[pr2-eugin]

Hello Ratib, I would like to address a few issues first.. Your Table names and field names must be sorted out, for several reasons..

* Having spaces in Field/Table Names might cause problems, when you are trying to use them in Queries,

* Try to avoid special characters in Field Names.. like A/C

* Rename the field name Date to something like accStartDate, Date is a reserved word and should not be used.

* Try to name each field uniquely, specially if they are between related tables like this.. When you try to "WRITE" a SQL query ambiguous name error would occur.. Also it is very confusing in the above post which Date, Explanation you actually want it to be from.. Is it from the 'Purchase Journal' table or 'Cash Disbursement Journal'?

So, having said that.. It is also unclear what you actually have tried so far, and what error you are getting..

Well, I think what you need is a JOIN.. With the following Query you might be able to get the desired result..

SELECT PurchaseJournal.[AccID], PurchaseJournal.[accStartDate], PurchaseJournal.Explanation, PurchaseJournal.CreditAmount, CashDisbursementJournal.DrAmt
FROM CashDisbursementJournal INNER JOIN PurchaseJournal ON PurchaseJournal.[AccID] = CashDisbursementJournal.[AccID_FK]

I have changed the names.. so you might want to go through it, and adapt it to your design..

 

[Ratib Hussaini]

thank you sir for the reply.and sorry for some confusing points in my question. As you said i made the corrections in fields name. I dont know why you have used AccountID from purchase journal. I think my question is not very clear, I make it much clear by changing number of fields and fields names.
There is a master table named Accounts with following fields
1. AccountID
2. AccountName
3. AccountNumber

There is a child table named PJ with following fields
1. PID
2. AccountID(FK)
3. PDate
4. POID
5. Explanation
6. CrAmount

There is another child table named CD with following fields
1. CID
2. AccountID (FK)
3. CDate
4. POID
5. Explanation
6. DrAmount

Now with these 3 tables I want to create a form (master/subform) the master form fields will be Accounts table fields and a subform which should contain following fields.

TransactionDate, POID,Explanation, CrAmount, DrAmount, Balance (sum(credit)-sum(debit))

the date, POID and explanation should be common fields for both the tables PJ and CD, because the user must know the date and explanation for each CrAmount and DrAmount.
So for this reason I wanted to create a query.
I hope now it is more clear than my previous question. Your help will be highly appreciated. thanks

 

[Ratib Hussaini]

thanks Hartman for the reply, at first I used select query, it was showing some aggregate functions error,, but later I got to know that it wont work by select query. So I tried union instead, it works fine but some errors there, like new transactions comes to the top of other transactions not beneath them, I means the first transaction comes in the last in union result. and second problem is that in Credit field it shows symbols instead of numbers, the code is as below:

select accountDebited as ACname, CDate as TrDate, POID, Explanation, Dr, null as Cr from CDJ
union
select ACName,PDate as TrDate, POID,Explanation,null as Dr,CrAmt as Cr from PJ

As transactions are in different currencies and I haven't wanted currency symbol to be appeared, so in table design for credit and debit fields I used Standard data type, and added a new filed named currency field that shows the symbol instead, so I avoid to show it in my code as it doesnt have anything with the problem.

and yeah another problem is that in child tables in credit debit fields, the amount is only in numbers with no currency symbol as I have used standard data type, but in union in debit field it shows currency symbol as well, and in credit field it shows just some symbols instead of numbers.