SELECT SQL, can you have two

[Alan]

I have a report which i need to report two sets of number on it from
different table,

example

strSQL = "TRANSFORM CCur(Nz(Sum([Amount]),0)) AS X"
strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as
SalesGroupingField FROM [Sales Analysis] "
strSQL = strSQL & " Where [Year]=" & TempVars![Year]
strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" &
TempVars![Display] & "]"
strSQL = strSQL & " Pivot [Sales Analysis].[Quarter] In (1,2,3,4)"

Me.RecordSource = strSQL
Me.SalesGroupingField_Label.Caption = TempVars![Display]



'strSQL1 = "TRANSFORM Ccur(Nz(Sum([Amount]),0)) AS Y"
' strSQL1 = strSQL1 & " SELECT [" & TempVars![Display1] & "] as
SalesGroupingField2 FROM [1a11f raw] "
'strSQL1 = strSQL1 & " Where [Year]=" & TempVars![Year1]
'strSQL1 = strSQL1 & " GROUP BY [" & TempVars![Group By1] & "], [" &
TempVars![Display1] & "]"
'strSQL1 = strSQL1 & " Pivot [1a11f raw].[Quarter] in (1,2,3,4)"


The question i'm asking can you do this?? because i'm not getting the second
amount

 

[Daryl S]

Alan -

You can put subreports into reports so that you can display different result
sets in one report.

You can also use UNION queries to combine the results of two queries into
one recordset, and you could use that as the source for your report.

It all depends on how you want this data to look?

 

[Alan]

Data need to be set out like so


Customer NUMBER Q1A Q1F

C0001 1000 1000

so i would have two different text boxes , pulling information from two
different report

Alan -

You can put subreports into reports so that you can display different result
sets in one report.

You can also use UNION queries to combine the results of two queries into
one recordset, and you could use that as the source for your report.

It all depends on how you want this data to look?

--
Daryl S

I have a report which i need to report two sets of number on it from
different table,

example

strSQL = "TRANSFORM CCur(Nz(Sum([Amount]),0)) AS X"
strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as
SalesGroupingField FROM [Sales Analysis] "
strSQL = strSQL & " Where [Year]=" & TempVars![Year]
strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" &
TempVars![Display] & "]"
strSQL = strSQL & " Pivot [Sales Analysis].[Quarter] In (1,2,3,4)"

Me.RecordSource = strSQL
Me.SalesGroupingField_Label.Caption = TempVars![Display]



'strSQL1 = "TRANSFORM Ccur(Nz(Sum([Amount]),0)) AS Y"
' strSQL1 = strSQL1 & " SELECT [" & TempVars![Display1] & "] as
SalesGroupingField2 FROM [1a11f raw] "
'strSQL1 = strSQL1 & " Where [Year]=" & TempVars![Year1]
'strSQL1 = strSQL1 & " GROUP BY [" & TempVars![Group By1] & "], [" &
TempVars![Display1] & "]"
'strSQL1 = strSQL1 & " Pivot [1a11f raw].[Quarter] in (1,2,3,4)"


The question i'm asking can you do this?? because i'm not getting the second
amount

 

[Daryl S]

Alan -

It looks like you want one column each from two crosstab queries? Depending
on your data, it might be easier to dump the results in a temporary table and
run your report from that.

The complexities come when you could have a customer that would only show up
in one of the queries and not the other, and especially when this could
happen with either query. Just getting a list of valid customers would not
be easy because you would need to pull all the customers from the first query
and then include all the customers in the second query that were not in the
first query. Then crosstab queries cause issues if the column names can
change when they are run. To use a crosstab query in another query, you
would need to define the column titles. In your example, this would be the
Q1A from one query and Q1F from another query. What happens if there is no
data for Q1A? The crosstab will be OK, but the query based on it will not.

So, you may want to build a temporary table keyed on your customer number
with the column names you need for your report. Then build queries to
populate the table, and run the report. I use the term 'temporary table' to
be a table that holds data for a short period of time, even if the table
remains in the database permanently. The data in the table is not to be
relied on except at the time it is populated. For this reason, I usually
delete the records from the table after I am done (e.g. after running the
report).

You can add ALL the possible customers to the table first, then add in the
Q1A and Q1F data based on update queries similar to what you have written.
Then if any customers have no data in either the Q1A or Q1F columns, you can
delete those records before running the report.

I hope that helps. If my assumptions are wrong, then you need to give us
more data (your Data example did not match your SQL statements, so I may have
mis-understood your goal).

--
Daryl S

Data need to be set out like so


Customer NUMBER Q1A Q1F

C0001 1000 1000

so i would have two different text boxes , pulling information from two
different report

Alan -

You can put subreports into reports so that you can display different result
sets in one report.

You can also use UNION queries to combine the results of two queries into
one recordset, and you could use that as the source for your report.

It all depends on how you want this data to look?

--
Daryl S

I have a report which i need to report two sets of number on it from
different table,

example

strSQL = "TRANSFORM CCur(Nz(Sum([Amount]),0)) AS X"
strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as
SalesGroupingField FROM [Sales Analysis] "
strSQL = strSQL & " Where [Year]=" & TempVars![Year]
strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" &
TempVars![Display] & "]"
strSQL = strSQL & " Pivot [Sales Analysis].[Quarter] In (1,2,3,4)"

Me.RecordSource = strSQL
Me.SalesGroupingField_Label.Caption = TempVars![Display]



'strSQL1 = "TRANSFORM Ccur(Nz(Sum([Amount]),0)) AS Y"
' strSQL1 = strSQL1 & " SELECT [" & TempVars![Display1] & "] as
SalesGroupingField2 FROM [1a11f raw] "
'strSQL1 = strSQL1 & " Where [Year]=" & TempVars![Year1]
'strSQL1 = strSQL1 & " GROUP BY [" & TempVars![Group By1] & "], [" &
TempVars![Display1] & "]"
'strSQL1 = strSQL1 & " Pivot [1a11f raw].[Quarter] in (1,2,3,4)"


The question i'm asking can you do this?? because i'm not getting the second
amount