Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2010
    Posts
    6

    Unanswered: help with multiple instances of records

    I think there is a simple work around to this, but I canít find it. Iím creating a report using multiple tables:

    Name
    Books
    Magazines

    When I run a query including just the Name and Books tables this is the result:

    John
    Book1
    Book2
    Book3

    When I run a query including just the Name and Magazines tables this is the result:

    John
    Magazine1
    Magazine2
    Magazine3

    But when I run the all three tables, I get:

    John
    Magazine1
    Magazine1
    Magazine1
    Magazine2
    Magazine2
    Magazine2
    Magazine3
    Magazine3
    Magazine3
    Book1
    Book1
    Book1
    Book2
    Book2
    Book2
    Book3
    Book3
    Book3

    Any help is appreciated! Hiding Duplicates doesnít work, because the blank space of the duplicate record in the report makes the format unusable.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are experiencing what i call "cross join effects"

    because there is more than one one-to-many relationship involved in the query, each row joined from the first 1-to-m table is matched with every row from the other 1-to-m table

    your best bet is to run two separate queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2010
    Posts
    6

    Thumbs up Managing duplicate redundant multiple records Visual Studio or MS Access

    Thanks r937!

    I was also able to manipulate the grouping. I am using Visual Studio, but I bet the same holds true for MS Access.

    I found that by editing the Group List and the Details Grouping to group on different Fields I was able to get the results that I was looking for.

    For anyone with a similar problem just play around with different expressions within the groups until you find the right ones.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm not sure what you just said, but using grouping to suppress the cross join effects is poor strategy -- the query is unnecessarily complex, and you are burning cycles to overcome the complexity
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2010
    Posts
    6
    Ok, I see what you are saying. How would I join the two separate queries in one report?

    I've tried to work this problem with a subreport, but have been unable to. The subreport would always display the "Books" and "Magazines" from every record, for each of the "Names". Something analogous to this:

    John
    Book1.John
    Book2.John

    (Subreport --
    Magazine1.John
    Magazine1.Carl
    Magazine2.John
    Magazine2.Carl
    --)

    Carl
    Book1.Carl
    Book2.Carl

    (Subreport --
    Magazine1.Carl
    Magazine1.John
    Magazine2.Carl
    Magazine2.John
    --)

    Any thoughts? Thanks again for your help! I had been working this straight for two days before finally posting for assistance.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, someone else will have to step in here, i don't do access reports
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Aug 2009
    Location
    Up Nort' Wi
    Posts
    140
    What's your table relationship layout look like?
    I'm assuming "Reader to Books" is Many to Many, same with "Reader to Magazines"

    You shouldn't be getting a Cartesian join with something like you've shown.

    With a DISTINCT clause in your SQL and Group by Reader (or however you want it grouped) it should show the pertinent record(s) once.

    Do you have an empty copy of the db that you can upload in .Zip format?

    Sam
    Good, fast, cheap...Pick 2.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by SCrandall View Post
    With a DISTINCT clause in your SQL ...
    indeed, there's the rub

    the FROM clause produces a bazillion cartesian rows, and DISTINCT then has to sort them all before it plows through them and pulls out only the distinct ones

    this query is very inefficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2010
    Posts
    6
    SCrandall: By using the GROUP function on the reporting front end, I have been able to get the records to show once. However, as r937 pointed out, running like that is inefficient and very processor intensive.

    My actual query and tables are more complex than my example, but it provided a relatable set. Would using DISTINCT help me out at all?

    Here is my actual SQL query:

    Code:
    SELECT     Scan.IP, Scan.OS, Scan.HostName, Alert.Description, Alert.[Level], Alert.Name, Alert.Type, Domains.DomainName, Ports.Name AS Expr1, 
                          Ports.Description AS Expr2, Backdoors.Value, Patches.AppliesToCategory, Patches.BulletinID, Patches.Severity, Patches.Title, 
                          Patches.IsMissing
    FROM         Scan INNER JOIN
                          Scans ON Scan.ScansID = Scans.ScansID LEFT OUTER JOIN
                          Patches ON Scan.ScanID = Patches.ScanID LEFT OUTER JOIN
                          Backdoors ON Scan.ScanID = Backdoors.ScanID LEFT OUTER JOIN
                          Ports ON Scan.ScanID = Ports.ScanID LEFT OUTER JOIN
                          Domains ON Scan.ScanID = Domains.ScanID LEFT OUTER JOIN
                          Alert ON Scan.ScanID = Alert.ScanID
    WHERE     (Scans.ScanTarget = 'file:E:\temp\Servers.txt') AND (Patches.IsMissing = 1)

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by nolamiami View Post
    My actual query and tables are more complex than my example
    please describe every table relationship involved in the query, and indicate which are the foreign keys and which are the primary keys
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2010
    Posts
    6
    r937 -

    Attached is a graphical representation of my table relationships and keys. To sum, the foreign key and the primary key for each table are as follows:

    Scan: Scan.ScanID (P) Scan.Scans.ID (F)
    Scans: Scans.ScansID (P)
    Patches: Patches.UpdateID (P) Patches.ScanID (F)
    Backdoors: Backdoors.BackdoorsID (P) Backdoors.ScanID (F)
    Ports: Ports.PortsID (P) Ports.ScanID (F)
    Domains: Domains.DomainsID (P) Domains.ScanID (F)
    Alert: Alert.AlertID (P) Alert.ScanID (F)
    Attached Thumbnails Attached Thumbnails table relationships.JPG  

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so each ScanID can have multiple rows in all the other tables?

    i'll simply say what i said back in post #2, you need separate queries for each one-to-many relationship
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2010
    Posts
    6
    OK that sounds reasonable.

    Thanks again for taking a look!

Posting Permissions

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