Results 1 to 4 of 4

Thread: MS Access - repeating results

  1. #1
    Senior Member
    Join Date
    Dec 2004
    Posts
    360
    Thanks
    27
    Thanked
    2 times in 2 posts
    • piggeh's system
      • Motherboard:
      • ASUS AiLife IP35
      • CPU:
      • Intel Q6600 G0
      • Memory:
      • OCZ 2x2GB DDR2 (5-4-4-18)
      • Storage:
      • 500GB Spinpoint
      • Graphics card(s):
      • Inno3D 8800GT
      • PSU:
      • Corsair HX520W
      • Case:
      • Antec 900 Case
      • Operating System:
      • Vista 64
      • Monitor(s):
      • Samsung 24" SM2494HS
      • Internet:
      • Virgin Cable

    MS Access - repeating results

    I have recently been working on a purchase order database in Access for work. This basically works with a purchase order details table containing normal order info like delivery date, delivery address, supplier name etc, then a subform which contains the individual item lines (product codes, descriptions, etc).

    I then output it onto a report which uses a query as its source so that it only outputs the PO showing on the form at the time.

    This all works splendidly except that when it outputs onto the report, if there are more than one product row on the subform, it will then repeat the results, as per below.



    So if there's 3 items on the purchase order, it will show all items 3 times. If there's 4, it will show 4 times, etc..

    Has anyone come across this before and know how to fix it please?

  2. #2
    PHP Geek Flash477's Avatar
    Join Date
    Dec 2008
    Location
    Devon
    Posts
    822
    Thanks
    51
    Thanked
    72 times in 65 posts

    Re: MS Access - repeating results

    Have you checked the query and on what key the subform is joined on?

  3. #3
    Senior Member
    Join Date
    Dec 2004
    Posts
    360
    Thanks
    27
    Thanked
    2 times in 2 posts
    • piggeh's system
      • Motherboard:
      • ASUS AiLife IP35
      • CPU:
      • Intel Q6600 G0
      • Memory:
      • OCZ 2x2GB DDR2 (5-4-4-18)
      • Storage:
      • 500GB Spinpoint
      • Graphics card(s):
      • Inno3D 8800GT
      • PSU:
      • Corsair HX520W
      • Case:
      • Antec 900 Case
      • Operating System:
      • Vista 64
      • Monitor(s):
      • Samsung 24" SM2494HS
      • Internet:
      • Virgin Cable

    Re: MS Access - repeating results

    its joined on the POID, which is the primary key within the purchase order details table and which is used as the purchase order number.

    PO Details table
    POID
    Supplier
    Delivery date
    etc
    ..

    PO Items table
    ItemID
    POID
    Description
    Qty
    etc
    ..

    I think it's the query - the report design has a sub query for the items which is maybe where it's going wrong. Will have a look tomorrow morning (getting late now and brain is fried).

    I had to create a second query within the report as generating the results as one query kept creating an error regarding too many fields. There's about 30 or so fields that I need on the report:

    Supplier
    Address1
    Address2
    Address3
    Address4
    Postcode

    Invoice Address
    (then similar fields to above)

    Delivery Address
    (same)

    PO Date
    Delivery Date
    Authoriser
    Queries Contact
    Requisitioner
    PO Number
    Contact Tel
    PO Total

    Then PO Items

    Supplier Code
    Description
    Qty
    Unit of Measure
    Cost/Unit
    Total Cost

    Can't streamline these really, though I would have thought Access could handle 30 fields! I guess maybe because this is a query within a query it's generating multiple times?

  4. #4
    Senior Member
    Join Date
    Dec 2004
    Posts
    360
    Thanks
    27
    Thanked
    2 times in 2 posts
    • piggeh's system
      • Motherboard:
      • ASUS AiLife IP35
      • CPU:
      • Intel Q6600 G0
      • Memory:
      • OCZ 2x2GB DDR2 (5-4-4-18)
      • Storage:
      • 500GB Spinpoint
      • Graphics card(s):
      • Inno3D 8800GT
      • PSU:
      • Corsair HX520W
      • Case:
      • Antec 900 Case
      • Operating System:
      • Vista 64
      • Monitor(s):
      • Samsung 24" SM2494HS
      • Internet:
      • Virgin Cable

    Re: MS Access - repeating results

    Oh well, sorted.

    I managed to get the main report to accept the additional fields that it stopped on before and got rid of the sub-report/query.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. old xp installation user access
    By vincent4yuffie in forum Help! Quick Relief From Tech Headaches
    Replies: 3
    Last Post: 03-11-2006, 11:14 AM
  2. NT4 Domain - A: drive access
    By joshwa in forum Software
    Replies: 1
    Last Post: 27-08-2003, 09:07 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
  •