Results 1 to 15 of 15

Thread: Query Help (2K)

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

    Query Help (2K)

    Hi,
    I have a query that combines two tables. TblMain & TblPayments.
    The fields I'm interested in are TblMain:Chambers & TblPayments:Actual.
    There are other fields in both tables that maybe used.

    Chambers is essentially a list of buildings.
    Actual is a list of payments in

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

    Re: Query Help (2K)

    So presumably in the ChamberCatergory I will have to list every other Chambers (totalling around 50) rather than being able to differentiate between HCA & every other one?
    The total I'm after is literaly HCA and every other. I'm presuming that if every Chambers is listed then this will provide a list for each chambers and the subsequent queries will provide totals for these.

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

    Re: Query Help (2K)

    Instead of Chambers, use a calculated field in the query:

    ChamberCategory: IIf([Chambers]="HCA","HCA","Other")

    You can create a Totals query with ChamberCategory (group by) and Actual (sum).
    Create another Totals query based on tblPayments that simply calculates the sum of Actual.
    Create a third query based on the previous two to calculate the percentages.

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

    Re: Query Help (2K)

    The expression I posted will divide Chambers into two groups: the first consists of HCA only, the other consists of all other values. I thought that was what you wanted. I'm feeling confused now.

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

    Re: Query Help (2K)

    No that's fine, just by reading it I thought that it was a generic expression where you included further details for other chambers, ie Other as you didn't know the name of the Chambers. My mistake!! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

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

    Re: Query Help (2K)

    Sorry to have to come back on this one but I'm having problems.
    I've created the relevant queries & used crosstab queries from them to get the totals I want. However I now have to specify from the original query, fields for allowing the user to select a month. This in itself isn't the problem as I know how to do this, but the crosstab query doesn't recognise the expression:
    Field: Expr1: Nz(Month([DateSent]),1)
    Criteria: [Forms]![FrmReports]![CboMonth]
    As valid fields.
    Is it possible to work round this?

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

    Re: Query Help (2K)

    Try the following:
    - Open the query in design view.
    - Select Query | Parameters...
    - Enter the parameter exactly as in the criteria line: [Forms]![FrmReports]![CboMonth]
    - Select Integer as data type.
    - Click OK.

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

    Re: Query Help (2K)

    That's done the trick..........I vaguely remember using that about two years ago. Thanks.
    One final question (i hope).
    Is there any reason why a report based on a Crosstab query has no fields.
    Will I have to reference to this query some other way as Access is currently sticking me in a loop requesting the values of CboMonth & CboYear whenever I begin to build the report. I have a suspicion that I'm approaching this from the wrong angle.

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

    Re: Query Help (2K)

    In the first place, you should open frmReports when you want to create/edit the report.

    In the second place, will the crosstab query always have the same columns?

    If so, you can make things a lot more efficient by specifying the column names explicitly in the Column Headings property of the crosstab query.

    If not, you cannot just design a report based on the crosstab query, because when the user runs the report, the query might return different columns than when you designed the report. There are code-based solutions for this.

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

    Re: Query Help (2K)

    This has solved the problem & the crosstab query now has the relevant fields.
    I have also specified the column headings in the report as they will always be the same.

    By all intents the query seems to be okay.
    This is the SQL:
    TRANSFORM Sum(QryHCAvsChambersPayments.Actual) AS SumOfActual
    SELECT QryHCAvsChambersPayments.ChamberCatergory, Sum(QryHCAvsChambersPayments.Actual) AS [Total Of Actual]
    FROM QryHCAvsChambersPayments
    GROUP BY QryHCAvsChambersPayments.ChamberCatergory
    PIVOT QryHCAvsChambersPayments.Name In ("ChamberCatergory","Name","Actual","Total of Actual");

    However when I go to create a report based on this query there is no data displayed, although when I run the query the relevant data is displayed. Can I figure out why.......................

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

    Re: Query Help (2K)

    You have specified field names as column headings instead of field values. You should specify the values of the Name field, for example:

    ...
    PIVOT QryHCAvsChambersPayments.Name In ("Adams","Bates","Cheever","Davidson");

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

    Re: Query Help (2K)

    Sorry, final question I promise! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    With regards to the parameters if I need it to be based on a specific field (ie DateSent) can I use this in the Query Parameters box. As basically I need to filter all records that have been sent in March 2006, April 2006, May 2006, etc.

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

    Re: Query Help (2K)

    You must enter the parameter in the Criteria line, in the column for the DateSent field. This tells Access to which field the parameter belongs.

    In the Query | Parameters dialog, you only specify the name and data type of the parameter, not to which field the parameter belongs.

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

    Re: Query Help (2K)

    Still cannot get this right.....think I'll go and shoot myself!!!
    Right...
    In the Query Parameter I have entered the relevant details on two lines.

    [Forms]![FrmReports]![CboMonth] Integer
    [Forms]![FrmReports]![CboYear] Integer

    In the criteria field of the query, under DateSent (which is a date field)

    Is Not Null AND [Forms]![FrmReports]![CboMonth and this is where i get stuck as the field is a date field and I need to reference the month to CboMonth & the Year to CboYear

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

    Re: Query Help (2K)

    You'll have to do it a bit differently:

    - Remove the criteria from the DateSent column.
    - Add a calculated column:

    Year([DateSent])

    - Clear the Show check box for this column.
    - Enter [Forms]![FrmReports]![CboYear] in the criteria line for this column.
    - Add another calculated column:

    Month([DateSent])

    - Clear the Show check box for this column.
    - Enter [Forms]![FrmReports]![CboMonth] in the criteria line for this column.
    - Save the query.

Posting Permissions

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