Results 1 to 12 of 12
  1. #1
    Grofica is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Location
    Vegas
    Posts
    6

    Exclamation Report issue driving me crazy

    Good afternoon all, forgive this being my first post but i am so angry I just dont even know what to do.

    My problem is this:

    I have a datebase. I have 2 main Tables (called new store and Rent Data). I have them linked by a primary key on a one to one type (include all records from rent data and only those of new store where the joined fields are equal.)

    I have one query that takes specific data from my two tables. the main point of concern being my status (only extracts certain data that is "pending approval")

    I have a report generated off this query however when i "test" it and mark only one item "pending approval" it generates approximatly 60 something lines and repeats it over and over again. i can not get it to pull the report with that item being listed only 1 time.

    someone please help!

    Thanks

    ~Grofica

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Does the query return the one desired record, or is the repetition coming from there (which is my assumption)? If the problem is in the query, what is the SQL of the query?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Grofica is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Location
    Vegas
    Posts
    6
    The repetition has to be coming from the query but i am not sure how... i changed only 1 record to test it and it is just repeating over and over again...

    i just used the basic "pending approval" under criteria in the design view of the Query.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you change it to SQL view and copy the SQL here? I suspect the problem is with the tables, and you can't see them in that image.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Grofica is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Location
    Vegas
    Posts
    6
    you are a life saver!

    SELECT K.ID, [New Store Developement].Approval, [New Store Developement].Region, [New Store Developement].[Location Name], [New Store Developement].[Location Address], K.[Sq Feet], K.Rent, K.NNN, [Rent]+[NNN]*[Sq Feet]/12 AS [Monthly Rent], K.[Delivery Condition], K.[Contractor bid to VBox], K.[Vanilla shell bid], K.[TI allowance], K.[V approved], Kn.[MDF Allowance], K.[Estimated Delivery], K.[Delivery days per LOI], K.[Estimated open date]
    FROM K, [New Store Developement]
    WHERE ((([New Store Developement].Approval)="Pending Approval"));

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    As I suspected, the lack of a join between the tables is causing you to get a Cartesian product. In design view, you need to click and drag between the key field shared by the two tables (the field(s) that relate them). You'll see a line between them. In SQL view it will look like:

    FROM K INNER JOIN [New Store Developement] ON K.FieldName = [New Store Developement].FieldName

    Basically you have to tell the query how the two tables are related. If relationships are set up, it will usually join them automatically.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Grofica is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Location
    Vegas
    Posts
    6
    The relationships are set up.... did i perhaps do it incorrectly?

    i have it set as a one to one with a join type of "include all records from K and only those records from "new store" where the joined fields are equal.

    i did it like that so that when i entered new data on K. it would update on the New store sheet.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Like I said, usually the join will get automatically added, but not always. Try this in SQL view:

    SELECT K.ID, [New Store Developement].Approval, [New Store Developement].Region, [New Store Developement].[Location Name], [New Store Developement].[Location Address], K.[Sq Feet], K.Rent, K.NNN, [Rent]+[NNN]*[Sq Feet]/12 AS [Monthly Rent], K.[Delivery Condition], K.[Contractor bid to VBox], K.[Vanilla shell bid], K.[TI allowance], K.[V approved], Kn.[MDF Allowance], K.[Estimated Delivery], K.[Delivery days per LOI], K.[Estimated open date]
    FROM K INNER JOIN [New Store Developement] ON K.ID= [New Store Developement].ID
    WHERE ((([New Store Developement].Approval)="Pending Approval"));
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Grofica is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Location
    Vegas
    Posts
    6
    in a completely non-weird way... i love you!

    you solved the problem! THANK YOU THANK YOU THANK YOU!

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    LOL! For your own good, I won't tell my wife. She doesn't like to share.

    Welcome to the site. Just noticed the Vegas; I get down there every couple of months for my employer.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    Grofica is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2011
    Location
    Vegas
    Posts
    6
    well i didnt mean it that way....

    you do not know how much time i spent yesterday going "what the heck is wrong" i must have checked everything like 50 times and spent hours trying to google the error. (that was the first time google ever let me down - im still a little let down for that)

    i even asked a few of the fellow geeks at work but i guess i am a little more geeky then them.they all looked at me like i was speaking chinese. thats why i joined this forum is cause i was so mad i could not figure it out. and i knew i needed to find someone smarter then me.

    I told me hubby i got my answer from a forum and he was happy for me i got it figured out but hes not a big computer person so he just nods his head and says good... :-)

    oh this started out my day fantastic! thank you!.

    Yeah we choose vegas cause its warm and the houses are nice! (and cheap) he he he he again thank you.... that really made my day!

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No problem, and don't worry; I knew you what you meant with the "love" bit. I was just kidding around. If you get stuck again, post back.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating a driving relationship on a form
    By ld8732 in forum Database Design
    Replies: 1
    Last Post: 12-31-2010, 06:20 AM
  2. Autonumber gone crazy
    By asearle in forum Access
    Replies: 1
    Last Post: 07-27-2010, 05:41 AM
  3. Pls help... driving me insane!
    By Maverick1501 in forum Reports
    Replies: 1
    Last Post: 03-31-2010, 06:26 AM
  4. HELP with crazy receipt numbering
    By jlm722 in forum Forms
    Replies: 15
    Last Post: 09-23-2009, 01:49 PM
  5. Access 2007 Form Changes Driving Me Nuts
    By Patrick.Grant01 in forum Forms
    Replies: 3
    Last Post: 05-19-2009, 09:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums