1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Repeating Report in Access 2007

Discussion in 'Business Applications' started by ibanezsicko, Apr 15, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. ibanezsicko

    ibanezsicko Thread Starter

    Joined:
    Mar 19, 2010
    Messages:
    87
    Hi, I have are report which displays results from a search that includes all of my tables.
    I am currently testing the report and upto now it is displaying the correct results but is repeating them.

    For example, a search i am running should return two results, but is repeating them over and over.

    Any help would be great.

    cheers
    Lewis
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Lewis, duplication is normally the result of having more than one table in the query and the relationship between them.
    Particularly the way that the table are "Joined" in the query. If the default "all records where both records are equal" is used you get a "set of records" from each table for each single record in the "Master" table.
    You need to identify the Master or main table and check the joins from that to the other tables.
    If you right click on the join line you can edit it, you need to selct the option that says "all records from (the main table) and only those equal in the other table".
    You can also try setting the Query Property "Unique Records" or "Unique Values" to "Yes".
     
  3. ibanezsicko

    ibanezsicko Thread Starter

    Joined:
    Mar 19, 2010
    Messages:
    87
    Hi there,

    Ive sorted my duplicating results issue by using a union query. however, i now have another problem.

    If i search for a works order #, e.g WO3
    the query looks for everything containing that. so the results i get back would be something like:

    WO3
    WO31
    WO3425 etc etc

    This is probably due to the 'Like' in the WHERE clause.

    Is the anything different to use, so that it searches for exact matches.


    Cheers
    Lewis
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Do not use the Like "*".
    Use whatever the user is looking for, ideally create a "Report Form" where the user can enter data to be searched for.
    For exact matching I would use a works order Combo box to make the selection because they avoid typing errors.
     
  5. ibanezsicko

    ibanezsicko Thread Starter

    Joined:
    Mar 19, 2010
    Messages:
    87
    Hi,

    I'm not entirely sure what you mean by 'Report Form' but if i change the query to something like:
    ([table1].[field1] = [searchtable].[field1])
    as opposed to:
    ([table1].[field1] Like "*" & [searchtable].[field1] & "*")
    ... it doesnt return any results if i leave one (or more) of the fields empty.

    Is their anything i can inlcude in the query that ignores the fields if they're empty?

    Lewis
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Lewis, there are 2 ways to do what you want, one is careful arrangement of the Query design and the other is to use VBA code to rewrite the Query each time you use it.
    It also depends on what you want it to do with the searched fields, i.e. do you want it to find records that have any one of the fields or only records that have all the fields equal to the ones you are searching for.
    The difference is using an "Or" (any one) or using an "And" (all of them)

    Can you post a zipped copy of the database in Access 2003 format for me to look at, with some instructions of exactly what you want it to do?

    I can post an example of a Search form & query that uses "complicated" combinations of fields for you to look at.
     
  7. ibanezsicko

    ibanezsicko Thread Starter

    Joined:
    Mar 19, 2010
    Messages:
    87
    Hi,

    I've attached the database and a little 'readme' with some info.

    Thanks so much for your time and effort.

    Lewis
     

    Attached Files:

  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Lewis, as it doesn't appear you are able to change the database I have changed the SQL for the 1st Table in your qryUnion Query and it works with entering a Works Order or a Works Order & a Sales Order.
    I will leave it up to you to do the other Tables in the Union query's SQL.
    To have Inclusive or Exclusive you have to use either "AND" or "OR" as I have here.

    With a fair bit of work I could probably convert the Queries to a Single QueryDef and One Report, but I am not sure if it is worth it if you are happy with the way it currently works.

    I left the first test query that I wrote to get the correct SQL syntax, it is called "tblCertificateOfConformity Query1", it can be deleted.
     
  9. ibanezsicko

    ibanezsicko Thread Starter

    Joined:
    Mar 19, 2010
    Messages:
    87
    I dont use forums very much, so please excuse my naivity, but where can i find the edited database?
     
  10. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Sorry, for some reason it didn't Attach.
     

    Attached Files:

  11. ibanezsicko

    ibanezsicko Thread Starter

    Joined:
    Mar 19, 2010
    Messages:
    87
    No problem.

    Thanks again. I'll have another go with it next chance i get.

    Cheers
    Lewis
     
  12. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/917073

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice