Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reporting (Access 2000)

    I have a report which displays all products in an invoice. Each product has an status attatched to it. (Eg. Shipped, backorder). Also, a product can have multiple entries. EG 3 Shipped, 3 Backorder.

    In my report, each product that is listed shows: The total amount ordered, Total Shipped, Total Backorder

    Currently, the report will show the same record twice if there are two records of it in the invoice. Is there a way I can devise a query (s) which has the record only showing once instead of twice? the current report is based on multiple query's.

    This image shows an example of the senerio
    Attached Images Attached Images

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

    Re: Reporting (Access 2000)

    Try setting the Unique Values property of the query that acts as record source to Yes.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reporting (Access 2000)

    Unfortunaly, that did not work. There is one field in the recordset that is unique for each one, which is an autonumber

  4. #4
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reporting (Access 2000)

    Also, if the backorder and shipped amount are not the same, then that will not work either. I removed the PK from the query results to test what you have mentioned above

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

    Re: Reporting (Access 2000)

    You should only display those fields in the query that you want to display in the report.

  6. #6
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reporting (Access 2000)

    Yes I do have that now. (that id was there at one point) but all the other values in the query are required [img]/forums/images/smilies/sad.gif[/img]

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

    Re: Reporting (Access 2000)

    If you have required values that are not the same, you will get different lines. What else do you expect? Or perhaps I don't understand what you want to accomplish.

  8. #8
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reporting (Access 2000)

    I do understand that, but there has got to be a way to get data in a report that does nto duplicate the product if part of it is on backorder and part of it is in the order.

    Here is how the data the stored
    Each product purchased has its own record in a table. For each records, the productID, QTY, Single Price, Extended price, and product status is stored. Product Status basically states if a product was instock, on backorder, (or new, damaged, or sold out, but that does apply here)
    If a qty of a product ordered was 5, and 3 were in stock, there would be two records in the db.
    One record stating the details of the product instock, (qty 3 with the single price, extended prices and the product status instock)
    The other record with (qty 2 with the single price, extended prices and the product status backorderd)

    To get the information in a report. I have a qry which gets all products (instock) and another qry to get all products on backorder. Both querys are based on an invoiceID.
    After that, I have another qry which uses the 2 qry's above and a qry to get the product details. The 2 qry's above use a left join to the product qry.

    That is how I get my data. I know that will bring me a product twice if part of the product was shipped and part was put on backorder.

    I do need to rewrite these query's to be able to show both a product which is party shipped and partly on backorder on the same line, but I am unsure how to retrieve the information in this way.

    ANy thoughts on this would be awesome

    Thanks again for taking the tim eon this problem.

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

    Re: Reporting (Access 2000)

    I don't understand your description completely, but it seems to me that you need to create

    1) A union query based on the in-stock products query and the back-order products query that returns all invoiceID / productID combinations that occur.

    2) A query based on the in-stock products query and the back-order products query plus the union query from step 1. Both queries should be joined to the union query on invoiceId and productID, in an outer join that returns all records from the union query. You can put the number in stock in one column and the number in backorder in another column. Each invoiceID / productID combination will have one record in this query; this is the one you will use for your report.

Posting Permissions

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