Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Unanswered: MS Access Report problem

    I am relatively new to Access. I am building a Contract Management Database named OptimizeItAudit in Access 2003. Two of the tables are ‘linked” via ODBC to a data warehouse.

    My report is built on a query with fields selected from all three tables.
    The three tables are dbo_OptimizeIt1, dbo_OptimizeIt_ExcessUsages, and dbo_OptimizeIt_Audit. They are linked as a one-to-many relationship.
    An example of the tables and their field names follow:

    Dbo_OptimizeIt1 dbo_OptimizeIt_ExcessUsages dbo_OptimizeIt_Audit
    CustomerName PartNumber ContractNumber
    CustomerCity ExcessUsage AuditDate
    CustomerState SoldToId Comments
    ContractNumber
    StartDate
    PartNumber
    Model
    SerialNumber

    I have a main report (Report1) that lists the contract and customer data, and a sub-report (SubReport1a) within a Group Header that lists customer detail from both the dbo_OptimizeIt1 and dbo_OptimizeIt_ExcessUsages tables. The report works well as it is.

    However, I need to add additional detail from the table dbo_OptimizeIt_Audit. This detail needs to be at the “same level” as SubReport1a. I created SubReport1b but when I include it in Report1 (either creating a separate gropu or using the same group as SubReport1a) the data in SubReport1a is replicated within SubReport1b or the data in SubReport1b is replicated within SubReport1a.

    I tried creating the report without using sub-reports, using Group Headers, but I still get the same results. Following is what my report should look like.

    Customer Name
    Jones Construction
    Anywhere USA

    Contract # Start Date
    QQQ666999 11/27/2006

    Audit Date Comments
    2/14/2007 This is the first audit for this contract.
    11/15/2007 The following changes were made to contract.
    4/8/2008 Additional changes were made to the contract.

    Part # Model Serial # Excess Usage
    QQ123 ABC 123456789 42,375
    AA222 XYZ 987654321 389,403
    BB333 RRR 765498230 0
    97531 ABC 234567890 12,349

    Any help you can provide is truly appreciated.


    Krazy Kasper

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Quote Originally Posted by KrazyKasper
    However, I need to add additional detail from the table dbo_OptimizeIt_Audit. This detail needs to be at the “same level” as SubReport1a. I created SubReport1b but when I include it in Report1 (either creating a separate gropu or using the same group as SubReport1a) the data in SubReport1a is replicated within SubReport1b or the data in SubReport1b is replicated within SubReport1a.

    Krazy Kasper
    Not sure I quite understand but to prevent duplication when using multiple tables in a query for a report, remove any extra fields not needed and set the unique values in the query (i.e. show the properties box when in the query design, click in a blank area at the top by the tables and change the "Unique Values" or "Unique Records" property from No to Yes.) Also if using a subreport (which you should do if using a relational table with many records to the one record in the main table), view the properties of the subreport in the main report and make sure the "Link Child Fields" and "Link Master Fields" is set on the correct relational join field. As practice, I also make sure the relational join field is on both reports (making that field invisible on the reports).
    Last edited by pkstormy; 04-08-08 at 19:25.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are duplicating information then there are two ways round that

    one use a query rather than a tbale as the recordsource for the report, and join wahtever tables you need as part of the query. whether you can do that given that you are extractng data from elsehwere is a moot point.. it maybe the 'owners' of that data can provide a query for you.

    if you cannot create a single query then repeating dats then you could try inserting a sub report into a subreport.. but place the sub report say in a group footer

    if you are getting repeating information... an d its hard to know what the repeating information is.. be much easier to diagnose if you suplied a PDF or JPG/PNG (do a screen dump in preview) of the report......


    another approach is to have the main report pull up say the customer details (and probably contractID) link in the audit details as one subreport, link in the part details as another subreprot.

    you can be very very selective about what columns you have visible in a report, you can even have columns in the rpeort which are not visible in print. Its an easy, and lazy, way of being able to do some calculations, or do some otnher manipulations without resorting to functions

    if you are still struggling then it may make sense to post a cut down version of your db contianing some sample data and the report + queries, or perhaps just the query design... compact and repair the mdb before zipping it.

    HTH

  4. #4
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56
    I was able to solve the problem by putting the sub-report in the page footer.
    Thanks to everyone for your help.

    Krazy

Posting Permissions

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