Re: 2 crosstabs

Looks like a solution I posted. I don't think it will work with your two
crosstabs since your Row Headings are not the same.

--
Duane Hookom
MS Access MVP

"geebee" <geebee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DC9EA8D5-48C3-40B2-9DF3-52B31D46197F@xxxxxxxxxxxxxxxx
hi,

I have the following 2 queries:

calls_by_status:
TRANSFORM Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode.[COUNT( *
)]) AS [SumOfCOUNT( * )]
SELECT tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode
GROUP BY tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus
PIVOT tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt;

calls_by_status_and_code:
TRANSFORM Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode.[COUNT( *
)]) AS [SumOfCOUNT( * )]
SELECT tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode.[party code],
tbl_CACS_activity_by_resolvedstatusandcontactcode.[activity cd]
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode
GROUP BY tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode.[party code],
tbl_CACS_activity_by_resolvedstatusandcontactcode.[activity cd]
PIVOT tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedDt;

I have no problem using one query as the source for a line graph. The
problems comes when I want to combine both queries into one line graph. I
have seen a post suggesting the following SQL, but I cant seem to make any
sense of it to generate my own SQL:

TRANSFORM Sum(IIf([FldName]="Quantity",[Quantity],[Quantity]*[Order
Details]![UnitPrice])) AS DaVal
SELECT Products.ProductName
FROM tblXtabColumns, Orders INNER JOIN (Products INNER JOIN [Order
Details]
ON Products.ProductID = [Order Details].ProductID) ON Orders.OrderID =
[Order Details].OrderID
WHERE (((Orders.OrderDate) Between #1/1/1998# And #3/31/1998#))
GROUP BY Products.ProductName
PIVOT [FldName] & Month([OrderDate]);


Does anyone have something I can work with?

Thanks in advance,
geebee



.

Relevant Pages

  • Re: ANSI 97 SQL (MS ACCESS)
    ... > automatically bluid the queries. ... > the INNER JOIN statements are quite different from SQL2000. ... Access formats its joins in a funky manner (lots of parentheses and ... Simply put, if you're familiar with SQL, write the ...
    (microsoft.public.vb.general.discussion)
  • Re: ANSI 97 SQL (MS ACCESS)
    ... > My problem is in Access's SQL syntax. ... > automatically bluid the queries. ... > the INNER JOIN statements are quite different from SQL2000. ... SQL joins are SQL joins. ...
    (microsoft.public.vb.general.discussion)
  • Re: The wisdom of the object mentors (Was: Searching OO Associations with RDBMS Persistence Models)
    ... SQL can ... Just the traces are a set of line segments that form a graph of edges and nodes. ... To do this you are going to have to walk the graph of traces over and over again. ... This is better done in ram, than through repeated tiny SQL queries, each of which gives you the next few line segments. ...
    (comp.object)
  • Re: DB structure question
    ... would run without flaw in SQL Server. ... > and use the Access Query Builder to create my query using the GUI Design ... > FROM (Reviews As A INNER JOIN Book_users As B ... > All queries should be designed, created and tested using the Access Query ...
    (microsoft.public.inetserver.asp.db)
  • Re: Relational question
    ... the two queries are equivalent but with further explanation ... requiring its use in an equivalent SQL query. ... >Note that if this were an INNER JOIN we would only need to ... >>otherwise the two original joins do not even have the same sets of ...
    (microsoft.public.sqlserver.programming)