Parts List Per Vin - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Parts List Per Vin

David Clement

3 years ago

I have made a form/database to LIST the types of filters a truck uses based on its VIN Number. My goal is to type in the VIN Number and have it give me a list of part numbers, descriptions and quantities for said truck, even type the last 6 digits of the VIN number like parts sales people do. It works fine, but I know it is NOT what Richard would do. This database does not need to be searchable, except for the VIN number, which I have made the primary key with no duplicates. I can not think of another way to do this so it is correctly done. Maybe what I want to do is not possible with access. That is why I am asking here.
How should it be done?
I will include some screenshots.

Thank you.

	David Clement OP @Reply 3 years ago

	David Clement OP @Reply 3 years ago

	David Clement OP @Reply 3 years ago

	David Clement OP @Reply 3 years ago
I'm sorry for the mis-spelling.

David Clement OP @Reply

3 years ago

By the way, each truck may have different filters or not as many from one truck to another. The list is only based on the VIN number for that truck. As it stands now, each time a parts salesperson gets an order, they have to look up all the filters each time, but he filters or the quantites don't change. I am just trying to make it easier for the people that pick the orders. The SQL system we use as far as I know can't do this.

	Adam Schwanz @Reply 3 years ago
First thing that sticks out is you should use a one to many or many to many relationship so instead of having 10 sets of fields you could have 1 or 100 and not have to make 100 fields. Relationships One to many may be fine, but if not heres many to many too Many-to-Many

	David Clement OP @Reply 3 years ago
How would a many to many relationship show me the list parts I would need to see at one glance? I am still not up to speed on all this. In my mind and help to see the error of my ways, 1 record for 1 truck which has 7 different filters for it would still be 1 record for that truck. A different truck may only have 4 filters.

Adam Schwanz @Reply

3 years ago

If you are only going to search by VIN and then have the related parts come up you are going to use a one to many, I just linked the other video for information in case I misunderstood something in your question or if it was something like this vehicle can use this set of parts, or be upgraded/changed to a second set of parts kind of thing.

Basically you just need a simple one to many to make a list of parts for the vehicle, and then all of your work is just going to be data entry

Scott Axton @Reply

3 years ago

David -
Just to clarify a bit. The Many-to-Many comes in where you might have an air filter, for instance, that could be used in many vehicles.

The look up for a specific VIN tells what parts that particular vehicle uses. The efficiency is that if you have the parts in their own table then it can be used for multiple different vehicles. You don't enter it in multiple times.

So you would have the vehicle table - the parts table - and the junction table. The junction table just relates the vehicle(s) to the part(s).

Hope that make a little more sense.

	David Clement OP @Reply 3 years ago
Using a many to many database, how would I see the parts in (for the lack of a better term) a form type view to see all the parts for that truck?

	Scott Axton @Reply 3 years ago
Go watch the Many-to-Many video (again if needed). In it Richard shows the table set up as well as how to make the forms to see what you need. While you are watching it just think Vehicle - Junction - Parts. for the relationships.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View