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

    Field in table set to a query value (Access 2000)

    I am in a dilema here for the relationships, and I am wondering if this is possable. Basically, I have a products table and a mixed product table. A mixed product is a combination of products. Each table have all column the same fields except for the qtyOnHand for the mixed products. Currently, the mixedproducts table does not have QtyOnHand. I am wondering if it is possable to have a QtyOnHand field set up for the MixedProducts, to equal a query value. Mixed Products also link to another table which tell what the components of the mixed products are.

    My second part is this. If I can make a field equal a query, would it be wise to merge the two tables together(Products and MixedProducts), since they would contain all the same columns, and add a boolean field to determine if the product is mixed or not? The thing that I worried about is that the QtyOnHand for MixedProduct would be a query, whereas for a product, it would be just an integer?

    Any ideas would be great?

    Thanks

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

    Re: Field in table set to a query value (Access 2000)

    Will the QtyOnHand value for a given mixed product be calculated (for example from information about its components), or is it fixed?
    If it is calculated, will the outcome of the calculation ever change for a given mixed product, or does it remain the same once it has been calculated?

    If QtyOnHand is essentially fixed (even if it has to be calculated initially), it makes sense to store it in a field in the table, and it would also make sense to combine the Products and Mixed Products tables. This would still be valid if the value of QtyOnHand changes very seldom, say once a year.

    On the other hand, if QtyOnHand needs to be recalculated frequently or whenever you use it, it's better not to store it in a field in the table, but always use a query. And in that case, don't merge the tables.

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

    Re: Field in table set to a query value (Access 2000)

    I decided to go with how I currently have it. The QtyOnHand does get recaclauted alot, since it is based on the product qty, which does get changed alot. I was just hoping I could make getting the qty for mixed products a little easier for the serveral different instaces that I need it.

    Thanks for your suggestions

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

    Re: Field in table set to a query value (Access 2000)

    You can use a union query to combine mixed and non-mixed products for reporting purposes.

    If combining the tables into one would make life easier for you, you could do that, with a boolean field IsMixed to indicate whether a product is mixed. For mixed products, the QtyOnHand field would stay empty. You could then create a query that returns all field from the combined table, plus a calculated field of the form
    CalculatedQty: IIf([IsMixed],calculated_expression,[QtyOnHand])
    where calculated_expression is a formula that calculates QtyOnHand for mixed products.

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

    Re: Field in table set to a query value (Access 2000)

    Yeah I have been using Union Querys for reporting, so that is not so much an issue. It is just harder then I think it needs to be for finding backorder and damaged items in an invoice. Nevertheless, since I am so far along now, it would take to much time to go back and change the forms.

    Thanks

Posting Permissions

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