1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

An Access (97) db and questions

Discussion in 'Business Applications' started by Gram123, Jan 16, 2002.

Thread Status:
Not open for further replies.
Advertisement
  1. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Okay,
    I've been asked "to look" into creating a db (in Access 97), and to guage how complicated it would be / how much time it would take to set up. I'm not actually going to do this yet, I jsut want to know feasability.

    The database is to highlight differences in vehicle damages between point A and point B.
    Each record would be a specific vehicle, identified by the Chassis Number (primary key). It would have a date, a ship number and an inspector name (probably from a drop down list).
    The main part of the form would consist of the damage the vehicle has sustained. This is split into 3 fields which are 3-digit codes for Part, Damage and Repair. Each code corresponds to a name (which I guess I would show below each code using subforms).
    The drop down tables for these three code fields are already in operation in another database so could be easily nicked.

    I would need to have 10 rows of fields for "1st point of rest" and possibly another 10 for "Additional Damage". The point of this is to identify the state a vehicle was in at point A and what additional damage it has sustained by point B.

    :confused: I guess the form would look something like the attached.:confused:

    I've only put 4 rows here and the codes are repeated here just cos it's quicker to show y'all.

    Someone (fortunately not me) would then have to input all of the code fields at 1st point of rest and later go in and find the chassis number to add the additional damage.
    I'd then set up reports to show the amount of additional damage grouped by chassis (using the names associated with the codes) for a set period of time or a set ship number.
    The amount of damages will vary from 1 up to probably 6, but they've asked me to provide for 10 at either point of inspection.

    Q1. Is this a really stupid way to set this up?
    Q2. Would I need to have a different field for each set of codes like this?:
    1st point column Row1 Code 1
    1st point column Row 1 Code 2
    1st point column Row 1 Code 3
    1st point column Row2 Code 1
    etc etc
    If so, this would total 60 fields!

    Originally, they asked if I could compare individual fields, and extract only those that differ. The idea was to say:
    If Col1 Row1 Code 1 is equal to C2R1C1 AND
    C1R1C2 is equal to C2R1C2 AND
    C1R1C3 is equal to C2R1C3
    Then don't show the rowe info on the report, Else show the row info.
    However, this seemed like a complete nightmare and a lot of duplicated info, so they agreed to my idea of only listing new info in an 'additional' area.

    I'm a bit lost and confused at present, so apologies if this doesn't make any sense.
    Any ideas to simplify this would be greatly appreciated.

    Cheers,
    Gram

    Oh, and my disclaimer - I don't know any VB.
     

    Attached Files:

  2. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Hi Gram, you've been awfully quiet lately. Probly busy cooking this one up, eh?

    Well, other than the fact that I have absolutely no idea what you're talking about :D , seems pretty straightforward to me. You definitely do not want a field for each of those dropdowns. Not only would it be a nightmare to input, query, and maintain, it would be bad db design. (Might be about time you read up on db normalization, by the by). You need to build a lookup table with (at least) four fields: the ID, the description of the category, a Yes/No for used by FirstPointOfRest, and a Yes/No for used by Additional Damage. This table will obviously have at least 10 rows, maybe more if there are things that belong to only one or the other category.

    So you have your main table. You have then a second table for first point of rest, with an FK link to ChassisNum or whatever the hell your main table PK is, and then an FK to the ID from the lookup table. These two fields together can be your PK, or you can make a separate ID field if you wish. It will also have whatever other fields you need to describe each of those 10 points for the chassis number in question. Third table, similar setup, but it's for Point B. (See where this is going?)

    Here's how you do it, no VBA: You build a macro to run an append query. When the user adds a new record to the main table, you select the ID field out of the lookup table (where PointOfRest is checked, of course, only), and the chassis number from the form, and append a new set of records to the second table. I can't tell from your description, but you might want to fire off a second query as well, to get the PointB fields loaded up, too. Then you use a subform, refreshed after the query, which will show you the 10 line items, and the user just hops down there and fills in whatever has to be filled in. (The subform doesn't allow additions or deletions, you see, because all the user would be doing is "filling out" already existing records).

    Then, actually, you can very easily compare Point A and Point B, because you just link up the ID codes from table2 and table3, select for a given chassis number, and see the differences.

    This should get you started...
     
  3. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Hey Down, I knew I could rely on you.
    Yeah, I've not had anything complicated to do with Access for a while, plus I've had some internet connection probs (fixed now).
    Well, as usual, the sentiment is mutual!!:p :p

    Additional and new info...

    - The codes and their descriptions are all unique.

    - There could be records with damage information in only the second point of rest or both, but no two rows on a record (vehicle, identified by chassis) will be the same - i.e. if a row of damage info appears at one point of rest, the exact same combination of three will not appear at the other point of rest. However, any of the allowed 'combo of three' codes can appear in any row and column.

    - I've been thinking, that there may be a simpler way, if I'm allowed to do it:
    I have previously set up a db that contains all of the damage info. In other words, making this new database would be duplicating a hell of a lot of information. (But hey, it's not my idea...).
    The damage is currently only reported at the '2nd point of rest', and it contains a lot of additional information, such as repair costs, vehicle model, country of origin etc. Furthermore, the records in that db are the equivalent of rows in this one, so several records have the same Chassis number (normalisation, you say?).

    I was thinking of simply adding a new field that would identify whether there was damage at both points or just at the 2nd point.
    Rather than entering all data at the 2nd point, the user would begin entering data to some of the fields (i.e. those listed in the 'potential' db in my original question) at the first point.
    Then when the vehicles got to the 2nd point, they could add (find by Chassis) the remaining data for these semi-complete records and tick "Damage at both Points" (or something similar).
    If a vehicle is only noted with damage at the second point, then searching for the Chassis will come up with no results and they can enter it as a new complete record and tick "Damage at 2nd point only".

    This sounds to my mind like a better option (it may sound like gobbledegook to you!!). It just means that the user will have to be careful with Chassis entries.

    And perhaps it would be better with two forms - one for 1st point and the existing one for 2nd point.

    What do you think?

    Gram
     
  4. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    OK, Gram, I'm beginning to feel reeeeally dumb. That last post didn't make it any clearer for me, at all. Oh well.

    Sounds to me like you've already done a lot of work towards what you're trying to do next, first of all, and throwing that out and starting over would be silly. Next, sounds to me like you only need one table for the damage line-items, and not two as I suggested before. As you say, probably two fields, Yes/No, to say "is this Point 1" and "is this Point 2", then it's obviously both if it's checked. Then whatever other fields you need to add to your existing table.

    Also, I'd give them chassis numbers in a drop-down list if they need to be careful, and make them do something special to add a new one. For example, I often work with a totally separate, much more guided form for adding a new "master" record than what the user sees when editing. That way I can lock certain fields, and make (more or less) sure the user gets the master stuff right the first time around...
     
  5. Gram123

    Gram123 Thread Starter

    Joined:
    Mar 15, 2001
    Messages:
    1,829
    Okay Down,
    I'll try not to lose you again!!

    I've basically added an Option group to the main form with these options:
    > Damage Equal at 1st and 2nd Point
    > Damage Deteriorated After Point 1 Inspection
    > Damage at Point 2 Only

    If the user picks option one, they need to simply enter more details for that record.
    If they pick option two, they need to alter already existing details.
    If they pick option three, they need to enter a new record.

    This is a required field, with no default option, so it forces the user to choose. Based on this I've set up a couple of reports that list all claims with either the second or third options selected for a specified Ship Number, grouped by the Chassis Number.
    This is fine, it works, and it saves retyping lots of information. We'll see if the bosses accept it.

    The only concern I have is with the 'deterioration' option. As the user will be overwriting the damage they originally entered, they will lose that original info, so we won't know what it's deteriorated from. In other words, we won't know how much worse a damage has got.

    For example, if the 2nd point info was:
    - Roof
    - Scratched and Dented
    - Repair and Paint

    We won't know if the 1st point damage was:
    - Roof
    - Scratched
    - Paint

    Or:
    - Roof
    - Dented
    - Repair

    Ah, so it may be necessary to 'keep' the original info, but at the same time, we shouldn't keep the info in the main table, as it is irrelevant to the amount charged to the customer. Erm... if you see what I mean...
    This is an issue that wasn't considered when they dreamed up this crazy idea.

    Anyway, I'll probably submit it as it is (on Monday) and explain it's limitations. If more work is needed on this I'll post back, otherwise I'll just let the thread sink to the bottom!

    Cheers mate.
    Gram
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/65116

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice