Results 1 to 14 of 14
  1. #1
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59

    Unhappy Unanswered: Relationships and querries and subforms HELP Please

    Hi

    I'm having a little problem with a query I'm trying to construct; I have a form with client orders in it in a subform based on the order details query,

    The client orders table has the following fields
    OrderdetailsID, PK
    OrderID,
    Product,
    Quantity,
    UnitPrice,
    Total,
    InternalID, (this is what I'm trying to link to the stock/equipment table)

    The stock/equipment table has the following:
    EquipID, PK
    InternalID, (this is what I want to be able to join to the order details table)
    EquipmentType,
    Serial,
    Etc,
    Etc,
    Sold, (Want this to appear on order details subform)
    Date Sold, (Want this to appear on order details subform)
    Ref, (Want this to appear on order details subform)

    I thought if I created a relationship between the two tables via the internalID's, Then amended my order details query to include the stock/equipment table with the Sold, Date Sold, Ref fields added. I would then be able to enter an internalID on the internalID field on the order details subform and it would allow me to mark that item as sold in the stock/equipment table via that query.

    But it didn't, instead I get no order details what so ever, or if i mess around with it i get the results replicated 1000's of time so i end up with 2560 results.

    I'm doing something wrong

    Any ideas please, my testdb is attached with the problem

    Thanks

    Icerat
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59

    Arrow Anyone

    Hi
    Anyone got any ideas on this, got me boss breathing down my neck for it, it has to be linked on the InternalID if possible as every component we have is catalogued on that ID.

    All i need the subform to be able to do is display the sold, date sold, ref fields so that i can mark items as sold and the date they were sold along with the customer "ref" number.

    Thanks
    Icerat
    Last edited by Icerat; 07-20-04 at 12:14. Reason: Explain myself a little better

  3. #3
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    Hi
    Thought i would try and Explain myself a little better

    Thanks
    Icerat

  4. #4
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Hi Icerat,

    You can set up a subsubform. Something like the attached screen.

    (The order details were blank because the subform was trying to link to the master form on orderid...which was located in the orders subform).


    HTH,

    Chris
    Attached Files Attached Files

  5. #5
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    Thanks for the respose

    I need the db to show me the orders taken by each employee for each individual customer, like it does at the mo, if you click an employee in the top form it displays the order details for that client, say that client comes back again and another employee takes and order all the employee has to do is select there name in the orders subform and then add the relivent order details in the suform below it,

    Can this be done, all i need the order details subform to be able to do is display the sold, date sold, ref fields from the stock/equipment table so that i can mark items as sold, along with the date they were sold as well as the cutomer "ref" number.

    Thanks hope you can help some more, could you post the amendments to the db as an attachement so i could look at the db.

    Thanks again
    Icerat

  6. #6
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    No Prob. I'll do it after hours here, so it may take a few hours (it's 11am here)

    If someone else gets to it first, that's cool too.

  7. #7
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    I'm attaching your db. The forms are labeled with 'TEST'.

    You may want to revisit your table structure. I was a little confused with InternalID and the placement of some of the fields.

    For instance, tblProducts should be tied to tblEquipment then to tblOrderDetail.

    There are many gurus here that could help you with that much better than I could. Once the data structure is solid, the forms/reporting tend to fall into place.

    HTH,

    Chris
    Attached Files Attached Files

  8. #8
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    Hi
    Thanks for the reply i'll definitly have a look and see what youve done, thanks for the help, when you say "You may want to revisit your table structure" can you explain a little more please as im new to access and would love any assistance you can give.

    Thanks
    Icerat

  9. #9
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Sure, if you can wait until tomorrow...I'll upload a sample of how I would lay out the tables.

    You can also search for 'data normalization'. There are a lot of articles here or Google it. (Not certain if the search function is working on this forum yet)

  10. #10
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    hi
    np i'll wait,

    thanks
    Icerat

  11. #11
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    Hi cpgospi

    Cracked it, its not exactly how i wanted it but it works, when i now enter the InternalID in the order details subform and expand the table underneath it shows the details for that item so that i can now mark the item as sold.

    If you have any sugesstions on how i can improve it, table layout etc etc it would be greatly appriciated.

    Ive attached the db so you can have a look.

    Thanks again cpgospi

    Icerat
    Attached Files Attached Files

  12. #12
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Hey Icerat,

    Looks good to me. The only suggestion I would make is joining the tables on differant fields.
    Typically you would want to join tables from a Primary Key (usually autonumber if possible) to a Foreign Key (type long integer). It makes it a more efficient DB.
    You've actually done that in the tbl_OrderDetails but not in tbl_stock/equip
    Using your setup, you will be storing duplicate info in 2 tables (like in tblLocation & tblStock/Equip). The duplicate data being potentially 50 Char text strings. So storing long integers as the foreign key field would be much more efficient in tbl_stock/equip. The way you have it may work just fine depending on the scope of your DB.

    Hope that helps. Let me know if you need clarification. (Was up all night with a sick dog, so I'm a little groggy today.)

    Chris

  13. #13
    Join Date
    Jul 2004
    Location
    Kent
    Posts
    59
    Hi Chris
    thanks for the advise, im pretty new to access so im not 100% sure what youu going on about but i think i have an idea. is there anyway you point me in the right direction to improving the design.

    Thanks
    Mike
    Attached Files Attached Files
    Last edited by Icerat; 07-22-04 at 09:38.

  14. #14
    Join Date
    Sep 2003
    Location
    MN US
    Posts
    313
    Hi Mike,

    This may give you some ideas.

    Thanks,

    Chris
    Attached Files Attached Files

Posting Permissions

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