Execute multiple crosstab queries with one parameter

  • Thread starter Gary Krekemeyer
  • Start date
G

Gary Krekemeyer

I would like to combine the following crosstab queries
into a single query based on a single parameter.

The parameter is a six digit "Project No_" or "Job No_".
Both are the same field labeled differently.



The queries are as follows:


Query 1

TRANSFORM Sum([dbo_UCI Dataset (Final Version)$Job Budget
Entry].[Total Cost]) AS [SumOfTotal Cost]
SELECT [dbo_UCI Dataset (Final Version)$Job Budget Entry].
[Job No_], Sum([dbo_UCI Dataset (Final Version)$Job Budget
Entry].[Total Cost]) AS [Total Of Total Cost]
FROM [dbo_UCI Dataset (Final Version)$Job Budget Entry]
WHERE ((([dbo_UCI Dataset (Final Version)$Job Budget
Entry].[Job No_])="992130"))
GROUP BY [dbo_UCI Dataset (Final Version)$Job Budget
Entry].[Job No_]
ORDER BY [dbo_UCI Dataset (Final Version)$Job Budget
Entry].[Job No_]
PIVOT [dbo_UCI Dataset (Final Version)$Job Budget Entry].
[Phase Code];

Query 2

TRANSFORM Sum([dbo_UCI Dataset (Final Version)$Job Budget
Entry]![Total Cost]) AS [SumOfTotal Cost]
SELECT [dbo_UCI Dataset (Final Version)$Job Budget Entry]!
[Job No_] AS Expr1, Sum([dbo_UCI Dataset (Final Version)
$Job Budget Entry]![Total Cost]) AS [Total Of Total Cost]
FROM [dbo_UCI Dataset (Final Version)$Job Budget Entry]
WHERE ((([dbo_UCI Dataset (Final Version)$Job Budget
Entry]![Phase Code])="0" Or ([dbo_UCI Dataset (Final
Version)$Job Budget Entry]![Phase Code])="1" Or ([dbo_UCI
Dataset (Final Version)$Job Budget Entry]![Phase Code])
="2" Or ([dbo_UCI Dataset (Final Version)$Job Budget
Entry]![Phase Code])="4"))
GROUP BY [dbo_UCI Dataset (Final Version)$Job Budget
Entry]![Job No_]
PIVOT [dbo_UCI Dataset (Final Version)$Job Budget Entry]!
[Phase Code];


Query 3

TRANSFORM Sum([dbo_UCI Dataset (Final Version)$Project
Lien Forecast Journal].Amount) AS SumOfAmount1
SELECT [dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal].[Project No_], Sum([dbo_UCI Dataset
(Final Version)$Project Lien Forecast Journal].Amount) AS
[Total Of Amount]
FROM [dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal]
WHERE ((([dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal].[Project No_])="992130"))
GROUP BY [dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal].[Project No_]
ORDER BY [dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal].[Project No_]
PIVOT [dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal].[Project Sub Code];


Query 4

TRANSFORM Sum([dbo_UCI Dataset (Final Version)$Lien Ledger
Entry].Amount) AS SumOfAmount
SELECT [dbo_UCI Dataset (Final Version)$Lien Ledger Entry].
[Project No_], Sum([dbo_UCI Dataset (Final Version)$Lien
Ledger Entry].Amount) AS [Total Of Amount]
FROM [dbo_UCI Dataset (Final Version)$Lien Ledger Entry]
WHERE ((([dbo_UCI Dataset (Final Version)$Lien Ledger
Entry].[Project No_])="992130") AND (([dbo_UCI Dataset
(Final Version)$Lien Ledger Entry].[Document Type])=1 Or
([dbo_UCI Dataset (Final Version)$Lien Ledger Entry].
[Document Type])=2 Or ([dbo_UCI Dataset (Final Version)
$Lien Ledger Entry].[Document Type])=6))
GROUP BY [dbo_UCI Dataset (Final Version)$Lien Ledger
Entry].[Project No_]
ORDER BY [dbo_UCI Dataset (Final Version)$Lien Ledger
Entry].[Project No_]
PIVOT [dbo_UCI Dataset (Final Version)$Lien Ledger Entry].
[Sub Code];

Query 5

SELECT qry_Budget_Line.[Job No_], qry_Budget_Line![Total
Of Total Cost]-qry_forecast_Lien_Line!Expr1 AS Expr9,
qry_constuction_budget![Total Of Total Cost]-
qry_forecast_Lien_Line!Expr2 AS Expr10, qry_Budget_Line!
[5]-qry_forecast_Lien_Line!Expr4 AS Expr11,
qry_Budget_Line![6]-qry_forecast_Lien_Line!Expr5 AS
Expr12, qry_Budget_Line![7]-qry_forecast_Lien_Line!Expr6
AS Expr13, qry_Budget_Line![8]-qry_forecast_Lien_Line!
Expr7 AS Expr14, qry_Budget_Line![9]-
qry_forecast_Lien_Line!Expr8 AS Expr15, qry_Budget_Line!
[3]-qry_forecast_Lien_Line!Expr3 AS Expr16
FROM (qry_Budget_Line INNER JOIN qry_forecast_Lien_Line ON
qry_Budget_Line.[Job No_] = qry_forecast_Lien_Line.
[Project No_]) INNER JOIN qry_constuction_budget ON
qry_Budget_Line.[Job No_] = qry_constuction_budget.Expr1;

Query 6

SELECT qry_Liened_Line.[Project No_], qry_Budget_Line!
[Total Of Total Cost]-qry_Liened_Line![Total Of Amount] AS
Expr1, (qry_constuction_budget![Total Of Total Cost])-
qry_Liened_Line![1] AS Expr2, qry_Budget_Line![3] AS
Expr3, qry_Budget_Line![5]-qry_Liened_Line![5] AS Expr4,
qry_Budget_Line![6]-qry_Liened_Line![6] AS Expr5,
qry_Budget_Line![7]-qry_Liened_Line![7] AS Expr6,
qry_Budget_Line![8]-qry_Liened_Line![8] AS Expr7,
qry_Budget_Line![9] AS Expr8
FROM (qry_constuction_budget INNER JOIN qry_Budget_Line ON
qry_constuction_budget.Expr1=qry_Budget_Line.[Job No_])
INNER JOIN qry_Liened_Line ON qry_Budget_Line.[Job No_]
=qry_Liened_Line.[Project No_];


Query 7

SELECT [dbo_UCI Dataset (Final Version)$Job].[Person
Responsible], [dbo_UCI Dataset (Final Version)$Job].No_,
[dbo_UCI Dataset (Final Version)$Resource].Name, [dbo_UCI
Dataset (Final Version)$Job].Description
FROM [dbo_UCI Dataset (Final Version)$Job] INNER JOIN
[dbo_UCI Dataset (Final Version)$Resource] ON [dbo_UCI
Dataset (Final Version)$Job].[Person Responsible] =
[dbo_UCI Dataset (Final Version)$Resource].No_;


Thanks
 
D

Duane Hookom

Can't you just combine them into a single query and join the equal fields to
create a single query? Filter this with a control on a form. You may need to
specify the query parameter data types.

--
Duane Hookom
MS Access MVP


Gary Krekemeyer said:
I would like to combine the following crosstab queries
into a single query based on a single parameter.

The parameter is a six digit "Project No_" or "Job No_".
Both are the same field labeled differently.



The queries are as follows:


Query 1

TRANSFORM Sum([dbo_UCI Dataset (Final Version)$Job Budget
Entry].[Total Cost]) AS [SumOfTotal Cost]
SELECT [dbo_UCI Dataset (Final Version)$Job Budget Entry].
[Job No_], Sum([dbo_UCI Dataset (Final Version)$Job Budget
Entry].[Total Cost]) AS [Total Of Total Cost]
FROM [dbo_UCI Dataset (Final Version)$Job Budget Entry]
WHERE ((([dbo_UCI Dataset (Final Version)$Job Budget
Entry].[Job No_])="992130"))
GROUP BY [dbo_UCI Dataset (Final Version)$Job Budget
Entry].[Job No_]
ORDER BY [dbo_UCI Dataset (Final Version)$Job Budget
Entry].[Job No_]
PIVOT [dbo_UCI Dataset (Final Version)$Job Budget Entry].
[Phase Code];

Query 2

TRANSFORM Sum([dbo_UCI Dataset (Final Version)$Job Budget
Entry]![Total Cost]) AS [SumOfTotal Cost]
SELECT [dbo_UCI Dataset (Final Version)$Job Budget Entry]!
[Job No_] AS Expr1, Sum([dbo_UCI Dataset (Final Version)
$Job Budget Entry]![Total Cost]) AS [Total Of Total Cost]
FROM [dbo_UCI Dataset (Final Version)$Job Budget Entry]
WHERE ((([dbo_UCI Dataset (Final Version)$Job Budget
Entry]![Phase Code])="0" Or ([dbo_UCI Dataset (Final
Version)$Job Budget Entry]![Phase Code])="1" Or ([dbo_UCI
Dataset (Final Version)$Job Budget Entry]![Phase Code])
="2" Or ([dbo_UCI Dataset (Final Version)$Job Budget
Entry]![Phase Code])="4"))
GROUP BY [dbo_UCI Dataset (Final Version)$Job Budget
Entry]![Job No_]
PIVOT [dbo_UCI Dataset (Final Version)$Job Budget Entry]!
[Phase Code];


Query 3

TRANSFORM Sum([dbo_UCI Dataset (Final Version)$Project
Lien Forecast Journal].Amount) AS SumOfAmount1
SELECT [dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal].[Project No_], Sum([dbo_UCI Dataset
(Final Version)$Project Lien Forecast Journal].Amount) AS
[Total Of Amount]
FROM [dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal]
WHERE ((([dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal].[Project No_])="992130"))
GROUP BY [dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal].[Project No_]
ORDER BY [dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal].[Project No_]
PIVOT [dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal].[Project Sub Code];


Query 4

TRANSFORM Sum([dbo_UCI Dataset (Final Version)$Lien Ledger
Entry].Amount) AS SumOfAmount
SELECT [dbo_UCI Dataset (Final Version)$Lien Ledger Entry].
[Project No_], Sum([dbo_UCI Dataset (Final Version)$Lien
Ledger Entry].Amount) AS [Total Of Amount]
FROM [dbo_UCI Dataset (Final Version)$Lien Ledger Entry]
WHERE ((([dbo_UCI Dataset (Final Version)$Lien Ledger
Entry].[Project No_])="992130") AND (([dbo_UCI Dataset
(Final Version)$Lien Ledger Entry].[Document Type])=1 Or
([dbo_UCI Dataset (Final Version)$Lien Ledger Entry].
[Document Type])=2 Or ([dbo_UCI Dataset (Final Version)
$Lien Ledger Entry].[Document Type])=6))
GROUP BY [dbo_UCI Dataset (Final Version)$Lien Ledger
Entry].[Project No_]
ORDER BY [dbo_UCI Dataset (Final Version)$Lien Ledger
Entry].[Project No_]
PIVOT [dbo_UCI Dataset (Final Version)$Lien Ledger Entry].
[Sub Code];

Query 5

SELECT qry_Budget_Line.[Job No_], qry_Budget_Line![Total
Of Total Cost]-qry_forecast_Lien_Line!Expr1 AS Expr9,
qry_constuction_budget![Total Of Total Cost]-
qry_forecast_Lien_Line!Expr2 AS Expr10, qry_Budget_Line!
[5]-qry_forecast_Lien_Line!Expr4 AS Expr11,
qry_Budget_Line![6]-qry_forecast_Lien_Line!Expr5 AS
Expr12, qry_Budget_Line![7]-qry_forecast_Lien_Line!Expr6
AS Expr13, qry_Budget_Line![8]-qry_forecast_Lien_Line!
Expr7 AS Expr14, qry_Budget_Line![9]-
qry_forecast_Lien_Line!Expr8 AS Expr15, qry_Budget_Line!
[3]-qry_forecast_Lien_Line!Expr3 AS Expr16
FROM (qry_Budget_Line INNER JOIN qry_forecast_Lien_Line ON
qry_Budget_Line.[Job No_] = qry_forecast_Lien_Line.
[Project No_]) INNER JOIN qry_constuction_budget ON
qry_Budget_Line.[Job No_] = qry_constuction_budget.Expr1;

Query 6

SELECT qry_Liened_Line.[Project No_], qry_Budget_Line!
[Total Of Total Cost]-qry_Liened_Line![Total Of Amount] AS
Expr1, (qry_constuction_budget![Total Of Total Cost])-
qry_Liened_Line![1] AS Expr2, qry_Budget_Line![3] AS
Expr3, qry_Budget_Line![5]-qry_Liened_Line![5] AS Expr4,
qry_Budget_Line![6]-qry_Liened_Line![6] AS Expr5,
qry_Budget_Line![7]-qry_Liened_Line![7] AS Expr6,
qry_Budget_Line![8]-qry_Liened_Line![8] AS Expr7,
qry_Budget_Line![9] AS Expr8
FROM (qry_constuction_budget INNER JOIN qry_Budget_Line ON
qry_constuction_budget.Expr1=qry_Budget_Line.[Job No_])
INNER JOIN qry_Liened_Line ON qry_Budget_Line.[Job No_]
=qry_Liened_Line.[Project No_];


Query 7

SELECT [dbo_UCI Dataset (Final Version)$Job].[Person
Responsible], [dbo_UCI Dataset (Final Version)$Job].No_,
[dbo_UCI Dataset (Final Version)$Resource].Name, [dbo_UCI
Dataset (Final Version)$Job].Description
FROM [dbo_UCI Dataset (Final Version)$Job] INNER JOIN
[dbo_UCI Dataset (Final Version)$Resource] ON [dbo_UCI
Dataset (Final Version)$Job].[Person Responsible] =
[dbo_UCI Dataset (Final Version)$Resource].No_;


Thanks
Click to expand...
 
G

Gary Krekemeyer

Thanks for the reply.

I'm only a beginner with Access.

Would you please provide a more detailed description.

Gary
-----Original Message-----
Can't you just combine them into a single query and join the equal fields to
create a single query? Filter this with a control on a form. You may need to
specify the query parameter data types.

--
Duane Hookom
MS Access MVP


Gary Krekemeyer said:
I would like to combine the following crosstab queries
into a single query based on a single parameter.

The parameter is a six digit "Project No_" or "Job No_".
Both are the same field labeled differently.



The queries are as follows:


Query 1

TRANSFORM Sum([dbo_UCI Dataset (Final Version)$Job Budget
Entry].[Total Cost]) AS [SumOfTotal Cost]
SELECT [dbo_UCI Dataset (Final Version)$Job Budget Entry].
[Job No_], Sum([dbo_UCI Dataset (Final Version)$Job Budget
Entry].[Total Cost]) AS [Total Of Total Cost]
FROM [dbo_UCI Dataset (Final Version)$Job Budget Entry]
WHERE ((([dbo_UCI Dataset (Final Version)$Job Budget
Entry].[Job No_])="992130"))
GROUP BY [dbo_UCI Dataset (Final Version)$Job Budget
Entry].[Job No_]
ORDER BY [dbo_UCI Dataset (Final Version)$Job Budget
Entry].[Job No_]
PIVOT [dbo_UCI Dataset (Final Version)$Job Budget Entry].
[Phase Code];

Query 2

TRANSFORM Sum([dbo_UCI Dataset (Final Version)$Job Budget
Entry]![Total Cost]) AS [SumOfTotal Cost]
SELECT [dbo_UCI Dataset (Final Version)$Job Budget Entry]!
[Job No_] AS Expr1, Sum([dbo_UCI Dataset (Final Version)
$Job Budget Entry]![Total Cost]) AS [Total Of Total Cost]
FROM [dbo_UCI Dataset (Final Version)$Job Budget Entry]
WHERE ((([dbo_UCI Dataset (Final Version)$Job Budget
Entry]![Phase Code])="0" Or ([dbo_UCI Dataset (Final
Version)$Job Budget Entry]![Phase Code])="1" Or ([dbo_UCI
Dataset (Final Version)$Job Budget Entry]![Phase Code])
="2" Or ([dbo_UCI Dataset (Final Version)$Job Budget
Entry]![Phase Code])="4"))
GROUP BY [dbo_UCI Dataset (Final Version)$Job Budget
Entry]![Job No_]
PIVOT [dbo_UCI Dataset (Final Version)$Job Budget Entry]!
[Phase Code];


Query 3

TRANSFORM Sum([dbo_UCI Dataset (Final Version)$Project
Lien Forecast Journal].Amount) AS SumOfAmount1
SELECT [dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal].[Project No_], Sum([dbo_UCI Dataset
(Final Version)$Project Lien Forecast Journal].Amount) AS
[Total Of Amount]
FROM [dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal]
WHERE ((([dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal].[Project No_])="992130"))
GROUP BY [dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal].[Project No_]
ORDER BY [dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal].[Project No_]
PIVOT [dbo_UCI Dataset (Final Version)$Project Lien
Forecast Journal].[Project Sub Code];


Query 4

TRANSFORM Sum([dbo_UCI Dataset (Final Version)$Lien Ledger
Entry].Amount) AS SumOfAmount
SELECT [dbo_UCI Dataset (Final Version)$Lien Ledger Entry].
[Project No_], Sum([dbo_UCI Dataset (Final Version)$Lien
Ledger Entry].Amount) AS [Total Of Amount]
FROM [dbo_UCI Dataset (Final Version)$Lien Ledger Entry]
WHERE ((([dbo_UCI Dataset (Final Version)$Lien Ledger
Entry].[Project No_])="992130") AND (([dbo_UCI Dataset
(Final Version)$Lien Ledger Entry].[Document Type])=1 Or
([dbo_UCI Dataset (Final Version)$Lien Ledger Entry].
[Document Type])=2 Or ([dbo_UCI Dataset (Final Version)
$Lien Ledger Entry].[Document Type])=6))
GROUP BY [dbo_UCI Dataset (Final Version)$Lien Ledger
Entry].[Project No_]
ORDER BY [dbo_UCI Dataset (Final Version)$Lien Ledger
Entry].[Project No_]
PIVOT [dbo_UCI Dataset (Final Version)$Lien Ledger Entry].
[Sub Code];

Query 5

SELECT qry_Budget_Line.[Job No_], qry_Budget_Line![Total
Of Total Cost]-qry_forecast_Lien_Line!Expr1 AS Expr9,
qry_constuction_budget![Total Of Total Cost]-
qry_forecast_Lien_Line!Expr2 AS Expr10, qry_Budget_Line!
[5]-qry_forecast_Lien_Line!Expr4 AS Expr11,
qry_Budget_Line![6]-qry_forecast_Lien_Line!Expr5 AS
Expr12, qry_Budget_Line![7]-qry_forecast_Lien_Line!Expr6
AS Expr13, qry_Budget_Line![8]-qry_forecast_Lien_Line!
Expr7 AS Expr14, qry_Budget_Line![9]-
qry_forecast_Lien_Line!Expr8 AS Expr15, qry_Budget_Line!
[3]-qry_forecast_Lien_Line!Expr3 AS Expr16
FROM (qry_Budget_Line INNER JOIN qry_forecast_Lien_Line ON
qry_Budget_Line.[Job No_] = qry_forecast_Lien_Line.
[Project No_]) INNER JOIN qry_constuction_budget ON
qry_Budget_Line.[Job No_] = qry_constuction_budget.Expr1;

Query 6

SELECT qry_Liened_Line.[Project No_], qry_Budget_Line!
[Total Of Total Cost]-qry_Liened_Line![Total Of Amount] AS
Expr1, (qry_constuction_budget![Total Of Total Cost])-
qry_Liened_Line![1] AS Expr2, qry_Budget_Line![3] AS
Expr3, qry_Budget_Line![5]-qry_Liened_Line![5] AS Expr4,
qry_Budget_Line![6]-qry_Liened_Line![6] AS Expr5,
qry_Budget_Line![7]-qry_Liened_Line![7] AS Expr6,
qry_Budget_Line![8]-qry_Liened_Line![8] AS Expr7,
qry_Budget_Line![9] AS Expr8
FROM (qry_constuction_budget INNER JOIN qry_Budget_Line ON
qry_constuction_budget.Expr1=qry_Budget_Line.[Job No_])
INNER JOIN qry_Liened_Line ON qry_Budget_Line.[Job No_]
=qry_Liened_Line.[Project No_];


Query 7

SELECT [dbo_UCI Dataset (Final Version)$Job].[Person
Responsible], [dbo_UCI Dataset (Final Version)$Job].No_,
[dbo_UCI Dataset (Final Version)$Resource].Name, [dbo_UCI
Dataset (Final Version)$Job].Description
FROM [dbo_UCI Dataset (Final Version)$Job] INNER JOIN
[dbo_UCI Dataset (Final Version)$Resource] ON [dbo_UCI
Dataset (Final Version)$Job].[Person Responsible] =
[dbo_UCI Dataset (Final Version)$Resource].No_;


Thanks
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query loses data due to joins 3
Problem with Null Queries 3
Eliminating Duplicates from query 3
Joining two queries into one. 6
two queries from same data in table into one query 2
Several parameter queries with same question 0
Question/Problem 8
Need Help With SQL Query 2

Top