Join Problem

[wizcow]

Join Brain Bender

Sorry in advance for the length of this post.

In my db I have an Invoice form and a Return form.

What I want to do is output archived data from these two forms into a report.

The report is sorted by the employee who did the sale and by payment type.

The report prints the name of the employee and under each employee the payement types and amounts are listed.
Here is a brief example...

Employee1
visa $25.00
cash $ 5.00
invoice total $30.00

Employee2
visa $12.00
cash $ 0.00
invoice total $12.00

I have this part working.
------------------------------------------------------------------

I want to add a 'return' total, with the total, like this..

Employee1
visa $25.00
cash $ 5.00
return total $ 2.00
invoice total $28.00

This is what I have tried..

I have a query based on the invoices
I have a query based on the returns that totals the returns for each employee.
I have a union query that groups the invoice/return dates and employees together.

I based my report on a query based on these three querys as I am trying to get the invoice data and return data together in one query.

I feel I am having trouble with left and right joins, as I am not getting the right data back.

If you have had the patience to read all this, thank you.
I'm hoping someone can see what is going wrong here.

Tom

 

[Len Boorman]

get confused with left and right sometimes myself so here is an alternative method perhaps

Set up a table with all the fields from payment query and additional field returns

First query to get payment type Okay but then append this data to a table.

Run your returns query and update the returns field

Should now be able to query the table and do the calc's as required.
Do not forget to clear all records each time you run the sequence.

Not exactly answering your question I know but maybe an alternative.

HTH

Len B

 

[wizcow]

Thank you both for your replys.

Len, I think I would like to accomplish this using just querys if I can.

Pat, this is very close to what I would like.
The problem now is that the Invoice Amount and Return Amounts are now in the same column and I have no way of knowing which is a return and which is an invoice.

Thanks again
Tom

 

[Len Boorman]

wizcow

No problem using Pat's solution. Learnt something there myself

Didn't like my solution a lot myself.

Len B

 

[wizcow]

Ah...

Its coming to me now!

Thanks again Pat.

Tom

 

[wizcow]

I do have one more question.

How do I get the 'Return Total' to be negative?
Can I do some formatting in SQL. I'd like to make it red too if I can.

Thanks again
Tom

 

[wizcow]

Pat

How you made any sense out of my above post, I do not know.

However once again you have solved the entire problem that has been vexing me for weeks.

Thank you
Tom