combine 2 queries

[smile]

I have 2 queries (one for debit records other for credit records)that I need to put into 1 so I can build a report.

1st query has these columns:

Date
KIO Order number
Sum total

2nd query has these columns:

Date
KPO Order number
Sum total

I need to put them into one query that would work like this:

Date          KPO Order Number    KPO.Sum Toal    KIO Order Number   IO.SumToal
2008.01.01          001                 100
2008.01.02          002                  50                 001             45
2008.01.03                                                  002             114
2008.01.04          003                  54
2008.01.10          004                  30                 003             100

My problem is that first table and second table has no relationship with a third table that would have common data field like date that would be entered using lookup? And that for a certain date one table can have no data like the blanks above.

Then I would build a report and do calculations there

 

[Guus2005]

Apparently they have the date in common.

First create a query which shows all the dates you want in your report.
Something like:

Select 
Date
From 1stQry
union
Select 
Date
From 2ndQry

This is the 3rdQry
Now create a fourth query using the first three. Connect 1stQry and 2ndQry to the date field of 3rdQry. Make sure that you show ALL records from 3rdQry in both cases, creating two outer joins.

Create a report based on this fourth query.

HTH

 

[smile]

Thanks, it works

Would it work with 4 queries? I just realised that I would need to all 2 more queries from separate tables that have field date also.

Select 
Date
From 1stQry,2ndQry
union
Select 
Date
From 3ndQry,4ndQry

Then I would create normal qurie like you said and link all 4 queries date to union querie date field?

 

[Guus2005]

Yes that would work however not the way you describe:

select date from 1stqry
union
select date from 2stqry
union
select date from 3stqry
union
select date from 4stqry

Store this query and proceed like described earlier.

HTH

 

[smile]

I would also like to filter by date interval. Is this ok?

select date from 1stqry
union
select date from 2stqry
union
select date from 3stqry
union
select date from 4stqry
WHERE [date] = Between [enter start date] And [enter end date]

 

[boblarson]

I would also like to filter by date interval. Is this ok?

select date from 1stqry
union
select date from 2stqry
union
select date from 3stqry
union
select date from 4stqry
WHERE [date] = Between [enter start date] And [enter end date]

That would only limit the last query. You'll need a where clause on each sub query (I believe) in order to get what you want.

 

[Guus2005]

That would only limit the last query. You'll need a where clause on each sub query (I believe) in order to get what you want.

Or you can create yet another subquery ...

select A.date from (select date from 1stqry
union
select date from 2stqry
union
select date from 3stqry
union
select date from 4stqry) A
WHERE A.[date] = Between [enter start date] And [enter end date]

I can give you all the answers but you have to understand what you are doing

 

[smile]

I am trying to group 4 queries that are made from 4 tables. Each table has date field with various dates.

I try to build union query that would ash me date interval and show all dates as first column, then I wan't to add extra columns from those 4 queries so they woul be displayed as my first post shows.

The problem is where to build the date filter. I was thinking to make it in union query. This kind of query can be built only with code, but my SQL knowledge is not so strong.

 

[Guus2005]

I know what you are doing. I have given you the answer. What i mean is that you have to understand the SQL code i am providing. Do you?

 

[smile]

I know what you are doing. I have given you the answer. What i mean is that you have to understand the SQL code i am providing. Do you?

SQL is not what I'm used to work with if that is what you ask.

I do understand:

Select 
Date
From 1stQry
union
Select 
Date
From 2ndQry

So to use 4 queries I must use the code you said, but I did not understand about "create yet another subquery ..."

select A.date from (select date from 1stqry
union
select date from 2stqry
union
select date from 3stqry
union
select date from 4stqry) A
WHERE A.[date] = Between [enter start date] And [enter end date]

Maybe I try to explain better.

4 Tables have common "Date" field I need to limit my input data by this field from all queries.

I need to create output like this:

Date field qry1 field qry2 field qry3 field qry4

As I understand I must do the same as with 2 queries but how to make union query filter by date? Can it do this? Or should this be done at the 4 queries that input data into the union query?

 

[Guus2005]

This query

select A.date from (select date from 1stqry
union
select date from 2stqry
union
select date from 3stqry
union
select date from 4stqry) A
WHERE A.[date] = Between [enter start date] And [enter end date]

Can be split for better readability:

Perhaps this is better to read:
Create a Union query named qryUnion:

select date from 1stqry
union
select date from 2stqry
union
select date from 3stqry
union
select date from 4stqry

Use the following query to get the final result

select date from qryUnion
WHERE qryUnion.[date] = Between [enter start date] And [enter end date]

This is the same query as above but split in two.

HTH

 

[smile]

Thanks for your time. I'll try that.