Make relationship with wildcards

[blarson0]

Hi! I am working on a database that will list off parts of assemblies that my employer makes in a factory. Sometimes, when the plans change or something is wrong with the parts list, they will issue a Temporary Deviation. That will be in another table, linked to the parts list table. So the structure is like this:
[tt]
Parts List Temp Dev
Assy Num <--many-------------------1-> Assy Num
Part Temp Dev Text
Revision Date
Quantity Author
etc... etc...
[/tt]
sometimes, there will be a temporary deviation for a whole line of assemblies. So, instead of writing out a temporary deviation for 78364-AAA-02, 78364-AAB-02... all the way to 78364-ZZZ-02 isn't really an option. If they write out 78364-???-02, can I make a relationship with that? So that the parts list 78364-GFC-02 will link with 78364-???-02?

Thanks!

-Brad

 

[Steve101]

Brad,
(a) In the relationships diagram, you WILL be able to establish the 1 to many relationship between the two tables, but you WILL NOT be able to set up Referencial Integrity (RI), because the sibling record will not necessarily have a parent.

(b) As a consequence of the above, you won't be able to use cascade updates/deletes.

(c) You will need to control the above via queries and VBA code.

(d) You will need to rely fairly heavily on the LIKE operator and use of wildcard characters for pattern matching in your code and in your queries. Check online help for more details, or just ask if you need further direction.

Good luck,
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au