Joining Multiple Queries

[dje]

Apologies if previous post hits the forum. I pressed enter in error.

I have 6 queries which have been derived from a common table field and
similar queries from other tables. Each query has a date and a number and
each query has exactly the same dates although there may not be a number
every day - where there were two numbers for a day I have grouped by date
from the basic data to sum the figure. Hence I have (number of records
minimised just to show principle)

nb Headings are "mydate" and variations on balance ie "Eurbal", "USDbal" etc

Eur Query USD Query App Buy Query CAD Query U/T Query Met Query
1/12/07 100 1/12/07 50 1/12/07 0 1/12/07 230 1/12/07 125
1/12/07 225
2/12/07 0 2/12/07 100 2/12/07 150 2/12/07 0 2/12/07 0
2/12/07 154
3/12/07 250 3/12/07 0 3/12/07 550 3/12/07 333 3/12/07 342
3/12/07 155
4/12/07 350 4/12/07 340 4/12/07 0 4/12/07 234 4/12/07 111
4/12/07 156

What I am trying to do is have one query with the date as 1 column and the
figures side by side so that I can add them together. Eventual output is via
a report.

I have tried brackets around joins but do not under the syntax. I have also
tried joining 2 queries at a time but for a reason I do not understand a
table earlier in the structure appears meaning design view shows 3
tables/queries to join and left join does not work - inner joins do not work
as I want all dates to show for all queries not just the fields that match -
across all 6 queries this meant my output is one record rather than 10 in my
actual test database. I have tried joining in relationships and joining
tables in design view to no avail.

Having spent a long time to get to thiis stage (I am a novice) I have
reached the stage where I am about to give up and go back to Excel even
though I know Access will be much more robust for me. Any assistance would be
appreciated.

Thanks

 

[George Nicholson]

Create a UNION query that combines the data from your 6 queries.
- Union queries have to be constructed in SQL view of the query
designer.
- Rename fields & queries as necessary
- Each SELECT clause must have the same number of fields
- Field names in the final output are derived from the first SELECT.

SELECT "Eur" As Source, MyDate, [EurBal] as Balance FROM [Eur Query]
UNION SELECT "USD", MyDate, Balance FROM [USD Query]
UNION SELECT "App", MyDate, Balance FROM [App Buy Query]
UNION SELECT "CAD", MyDate, Balance FROM [CAD Query]
UNION SELECT "U/T", MyDate, Balance FROM [U/T Query]
UNION SELECT "Met", MyDate, Balance FROM [Met Query]

Then create a crosstab query based on the Union query.
Dates as Rows, Source as Columns, Balance as Value.
Add Balance again as a RowHeading and set it to Sum.

 

[dje]

George

Thank you so much. worked like a dream and I now have the results I was
looking for.

Create a UNION query that combines the data from your 6 queries.
- Union queries have to be constructed in SQL view of the query
designer.
- Rename fields & queries as necessary
- Each SELECT clause must have the same number of fields
- Field names in the final output are derived from the first SELECT.

SELECT "Eur" As Source, MyDate, [EurBal] as Balance FROM [Eur Query]
UNION SELECT "USD", MyDate, Balance FROM [USD Query]
UNION SELECT "App", MyDate, Balance FROM [App Buy Query]
UNION SELECT "CAD", MyDate, Balance FROM [CAD Query]
UNION SELECT "U/T", MyDate, Balance FROM [U/T Query]
UNION SELECT "Met", MyDate, Balance FROM [Met Query]

Then create a crosstab query based on the Union query.
Dates as Rows, Source as Columns, Balance as Value.
Add Balance again as a RowHeading and set it to Sum.

--
HTH,
George

Apologies if previous post hits the forum. I pressed enter in error.

I have 6 queries which have been derived from a common table field and
similar queries from other tables. Each query has a date and a number and
each query has exactly the same dates although there may not be a number
every day - where there were two numbers for a day I have grouped by date
from the basic data to sum the figure. Hence I have (number of records
minimised just to show principle)

nb Headings are "mydate" and variations on balance ie "Eurbal", "USDbal"
etc

Eur Query USD Query App Buy Query CAD Query U/T Query Met
Query
1/12/07 100 1/12/07 50 1/12/07 0 1/12/07 230 1/12/07 125
1/12/07 225
2/12/07 0 2/12/07 100 2/12/07 150 2/12/07 0 2/12/07 0
2/12/07 154
3/12/07 250 3/12/07 0 3/12/07 550 3/12/07 333 3/12/07 342
3/12/07 155
4/12/07 350 4/12/07 340 4/12/07 0 4/12/07 234 4/12/07 111
4/12/07 156

What I am trying to do is have one query with the date as 1 column and the
figures side by side so that I can add them together. Eventual output is
via
a report.

I have tried brackets around joins but do not under the syntax. I have
also
tried joining 2 queries at a time but for a reason I do not understand a
table earlier in the structure appears meaning design view shows 3
tables/queries to join and left join does not work - inner joins do not
work
as I want all dates to show for all queries not just the fields that
match -
across all 6 queries this meant my output is one record rather than 10 in
my
actual test database. I have tried joining in relationships and joining
tables in design view to no avail.

Having spent a long time to get to thiis stage (I am a novice) I have
reached the stage where I am about to give up and go back to Excel even
though I know Access will be much more robust for me. Any assistance would
be
appreciated.

Thanks

 

[George Nicholson]

Congratulations!

Glad you got it working, they can be tricky and hard to explain in a brief
posting.

--
HTH,
George

George

Thank you so much. worked like a dream and I now have the results I was
looking for.

Create a UNION query that combines the data from your 6 queries.
- Union queries have to be constructed in SQL view of the query
designer.
- Rename fields & queries as necessary
- Each SELECT clause must have the same number of fields
- Field names in the final output are derived from the first SELECT.

SELECT "Eur" As Source, MyDate, [EurBal] as Balance FROM [Eur Query]
UNION SELECT "USD", MyDate, Balance FROM [USD Query]
UNION SELECT "App", MyDate, Balance FROM [App Buy Query]
UNION SELECT "CAD", MyDate, Balance FROM [CAD Query]
UNION SELECT "U/T", MyDate, Balance FROM [U/T Query]
UNION SELECT "Met", MyDate, Balance FROM [Met Query]

Then create a crosstab query based on the Union query.
Dates as Rows, Source as Columns, Balance as Value.
Add Balance again as a RowHeading and set it to Sum.

--
HTH,
George

Apologies if previous post hits the forum. I pressed enter in error.

I have 6 queries which have been derived from a common table field and
similar queries from other tables. Each query has a date and a number
and
each query has exactly the same dates although there may not be a
number
every day - where there were two numbers for a day I have grouped by
date
from the basic data to sum the figure. Hence I have (number of records
minimised just to show principle)

nb Headings are "mydate" and variations on balance ie "Eurbal",
"USDbal"
etc

Eur Query USD Query App Buy Query CAD Query U/T Query Met
Query
1/12/07 100 1/12/07 50 1/12/07 0 1/12/07 230 1/12/07 125
1/12/07 225
2/12/07 0 2/12/07 100 2/12/07 150 2/12/07 0 2/12/07 0
2/12/07 154
3/12/07 250 3/12/07 0 3/12/07 550 3/12/07 333 3/12/07 342
3/12/07 155
4/12/07 350 4/12/07 340 4/12/07 0 4/12/07 234 4/12/07 111
4/12/07 156

What I am trying to do is have one query with the date as 1 column and
the
figures side by side so that I can add them together. Eventual output
is
via
a report.

I have tried brackets around joins but do not under the syntax. I have
also
tried joining 2 queries at a time but for a reason I do not understand
a
table earlier in the structure appears meaning design view shows 3
tables/queries to join and left join does not work - inner joins do not
work
as I want all dates to show for all queries not just the fields that
match -
across all 6 queries this meant my output is one record rather than 10
in
my
actual test database. I have tried joining in relationships and joining
tables in design view to no avail.

Having spent a long time to get to thiis stage (I am a novice) I have
reached the stage where I am about to give up and go back to Excel even
though I know Access will be much more robust for me. Any assistance
would
be
appreciated.

Thanks