Solved Starting Again - Table Relationships.

[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.

 

[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"

 

[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.

 

[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.

 

[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.

 

[Tieval]

Oh, and Pat, you are correct the colours are terrible.

I tend to deal with the operational side of things and it should therefore all be default grey black and white but I have the tendency to play with looks while I am thinking about more important things and my development work always looks a mess