Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query query. (2000)

    Hi,
    Need some direction here. I have attached a database that I need to get some data from in the guise of reports. Yet again I am having difficulty with the queries.
    I have split the queries into three dependent on the field 'File Quality', Satisfactory, Sufficient and Insufficient. I have then split these by 'Days Late' , =0, >0 And <=5, >5. I now need to split this data into its relevant area, 'File Location' of which there are three.
    What I eventually need to display is:
    Each Location with File Quality of Satisfactory,Sufficient & Insufficient. Then I need to split these into Days Late, =0,>0 And <=5, >5:

    File Origin - Norwich
    0 1-5 5+
    Satisfactory
    Sufficient
    Insufficient

    And so on.
    I can't help feel that I'm complicating things..............would Cross Tab queries help?
    Attached Files Attached Files

  2. #2
    Lounger
    Join Date
    Jan 2004
    Location
    Derry, Derry, Ireland, Northern
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query query. (2000)

    Hi,

    I'd recommend making one query that incorporates all the fields you're interested in. You can then use controls in the reports to break out the into in the way you want. I'll try to make a mock up (no doubt someone else will beat me to it..)

    HTH

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query query. (2000)

    Try using one query. Use something like group: iif([dayslate]>5,"5+",iif([dayslate]<=5 and [dayeslate]>0,"0-5","0"))
    This would give you one entry called group as a summary of the dayslate. Do likewise for the other fields you wish to use as a cretia for your report. In the report you could then create sections for each group.
    Hope it at least points you in the right direction

  4. #4
    Lounger
    Join Date
    Jan 2004
    Location
    Derry, Derry, Ireland, Northern
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query query. (2000)

    Hi,

    Try this for a solution look at rptJPM
    Attached Files Attached Files

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query query. (2000)

    Cheers,
    I always get stuck with the queries and think I should do them how I would logically perform them manually.................
    Thanks for any help you can offer

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query query. (2000)

    Hi,
    You've based the report on TblJPM which is fine, I always presumed that reports needed to be based on queries....but then I'm not very experienced with Access.
    Presumably if I now need to collate percentages and totals from the report this would be done by adding the txtboxes together.
    Thanks for your help......I would have been a long while happily overcomplicating things.

  7. #7
    Lounger
    Join Date
    Jan 2004
    Location
    Derry, Derry, Ireland, Northern
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query query. (2000)

    Hi,

    You don't HAVE to use queries (although I usually do) - it was just quicker to use the table today [img]/forums/images/smilies/smile.gif[/img]. You could simply create a query using all the fields in the table as the basis of the report.

    I think we all have a tendency to over-complicate things [img]/forums/images/smilies/smile.gif[/img] I find just browsing this lounge gives great ideas for simplicity.

    Best of luck with the database, it seems to have been going on for ever, or do you just have a whole bunch of projects going on?

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query query. (2000)

    Hi,
    About 6 ongoing at the moment!
    I've learnt more through the lounge than I have on training courses............just a bit of a long process..
    Thanks for your help........I'll get too know what formulas to use for queries/reports eventually.

Posting Permissions

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