Queries with no records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that is giving totals from three independent tables. A query
with three independent tables is the source of this report. The problem is
that if one of the tables in this query has no records then the guery gives
no records even if the other two tables have some records.
Can anybody syggest a way to overcome this problem?
If there is a way to make a query with no records to return a zero for the
total of a field, that would do.
 
Hi Michalis,

In order to resove the "No Records" problem:

you can change the type of JOIN between your tables.

Right-click on the Join line and choose
Join Properties

instead of option 1, choose option 2 or 3 to show ALL
records in the table that has them

Once you start changing your Joins, you need to be
consistent with the "flow" of the data and change other
Joins appropriately or Access will tell you that you have
"ambiguous" joins...

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 
Hi Christal and thank you for your responce.
The problem is that these three tables from which I get totals for some
fields in my query are not related in any way so there is no join between
them.
Thanks again

Michalis
 
Hi Michalis,

if you can, add fields to your query so you CAN do a Join...

here is a trick on using criteria to simulate outer joins
(although not quite as good)

(condition) or Is Null


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 
Hi Crystal
I have a similar problem.. I have 3 queries and I trying to bring them
together in one query i.e.

qryMonthlyCommission contains "month" and "commission" (there is one record
for each month)
e.g. January $100
February $200
March $150
qryMonthlyInterest contains "month" and "interest" (there is one record for
each month)
e.g. January $50
February $60
March $55
qryMonthlyDue contains "month" and "due" (there is one record for each month)
e.g. January $40
February $30

in my qryMonthlyEarnings I attempt to bring these together: "month"
"commission" "interest" "due"
however it doesnt not include a record for march
e.g. January $100 $50 $40
February $200 $60 $30

but what I want it to do is
e.g. January $100 $50 $40
February $200 $60 $30
March $150 $55 $0

I tried to link the queries and I tried to us "is null" criteria but nothing
works! this is the code for my qryMonthlyEarnings query:

SELECT [Monthly Commission].Month AS MonthName,
Format([MonthName],"mmmm yyyy") AS [Month],
[Monthly Commission].[Sum Of Euro Close Comm] AS Commission,
[Monthly Interest].[Total Interest] AS Financing,
[Monthly Commission]![Sum Of Euro Close Comm]+[Monthly Interest]![Total
Interest] AS [Total Received], [Monthly Due].[Commission Due]
FROM [Monthly Interest], [Monthly Due], [Monthly Commission]
WHERE ((([Monthly Commission].Month)=[Monthly Due]![Month]) AND (([Monthly
Due].Month)=[Monthly Interest]![Month])) OR ((([Monthly Commission].Month) Is
Null) AND (([Monthly Due].Month) Is Null));

Any ideas?

Many thanks!
 
Hi Claire,

The easiest way is to make a table for months and link it to
each of your queries -- show all the records in tblMonths
and just those records in corresponding tables/queries where
the records match using the Join properties

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 
you're welcome, Michalis ;)

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 
Back
Top