G
Guest
I have a complicated query that pulls information from several linked tables.
It works great but about one record on each fourth or fifth page is either
doubled in value or has data that is from the twilight zone. This report runs
about 70 pages so I didn't notice the problem until I accidentally ran the
report twice in succession and found the totals didn't match. If anyone can
weed through this and give me some kind of idea on what is causing the
problem or how to solve it, I would be very grateful. Thanks in advance.
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT DISTINCTROW invtax.taxrate, invtax.staxcode, Cms1.TaxCode,
invoice.order, invoice.trans_id, IIf([lastname]="",[company],[lastname]) AS
Name, invoice.inv_date, invoice.merch, invtax.taxable,
IIf(invtax.exempt=0,0,invtax.exempt-invoice.shipping) AS NTX,
invoice.shipping, invoice.stax, invoice.amount
FROM invtax RIGHT JOIN (((invoice INNER JOIN Invoice1 ON (invoice.trans_id =
Invoice1.trans_id) AND (invoice.order = Invoice1.order)) INNER JOIN Cms1 ON
invoice.order = Cms1.CMSID) INNER JOIN (cms INNER JOIN cust ON cms.custnum =
cust.custnum) ON Cms1.CMSID = cms.order) ON invtax.trans_id =
Invoice1.trans_id
WHERE (((invoice.inv_date) Between [StartDate] And [EndDate]) AND
((invtax.taxtype)="S"))
ORDER BY Cms1.TaxCode, invoice.order, invoice.trans_id;
It works great but about one record on each fourth or fifth page is either
doubled in value or has data that is from the twilight zone. This report runs
about 70 pages so I didn't notice the problem until I accidentally ran the
report twice in succession and found the totals didn't match. If anyone can
weed through this and give me some kind of idea on what is causing the
problem or how to solve it, I would be very grateful. Thanks in advance.
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT DISTINCTROW invtax.taxrate, invtax.staxcode, Cms1.TaxCode,
invoice.order, invoice.trans_id, IIf([lastname]="",[company],[lastname]) AS
Name, invoice.inv_date, invoice.merch, invtax.taxable,
IIf(invtax.exempt=0,0,invtax.exempt-invoice.shipping) AS NTX,
invoice.shipping, invoice.stax, invoice.amount
FROM invtax RIGHT JOIN (((invoice INNER JOIN Invoice1 ON (invoice.trans_id =
Invoice1.trans_id) AND (invoice.order = Invoice1.order)) INNER JOIN Cms1 ON
invoice.order = Cms1.CMSID) INNER JOIN (cms INNER JOIN cust ON cms.custnum =
cust.custnum) ON Cms1.CMSID = cms.order) ON invtax.trans_id =
Invoice1.trans_id
WHERE (((invoice.inv_date) Between [StartDate] And [EndDate]) AND
((invtax.taxtype)="S"))
ORDER BY Cms1.TaxCode, invoice.order, invoice.trans_id;