Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query lists multiple records (2003)

    Hi I have a query which I believe is the cause of a summing problem I have with a report. The database records clients who have work placements, and who have consultant contact. The 3 relevant tables are tblClients, tblPlacements, and tblClientContact. The relationships are one to many between tblClients and tblPlacements, and between tlbClients and tblClientContact (ie the contact is NOT related to a particular placement).

    I am trying to design a report that lists client name (and other client info from tblClients), hours worked a week (from tblPlacements) from each placement, and then the sum of the client contact hours from tblClientContact.

    I am confused about whether the design of the underlying query is correct because when I view the query, for any client with more than one placement, the client contact is listed multiple times (as many times as there are placements). Similarly, when I attempt to sum client contact time in the report I get a doubling of the actual time if there are 2 placements. I am now unclear as to how to correctly set up the grouping in the report design.

    The query design has the 3 tables noted above, showing a 1 to many relationship between tblClients and the other 2 tables. Can anyone explain what I am doing wrong?

    Cheers,
    Roger

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

    Re: query lists multiple records (2003)

    You cannot do this in one query, as you have found. You'll have to design separate queries:
    1) A totals query based on tblPlacements that calculates hours worked per client.
    2) A totals query based on tblClientContact that calculates contact hours per client.
    3) A query based on tblClients and the above queries, joined on the client ID field. Add client info fields from tblClients, hours worked from the first query and contact hours from the second query.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query lists multiple records (2003)

    OK, I see.

    I need to clarify one point. The tblPlacements table has a field that records hours worked per week, this is static data. I don't need to total this.

    So am I correct that I only need to create the totals query you mention under (2). Then create a select query based on tblClients, to which I add the totals query based on tblClientContact and select query based on tblPlacements?

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

    Re: query lists multiple records (2003)

    In that case, you can indeed skip step 1)
    In step 3), you can create a query based on tblClients, tblPlacements and the totals query based on tblClientContact, where tblPlacements and the totals query are joined to tblClients on the client ID field.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query lists multiple records (2003)

    Thanks for the prompt replies! Sorry for all the stupid quesions, I have become quite lost with this one...

    In the design view of the totals query, I have added all the fields from the table, clicked the Totals button, and in the Totals row, is the Group By option in all the fields except the Duration field, which is Sum. Is that the correct way to total the Duration field? When I view the query, it simply lists all the individual records from tblClientContact.

    When I add this query in the design of my final query (step 3) both tables joined to tblClients (via the clientID field) are join type (1) - "only include rows where the joined fields from both tables are equal". Is this correct?

    And about the design of the report. Does this now become a report without the need for any levels of grouping required? If not, what would I group on? Client Name?

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

    Re: query lists multiple records (2003)

    In the totals query, you should only add the ClientID field (with Total option set to Group By) and the Duration field (with Total option set to Sum). You shouldn't add any other fields to the query grid.

    In the query in step 3), the join type depends on what you want. If you want to include only clients that have related records in the tblPlacements and tblClientContact tables, use the default type (1). If you want to include clients without related records too, select the option to return ALL records from tblClients (depending on how you set up the join, it can be option 2 or 3).

    As far as I can tell, you don't need to group the report on anything, unless you want to group clients by region or something like that.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query lists multiple records (2003)

    The design of the totals query as you suggest creates another difficulty - I am filtering report data based on the date of the client contact, this field would be removed from the query. I do this via the report's On Open event, with the criteria from a form being passed to the date field criteria - "Between [forms]![datedialog]![txtstartdate] And [forms]![datedialog]![txtenddate]. It would seem that I need to base my new totals query on another select query, to which is passed this criteria? Is that correct, or can I modify my "step 3" query somehow (which would be simpler)?

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

    Re: query lists multiple records (2003)

    Modifying the query from step 3) won't help - the totals have already been calculated at that stage.

    You should add the date field to the totals query from step 2), set the Total option to Where (this will automatically clear the Show check box) and put the condition in the Criteria line.

  9. #9
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query lists multiple records (2003)

    Hi Hans, OK, I have the query displaying the data via this new method, and consequently the report (including via the date filter). However an issue remains.

    In both the query and the report, the summed contact hours is listed multiple times - once for each placement when a client has more than one placement. Currently if I were to sum the contact hours in the report footer, it is overstated by 'number of placements times contact hours' (for any client with multiple placements). This is the situation I started with originally (though this method with the totals query is much better). I need to be able to list any placement information (and so for some clients multiple placements) but then just the placement once.

    Does the following appear to be the correct approach? Change the structure of the report to group by client, include the placement info in the detail, and the summed contact hours in the client header (or footer)? Or am I needing to do something else?

    Cheers, Roger

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

    Re: query lists multiple records (2003)

    I think you should have created a totals query based on tblPlacements after all. If not, I don't understand the situation.

  11. #11
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query lists multiple records (2003)

    Hi Hans, the client concerned appreciates that this particular report that they were hoping for will instead need to be based on the report from Access, exported to Excel and finished from there. Were I to do this myself it would take only a few minutes. Consequently it is not worth spending any more time on. With hindsight I ought not have gone down this path with this particular report, I suspect (and you seem to be confirming) that the desired outcome is not acheivable.

    I really appreciate the assistance you have provided, the query you have shown me how to create is a much tidier method, I will use this and leave it at that. Thank you for your help.
    Roger

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

    Re: query lists multiple records (2003)

    It's up to you - a lot is possible with queries and reports, but if it isn't worth spending more time on it, that's fine.

Posting Permissions

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