Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2011

    Unanswered: Duplicate Records in Crosstab Query

    I'm getting duplicate records in a crosstab query because I'm using a filter in the DoCmd.openreport with two separate fields.

    I have a query that reports a timesheet for every employee, then I split it with a form that shows a single department or a single manager's employees. So one crosstab query and one report. Everything works except employees with more than one manager have two identical records when reporting by department. They are reported correctly by manager but duplicated in department reports.

    Example data:
    Joe Smith, Dept 1, Mgr 1, 1, 2, 3 Duplicate
    Joe Smith Dept 1, Mgr 2, 1, 2, 3 Duplicate
    Sue Smith, Dept 2, Mgr 1, 7, 5, 3
    Sue Smith Dept 3, Mgr 1, 9, 4, 7

    Query fields - Name, Code (irrelevant probably), Project (irrelevant), Department, ReportTo (manager), 2/21, 2/22... for two weeks

    It was a pain to get the report to update dates correctly and they will be moved to a separate FE, so it would be best to limit it to one query and one report. The magic bullet solution would be to do a max or first/last in VBA in docmd.openreport, but I can't get it to work and there are probably other ways.

    This part of the code is pretty simple:
    filt = "Department = '" & choice & "'"
    filt = filt & " AND ReportToFK = Max([TimeSheetReport_All].ReportToFK)"
    DoCmd.OpenReport "TimeSheetReport_All", acViewReport, , filt
    The first part of filt works. The second part is an attempt.

    I can do a workaround by creating two queries or two reports, but that's double maintenance. I hope I'm just ignorant of VBA filtering capabilities.

    Let me know if I might have left important information out. Thanks in advance for any help!

  2. #2
    Join Date
    Dec 2004
    Madison, WI
    I'd remove the 'Filtering' parameter for the report and instead use the 'Where' parameter - such as docmd.openreport "MyReportName",,,"[SomeField] = '" & SomeValue & "'" (notice the 3 commas).

    If dups are an issue, I'd create a query to first show the records uniquely (with any date or other criteria) and then base the crosstab part of the report(/query) off of that query.

    For embedding a crosstab type query into a report, this example might help:
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2011
    Thanks for the reply, pkstormy. I would double check, but I think I am sending the Where string (ill named as filt) into the Where argument and not the filter. If I can't find a way to only temporarily ignore the ReportTo field or get Max() or First() involved, I'll have to create another query like you suggested. It's not the end of the world, but knowing how to do this would save a lot of time in the future too.

    Thanks again.

Tags for this Thread

Posting Permissions

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