SQL / JOIN problem in Access Report !

Hi,

In my Access Report ...
If there is "NO" entry i.e. (no corresponding CODE) in my Data-File,
then even the Master-File entry does not show up in the report.

eg.

Mast-File
Code Op.Bal
1 20
2 25
3 30


Data-File
Code Debit Credit
1 10
1 15
3 40
1 30
3 5


Report
Code Op.Bal
1 15
3 65


In my Access Report ... Code "2" never shows up as there is no entry in
the data file for it.

I have a field in my Access Report which is supposed to display the
final Op.Bal as :

Final Op.Bal = Op.Bal (from the mst-file) + debits - credits.

I am using a query as my record source built using the Query Designer
Grid.

My SQL is very weak ... could someone please post some simple SQL or
guide me how to modify my Query Design.

Hope I've been clear.

Thx & Best Rgds,
Prakash.

re: SQL / JOIN problem in Access Report !

Use an outer join between the two tables instead of an inner join.
Then use NZ or whatever to convert the null to a zero so you can do
math on it. Base your report on the query, not the table.

re: SQL / JOIN problem in Access Report !

Thx Pietlin for the really prompt reply.

I'm real sorry ... but unfortunately I don't know how to write a join.
I'm from a Clipper background and have yet to learn SQL syntax.

Could you or someone else be kind enough to post the SQL here so that I
can modify my code accordingly ?

Thx & Best Rgds,
Prakash.


pietlinden@hotmail.com wrote:[color=blue]
> Use an outer join between the two tables instead of an inner join.
> Then use NZ or whatever to convert the null to a zero so you can do
> math on it. Base your report on the query, not the table.[/color]

re: SQL / JOIN problem in Access Report !

Prakash wrote:[color=blue]
> Hi,
>
> In my Access Report ...
> If there is "NO" entry i.e. (no corresponding CODE) in my Data-File,
> then even the Master-File entry does not show up in the report.
>
> eg.
>
> Mast-File
> Code Op.Bal
> 1 20
> 2 25
> 3 30
>
>
> Data-File
> Code Debit Credit
> 1 10
> 1 15
> 3 40
> 1 30
> 3 5
>
>
> Report
> Code Op.Bal
> 1 15
> 3 65
>
>
> In my Access Report ... Code "2" never shows up as there is no entry
> in the data file for it.
>
> I have a field in my Access Report which is supposed to display the
> final Op.Bal as :
>
> Final Op.Bal = Op.Bal (from the mst-file) + debits - credits.
>
> I am using a query as my record source built using the Query Designer
> Grid.
>
> My SQL is very weak ... could someone please post some simple SQL or
> guide me how to modify my Query Design.
>
> Hope I've been clear.
>
> Thx & Best Rgds,
> Prakash.[/color]

This might help, Prakash:

SELECT M.Code, M.OpeningBalance,
IIF(ISNULL(T.SumDebits),0,T.SumDebits) as Debits,
IIF(ISNULL(T.SumCredits),0,T.SumCredits) as Credits,
M.OpeningBalance + (Debits - Credits) as EndBalance

FROM [Mast-File] M LEFT JOIN
(SELECT Code, Sum(Debits) AS SumDebits, Sum(Credits) AS SumCredits
FROM [Data-File]
WHERE TransDate Between #1/1/05# AND #3/5/05#
GROUP BY Code) T

ON M.Code = T.Code;

(note: I'm assuming your data-file table has transaction dates. If not,
delete that WHERE clause)

Also, this query might not run in Access97 - though it will in Access 2000
or later.

re: SQL / JOIN problem in Access Report !

"Prakash" wrote
[color=blue]
> I'm real sorry ... but unfortunately I don't
> know how to write a join. I'm from a
> Clipper background and have yet to
> learn SQL syntax.[/color]


One of the nice things about Access is that you can use it quite well
without knowing much of anything about SQL. Use the Access Query Builder...
you join table by clicking and dragging a field to the comparable field in
another table that you have added as a data source. Right-click the
resulting join line, and choose the appropriate option so you will see the
main table records even if there is not a related record.

But, a newsgroup is not the place for a basic tutorial on building queries
visually...

Check your local bookstore for a beginner-to-intermediate level Access book:
one I like for true novices with Access is Microsoft Press' _Microsoft
Access Step by Step_, another that goes much deeper is Microsoft Press'
_Access 2003 Inside Out_ by John Viescas.

Or, if you are using Access 2003, in the help pane click "Training" and
follow the links. If you aren't using 2003, the base URL is:
http://office.microsoft.com/en-us/training/default.aspx

You'll find (many) other resources at http://www.mvps.org/access and links
to resources at http://ntaccess.tripod.com.

Larry Linson
Microsoft Access MVP

re: SQL / JOIN problem in Access Report !

DFS & Larry ... Thank you both for your replies.

Larry I have been using the Access Query Builder all along & since I've
been getting by I have'nt really taken the plunge to learn SQL. Yes, I
do own a few Access books but there isn't much on SQL there. These days
I'm not too much ino programming ... mostly marketing, but still
maintaining a few odd installations here & there.

Ok ... I did dbl-click on the join line & have used the 2nd option
where it says :
"Include all records from 'DPATMST' and only those records from
'DPATDAT' where the joined fields are equal."
In fact I tried this before I made a post here ino the NG but still the
Main Table record does not show up.

On examining DFS's code, I see he has made use of the ISNULL function.
Could that have made the difference ?

Unfortunately I'm in the interior and won't have access to my
application for the next 3 to 4 days.

Would one of you please be kind enough to correct the SQL code below to
make it work and also tell me how I can make the same modification into
the Query Builder (maybe using ISNULL if reqd) ... so that I do not
have to type in the SQL. This way maintaining & modifying the code in
future would be significantly easier for me.


SELECT DPATDAT.CODE, DPATMST.COMPANY, DPATDAT.INV_DATE, DPATDAT.REF,
DPATDAT.DEBIT, DPATDAT.DETAILS, DPATDAT.CREDIT, DPATDAT.CLEARED,
DPATDAT.TRAN_NO, DPATDAT.OMIT, DPATMST.ADDR1, DPATMST.ADDR2,
DPATMST.ADDR3, DPATMST.MON_RENT, DPATMST.AGR_FROM, DPATMST.AGR_TO,
DPATMST.OPBAL
FROM DPATMST LEFT JOIN DPATDAT ON DPATMST.CODE = DPATDAT.CODE
ORDER BY DPATDAT.TRAN_NO;


And oh yes ... I am using Access XP (2002).

Once again ... thx so much for your help.
Best Rgds,
Prakash.