Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628

    Unhappy Unanswered: Need help with a query...

    Im trying to build a mechanic's aid for building bike wheels. On the front wheel build form, the mechanic selects the following details (all are combo boxes):

    Rim Name (table name - Rims)
    No. of Spokes required (table name - holes)
    Hub Name (taken from a query - front_hub, this is due to the hub table having front and rear hubs in it.)
    Cross Pattern (table name - lace)

    What i am trying to do is to get access to then run a query on all these tables and query, based on what the mechanic has selected, and then provide the necessary technical information for the spoke length calculation to take place.

    The results should have the following fields:

    Rim - Rim name
    ERD - Effective Rim Diameter
    OSB - Lateral Spoke Bed Offset
    ISO - Bead Seat Diameter
    Size - Wheel size (i.e. 19. 20, 24 or 26")
    Hub - Hub Name
    S - Spoke Hole Diameter
    dL - Left Flange Diameter
    wL - Width from the centre of the hub to the centre of the left flange
    dR - Right Flange Diameter
    wR - Width from the centre of the hub to the centre of the Right flange
    F/R - Front or Rear
    OLD - Over Lock Nut Dimension, or frame spacing.
    Cross - Cross Pattern
    Holes - Number of spokes required

    If anyone can help me with this i would be greatly appreciative as im getting pretty stumped with this one.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    possibly i might help, if i knew the table layouts and how they are related

    but i sense trouble

    how could you have a "holes" table which has the number of spokes required? i mean, how in the world could that be a different table than the Rims table? a rim has holes, and it isn't even a case of a one-to-many relationship, because a rim can only have one number of holes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    ok, here's a bit of clarification for you.

    The holes element was originally going to be just that for both the rims and hubs, but i ended up changing it to the number of spokes required as the mechanic should know that, the name of the rim and the hub at the very least.


    I've attached the database for you to have a look at, so you can understand the layouts, what im trying to achieve etc.
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ordinarily i wouldn't open somebody's database just to gain enough understanding so that i can help them phrase their question

    in this case you're in luck, i'm a cyclist myself, so i don't mind

    and ordinarily there's no guarantee that i can even open the database because i usually don't have the same database they do

    in this case you're in luck, i can see your access 97 tables fine

    as i feared, your tables are not related

    so it has to be the combobox values that select one row from each table, thus they can be "cross joined"

    the tricky part is determining which query to use, front_hub or rear_hub

    i'm sorry, i don't know how to do that based on the front/rear form

    (i don't use forms or combo boxes so i have no idea how to generate the right query)

    here's the query for the front hub:
    Code:
    select rims.rim
         , rims.ERD
         , rims.OSB
         , rims.ISO
         , rims.wheel_size
         , h.Hub
         , h.s
         , h.dl
         , h.wl
         , h.dr
         , h.wr
         , h.[f/r]
         , h.cogs
         , h.OLD
         , lace.cross
         , holes.holes
      from rims
         , front_hub as h
         , lace
         , holes
     where rims.ID    = combobox value
       and h.hubID    = combobox value
       and lace.id    = combobox value
       and holes.id   = combobox value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Thanks for that, i'll explain the setup of it a bit further.

    I thought it would be easier for the mechanic to choose which wheel he was building (rather than having to put the dimensions for the front and rear wheel in when he's only working on the one wheel).

    I have been thinking on seperating the hubs table to have only front and rear hubs (both individual tables), which could make things a little easier.

    Basically, the operation of the front wheel form and the rear wheel forms are the same, except for the data sources.

    The operation im trying to do is this:

    The user selects a rim. using the after update event, access goes and gets the specific data for that rim. I was trying originally to use the recordset option for this, but i ran into a problem applying the data to this excel formula:

    =SQRT((dL/2*SIN(2*PI()*Cross/(N/2)))^2+(ERD/2-((dL/2)*COS(2*PI()*Cross/(N/2))))^2+WL_Effective^2)-S/2

    I have already managed to get this to work as a spreadsheet, but as you can see from the rims and hubs measurement data, this can build up very quickly and is why i have been trying to put this into a database format.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, good luck, i don't think i can help you any further, because while i know sql, i don't know access forms and events

    hopefully somebody else will jump in here...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Thanks for trying anyway. If i get it working i'll give you a shout to let you see it in action.

  8. #8
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi

    I can help but I'm not sure what you want to happen. I've had a look at your db and can see the form for entering the criteria for a front wheel which looks fine. But when you click the calculate button, what do you want to happen ? Do you want the data to be dispayed on the form ? Do you want to see a query with all the data (which R937 has provided) ? Do you want to produce a report ? Do you want the data stored in a table ? How do you want to see the calculation ?

    If you don't need to store the data then I guess a nice report with the input data, lookup data and final calculation might be where you are heading ?

    Regarding the F/R thing, I like the way you have your hub table. I think you could maybe do this on one form and have your hub dropdown to show two columns (hub and f/r). That way you can get rid of the query too and just reference the table directly.

    Chris

  9. #9
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Ok, here's the plan:

    there will be a front wheel builder and a rear wheel builder (both seperate).

    What i need to happen is this (i'll use the front wheel as an example):

    The user selects the rim that they have from the drop down list (referenced to the table). From there, they then select the hub (referenced to the query for clarity - that way only the appropriate hubs are displayed, unless there is coding to do this automatically by referencing to the table).

    After that, the mechanic selects the cross pattern and then supplies the number of spokes required. (i'll eventually be providing information on the hubs and rims to automatically filter for the hub/rim with the same spoke capacity.)

    The information for the calculation will not be displayed, but the result will. A report would be nice, but in that case printing the dimentsions of the rims and hub would be beneficial.

    There is also going to be an "add new rim" and "add new hub" area to allow the mechanic to keep the database as up to date as possible, or to add in any custom parts that may have been made.

    I am going to try to make this database as diverse as possible to allow it to be used to calculate the spoke length for any spoked wheel be it bicycle, motor bike, or classic car.

    Basically, once the mechanic sets the rim and hub, i need the database to look up the values i said earlier, and then apply it to the Excel formula above.

    The idea to do it all off one form is actually a pretty good idea, but im not sure how to go about doing something like that.
    Last edited by cruickshanks; 10-01-04 at 10:09.

  10. #10
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    You need to set your hub combo (combo25) to store the hubID (in the same way you have done for spokes and rims and cross pattern i.e. set the width to zero. Obviously you will need to change your query to include hubID. Then your form will have all the info it needs to reference the required data.

    Also, I would consider showing more columns in the hub combo because the description is not unique e.g. DT Hugi FR disc (there are two and the user won't know the difference)

    Ok, here's some ideas.

    Create a text box on your form. Click on properties and in the control source put:
    =[Forms]![front_wheel_build]![combo25].value

    The point is this is to demonstrate the main way of referencing values stores in objects on forms. So whatever option you choose in the hub combo, the stored value (which is the leftmost one even if it has zero width) will be shown in the tex box.

    Lets take this a step further (you must have sorted the hubID thing out to do this). Using the same text box type the following in the control source(in properties):
    =DLookUp("dl","hubs","[hubID]=" & [Forms]![front_wheel_build]![combo25])

    The point of this is to show you that you can look up values from tables (given a unique identifier). This literally says lookup the value of "dl" in table "hubs" where the hubID is equal to whatever is stored in combo25 (hence you must use the hubID as the reference otherwise it won't be unique).

    In theory you could build up your whole formula like this and show the result in the text box (using a dlookup each time you need to reference one of your table value). In practice I think the amount you can write in the control source is limited (255 ? but I'm not sure) so we'll probably end up doing the calc in visual basic.

    Let me know when you've mastered the above priciples then we can move on to writing some vb.

    Chris

  11. #11
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    chris, Thaks man. That's really helped out. Ok I've got what you have written above done.

    Sory for not getting back to you sooner, but we had yesterday as a local holiday, so that's why i wasn't in the office.

  12. #12
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Ok I've got what you have written above done
    Did it work ? If so, well done !

    There are lots of ways to go from here. One easy way is to create a button (which I see you already have). Clicking on the button would run some vb code that would:
    1) read some values from the form
    2) lookup some values from tables based on the values from the form
    3) perform a calculation on the values
    4) enter the result into a new box on the form

    So....

    Go into the properties for the button and change the Name property to something more suitable e.g. CalcSpokes. (it's good practice to name your objects something meaningful so you can easily identify them in code. It's currently called Command29).

    In the properties, click on the ... (three dots) at the end of the line for the "On CLick" option.

    Select code builder and click ok. You will see something like:

    Code:
    Private Sub CalcSpokes_Click()
    
    End Sub
    Paste the following in between the Private Sub and End Sub lines:

    Code:
    'We have to declare variables before we can use them (they are case sensitive)
    Dim RimID, Spokes, HubID, LaceID As Integer
    Dim dl, ERD As Double
    Dim myAnswer As Double
    
    'read values from form into our variables
    RimID = Forms![front_wheel_build]![Combo18]
    Spokes = Forms![front_wheel_build]![Combo21]
    HubID = Forms![front_wheel_build]![Combo25]
    
    'lookup required values from tables
    dl = DLookup("dl", "hubs", "[hubID]=" & HubID)
    ERD = DLookup("ERD", "rims", "[ID]=" & RimID)
    
    'do a calculation (this is an example)
    myAnswer = dl * ERD * RimID
    
    'put the result in the form (my text box is called Text37 although a more appropriate name would be better)
    Forms![front_wheel_build]![Text37] = myAnswer
    The ' allows you to put comments in the code and they will be ignored when the program runs. If you read through the code it should be fairly self explanatory what we've done. We've just used the priciples previously describe to assign values to variables. Then we can perform complex calculations quite easily. I've just done this as an example calc but you can obviously declare more variables and do more lookups to get what you need to do your formula. I've also used more variables and steps in the code than is really needed but I did this for clarity.

    You can lookup various functions in the access help e.g. Square-root is sqr().

    Good luck

    Chris

  13. #13
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Thanks again Chris. One thing im having problems with is getting the following to add together:

    wr_effective = wr + OSB

    Take a Mavic d521 rim, 36 spokes, Hope Bulb hub and a 3 cross pattern. This will give the following equation:

    wr_effective = 34.0 + 0.0

    Sounds simple enough, but it doesn't work. the result comes out as 34.00.0 for some really strange reason.

    I have even tried adding the two numbers together through VB, but it just sees a "0" as a null value.

    Do you have any advice on how i could sort this problem out?

  14. #14
    Join Date
    Sep 2004
    Location
    Virginia, USA
    Posts
    98
    How do you have your variables (wr_effective, wr & OSB) declared in the code?

    It looks like wr & OSB are doubles but wr_effective is maybe text and it is just putting the numbers together (34.0 + 0.0 = 34.00.0 and if you added any other numbers 37.5 + 0.5 it would equal 37.50.5) do you see what I mean?

  15. #15
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by cruickshanks
    One thing im having problems with is getting the following to add together:
    wr_effective = wr + OSB
    Have you done this in code ? Probably best to post your code as it's easier for people to spot the problem. But my guess would be that wr and OSB have been declared as strings not double (precision numbers). So you need the following at the top of your code:

    Dim wr, OSB as Double

    Also, ensure you have wr_effective declared as double.

    HTH
    Chris

Posting Permissions

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