Query Question

[trish.martins1984]

I have to keep track of both insured and uninsured invoices at work. I make 2 tables. I also made a query that sum up fees in insured, fees in uninsured, and then totals up fees both insured and uninsured together.

Now the problem I'm having is that it only shows me the totals if both vendors are listed in both tables. How do I show all vendors even if they are only in one table in not the other?

 

[Alc]

One query to fetch all from table A, left join Table B.
One query to fetch all from table B, left join Table A.
One query to union the results of the first two.

 

[trish.martins1984]

One query to fetch all from table A, left join Table B.
One query to fetch all from table B, left join Table A.
One query to union the results of the first two.

But how do I join the two queries?

Sorry i'm kinda new at this!

thank you!

 

[Alc]

No problem, I should have said.
Assuming the queries are called Query1 and Query2

SELECT * FROM Query1
UNION
SELECT * FROM Query2;

Note: Make sure that Queries 1 and 2 have the same columns in the same order (e.g. Person, Date, AmountTo, AmountFrom). Query1 may have more blanks in the AmountTo column while Query2 may have more blanks in the AmountFrom. If the columns are in a different order in each query, the combined result will be off (as I learned the hard way).

 

[trish.martins1984]

Where do I put that? In that criteria section?

I have both my queries and they are both left joined... last part is tough.

Thank you

 

[Alc]

Where do I put that? In that criteria section?

I have both my queries and they are both left joined... last part is tough.

Thank you

1) Create a new query, as you did for the first two, just don't add any tables, etc to it.
2) In design view, go to the SQL view of the query. This should default to a white screen with "SELECT;" written in it.
3) Type in code along the lines outlined e.g.

SELECT * FROM [I]your first query[/I]
UNION
SELECT * FROM [I]your second query[/I];

4) Run this query. It should join the first two - assuming the structures are the same - so that

Person1 1 0
Person2 3 0

and

Person1 0 4
Person2 0 7

become

Person1 1 4
Person2 3 7

 

[neileg]

It would be better to have only one table with a field that indicated if the invoices were insured or not.

However I'm worried that Alc has sent you down the wrong track. Two left join queries will duplicate the records where there is matching data in both tables.

What I would do is:
1) Create a query that pulls all the data from the insured table. Add a calculated field in the query in the form of - Insured:"True"
2) Create a query that pulls all the data from the uninsured table. Add a calculated field in the query in the form of - Insured:"False"
3) Then either create a new table and append the records from both queries to that (creates the single table I advised) or create a union query that joins these two queries.
4) Create an aggregate query based either on the new table or the union query that sums the transactions by vendor and insured status.

 

[Alc]

All the unions I use are from tables where no values are present in both, so I forgot about the duplication aspect.

 

[trish.martins1984]

When I run the query I get a syntax erro.

This is what my joined queries look like

Insured Invoices
Payee; Insured fees; insured discount; total

Uninsured Invoices
payee; uninsured fees; uninsured discount; total

Should they be:

Insured Invoices
Payee; Insured fees; insured discount; total; uninsured fees; uninsured discount; total; grand total

and same for uninsured...

I'm def doing something wrong.

Thank you for your help!

 

[trish.martins1984]

All the unions I use are from tables where no values are present in both, so I forgot about the duplication aspect.

okay, so if i have the same vendor in both the uninsured and insured invoice table i can't use this SELECT UNION function or else it will give me duplicates.. am i correct?

 

[neileg]

When I run the query I get a syntax erro.

Which query?

This is what my joined queries look like

Insured Invoices
Payee; Insured fees; insured discount; total

Uninsured Invoices
payee; uninsured fees; uninsured discount; total

Looks OK

Should they be:

Insured Invoices
Payee; Insured fees; insured discount; total; uninsured fees; uninsured discount; total; grand total

and same for uninsured...

I'm def doing something wrong.

Thank you for your help!

Where is the syntax error?

 

[neileg]

okay, so if i have the same vendor in both the uninsured and insured invoice table i can't use this SELECT UNION function or else it will give me duplicates.. am i correct?

Yes, but it's the two left joins that produces duplicates, not the use of a union.

 

[Rabbie]

SELECT distinct * FROM Query1
UNION
SELECT distinct * FROM Query2;

should stop the duplicates

HTH

 

[trish.martins1984]

It would be better to have only one table with a field that indicated if the invoices were insured or not.

However I'm worried that Alc has sent you down the wrong track. Two left join queries will duplicate the records where there is matching data in both tables.

What I would do is:
1) Create a query that pulls all the data from the insured table. Add a calculated field in the query in the form of - Insured:"True"
2) Create a query that pulls all the data from the uninsured table. Add a calculated field in the query in the form of - Insured:"False"
3) Then either create a new table and append the records from both queries to that (creates the single table I advised) or create a union query that joins these two queries.
4) Create an aggregate query based either on the new table or the union query that sums the transactions by vendor and insured status.

Okay so I went ahead and created a table with all invoices in there. The only thing I added was a column that specifies if an invoice is insured or not.

I also made 2 queries: One with just insured fees and totals and the other just just uninsured fees and totals....

Now how do I combine the two?

Thank you in advance for your help.

 

[ajetrumpet]

The only thing left to do is UNION the two queries...

 

[trish.martins1984]

The only thing left to do is UNION the two queries...

I used the report feature to sum everything up. It breaks it down nicely. Do you think that is just as effective?

 

[ajetrumpet]

If you're talking about the object, yes of course. That's the purpose of a report.

Do you think that is just as effective?

Just as effective as what??

 

[neileg]

The only thing left to do is UNION the two queries...

Unless you want to show this data in a report, in which case you can do the sub totals and grand totals in the report.