INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How should I structure my table need help I'm building an auto recycling database

How should I structure my table need help I'm building an auto recycling database

How should I structure my table need help I'm building an auto recycling database

(OP)
Hi all I need help with a table structure. I'm building a database for a friend who has a auto recycling shop now I'm trying to put together a table for a stock list.

So far it is called tbl_stocklist
It has p/key:stock_list_id
Car_number
Vin_number
Car_colour
Body_type
Etc
what I need to know is how do I add the parts that the car has that can be re-used will I have to add each part individually or just a parts field.? As a car has many parts.
this will need to be search able later.

Thanks in advance for your help
regards,
Jesse

RE: How should I structure my table need help I'm building an auto recycling database

You need to read up on relational database design. You can google and find lots of links. You will see the term "normalized" or "normal" design which means proper database design. You will need to learn how to then join your different tables. Definately spend some time learning the basics, because it will save in the long run.

You will have to have at least the following tables (but likely many more as it grows)

tblVehicles
Vin_number 'if all your cars have a unique vin then this is a good natural primary key
Car_number
Car_colour
Body_type
'other fields that uniquely identify a vehicle

tblParts
partName
partType
partDescription
partSerial
'other fields that uniquely describe that part
vin_number_FK 'you would add a foriegn key that relates the part to the parent vehicle

Now you have a one to many relationship. Multiple "child" parts would relate to a single parent car. You can then use queries to join the data to enter or display it. You would want to learn how to build a main form with a subform to enter and show a car and all of its parts.

RE: How should I structure my table need help I'm building an auto recycling database

An additional consideration, from someone who shops at junkyards quite often. Sometimes parts from different years are interchangeable. For instance, I wanted a black hood for my 2004 Civic and the 2005 model uses the exact same hood. You might want to check out car-part.com

If your friend wants to cross-list at car-part.com, they probably require a particular database structure.

==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright


RE: How should I structure my table need help I'm building an auto recycling database

(OP)
Thanks guy's for your guidance should I post what I have so far, so you have a better idea of what is going on so far? I understand the basic structure and a one to many relationship well at least I think I do its just that I'm not sure how best to store the information. I'll put up pics soon of what I have.

RE: How should I structure my table need help I'm building an auto recycling database

(OP)
here are the screen caps they show what tables i have and what each table contains, i hope this helps you guys to help me, i can be a bit thick sometimes.

thanks in advance
Regards
Jesse

RE: How should I structure my table need help I'm building an auto recycling database

(OP)

RE: How should I structure my table need help I'm building an auto recycling database

I see some repetition of data.
For example if Vin Number is your PK that is the only field needed to join your tables. no need to repeat car number in the tables. In the sales tables you should only have the part ID, and no keys to the car table. Because A sale relates to a part and the part automatically relates to the car table. So I think you can remove car number from every table (except the car table). In the sales table remove the vin and car number.

RE: How should I structure my table need help I'm building an auto recycling database

(OP)
so my sales table now has sale_id, Part_id do i need to keep Name_of_part, Position_on_the_car and colour or will that data be obtained from somewhere else.

ill apologize now i have not worked in access for a good 8 years so i will be looking for lots of help to get this job done.


thanks
Jesse

RE: How should I structure my table need help I'm building an auto recycling database

That is information that is unique about a part, therefore that information would be stored in the parts table.

RE: How should I structure my table need help I'm building an auto recycling database

I suggest you find and use a naming convention that doesn't allow spaces. You seem to use underscores in place of spaces in some field names but not others.

I prefer EngineType to either [Engine Type] or Engine_Type. It's up to you but consistency is a nice touch.

You might also want to review spelling. No one should ever see actual field names other than you as the developer but correcting field and table names after the fact is not fun.

I would probably move to a more normalized table structure. For instance tbl_Parts might have a PartID, PartType, and a few other fields. A related table of PartAttributes might have


PartID     Attribute      AttributeValue
1          Covering       Fabric
1          Colour         Brown
1          Position       Front Right 

Each attribute of each part would create a new record in a table. You could create a table that identifies the attributes common to specific part types. This solution would be more difficult to implement but would allow you to add attributes on the fly without changing table structures, forms, reports, or queries.

Duane
Hook'D on Access
MS Access MVP

RE: How should I structure my table need help I'm building an auto recycling database

(OP)
@dhookom
sorry about the naming convention i generally use the _ instead of spaces but i have noticed that in my lack of sleep i have missed a few ill correct that. i have fixed the spelling as well i should have waited to get started when my full attention was able to be given.

i like your thoughts on the Attribute table is there a way that i can pick you brain on skype or by email, as this will be only my second database ever i know its a bit ambitus but i learn best by jumping in the deep end and doing the sink or swim method LOL.

@MajP

thanks i will remove the extras now

RE: How should I structure my table need help I'm building an auto recycling database

(OP)
ok new question the vin number im told could be alpha-numeric can i still use it as a PKey?

RE: How should I structure my table need help I'm building an auto recycling database

It is common to use string PKs. Vin has all the properties of a good PK: unique, guaranteed, non changing. But you can always use an auto number.

RE: How should I structure my table need help I'm building an auto recycling database

(OP)
it currently is an auto number field so if i change it to string if would be fine.

RE: How should I structure my table need help I'm building an auto recycling database

A VIN is a 17 digit alpha numeric string. It has a very specific format containing lots of information about where and when the vehicle was made. I wrote this vb implementation of the common algorithm for checking if an entered VIN is valid. May come in useful when entering the VIN, you probably want to validate it.

CODE

Public Function isVIN(strVIN As String) As Boolean
 Dim I As Integer
 Dim intCount As Integer
 Dim intCount2 As Integer
 Dim aModelYears() As Variant
 Dim aWeights() As Variant
 Dim aCharacters() As Variant
 Dim aCharacterValues() As Variant
 Dim aCheckDigits() As Variant
 Dim aVIN_Array(0 To 15) As Variant
 Dim intTotal As Integer
 Dim intRemainder As Integer
' Check VIN lenth
If Not Len(strVIN) = 17 Then
  MsgBox "ERROR - VIN lenth must be 17 characters long." & Chr(13) & "You only entered " & Len(strVIN) & " characters."
  Exit Function
End If
'make VIN all caps
strVIN = UCase(strVIN)
' model years 1980 - 2000
aModelYears = Array("A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "R", "S", "T", "V", "W", "X", "Y")
' weights for multiplyer
aWeights = Array("8", "7", "6", "5", "4", "3", "2", "10", "9", "8", "7", "6", "5", "4", "3", "2")
'characters
aCharacters = Array("A", "B", "C", "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9")
'// character values
aCharacterValues = Array("1", "2", "3", "4", "5", "6", "7", "8", "1", "2", "3", "4", "5", "7", "9", "2", "3", "4", "5", "6", "7", "8", "9", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9")
' check digit 0 - 9 and 10 = X
aCheckDigits = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "X")
'// push each character of the vin into an array removing the 9th character (check digit)
MsgBox "Your VIN's check digit is " & Mid(strVIN, 9, 1)
For intCount = 0 To 16
  If intCount < 8 Then
    aVIN_Array(intCount) = Mid(strVIN, intCount + 1, 1)
  ElseIf intCount > 8 Then
    aVIN_Array(intCount - 1) = Mid(strVIN, intCount + 1, 1)
  End If
Next intCount
'replace with char values
For intCount = 0 To 15
  For intCount2 = 0 To UBound(aCharacters)
    If aVIN_Array(intCount) = aCharacters(intCount2) Then
      aVIN_Array(intCount) = aCharacterValues(intCount2)
    End If
  Next intCount2
Next intCount
'For I = 0 To UBound(aVIN_Array)
'  Debug.Print aVIN_Array(I)
'Next I
'// preform the math
For intCount = 0 To 15
  intTotal = intTotal + aWeights(intCount) * aVIN_Array(intCount)
Next intCount
'debug.print intTotal
intRemainder = intTotal Mod 11
'Debug.Print intRemainder
  If Not Mid(strVIN, 9, 1) = aCheckDigits(intRemainder) Then
    MsgBox "ERROR - Check digit does not compute. Recheck your VIN number." _
          & " Computed check digit:" & aCheckDigits(intRemainder) _
          & " Your check digit: " & Mid(strVIN, 9, 1)
  Else
    MsgBox "Computed check digit: " & aCheckDigits(intRemainder) _
           & " VIN number seems to be valid."
    isVIN = True
  End If

End Function 

RE: How should I structure my table need help I'm building an auto recycling database

(OP)
thanks that will be an awesome idea i just need to add this to the form for cars incoming yes??

RE: How should I structure my table need help I'm building an auto recycling database

Actually if you spend some time with a few data tables and some vba, you should be able to enter a vin and prepopulate things like
year, make, model, where produced, options,... Could save a lot of work.
http://researchmaniacs.com/VIN/VIN-Decoder.html

RE: How should I structure my table need help I'm building an auto recycling database

(OP)
Hi Majp

thanks for your help so far. would you be willing to help me through this process as i am a newbie to vba and am in need of a good teacher. im happy to build the data tables thats no issue, but the vba i will need lots of help.

RE: How should I structure my table need help I'm building an auto recycling database

There are plenty of people that will chime in and help. There are specific forums for Access forms, reports, modules, and queries. The key is to learn to post clear concise questions without unnecessary information. If the poster spends the time to ask a good question they will get a lot of people to answer. On the other hand you will see people that will drop a thousand lines of code and ask, "why does this not work?". Also you will be amazed to see what the internet can return for code samples. I rarely post a question because I can almost always find a good answer just Googling. When it comes to writing code, I am a book person, and you can find plenty of good Access VBA books. VBA has not changed much so I you do not need Access 2013 books. You can get plenty of "developer handbooks" used for less than $5.00 if you get like 2002-2007. I would not spend more than $8.00. A lot of these type of books were 50-80 dollars originally. Just google for used Access VBA.

RE: How should I structure my table need help I'm building an auto recycling database

(OP)
Thanks Majp

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close