two dates from different tables into one

[Angi]

I'm trying to create a statement report that has invoices and payments
in the detail. The info is pulled from 3 tables: CompanyMain,
InvoiceMain and Payments. What I need help with is the detail
section...I want it sorted by the transaction date. I've tried
creating an expression DetDate: [OrderDate] Or [PaymentDate], sort
ascending, but that didn't work. The detail section only allows one
field or I can group them by invoices and payments...I need them
together as a group. Can someone help me??

What I need:
Date
4/4/05 Invoice # 5400 150.00
4/5/05 Invoice # 5403 200.00
4/12/05 Payment 150.00
4/18/05 Invoice # 5436 400.00

Fields I would have to use:
[OrderDate] or [PaymentDate]
[InvoiceID] or "Payment" (text)
[AmtRemaining] or [PaymentAmt]

TIA!
FYI: This a multi-group post

 

[Marshall Barton]

I'm trying to create a statement report that has invoices and payments
in the detail. The info is pulled from 3 tables: CompanyMain,
InvoiceMain and Payments. What I need help with is the detail
section...I want it sorted by the transaction date. I've tried
creating an expression DetDate: [OrderDate] Or [PaymentDate], sort
ascending, but that didn't work. The detail section only allows one
field or I can group them by invoices and payments...I need them
together as a group. Can someone help me??

What I need:
Date
4/4/05 Invoice # 5400 150.00
4/5/05 Invoice # 5403 200.00
4/12/05 Payment 150.00
4/18/05 Invoice # 5436 400.00

Fields I would have to use:
[OrderDate] or [PaymentDate]
[InvoiceID] or "Payment" (text)
[AmtRemaining] or [PaymentAmt]

You should post your query's SQL statement so folks can see
what you've got.

About all I can say at this point is that I think you
probably need to use a UNION query in there somewhere.
Possibly something like:

SELECT CompanyID, InvoiceID, OrderDate FROM InvoiceMain
UNION ALL
SELECT CompanyID, "Payment", PaymentDate FROM Payments

 

[Angi]

Marsh,
Sorry about that! I usually do and thought it would only make matters
worse. Here it is:

SELECT CompMain.*, InvoiceMain.InvoiceID, InvoiceMain.OrderDate,
DSum("ExtPrice","InvoiceDetails","Invoiceid =" & Invoicemain.invoiceid)
AS InvTotal, Payments.PaymentAmount, Payments.PaymentDate
FROM Payments INNER JOIN (CompMain INNER JOIN InvoiceMain ON
CompMain.CoID = InvoiceMain.CoID) ON Payments.coid = CompMain.CoID;

I've never done a UNION query, so where do I put the union??

Thanks!

 

[Marshall Barton]

Here it is:

SELECT CompMain.*, InvoiceMain.InvoiceID, InvoiceMain.OrderDate,
DSum("ExtPrice","InvoiceDetails","Invoiceid =" & Invoicemain.invoiceid)
AS InvTotal, Payments.PaymentAmount, Payments.PaymentDate
FROM Payments INNER JOIN (CompMain INNER JOIN InvoiceMain ON
CompMain.CoID = InvoiceMain.CoID) ON Payments.coid = CompMain.CoID;

I've never done a UNION query, so where do I put the union??

Since the Invoices and Payments are kind of independent of
each other, you can not Join the two together. I.e. they
are not related in a parent child relationship, but more of
a sibling relationship with the company as the parent. This
may be appropriate if an individual payment can be made for
multiple invoices.

The UNION query I proposed would be a virtual Transactions
table, which can then be Joined to the company table.

query Transactions:
SELECT CompanyID,
InvoiceID As TransID,
OrderDate As TransDate,
DSum( . . . ) AsTransAmount
FROM InvoiceMain
UNION ALL
SELECT CompanyID,
"Payment",
PaymentDate,
PaymentAmount
FROM Payments

Your new query:
SELECT CompMain.*,
Transactions.TransID,
Transactions.TransDate,
Transactions.TransAmount
FROM Payments INNER JOIN Transactions
ON Payments.CompanyID = Transactions.CompanyID

At least, that's what I think you're trying to achieve.

On another note, I am still trying to unravel the
relationships (or maybe it's a query Join issue?) of your
dependent combo boxes, but not getting much time to
concentrate on it. Did you get my status update email a few
days ago. Please email me with your comments or that you
never received it.

 

[Angi]

Marsh...my new pen pal! <g>
I'm going to try this new query in a bit. I figured I was going to
have to use a query to figure the fields and then pull those through, I
just didn't know how to go about doing it. I'm sure I'll be replying
with more errors!!!

On the other note, I did get your email...sorry for not getting back to
you yet. I'm going over the problems you said I have but, honestly, I
have been focusing on this part for now. Will get to that later today
as the company needs the db by Wednesday. As far as the coding in the
_onchange events, I mentioned some of those in the initial email to
you. I had just added them before I sent it to you as a test....didn't
work! The null issue...yeah, I know. You obviously can't have a
dependent box if the previous is null. I don't like the error but I
was going to try and fix that with some BeforeInsert code. What's even
better is they called me Thursday and I need to add a 4th dependent box
to that form (edges). Nothing like trying to stress me out!!!! Thanks
for all your help on that! You wouldn't believe how appreciative I am
to have another set of eyes that know what they're doing look at it.

Angi

 

[Angi]

Ok...the union query worked great and is pretty cool!! Now the only
problem I have is I need the TransAmt field to be currency format,
payments as negative red. I tried adding the format in the report, but
it won't use it. Also, I didn't need the payments inner join on the
second query. Here are my queries now.

qryTrans
SELECT InvoiceMain.CoID,"Invoice # "& InvoiceMain.InvoiceID AS TransID,
InvoiceMain.OrderDate AS TransDate,
DSum("ExtPrice","InvoiceDetails","Invoiceid =" &
[Invoicemain].[invoiceid]) AS TransAmt FROM invoicemain
UNION ALL SELECT Payments.coid, "Payment", Payments.paymentdate,
Payments.PaymentAmount
FROM Payments;


Statements qry
SELECT CompMain.*, qryTrans.TransID, qryTrans.TransDate,
qryTrans.TransAmt
FROM CompMain INNER JOIN qryTrans ON CompMain.CoID = qryTrans.CoID
ORDER BY qryTrans.TransDate;

 

[Marshall Barton]

Ok...the union query worked great and is pretty cool!! Now the only
problem I have is I need the TransAmt field to be currency format,
payments as negative red. I tried adding the format in the report, but
it won't use it. Also, I didn't need the payments inner join on the
second query. Here are my queries now.

qryTrans
SELECT InvoiceMain.CoID,"Invoice # "& InvoiceMain.InvoiceID AS TransID,
InvoiceMain.OrderDate AS TransDate,
DSum("ExtPrice","InvoiceDetails","Invoiceid =" &
[Invoicemain].[invoiceid]) AS TransAmt FROM invoicemain
UNION ALL SELECT Payments.coid, "Payment", Payments.paymentdate,
Payments.PaymentAmount
FROM Payments;


Statements qry
SELECT CompMain.*, qryTrans.TransID, qryTrans.TransDate,
qryTrans.TransAmt
FROM CompMain INNER JOIN qryTrans ON CompMain.CoID = qryTrans.CoID
ORDER BY qryTrans.TransDate;

All formatting should be done in the text box that displays
the value. The standard Currency format should be pretty
close to what you want, but you can always set the text
box's Format property to a custom format:
$#,##0.00;[RED]($#,##0.00);0.00

I don't understand what you are referring to about not
needing an Inner Join???

 

[Angi]

Ken,
In the post above mine, you said:

Your new query:
SELECT CompMain.*,
Transactions.TransID,
Transactions.TransDate,
Transactions.TransAmount
FROM Payments INNER JOIN Transactions
ON Payments.CompanyID = Transactions.CompanyID

I'm getting the right results so I thought I didn't need the inner join
so didn't put it in. The only two objects in the Statements qry are
the CompMain table and the qryTrans query. Bad??

Also, that's the line I was using in the Format property, but I copied
yours just in case I missed something...still just a number. Do I have
to do something in the qryTrans?

BTW, got the email. Looks great so far. I printed out the new code
and I'm gonna really look over it in a few. I do have a few questions,
but I'll continue the thread we started in Formscoding for that. Thank
you so, so much for all that work!

 

[Marshall Barton]

Ken,
In the post above mine, you said:

Your new query:
SELECT CompMain.*,
Transactions.TransID,
Transactions.TransDate,
Transactions.TransAmount
FROM Payments INNER JOIN Transactions
ON Payments.CompanyID = Transactions.CompanyID

I'm getting the right results so I thought I didn't need the inner join
so didn't put it in. The only two objects in the Statements qry are
the CompMain table and the qryTrans query. Bad??

Also, that's the line I was using in the Format property, but I copied
yours just in case I missed something...still just a number. Do I have
to do something in the qryTrans?

BTW, got the email. Looks great so far. I printed out the new code
and I'm gonna really look over it in a few. I do have a few questions,
but I'll continue the thread we started in Formscoding for that. Thank
you so, so much for all that work!

Ken??? ;-)

In the query that you said you were using:

Statements qry
SELECT CompMain.*, qryTrans.TransID, qryTrans.TransDate,
qryTrans.TransAmt
FROM CompMain INNER JOIN qryTrans ON CompMain.CoID =
qryTrans.CoID
ORDER BY qryTrans.TransDate;

you do have the same INNER JOIN I have. So I still don't
see what you mean by not needing it.

As for the formatting issue, I'll guess that the query
somehow decided that the amount field should be a text
value??? Try converting it back to a currency value:

SELECT CompMain.*, qryTrans.TransID, qryTrans.TransDate,
CCur(qryTrans.TransAmt) . . .

 

[Angi]

Marsh,
Ken...isn't that your middle name??? Ken Snell is helping me out
with another problem...sorry about that!

Ok, the CCur worked, but it made it an expression, so I had to fix all
that. Now the Format property is working and the payments are finally
red...after I went in and made the them negative (DUH!)

As far as the join...I just realized it IS the same except you have the
word payments where I have compmain. Sorry about that too!! Thank you
very much!!!

Ang

 

[Marshall Barton]

Marsh,
Ken...isn't that your middle name??? Ken Snell is helping me out
with another problem...sorry about that!

Ok, the CCur worked, but it made it an expression, so I had to fix all
that. Now the Format property is working and the payments are finally
red...after I went in and made the them negative (DUH!)

As far as the join...I just realized it IS the same except you have the
word payments where I have compmain. Sorry about that too!! Thank you
very much!!!

Great, I'm really glad we've got all that cleared up.