Stuck on Proper Technique

[GBalcom]

I'm creating a database whose sole purpose is to look into my ERP systems quoting module, and output a spreadsheet that I can suck into my engineering software.

The quoting module has relevant "Assembly Codes", which is the assemblies primary key. The engineering system requires the product name in the spreadsheet to match a product name in it's library to work successfully.

The trick is it is not always a 1 to 1 relationship. Several of the assembly codes in the quoting module align with two different engineering products (Essentially a Left or Right of the assembly Code).

When the app builds the import data in the background, if more than one record exists for a given assembly code, it will prompt the user to pick the correct one.

I'm struggling with whether my data requires a junction table, or if I can get away with just one table (for my engineering products), and a field for the quoting module's assembly code. I could easily search this table in a recordset, and determine if more than one record exists for a particular code, then prompt the user....

Thoughts appreciated!

PS....Mods, why am I still a "Newly Registered User", after 287 posts, and 3 years?

 

[jdraw]

Re
PS....Mods, why am I still a "Newly Registered User", after 287 posts, and 3 years?

I think you can change this to whatever you'd like to see, via Edit profile.

As for your post, I'm not sure of what you are asking in detail. If there is more than 1 part for the usual 1:1, then is there some logic you can use to determine L/R or select the first/last???
My first thought is that the part is not sufficiently defined/described if 2 parts are identified with a "unique identifier".

You know the set up better than readers. If you have some sample data and specific questions, someone may offer a solution.

 

[GBalcom]

Jdraw,
Thanks for your response. My question is this: Is this worth creating a junction table, and the 2 tables (QuoteAssembly, and EngineeringAssembly), when I can get it to work with just one table? (EngineeringAssembly)

Running through it in my feeble mind, I'm having a hard time coming up with any downside to using just one table.

 

[jdraw]

My recommendation is to try what you are thinking --see if it meets your needs.
Sometimes, things seem "obvious", but when you get down to the details, issues arise.
It seems simple enough (since I'm not close to the situation) to set up a few tests that involve your "troublesome part". Can you generate some "dummy requirement" and test your ideas? First a single table.

 

[GBalcom]

OK, thanks for the tip, I'll give it a try

 

[Rx_]

For others who read this later:
RE: the PS Why is my title....
http://www.access-programmers.co.uk/forums/showthread.php?t=280649

Including a tip on finding a good description.