update query on due dates?

[compucoyle]

ok. i have fought this till i'm blue in the face. and tried so many paths i'm hopelessly confused.i have run straight into the wall of stupid.
i'm working with a canned software that allows acces to go out and create queries and reports on it. following is a mini sample table of the thing giving me trouble.

apply to # due date document type amount
1234 05/01/01 i [invoice] 10.00
1234 05/10/01 p [payment] -2.00
1234 05/11/01 p -1.00
4321 02/01/00 i 50.00
4321 03/11/00 p -5.00
4321 03/20/00 p -1.00

that due date mess is my hang up. i've used datediff to come up with aging days, but because the software applies a due date of the date of a payment as the default of that field, my invoice is not coming up with a correct balance due on it. i need the grouping by apply to #, use the invoice due date as the date to use for my calculation and the net balance on the invoice. my result should be:
1234 05/01/01 7.00
4321 02/01/00 44.00
do i replace payment due dates for that particular apply to with the invoice due date? if so, how? or can i get the thing to only look at the invoice due date and yet use the rest of the data?
sorry for length on this.

 

[MichaelRed]

Without actually making the table and testing (I do not have time at the moment), I would start with a crosstab, grouping on the "Apply to". This should should show each account with the net of additions and subtractions.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[compucoyle]

that works fine for adding all the apply to's together. but i'm still stuck with doofy aging showing up because of the different due dates on the invoice vs the payments on it. can a crosstab query also be a maketable query? maybe i can create a new table summarizing all the apply to amounts and only showing the invoice due date? i got that one to show up first by telling the query to sort by apply to number first, then by invoice type, thus putting the invoice apply to data first.

 

[MichaelRed]

MakeTable <> CrossTab. However you can use the CrossTab as the &quot;Source&quot; for the MakeTable. The &quot;Safe&quot; way to do this is for the MakeTame to specify &quot;*&quot; as the field list, as other approaches tend to be somewhat MESSY about the data (fields) in the target table.

Two queries - called layering?


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over