Solved Starting Again - Table Relationships. (1 Viewer)

[Tieval]

Hi,
Been away from access for a couple of years and have a new project, I am after a little help bump starting my brain.

I have the part numbers for all parts on a range of motorcycles and am looking for a simple example set-up to start from. I have seven types of motorcycle and thousands of parts which need to be applied to one or more of the different types. The idea is to enter a part and all it's details and reference it to all the types of motorcycle it fitted. For example I want to enter 32645 and all it's details (not an issue) and then relate it to the 1950 RE1, the 1952 RE2 and the 1955 RE2 but not to the 1957 EN, part number 45632 would have all it's details and relate to the 1952 RE2 and the 1957 EN et cetera.

Any help would be greatly appreciated.

 

[Mike Krailo]

Just create a PartGroup table and use whatever part group name you want. Then use that key in your main Part table. You will of course have to pick a group for every part in that table.

 

[arnelgp]

the idea is the same for "tools".
each tool can be for "specific" type of work.
while others can be used on many type of works.

same with your motorcycle part.
so u nid a "partnumber" field
and anoher field "category" (numeric) which
you can "combine" numbers to form "several" category.

see this demo, this is about tools, but the principle can
be apply to partnumber.

 

[Tieval]

Hi Arnelgp,
That is spot on, exactly what I was looking for. I am thinking that I have seven types so in theory can just use 64 and 128 for the other two? Is that correct? After that I think I can work it all out (with a little thought).
Many thanks for your help.

 

[arnelgp]

on second, thought, this is more appropriate.

 

[arnelgp]

use 64 and 128

it's 2ˣ.

 

[Tieval]

Many thanks for this, even better. I had guessed the 2* bit and it all seems to work.

 

[Tieval]

Many thanks for your help, I now have it working for all seven bike types.

I do now have another question, I would like to search all parts for a particular type so would like to put a drop down box.

For example I could have a search field and type wheel into it and get all wheels from a description field but how could I break this to individual bike types as type 1 only would have a value of 2 and types 1 and 7 would have a value of 130.

 

[Pat Hartman]

If you want a part to be associated with multiple bikes, you need a many-to-many relationship. The example proposed gives you a 1-many and also, requires you to make design changes to add a new bike type. I've attached a sample that shows how m-m relationships work but you'll have to do some translating. The example is employee - empclass - class. Translate to motorcycle -cyclepart - Part. In your case, the middle table will just have an autonumber PK and the two FK fields - PartID and MotorcycleID unless you have junction data to store. For example, if you wanted to store the number of units used in the relationship. So a motorcycle normally has 2 wheels unless it has 3 so you could keep a field called unitCount if that makes sense to you.

The "middle" table is referred to as a junction table and it creates two separate 1-m relationships which taken together is how the m-m is implemented. In the database the example shows how to create the junction table from either perspective. One way uses a subform and the other uses a pop up form. I did that to give you options. They could have both used subforms or popups. Your choice. When you are making the relationship, a combo is used to list the available items from the other side of the relationship. combos are best when you have long lists. When you have short lists (7 is marginal), you could use an option group but just remember, using an option group requires design changes should you add an eighth motorcycle whereas the combo does not require changes. You just add the new part or the new motorcycle and the option automatically shows up in the list.

 

[Mike Krailo]

That's a very good point Pat. If there ever was a need to add more motorcycles to the mix, then design changes have to be made. So the many to many way is more future proof and doesn't require the bitwise translations of the Category column or design changes.

If you aren't concerned about that issue. Then you would simply do a bitwise AND for each type and check each bit.

Example:
If 2 AND [Category] = 2 then msgbox "Has Type 1"
If 4 AND [Category] = 4 then msgbox "Has Type 2"
If 8 AND [Category] = 8 then msgbox "Has Type 3"
If 16 AND [Category] = 16 then msgbox "Has Type 4"

 

[Pat Hartman]

The sample the code came from implements a 1-m relationship NOT a m-m. Even worse, it is placing the ONE value in one of 4 checkboxes. The example didn't even use an option group.

 

[Tieval]

A simple answer is that the bikes finished production in 1962 (before I was born!) so there will be no additions. I am looking at a build once parts database that covers all models between 1945 and 1962 so the suggestion from Arnelgp is perfect for my needs (many thanks). My theory is that I could have a continuous form in a form containing a drop down box that allows you to select a specific model, on changing the combo box you could refresh the continuous form to only those values.

I think my biggest problem is an understanding of the AND statement and how to implement it. I can see that the method can take a number and work out it's exact connotation as reloading records with a number allows it to select the appropriate check boxes but I am being a bit thick and cannot see why and therefore how to apply it to a search query.

 

[arnelgp]

there is a Combobox Test here:

 

[Tieval]

That also works a treat, many thanks again.

 

[Pat Hartman]

Are you sure it works? You described a many-many relationship. i.e. many bikes (7 is many) and many parts. That relationship REQUIRES a third table to implement UNLESS you flatten the structure and add 7 fields to the part table and that is not what the example shows. If you NEVER want to expand the database for other vehicles, then adding 7 columns probably seems simpler to you than creating a proper relationship. Your choice.

 

[Tieval]

Pat,
I am not the expert here but the method adds one column storing a data value between 2 and 254 and then works out what this applies to. It does work as per the attached. You may want to take it up with Arnelgp why there are technical issues but I do not see any problems. I don't ever want to expand it to additional vehicles but still don't see why that cannot be done unless there is an issue going beyond eight bit?

Any thoughts would be appreciated.

 

[Pat Hartman]

I see what arnel did. He is a very talented programmer and goes out of his way to help people so I'll give him that but I would never, ever have chosen this method, ever, and especially not for a novice. I'm pretty sure you don't understand what he did and no one who looks at the database would suspect the technique either which is why I didn't pick it up right away, that's how outre it is.

There is a lot to be said for the KISS method. Arnel could have used seven columns in the part table. One for each type of motorcycle. That would have been a poor design but at least it would be obvious and you would be able to understand it and work with it. Instead we not only have an unnormalized design which teaches you nothing should you ever want to build something else or expand this app, but one which is "slick" as well. Not my cup of tea. KISS = good, Slick = not so good. The place for slick is if you are designing an algorithm that is going to run thousands of times per second and you need all the speed you can get. Otherwise, go for the obvious. It saves a lot of brain cells and hair loss.

PS, if the database is just for you, ignore this comment but I had a lot of trouble reading the form. There is nowhere near enough contrast between the red background and the gray text. Personally I avoid bold colors as the form background but if you want to use them, make the text much darker so the user doesn't have to squint to see the words.

Good luck. You might want to keep the sample I posted for future reference if you want to learn how to build database applications. That is how this type of relationship (many-to-many) should be designed.

 

[mike60smart]

I agree with PAT

The example given by Arnel does not help anyone trying to learn about setting up a Relational Database.

 

[Mike Krailo]

Any help would be greatly appreciated.

The OP seemed to appreciate Arnel's effort here and I thought it was very creative myself. Kind of like how colors are stored inside one single numerical value (OLE) and can be converted back into R, G, B values using a function. I'll bet the OP did understand what Arnel did here.

 

[Tieval]

Hi All,

Many thanks for the interesting input and the effort put into replying.

Firstly I am no novice, just haven't touched it in a couple of years. I have a full understanding of bitwise as I use it for electronic equipment programming in C++, until I fully understood the idea of the solution I did not realise you could use it in Access VBA. We are all different and personally I find this easier than many-to-many relationships, the relationship method would have been more pure but requires (in my opinion) a much fuller understanding of relational database programming.

As with all databases, the first step is to fully understand the requirements, this normally leads to covering for future possibilities or ends with a problem further down the line. In this instance I know the complete requirement from the start and appreciate that it will not be expanded, admittedly if I were to take this solution and apply it to a larger range of motorbikes I may get into trouble but I have no intention of doing this and in the unlikely case of this happening would deal with it at the time.

I would again like to thank Arnelgp for his input as it is extremely useful and was a quick and perfectly acceptable solution to my issue.