Query occasionally misses orders

[studlength]

Hello. Access 2003
I'm in the testing phase of a new database with tables Customers,
Orders, and Payments. There is a one-to-many relationship from
Customers to Orders and a one-to-many relationship from Customers to
Payments. I created a report that shows each order and each payment
that a customer has made, as well as a running balance. The report is
based on a union query. I have a command button on a form
(frmCreateReports) that creates the report based on the customer
selected from a dropdown menu. It all works great, EXCEPT........
Occasionally, the report misses complete orders that should be
included. I can preview the report time and time again with no
problem, but every now and then the report leaves out orders (even
though I have not changed any data). I've tried to figure out what may
be causing this by retracing my steps but I've had no luck. The last
time it happened I noticed that the order that was left out was the
very first order ever entered (Order number 1). When it happened I
closed the report and tried creating it again. The mistake was still
there. So I closed the report and closed frmCreateReports, reopened
frmCreateReports, clicked on the command button and the report opened
correctly.
I am assuming that the problem is not with the report but rather the
query. Anybody know why a union query would produce inconsistent
results like this? Keep in mind, I'm not changing any of the data, I'm
just goofing around opening different forms, reports, etc and suddenly
the report opens incorrectly. I will keep trying to figure out a
pattern. If I learn anything new, I will repost.
Thanks.

 

[Allen Browne]

Well, there's lots of factors that could be at play here.

First, if you fire this report from a command button on the form where you
enter the invoices, make sure you save the current record first. Add the
line:
If Me.Dirty Then Me.Dirty = False

Next, a UNION query deduplicates. Better to use a UNION ALL, e.g.:
SELECT * FROM Table1
UNION ALL SELECT * FROM Table2;

Another possiblity is that you need to use outer joins. If there are
multiple tables in the query, and one table has no entry in a join field,
the default inner join won't output that record. For example, if your
InvoiceDetail table has a ProductID field (the product in that row of the
invoice) that could be Null (e.g. when charging for labor so there's no
product), you need to use an outer join. Double-click the line joining the 2
tables in the upper pane of query design, and choose option 2 or 3 (the left
or right join.)

Depending how you link payments to invoices, you may also have a problem
with invoices that have no payments (record doesn't show unless you use an
outer join), or multiple payments (record shows on multiple rows and
inflates the total.)

The next possibility is that the criteria is not working as expected. A
major area is the way you handle Nulls, and this is especially important
with outer joins too. See:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

You can also run into problems where you are not careful about data types
(e.g. using wildcards under date or numeric fields), or where calculated
fields. See:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

It will probably take you a while to work through those suggestions.
Hopefully that will solve it for you, and be educational at the same time.

 

[studlength]

Thanks for the reply Allen. The time and effort you and others spend
on this group do not go unnoticed.
The command button is on a seperate form that I set up that contains
only command buttons for creating various reports.
I tried changing the query to UNION ALL. Shortly after that, the
report misfired again. The frustrating part of all this is that I
can't figure out why it works correctly most of the time. Then, with
no changes made to the data or the database, the report suddenly omits
an order. If the problem was repeatable, I could surely come up with a
solution. It appears to be completely random.
Anyway, I'll work through your other possible solutions and hope for
the best. And believe me, this whole project has been extremely
educational. Thanks again.