Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sub-report (2000)

    Well I think I've dug myself a bit of a hole here; I've got a simple 3 table DB in which I have to create a report for individual RNDs (see attachment). And what's going wrong, somewhere along the line, is the report gives multiples, if there are multiple records in the second table. I've had a go with linking master & child fields but to no avail.

    Could someone show me where I'm going awry?
    Attached Files Attached Files

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

    Re: Sub-report (2000)

    How in heaven's name does the form work? It doesn't seem to matter what I fill in. And how is the report supposed to be opened from the form?

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub-report (2000)

    Sorry 'bout that, it's all still "work in progress". I stripped out a lot of stuff to get the size down for posting, too.

    In the form, if you put in a "date from" of 1/11/03 a "date to" of 30/11/03 & for the customer "bucks metal fasteners", then minimise the form & open up the report "ShopFloorReport" you'll see the problem. Where there are multiple related records for the 1st sub-report, I get extra pages on the whole report!? The main report has the others as sub-reports which all have their respective, but related, queries.

    Hope you can make sense of it now (I know it's far from elegant as yet [img]/forums/images/smilies/smile.gif[/img] ).

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

    Re: Sub-report (2000)

    You're trying to filter both the main report and subreport in one query. Since the subreport can return multiple records, the query returns multiple records. You will have to handle them separately. Moreover, the second subreport should be a subreport of the first one - see the relationships between the tables.

    I have attached a modified database. Note that qryFind is now based only on Table1, and that the other two find queries are not related to qryFind any more; they contain their own parameters referring to the form.

    Another problem was that there were controls behind both subreports; this made for a very strange display. I removed these.
    Attached Files Attached Files

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub-report (2000)

    Thank you Hans, that makes sense now [img]/forums/images/smilies/smile.gif[/img]

    The idea behind the buried controls (I thought I'd changed those, ho hum) is that the report will also be used for shopfloor personnel to write in the blanks, before handing it back for the data to be inputed.

    Thanks again

  6. #6
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub-report (2000)

    Argh, I've only just got around to testing the form/query/report properly & if I choose to find a record based purely on the DieNumber or EIS_No, I get ALL of the records from table1 as well as the selected record from table 2. Not sure where to go from this point. We often get questions like "Didn't we get some of this die last month?", so it's sort of vital to the project.

    All help will be VERY welcome [img]/forums/images/smilies/smile.gif[/img]

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

    Re: Sub-report (2000)

    Does the attached work for you? There are still separate queries to filter each of the tables, but there is a query combining them that is the record source for the report and its subreports.
    Attached Files Attached Files

  8. #8
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub-report (2000)

    Aah, what would I do without Woody's! Thanks once again Hans, that's excellant.

    Not wishing to seem pushy or ungrateful (because I am certainly grateful [img]/forums/images/smilies/smile.gif[/img] ), but do you think it'd be possible to be able to include the other records from tables 2 & 3 which are also associated with relevant table 1 record?

    PS isn't it odd that "Woodys!" shows as a spelling mistake!

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

    Re: Sub-report (2000)

    I don't understand. I thought you wanted to include only those records from all three tables that match the criteria. If you want to display all records from table 2 and 3 that are associated with the selected records in table 1, why set separate criteria for table 2 and 3?

  10. #10
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub-report (2000)

    Ah yes, I didn't mention that, sorry. What I wanted to do was show all related records for particular RNDs. So if I put in a die number I'd get the relevant records from table 1 & any other records from tables 2 & 3 associated with those table 1 records.

    Hope that makes sense to you, I'm not sure it does to me?!

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

    Re: Sub-report (2000)

    I'm afraid you've lost me. DieNumber is a field in Table2, not in Table1.

  12. #12
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub-report (2000)

    Oh dear, I did wonder if my explanation was up to the job.

    If against RND number 68, in table one there were 3 related records in table 2, & another 2 records in table 3, I'd like to show all of these all under the table 1 record. In the attached file, I've put in the main query the criteria for the RND, & the report shows all related records. I'd like to do the same by just selecting either the Die Number Or EIS number.

    Hope I've made a bit more sense.
    Attached Files Attached Files

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

    Re: Sub-report (2000)

    No, I'm afraid not. Look at the following situation:

    <table border=1><td align=center>Table 1</td><td align=center>Table 2</td><td align=center>Table 3</td><td align=center>A</td><td align=center>1</td><td align=center>a</td><td align=center>A</td><td align=center>1</td><td align=center>b</td><td align=center>A</td><td align=center>2</td><td align=center>c</td><td align=center>A</td><td align=center>3</td><td align=center>d</td><td align=center>B</td><td align=center>1</td><td align=center>e</td><td align=center>B</td><td align=center>2</td><td align=center>f</td><td align=center>B</td><td align=center>2</td><td align=center>g</td></table>
    You specify "a" for the value in Table 3. "Related" in Table 2 is value "1", and related to that in Table 1 are both "A" and "B", But selecting "A" and "B" will select *all* records in Table 2 and Table 3.

    <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  14. #14
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sub-report (2000)

    No it's not an easy one at all. I'll attempt to explain the scenario; (BTW we have over 40,000 dies) when a manager wishes to find out what RNDs we've had for a particular die, they'll come & ask one of the DB users, & I want to be able to show what other jobs were on the same RND.

    However, I'm thinking that this other info is not really necessary. So thank-you for your efforts, I do appreciate them. I'm just sorry to have given such a wild goose chase.

Posting Permissions

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