Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Multiple Entry Lookup (2002 SP-2)

    I'm not so sure I have posed the question correctly, or even if Access is capable of doing this. I have searched the help files, book, and Lounge files but can't seem to locate a reference to the subject. Like I said, I may not be posing the question correctly.

    I have a table (three actually, but I'll keep it simple for now) that has the fields: From (city(txt)), To (city(txt)), Distance (miles(num)), Rate (charge(currency)). On the frmOrderNew there are six comboBoxes using this table (only to simplify data entry) showing the point of shipment and five possible destinations. I then have a comboBox where a From/To point is selected returning the Distance to a seperate textBox elsewhere on the form (for logistics reasons) and another txtBox for the Rate. When you click on the comboBox you see From, To, Distance, and rate. Making a selection returns the From city in the comboBox (which is pretty much useless info at that point) and enters the distance in the seperate txtBox, but you have to remember what the Rate was to enter in yet another txtBox (for accounting purposes). This is all pretty archaic as you can see.

    As all of the information is in the same table, I thought there may be some means of extracting the Distance and Rate when entering the cities. I spent a good portion of the weekend experimenting, but without positive results. I would like to be able to enter the From and To points and have Access return the Distance in one txtBox and the Rate in another. I suppose what I'm really looking for at this point is either encouragement to continue the pursuit, or notice that I am wasting my time pursuing the impossible.

    As always, all input greatly appreciated.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Multiple Entry Lookup (2002 SP-2)

    I'm sure it's possible to get the distance and rate, but I'm thoroughly confused by your setup. You write that you have 6 combo boxes: one for origin and five for destination, and "I then have a comboBox where a From/To point is selected". Is this a seventh combo box, or one of the six you mentioned earlier? And what is the relation between this and the other combo boxes? If you try to explain which distance and rate you actually want to retrieve, things might become clearer.

  3. #3
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Multiple Entry Lookup (2002 SP-2)

    I thought purhaps I might cause some confusion in my attempt to keep the question short.

    frmOrderNew has six comboBoxes to select from (in actuality any of these points can be "From" or "To" points). The reason they are comboBoxes is to simplify data entry (start typng and combo completes). At this point (and that's what I'm trying to get away from) they serve no purpose other than to display the different points. The other comboBox (the seventh one) is used to select (limited to two points) the distance (displayed in the seperate textBox) and (if you remembered the rate that was also displayed in the drop-down) the charge you need to enter in the Rate txtbox.

    I'm just trying (at this point) to determine a way to do this calculation (lookup) on two points. Probably 95% of the hauls only involve two points, but there is the rare occation where there are more. That, at least for me, would seem to be far more complex.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Multiple Entry Lookup (2002 SP-2)

    Apparently, when you select an item in the seventh combo box, a distance is automatically entered in a separate text box. What expression or code is used to do that? If you can fill one text box, it should be possible to adapt it to fill another text box too.
    Look at the following two items:
    1. The Control Source of the text box displaying the distance. Does it contain an expression starting with = ?
    2. The AfterUpdate event of the seventh combo box. Does it say "Event Procedure"? If so, click the builder button (the three dots ... to the right of it) and look at the code.
    I don't know which of these (if any) is the case on your form.

  5. #5
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Multiple Entry Lookup (2002 SP-2)

    Hans,

    My apologies for having wasted your time thus far. I have made a rather grievious error in assuming that the six comboBoxes for displaying the cities was based on the same table as the comboBox for the Distance/Rate - they are not. In digging through the db I have now discovered that there are linked tables to two other db (Directions and Rates). I cannot understand why this would be used in this manner because I believe the cities in the Directions db are merely duplicated in the Rates db. As I look at the txtBox displaying the miles I see that the Control Source is the same as the comboBox where you select the Miles/Rates; there is no expression entered. There is also no Event Procedure in the comboBox (only a common Control Source with the txtBox).

    Perhaps I should investigate all this a bit further and come up with a more comprehensive description of the current process, and possibly attach a (spreadsheet?) showing how the tables are structured. In consideration of how one would approach a solution, might there be particular issues I should address irrespective of the current (ineffective) process?

    Thanks for your patience!
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Multiple Entry Lookup (2002 SP-2)

    Apparently, the combo box for Miles/Rates is a bound combo box. Just add a text box and set its control source to the name of the field containing the rate. When you change the selection in the combo box, the rate should change with it.

    If that doesn't do what you want, then yes, investigate the setup in more detail and try to provide more information. You could create a cut-down version of the database, or a spreadsheet explaining the structure, or a series of screen shots. Remember, an attachment should be below 100 KB. A compressed file (.zip) is a good way to get the size down.

  7. #7
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Multiple Entry Lookup (2002 SP-2)

    Hans,

    Thanks so much for your time thus far! I will investigate in full and post back.

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Apparently you missed my signature line <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    I don't know how to do screenshots or zip files. I also don't know if that's humorous, or just terribly sad.
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Multiple Entry Lookup (2002 SP-2)

    1a. If you have Windows XP, you can create compressed folders without installing extra software. In Windows Explorer, select one or more files, then right-click and select Send To/Compressed (zipped) Folder.
    1b. If you don't have Windows XP, you can get a very useful program named WinZip. You can download the <img src=/S/free.gif border=0 alt=free width=30 height=15> evaluation version from http://www.winzip.com.

    2a. The PrintScreen key on your keyboard copies a picture of your entire screen to the clipboard, ready to be pasted into any program that can handle pictures, for instance Paint and Word.
    2b. Alt+PrintScreen copies a picture of the currently active window to the clipboard, ready to be pasted.
    2c. There are lots of nice utilities for capturing part of the screen; if you do a search for screen capture or screenshot in the Software Finds and Wants forum, you'll find links to several of them.

  9. #9
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Multiple Entry Lookup (2002 SP-2)

    Hans,

    Thanks so much for the assist in formatting the "pics". I was able to download 'PrintKey' and use it in the attachment. Hopefully, I have not made matters worse in trying to describe the situation, and that everything is readable. Even if the application can't be improved, I've learned something else along the way.
    Attached Files Attached Files
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Multiple Entry Lookup (2002 SP-2)

    You don't really expect a serious answer, I hope? If<blockquote><hr>There is nothing in the application that tells me that they are on rate

  11. #11
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Multiple Entry Lookup (2002 SP-2)

    Hans,

    ROFL!! What's the problem, don't believe in A.I.?

    But seriously folks... <img src=/S/surrender.gif border=0 alt=surrender width=31 height=23>

    ..(although a column could be added to the Combo Box to reflect that rate)...

    I'm pretty much guessing at this point, but I was thinking that when you select the customer from 'Customer#' it might save the "rate" to another txtbox (perhaps even a hidden txtbox). That would give you a way of knowing which column in the tblFBMilesRates to grab. Of course it would also, somehow, have to be able to distinguish what table to use based on the equipment selected. Hell, I don't know - you're the expert! <img src=/S/king.gif border=0 alt=king width=21 height=22>

    <img src=/S/drop.gif border=0 alt=drop width=23 height=23>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Multiple Entry Lookup (2002 SP-2)

    If the type of rate (SSB, B or C) is a field in the Customers table, it should be possible to determine the actual rate, but your table structure is far from ideal for this. The way it is now, you'd probably need a horribly complicated combination of IIf's and DLookups, since the rate must be fetched from one of three different fields in one of two different tables. For easier lookups, you should use an intermediate table that lists one combination of journey, transport type and rate type per row:

    <table border=1><td align=center>[b]JourneyID[b]</td><td align=center>[b]TransportType[b]</td><td align=center>[b]RateType[b]</td><td align=center>[b]Rate[b]</td><td align=right>1</td><td>FB</td><td>SSB</td><td align=right>0.88</td><td align=right>1</td><td>FB</td><td>B</td><td align=right>0.92</td><td align=right>1</td><td>FB</td><td>C</td><td align=right>0.97</td><td align=right>1</td><td>LQ</td><td>SSB</td><td align=right>1.02</td><td align=right>1</td><td>LQ</td><td>B</td><td align=right>1.06</td><td align=right>1</td><td>LQ</td><td>C</td><td align=right>1.10</td><td align=right>2</td><td>FB</td><td>SSB</td><td align=right>0.97</td><td align=right>2</td><td>FB</td><td>B</td><td align=right>1.03</td></table>
    This looks more complicated to human eyes, but is much easier for looking up a rate value. It would probably mean a major overhaul of your database; I can't decide for you if you can afford that.

  13. #13
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Multiple Entry Lookup (2002 SP-2)

    Hans,

    Thanks for not abandoning ship! (Not that I've ever seen you do that) Suppose we might see a Woody's disclaimer for mental anguish after this post?

    I've got far more time than cents (or, I should say, sense). I don't mind putting in the hours (several hours) if it's going to be of some benefit. Before I get started on this adventure in monotony, I will put together a stripped-down "pic" showing what I have, what I think needs to be changed, and how I would like to accomplish the lookup. If you would be so kind, you could then look it over and possibly advise on the table(s) structure. I think I understand your "sample", but want to be certain before I start drinking heavily during all the data entry.

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

  14. #14
    5 Star Lounger bfxtrfcmgr's Avatar
    Join Date
    Aug 2002
    Location
    Fresno, California, USA
    Posts
    995
    Thanks
    12
    Thanked 0 Times in 0 Posts

    Re: Multiple Entry Lookup (2002 SP-2)

    Hans (and anyone else who wants to join in),

    I'm not certain that I completely follow your table idea as far as JourneyID goes. Might I assume that if there is a distance between 0 and 10 miles that is represented by the JourneyID of "1" and that a distance between 10 and 20 miles might be JourneyID of "2"? (see attachment). If so, I think I'm following.

    In the interest of following your example table (and my attachment):

    JourneyID = I suppose this would have to be built in a table reflecting distance ranges (i.e. 10 to 20 miles)
    TransportType = EquipmentType
    RateType = Rate
    Rate = $CWT
    Attached Files Attached Files
    Bryan,
    Not the smartest critter on the glacier. . .
    . . .but I'm persistent (does that count?)

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

    Re: Multiple Entry Lookup (2002 SP-2)

    Here is a rough idea. It is by no means meant as a finished product, only as an example of how to structure the database in such a way that you can retrieve the CWT. I have attached a picture of the tables and their relationships. There is another table, tblCWT, that contains the CWT for various distances, equipment types and rates. If there is a formula to compute the CWT (for instance, an increase of $0.05 for each 10 miles), you could use that instead.

    I will attach a demo database in my next reply
    Attached Images Attached Images
    • File Type: png x.png (4.2 KB, 0 views)

Page 1 of 2 12 LastLast

Posting Permissions

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