Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Suggestions Wanted (03)

    I have four queries; two relating to current year Sources and Uses of Funds and the other two for the prior year.

    I am looking for suggestions on how to produce a comparative report to current and prior year. The only things each of the four queries may have in common are categories and not all queries have activity in each category.. I was thinking of creating a new query based on all four but then talked myself out of it as the individual pieces of the pie did not add up to the new query.

    I'm looking forward to your comments and the New Year <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Thanks,
    John

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report Suggestions Wanted (03)

    Hi There

    Without the data (or sample) it is a bit difficult but I was thinking you could union the two queries using the format:

    Select [field1],[field2],[field3],[field4]
    from qry1

    UNION

    Select [field1],[field2],[field3],[field4]
    from qry2

    Assume both queries are pulling data field for field, like for like.

    You could then make a report from the new union query, maybe cross tab it etc....starters for 10
    Jerry

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Suggestions Wanted (03)

    Jerry,

    Thank you for the suggestion on using UNION. I was able to create a new query and report based on the new query.

    Cheers,
    John

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Suggestions Wanted (03)

    Jerry,

    An interesting observation of the results returns the category descriptions for all (Sources and Uses). However upon closing the query and then reopening it, the Sources descriptions are appearing as the autonumber from the tblCategory instead of the description itself.

    I tried modifying the SQL to only query on the Sources and the description appears just fine even if I close the query and reopen it.

    The SQL is quite simple:
    SELECT [Category],[Year],[Amt] from qryReceiptsAll
    UNION SELECT [Category],[Year],[Amt] from qrySpendAll;

    Is it possible within the SELECT to bind a column to the category?

    Regards,
    John

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report Suggestions Wanted (03)

    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>Seems a bit strange...

    1) Is Category a field that is getting its data from a look up table or value list in the table?

    2) Not sure what you mean by "...to bind a column to the category..."
    Jerry

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Suggestions Wanted (03)

    Jerry,

    I traced the activity back to a table which is being populated by a subform. I modified a few records in the table manually and ran the report again it the results are correct for the manual changes.

    I'll have to scrutinize the subform a little closer as the dropdown within the subform is visually correct.

    Thanks for your assistance.
    John

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Suggestions Wanted (03)

    The report wizard does a fair job in creating report from the new query however the report structure is limited. Is there a better way to portay the following report format?

    Example:
    <table border=1><td>Category</td><td>2006 Source</td><td>2005 Source</td><td>Apple Sales</td><td>123.45</td><td>14.50</td><td>Mixed Fruit Sales</td><td>75.00</td><td>70.00</td>
    </table>

    I have no issue setting the group but have challenges on setting up the columns. The query contains the following fields: Category, Year, Fund (Source or Use) and Amount. I have downloaded sample reports but they do not address this.

    Thanks,
    John

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report Suggestions Wanted (03)

    This looks very much like a cross tab query running to a report. I rend to create my crosstabs in a query first then run them to reports. I get more control over the outcome that way.

    I imagine you have a large amount of products and therefore would suggest that you keep products as the row header and the years as the column headers, due to ease of printing the report and viewing it on screen.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>I also think you could export the query to Excel, you will probably find you can control the data better as you will be able to calculate differences between the years
    Jerry

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Suggestions Wanted (03)

    Jerry,

    I was able to create a crosstab query however the column results are not quite what I need. I can return values for the years 2005 and 2006 which net out the "sources & uses" of funds. I would like to have values returned for:

    <table border=1><td>Category</td><td>2005 Source</td><td>2005 Use</td><td>2006 Source</td><td>2006 Use</td><td>abc</td><td>100</td><td>75</td><td>200</td><td>100</td></table>
    Instead of (which nets the source & use by year):
    <table border=1><td>Category</td><td>2005</td><td>2006</td><td>abc</td><td>25</td><td>100</td></table>
    Your suggestions are appreciated.

    John

    Cross-tab query

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

    Re: Report Suggestions Wanted (03)

    You'll have to create two crosstab queries - one for Source and one for Use, then create a third query based on the two crosstab queries joined on Category.

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Suggestions Wanted (03)

    Hans,

    That is the road that I was traveling down. <img src=/S/yep.gif border=0 alt=yep width=15 height=15> I thought perhaps there was a better way.

    Regards,
    John

  12. #12
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Suggestions Wanted (03)

    Hans,

    I hit a bump in the road. After creating the new query from the two cross-tab queries the result is not what I expect due to the "join properties". As you know there are three options to select from:

    1) Only include rows where the joined fields from both tables are equal
    2) Include ALL records from ...
    3) Include ALL records from .....

    Just when I thought I was grasping the concept. All I can do now is <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Thanks,
    John

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

    Re: Report Suggestions Wanted (03)

    So what is the problem?

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

    Re: Report Suggestions Wanted (03)

    If you mean that you want to include all categories:

    Do you have a table listing the categories? If so, you can create a query based on this table and on the two crosstab queries. Create an outer join from the table to each of the crosstabs on Category, specifying that you want to include all records from the table.
    Add the Category field from the table to the query grid, and the other fields from the crosstab queries.

    Instead of the table, you can also use a query that lists the categories you want to include; this could even be a union query.

  15. #15
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Suggestions Wanted (03)

    Hans,

    Your suggestion regarding the outer join worked.

    Thanks,
    John

Posting Permissions

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