Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cross Tab Query (WIN 2000 Acc 97)

    Using the search feature, I was able to establish how to use a parameter in my CrossTab query.
    But I couldn't find how to do the following.
    How can I combine 2 Crosstab queries? I have posted the code below:

    Crosstab1
    PARAMETERS [Enter Date] DateTime;
    TRANSFORM Count(tbl_EVTS.RecordNum) AS [The Value]
    SELECT tbl_EVTS.Date_Created, Count(tbl_EVTS.RecordNum) AS [Total Of RecordNum]
    FROM tbl_EVTS
    WHERE (((tbl_EVTS.Date_Created)=[Enter Date]))
    GROUP BY tbl_EVTS.Date_Created
    PIVOT tbl_EVTS.IPA;

    Crosstab2
    PARAMETERS [Enter Date] DateTime;
    TRANSFORM Count(tbl_EVTS.RecordNum) AS [The Value]
    SELECT tbl_EVTS.Date_Out, Count(tbl_EVTS.RecordNum) AS [Total Of RecordNum]
    FROM tbl_EVTS
    WHERE (((tbl_EVTS.Date_Out)=[Enter Date]))
    GROUP BY tbl_EVTS.Date_Out
    PIVOT tbl_EVTS.IPA;

    What I am trying to do, is the 1st query gives me a count of items received by client(column headings)
    and the 2nd query gives me a count of items completed by client.
    Ideally, I would like to have the clients across the top, and the words Received as the 1st row header
    and Completed as the 2nd row header.

    Is this possible?

    I tried to create a query and bring in both of these queries, but it kept prompting
    me to Enter a Date (My parameter)

    I appreciate any guidance you can give.

    Michael

  2. #2
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross Tab Query (WIN 2000 Acc 97)

    Thanks SOOOOOOOOOOOO much.
    It works perfectly and is just what I wanted/needed.
    <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35>

    Hans, I really appreciate your expertise and willingness to share.

    Michael

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cross Tab Query (WIN 2000 Acc 97)

    If you have crosstab queries with a parameter, the columns are going to vary. That makes it difficult to combine the queries. You can get around that by setting the ColumnHeaders property of both crosstab queries to a semicolon-delimited list of all clients (IPA). But if you have many clients, this will be (a) a lot of work, and ([img]/forums/images/smilies/cool.gif[/img] a nuisance, because many columns will remain empty.

    To display "Received" or "Completed", you can create a dummy field. The SQL would look like this:

    Crosstab1
    PARAMETERS [Enter Date] DateTime;
    TRANSFORM Count(tbl_EVTS.RecordNum) AS [The Value]
    SELECT "Received" AS Type, Count(tbl_EVTS.RecordNum) AS [Total Of RecordNum]
    FROM tbl_EVTS
    WHERE (((tbl_EVTS.Date_Created)=[Enter Date]))
    GROUP BY "Received"
    PIVOT tbl_EVTS.IPA In ("Client1","Client2","Client3","Client4");

    Crosstab2
    PARAMETERS [Enter Date] DateTime;
    TRANSFORM Count(tbl_EVTS.RecordNum) AS [The Value]
    SELECT "Completed" AS Type, Count(tbl_EVTS.RecordNum) AS [Total Of RecordNum]
    FROM tbl_EVTS
    WHERE (((tbl_EVTS.Date_Out)=[Enter Date]))
    GROUP BY "Completed"
    PIVOT tbl_EVTS.IPA In ("Client1","Client2","Client3","Client4");

    Now, you can create a union query with SQL

    SELECT * FROM Crosstab1
    UNION
    SELECT * FROM Crosstab2;

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross Tab Query (WIN 2000 Acc 97)

    I know about the NZ function, but can't seem to place it properly in the below code.

    PARAMETERS [Enter Date] DateTime;
    TRANSFORM Count(tbl_EVTS.RecordNum) AS [The Value]
    SELECT "Received" AS Type, Count(tbl_EVTS.RecordNum) AS [Total Of RecordNum]
    FROM tbl_EVTS
    WHERE (((tbl_EVTS.Date_Created)=[Enter Date]) AND ((tbl_EVTS.Requester)<>"hnv"))
    GROUP BY "RECEIVED"
    PIVOT tbl_EVTS.IPA In ("ClientA","ClientB","ClientC");

    If the count is 0 I need the 0 in the chart.

    Where would the NZ go?

    Again, thank you very much.

    Michael

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Cross Tab Query (WIN 2000 Acc 97)

    To get zeroes in crosstab query Value field if count = 0 I use expression like in this SQL:
    <pre> TRANSFORM CLng(NZ(Count(*),0)) AS VALUEFIELD</pre>

    Note: I also use CLng to convert to value to Long Integer because when exporting crosstab query results to Excel, if just Nz is used the values are exported as text, not numerical values, no matter what number format was specified for Value field, and worksheet functions like SUM do not work with exported data. If you intend to use results of query for numerical calculations recommend convert values to actual numbers to avoid possible pitfalls. If you use Count(*) expression you don't have to specify a field to count, and it is allegedly more efficient.

    HTH

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross Tab Query (WIN 2000 Acc 97)

    Thanks Mark.

    I tried using the code below, but when I ran the query, it came up blank
    instead of all zeros.

    PARAMETERS [Enter Date] DateTime;
    TRANSFORM CLng(NZ(Count((tbl_EVTS.RecordNum)),0)) AS [The Value]
    SELECT "Received" AS Type, Count(tbl_EVTS.RecordNum) AS [Total Of RecordNum]
    FROM tbl_EVTS
    WHERE (((tbl_EVTS.Date_Created)=[Enter Date]) AND ((tbl_EVTS.Requester)<>"hnv"))
    GROUP BY "RECEIVED"
    PIVOT tbl_EVTS.IPA In ("ClientA","ClientB","ClientC");

    (I also tried TRANSFORM CLng(NZ(Count(*),0)) AS [The Value] )

    It runs w/no error message, but comes up blank. I would expect zeros straight across.

    What am I missing?

    Thanks for your patience !!
    Michael

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Cross Tab Query (WIN 2000 Acc 97)

    I see nothing obviously wrong with SQL. I assume the column headings in PIVOT clause ("ClientA" etc) are valid. Hard to say what problem is w/o seeing actual data being queried. When you say it came up blank, do you mean no records returned by query, or query did return records, but no zeroes in value fields as expected?

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross Tab Query (WIN 2000 Acc 97)

    OK, this is what I have found:

    When there are records for at least one of the clients, the zeros will show up for the clients with
    no matching records.
    When there are no records for ANY of the clients, the query comes back blank - not 1 row with
    nothing in it - just a blank query with only the headings.

    Puzzling......

    Thank you Mark for your help (again !!)

    Michael

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Cross Tab Query (WIN 2000 Acc 97)

    Without being able to see the actual data you're working with, it's hard to provide a specific solution. What I sometimes do is this: Create crosstab query (or queries) that calculate the totals. Then create a second query based on the table with the records you want to display (whether or not there is any data returned in crosstab for that record) joined to the crosstab query using a left outer join. (You can add criteria to limit which records in table will be returned.)

    In this case there is no need to add the Nz function to crosstab, you'd use Nz (and CLng or CInt or CCur or whichever is applicable) in the second query, as illustrated below. For this to work correctly you need fixed column headings in the crosstab. In an attempt to demonstrate this, here is an example using the Employees and Orders tables in Northwind.mdb. The crosstab is counting the number of orders completed by each employee for customers whose CustomerID begins with "A":

    TRANSFORM Count(*) AS ValueField
    SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName, Count(*) AS TOTAL
    FROM Employees INNER JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID
    WHERE (((Orders.CustomerID) Like "A*"))
    GROUP BY Employees.EmployeeID, Employees.LastName, Employees.FirstName
    PIVOT Orders.CustomerID In ("ALFKI","ANATR","ANTON","AROUT");

    The SQL for second query is:

    SELECT Employees.LastName, Employees.FirstName, CLng(Nz([ALFKI],0)) AS [ALFKI COUNT], CLng(Nz([ANATR],0)) AS [ANATAR COUNT], CLng(Nz([ANTON],0)) AS [ANTON COUNT], CLng(Nz([AROUT],0)) AS [AROUT COUNT], CLng(Nz([TOTAL],0)) AS [TOTAL COUNT]
    FROM Employees LEFT JOIN qryCrosstab1 ON Employees.EmployeeID = qryCrosstab1.EmployeeID;

    If you copy these SQL statements to create new queries in Northwind (first qry is named "qryCrosstab1") and run 2nd query, you will get all 9 Employee records in resulting set of records, including Andrew Fuller, even though he had no orders for any customer beginning with "A" (what a slacker) (this assumes you haven't monkeyed around with the intrinsic Northwind data). As a science experiment (you should use a COPY of Northwind for this!), open Orders tables and delete all records where "Customer" field begins with "A". Crosstab query will return only the column headings, but 2nd query will return all 9 Employee records with zeroes straight across.

    Maybe you can adapt this concept for your own project?

    HTH

  10. #10
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Sacramento, California, USA
    Posts
    491
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross Tab Query (WIN 2000 Acc 97)

    Thanks (again) Mark !!

    I will take this home tonight - it appears I can adapt it to what I need, but would
    like a better look at it. It's a bit more relaxing at home [img]/forums/images/smilies/smile.gif[/img]

    I truly appreciate your assistance.

    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •