Results 1 to 14 of 14
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Possible SQL ! (A2000)

    I'll try to explain here what I'm trying to.

    Estimating damage to vehicles can sometimes be easy or very complicated, dependant on size of damage.
    The process starts with an estimator looking at the damage along with a tick sheet.
    Starting with the main items, all damage is noted and new parts are noted on the tick sheet.
    Now.
    A main item can be : Bumper, Door, Wing, Bonnet, etc etc etc.
    The estimator must then add other associated parts relevant to the main part.
    So.
    If a new Bumper is required, new brackets and a trim are also required.
    If a new door is required, new clips,door foil,bead sealant are also required.
    So here we can see a relationship.

    Now here's what I want to do.
    When the estimator selects, Bumper, The system will know the other parts are also required.
    I can do this quite easily using two list box's or combo's, the second having a relationship bound to the first.
    I don't want to do it that way.

    I have so far, three tables:
    tblEstimateItems (This holds the main Items, Bumper, Door, etc) with fields:
    |Code|EstimateItems|

    tblAssociatedItems (Holding the other required items) with fields:
    |MainCode|AssCode|AssociatedItem|

    tblEstimateDetails (Holding all info of the other two tables which are primarily lookups) With fields:
    |EstimateNo|Supp|Code|Item|

    The link between table 1 & 2 is [Code] & [MainCode] holding the same code.

    Ideally when the user selects a main part from tblEstimateItems via it's code, ie FB, table tblEstimateDetails is populated with
    FB - Front Bumper and all associated parts from tblAssociatedItems with the code of FB as it's [MainCode].

    Any Idea's on the best way to achieve this ??
    Attached Files Attached Files

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

    Re: Possible SQL ! (A2000)

    Dave,

    I'll have a closer look at this later on, but my first reaction is that you don't need to put the associated parts into tblEstimateDetails - it's superfluous information, so it violates relational database design. You can use a query to return *all* parts involved, and use this query as a basis for estimates, forms, reports, ...

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Possible SQL ! (A2000)

    Hans
    If you still have the demo I posted the other day, (re:Lookup Table) you will see what I mean from the input form.
    I suppose code behind the selection combo may be the answer.
    Thanks

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Possible SQL ! (A2000)

    <P ID="edit" class=small>(Edited by D Willett on 07-Feb-03 17:03. )</P>This is better on SubForm (sbfEstimateDetails), but need to change the SQL to work from the main form, (frmEstimateDetails) referring to the subform.


    Private Sub cboCode_AfterUpdate()
    If cboCode = "UN" Then
    txtItem.Locked = False
    txtItem = Null
    Else
    txtItem.Locked = True
    txtItem = cboCode.Column(1)
    Dim strSQL As String
    strSQL = "INSERT INTO tblEstimateDetails ( Code, Item )SELECT tblAssociatedParts.MainCode, tblAssociatedParts.AssociatedItem FROM tblAssociatedParts WHERE (((tblAssociatedParts.MainCode) = [Forms]![sbfEstimateDetails]![cboCode]))ORDER BY tblAssociatedParts.MainCode;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (strSQL)
    DoCmd.SetWarnings True
    Me.Requery
    DoCmd.GoToRecord , , acNewRec
    End If
    Exit Sub
    End Sub
    Attached Files Attached Files

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

    Re: Possible SQL ! (A2000)

    Dave,

    Referring to the subform isn't difficult, but see below before you rush to implement that.

    When a form with a subform is opened, only the main form is added to the Forms collection; the subform isn't. You *must* refer to the subform and its controls by way of the main form. In this case, you must replace [Forms]![sbfEstimateDetails]![cboCode] by [Forms]![frmEstimateDetails]![sbfEstimateDetails]![cboCode].

    See Forms: Refer to Form and Subform properties and controls on Dev Ashish's Access Web for an overview of referring to subforms and their controls.

    But there are several other issues to be solved before you can use this in a production application:
    <UL><LI>Your code only inserts an item code and description, but no estimate number, so the new records will be orphans.
    <LI>There is no link between the main item and the associated items inserted by it. If the main item is deleted or modified, nothing happens to its associated items.
    <LI>The associated items can be deleted or modified individually after they have been inserted.[/list]I noticed that the main and associated tables overlap partially, with differences in spelling. I think all items (main and associated) should be in one table, with either extra fields or a separate table to define the relationship between main and associated items. What is exact relationship between main items and associated items?
    <UL><LI>Can each associated item also act as main item, or are some of them only valid as associated item?
    <LI>One main item can have several associated items (e.g. NFD). Can one associated item belong to more than one main item? In the current setup, this isn't possible, since the Primary Key of tblAssociatedParts is on the associated item.
    <LI>Can there be cascading associated items? That is, selecting main item A selects associated item B, and this in its turn selects associated item C.[/list]The best way to structure your database depends on the answers to these questions. As you see, it's more complicated than it looked originally.

  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Possible SQL ! (A2000)

    Hans
    Thanks for your comments.
    I managed to sort the reference problem ie:

    Private Sub cboCode_AfterUpdate()
    If cboCode = "UN" Then
    txtItem.Locked = False
    txtItem = Null
    Else
    txtItem.Locked = True
    txtItem = cboCode.Column(1)
    Dim strSQL As String
    strSQL = "INSERT INTO tblEstimateDetails ( Code, Item )SELECT tblAssociatedParts.MainCode, tblAssociatedParts.AssociatedItem FROM tblAssociatedParts WHERE (((tblAssociatedParts.MainCode)=[cbocode]));"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (strSQL)
    DoCmd.SetWarnings True
    Me.Requery
    DoCmd.GoToRecord , , acNewRec
    End If
    Exit Sub
    End Sub

    I had the doc from Dev's site some-where on my hard drive but couldn't find it for the life of me !

    The issues as you mentioned, Estimate No was my next issue to sort, For the time being I am only playing about with a demo before implementing in the main db.
    Each item must have the facility to be deleted as mentioned individually, (Some Insurers, who are our main clients do not like these add-ons as we call them, dependant on contract)
    None of the associated items can be selected as a main item, hence the facility of the "UN" code.
    This gives the user greater flexibility, As with the Look-Up posting, there are far too many items to remember.
    The associated table will only ever be a hidden part, none editable, only by an administrator, adding, deleting only as the market trend changes.
    Our industry is very complicated where estimating is concerned.
    The whole item will only ever be as it is now, a two tier system, we will never facilitate a third selection.

    Your thoughts as ever are welcomed, thanks for taking the time out for your advice and knowledge.

    <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Possible SQL ! (A2000)

    Dave,
    <UL><LI>"None of the associated items can be selected as a main item."
    How about AFZ, FBFK, FBTAC, NFDH and NFDMC? They occur in tblEstimateItems and in tblAssociatedParts.
    <LI>"The associated table will only ever be a hidden part, none editable, only by an administrator, adding, deleting only as the market trend changes."
    What I meant in my previous reply was that once the associated items have been inserted into tblEstimateDetails, the user can delete them (which is what you want), but also change them to another part (which may or may not be what you want). If the user then changes or deletes the main item, what must be done with the associated parts (some of which may have been deleted or changed to another part)?
    <LI>If you keep the associated parts as a separate table, you won't be able to create a relationship between tblEstimateDetails and tblEstimateItems with referential integrity, because inserting associated items into tblEstimateDetails will create records with codes that are not present in tblEstimateItems.[/list]If you don't solve these issues, your application is going to be very fragile - your estimates will be in danger of being inaccurate; your company's accountant won't like that...

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

    Re: Possible SQL ! (A2000)

    Dave,

    To prevent the associated items from becoming poor little orphans, you can expand the append query to set not only code and item, but also the estimate number of the "parent". Something like

    strSQL = "INSERT INTO tblEstimateDetails (EstimateNo, Code, Item) SELECT [Forms]![frmEstimateDetails]![sbfEstimateDetails]![EstimateNo], MainCode, AssociatedItem FROM tblAssociatedParts WHERE MainCode=[Forms]![frmEstimateDetails]![sbfEstimateDetails]![cbocode];"

    assuming that EstimateNo is in sbfEstimateDetails. If you need the supp field too, you can add that the same way.

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Possible SQL ! (A2000)

    Aaaaaaahhhh

    I see what you mean now.
    My idea was to have a table for the main parts with a "No Duplicates" property. the Front Bumper code is FB.
    The second table tblAssoc........ could have duplicates as a link to the main items.

    FB|FBTAC|
    FB|FBS|

    So How do I accomlish this another way ?
    Another idea with setting up this way was giving the user the option to include the assoc......... items or not, on entering the main items.

    p.s
    I don't like the company accountant any-way, but don't tell him. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

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

    Re: Possible SQL ! (A2000)

    I will have a look at it. This is not a trivial problem...

  11. #11
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Possible SQL ! (A2000)

    This is not a trivial problem..., you're quite right Hans.

    I've been <img src=/S/compute.gif border=0 alt=compute width=40 height=20> about with it for a couple of days now, and feel I'm <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>.
    Pondering which is the best way to solve this. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> .

    I've got it working some where near to the way my users will be comfortable & will be manageable.

    Here's the only issue that's holding me up:

    Which is the best way to manage the sort order in the subform.
    I have to use a DoCmd.Requery to update the subform and pull all the associated parts to be visible data.
    So, Using the code FB, The subform requeries on after update of the combo and shows the main item and any assoc. items also.

    The Main item should be above the rest ie:
    MainItem
    Assoc
    Assoc
    Assoc
    MainItem
    Assoc
    Assoc
    Assoc

    Instead I get:

    Assoc
    Assoc
    MainItem
    MainItem
    Assoc
    Assoc so-forth and so-forth.

    So my problem is how to set these items in the order they are enterred.
    This does not have to be Alhabetically or Numerical, Simply as the data is enterred.
    I thought of adding a field Date/Time = Now(), but on requiry, the Assoc items have the same Date/Time right down to the same seconds.
    I thought of an extra field with AutoNumber, but as each Estimate has a different Estimate number, Confusion would set in duplicating.
    I need a way of tagging the main item to always stay at the top of the Assoc item.

    I'm stumped and can't think of a way to remedy this.

    Any Idea's ?

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

    Re: Possible SQL ! (A2000)

    I have been thinking about this and I haven't found a real solution. I have attached what I have come up with.

    All associated and "other" items are accessible via command buttons only; to see an overview, the user needs to preview a report.

    If I find time, I will post an explanation of some of the features later.
    Attached Files Attached Files

  13. #13
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Possible SQL ! (A2000)

    Very Interesting.
    I'll post back when I've digested it a little

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

    Re: Possible SQL ! (A2000)

    The version attached to my previous reply doesn't display the correct total amount in the form (it does in the report) - the DSum referenced the wrong query. I have attached a corrected version.
    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
  •