Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012

    Unanswered: Hyperlink substitution

    Hopefully I can explain what I want to do well enough for everyone to understand

    I am creating a database at work (with no access training...having to teach myself, got 4 books on order from amazon coming in that will hopefully help)
    the goal i have in mind is that you can search for your "component" and it will list all the drawings that component is on and the drawings are all hyperlinks to another database that i have links for.
    Well... i have the components listed and their drawings and i have the drawings to their links and i don't know how to set up a relationship between them and have a query show just the links.

    One table would be set up like this:
    Component A DWG1 DWG2 DWG3 DWG4
    Component B DWG5 DWG2 DWG6 DWG3

    multiple components can be on a drawing and a component can have multiple drawings

    the other table looks like this:

    DWG1 DWG1(hyperlink)
    DWG2 DWG2(hyperlink)
    DWG3 DWG3(hyperlink)

    What I need is basically :

    Component A DWG1(Hyperlink) DWG2(Hyperlink) DWG3(Hyperlink)
    Component B DWG4(Hyperlink) DWG3(Hyperlink) DWG1(Hyperlink)

    Here is a few of the lines from the first table showing the components and their drawings so hopefully you get a better idea.

    0BA-XPH-02-N 5G0941-BA-05
    0BA-XPH-03-N 5G0941-BA-06
    0IR-LPA-10A-N 6G0941-IR-04 5G0941-IR-20
    0IR-LPA-10-N 5G0941-IR-01 6G0941-IR-04 5G0941-IR-20

    Hopefully that makes sense... I am pretty sure that if i was in excel i would just do vlookup and substitute my values over, but i don't know how to do that in access and keep the hyperlink.

    Any ideas are much appreciated and I thank you in advance!

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    don't store simialr (repeating) values on a single row
    nearly alwasy whenever you see soemthign like drawing1, drawing2, drawing3 in a table defintion its a symptom of suspect design

    a drawing exists in its own right
    a part may be represented by many drawings (in these context many is the db definition ie more than one)
    a part exists in its own riight
    an assembly (essentially a bigger part) exists in its own right

    so I'd expect a table something like
    drawings //details specific to a drawing
    **optionally** drawingrevision
    parts //details sepcific to that part
    partsdrawings an intersection table identifying an association between a specific drawing and a specific part (and or revision number)
    partsgozinto //effectivley is an intersection table between parts and parts and identifies what part is used in a parent part. if you never ever had one part used in more than one parent assembly then you could store that in the parts table itself.

    why do it this way

    well what happens if, say someone decided that you needed say 5 drawings for this part.. your model breaks
    if you want to find where a part or drawing is used on your model you have to write the query to find the part in any one of however many drawing / part columns you have. this mode you look for the relevant piece of information in a single column

    when you want create your table you can use either a pivot table or build the list using code.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2012
    First let me thank you for replying! Any advice in this topic is much appreciated as i am still very much a newbie at access and i have no one in my office that can even tell you what an if/then loop is to help me program this thing or bounce ideas off of.

    I am trying to figure out how my queries will look for my user based on this path.
    What i am worried about is how all the information is stored right now and how its being used.
    basically i have an instrument located on a panel thats connected to a pipe with a senseline (smaller pipe). So i have multiple instruments on a panel and more than one senseline can be assigned to a instrument, more than one sense line can be located on a panel and through all of this i have drawings. Drawings can have multiple panels, multiple instruments and multiple senselines on each one AND panels/instruments/senselines can have multiple drawings assigned to each one as well.
    The idea behind the database was to give the engineers a way to look up a panel, see the drawings that the panel is located on, see what instruments it has, what senselines those instruments are hooked up to and have the drawings link them to our drawing database so they can pull the drawing up in one location.
    Alternatively, i was thinking that while i was doing that i could make it so you could look up a senseline and find out what instrument its tied to, what panel its on, and drawings associated with the panel or doing the same thing with instruments so that no matter where you started from, you could get the same information.

    If i use drawings as my pivot point, then i'll have the same problem i have now, just in reverse. i will have too many panels associated to one drawing and not sure how to have a code say "okay i need to look at each row and find every drawing that panel X is on even if its not located in the same column each time and then look to another table through a relationship to find my information"
    which is basically my issue now, i need it to look through each column to find drawing X and then use that drawing to link to another table which has drawing X associated with hyperlink X

    So i am worried that my dataset is whats really wrong because i have many to many to many to many =/


    *Offtopic* I like the bikes on your sig! My boyfriend just sold his CBR 1000. I don't like those crotch rockets at all so i have been trying to talk him into a goldwing so i can be comfy on the back but he says he isn't old yet lol. I will admit that crotch rockets are pretty though!

Posting Permissions

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