Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    93

    Unanswered: show more fields in crosstab query and outer join another query

    Hello Friends,

    I am having issues to figure out how to solve the below issue

    I am running the cross tab on inventory table to get sales record bi-weekly for the whole year

    query is working fine as expected but when i do the cross tab query, It's only showing up records only when there is a sale but doesn't show any biweekly dates when there is no sale

    so eventually i am trying to track down sales in a matrix form for the year by biweekly

    i am trying to get something like this

    Month 1/15 1/30/ 2/1 2/15 3/1 3/15...
    M&M 14 15 0 20 45 0

    and with the present query shows only

    Month 1/15 1/30 2/15 3/1
    M&M 14 15 20 45

    i even created seperate table just to have bi-weekly dates but the join doesn't seem to do any good with Crosstab query

    any thoughts will be appreciated. Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    See recent question by galaxy29.
    Consider using the nz function
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2007
    Posts
    93
    I tried using NZ function but results are the same

    I suspect as they are no records for the entire year in sales table, per say in every 14 days, its not showing up in the crosstab query

    Having said that i created a seperate table, just for dates of every 15 days in a year but i am have no idea how to link between the table and cross tab query to show all the dates in columns then just sales (table) records

    any thoughts ? Thanks

  4. #4
    Join Date
    Nov 2007
    Posts
    93
    I got the solution to have all the dates in cross tab, here you go

    In the Crosstab, view the Properties of the Column Heading and when the Property Sheet appears, you should see a "Column Headings" row. Enter all the Dates you want to see, seperated by a comma.

    Thanks all

Posting Permissions

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