Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Subreport (Access 2000)

    Can you help me with an advice about the construction of a report with subreports ? I am attaching my problem and i want at first to explain what i aim and then what i have done.My aim is to build an instrument for supervising the movements of the articles in the stocks of several of our affiliates.Each affiliate receives a certain quantity which we term imported The quantities sold by the affiliate we term Sold out.What remains in their warehouse we call warehouse.therefore:
    the products in the warehouse of each affiliate are imported minus soldout:
    Imported - soldout = warehouse
    Also we want at each moment to know whether output is equal to input or:

    Balance = imported - soldout - warehouse.Any difference here will show that something is not in order.

    I have built successfully the rtpInput and the rptOutput.By the way, with the preciuous help of the Forum. The problem i have is with the report StockFlow.I have made it with 2 supreports, but somehow i cannot show the movement for each product.I am only showing the total result, which is not relevant,
    i need to know the movement of each separate product.In short,my problemn is,that the report Stockflow shows only the total figures and not the figures of individual products.Our products are 260 and therefore it issems to me a formidable task to follow the balance of each product.
    can you advise me how can i begin working on this very important subject for me?
    I have simplified my database on purpose to emphasize my problem.

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

    Re: Subreport (Access 2000)

    What is the meaning of and the relationship between the fields Items, Size and Pack in the Products table?

    What is the meaning of and the relationship between the fields Cartons, Quantity, Liters and Pieces in the Order Details table?

    BTW it's hard to give advice about how to display info about each individual product if the database contains only one product.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subreport (Access 2000)

    Dear Hans,

    Thank you so much, you have written to me that you might help if you receive more information.
    You have asked me about Items, Size and Pack in the Products table? For example antifreeze is placed in cartons, the pack is 24 and the size is 1 ltr.But for the sake of simplicity please consider only cartons.
    In the form Help i have written the example.There are several products imported, and there are two sales out of them.The rest should constitute the OnStock in the warehouse.
    My reports rptInput and rptOutput are excellent.By the way i OWE them to you.You have
    helped me build them.

    What i want now is to compare the products in the reports input and output and see are they all
    right.
    All the people in my company are eager to know whether Access will handle this case.
    It is very interesting and i supose a lot of people will be interested too.It concerns auditing.
    If you make an audit in a warehouse, you must at first nullify.After that you begin calculations from the last Stock receipt.You must also note the last order made before the auditing.
    If we succeed, the programme might audit and revise the warehouse at each moment!!
    Therefore it is so important for us to know is it possible at all

    best regards

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

    Re: Subreport (Access 2000)

    Which field in the Products table corresponds to Cartons in the Order Details table?

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subreport (Access 2000)

    Thank you for your reply.

    The field cartons is only in the table order details and not in the the table products.
    The table order details is under the subform [Forder details extended].
    The table products contains the fields branch and items. In the field branch I write the cartons and in the field items the quantity.For each office there is a field in the table products.
    For example I have the fields branch0,branch1,branch2,etc
    Items0,items,items2 etc

    In the After update event of the field cartons of the subform Forder details extnded I have the following function
    Public Function SetBranches()
    Set MySubform = [Forms]![FOrderInformation]![Forder details extended].[Form]
    Set liters = [Forms]![FOrderInformation]![Forder details extended].[Form].[liters]
    Set UnitPrice = [Forms]![FOrderInformation]![Forder details extended].[Form].[UnitPrice]
    Set extendedprice = [Forms]![FOrderInformation]![Forder details extended].[Form].[extendedprice]
    Set Quantity = [Forms]![FOrderInformation]![Forder details extended].[Form].[Quantity]
    Set cartons = [Forms]![FOrderInformation]![Forder details extended].[Form].[cartons]
    Set pack = [Forms]![FOrderInformation]![Forder details extended].[Form].[pack]

    strCondition = "ProductID=" & MySubform.Productid
    City = Forms![FOrderInformation]![office] - 1
    strWhere = " WHERE " & strCondition
    DoCmd.SetWarnings False
    UpdateCartons = "UPDATE Products SET " & _
    " products.branch" & City & " = products.branch" & City & " - " & cartons & strWhere
    UpdateItems = "UPDATE Products SET " & _
    " products.items" & City & " = products.items" & City & " - " & cartons * pack & strWhere

    Kind regards

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

    Re: Subreport (Access 2000)

    I'm sorry, but this design violates relational database design. You shouldn't have branch and items fields in the products table. Instead, you need a separate table with 4 fields, ProductID, OfficeID, Cartons and Quantity. There should be a separate record for each ProductID - OfficeID combination. The present design leads to all kinds of problems, one of which is the current one.

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Subreport (Access 2000)

    I am grateful for your efforts.I have now some guides where i am wrong and how to improve and this means a lot to me.
    You have always been attentive and ready to help

    Kind regards.

Posting Permissions

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