Dear TT:
Is your accounting here open item. Are payments specifically allocated to
orders and their invoices. If so, then a payment may also have to be split
between 2 items.
If not, making an appearance that the payment on 1/5/05 was specifically for
the order on 1/3/05, and the payments on 1/8/06 and 3/2/06 were specifically
for the order on 12/5/05 is actually deceptive. This question also amounts
to asking whether each payment record is specifically related to a given
order (order date). To a large degree your table structure would tell me
this, but you have shown nothing of that. Do you actually split payments
between orders, assigning part of a payment to cover one order, and another
portion of that same payment to cover another order.
And, how do you treat two paymants made on the same date?
If you simply want to list order dates in one column and payment dates in
another, and if there is no association across the rows of your report
between orders and payments, then you could just Rank the orders by date and
the payments by date and join them on that. However, there are some tricks
to doing this:
- The ranking must be unique within each Item. A tie breaker needs to be
supplied beyond order date, which would not always be unique. There is
probably no reason why there could not be 2 or more orders on the same date
for the same Item. Break the tie with something like OrderNumber (my
suggestion). Same thing for uniqueness of payments. Break the tie with
something, perhaps the CheckNumber.
So if, two checks arrive the same day for the same Item, the one with the
lower check number would go first. There a slight risk of getting two
checks for the same Item on the same date with the same check number. They
could be on two different accounts! But this is sufficiently unlikely you
could probably get away with it. However, if you are logging the account
number on which the check is drawn, then adding this would make it almost
bullet proof for uniqueness. There's still the wild possibility of getting
two checks on the same date with the same account number, but on different
banks. I know, that's really wild. But thinking of the nearly impossible
is why I get the big bucks (yeah, right). So, if you have the bank routing
number, too, include that. I think it is now unique!
- Once you have a unique ranking on orders and another on payments within
each Item, you can JOIN on that ranking, as well as sorting the overall by
Item and Rank. However, you can never tell when the number of orders may be
more than the number of payments, or vice versa. There is likely to be some
of each. So the JOIN is what we call FULL OUTER. Access doesn't handle
this directly! You would need a UNION ALL of two JOINs, both LEFT and
RIGHT, of the queries that add the rankings.
I expect this general overview of the situation may not be enough to get you
through it. If you are an expert query coder, then perhaps this outline is
enough. If not, and you need some help with it, please post what is the
data you have for orders and payments, with table or query names, and
columns. You don't need to list all the columns, just the ones relevant to
what we have discussed, including the tie breaking columns such as I have
recommended. I can probably help you forward from there in 3 steps or so.
What do you think? I need to know first if you understand what I've said so
far. I prefer not to just do it for you, but to show you how it is done
step by step, and have you absorb it as we go.
Tom Ellison
